一、数据库的存储结构：页

索引结构给我们提供了高效的索引方式，不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的 存储引擎负责对表中数据的读取和写入工作。不同存储引擎中存放的格式一般是不同的，甚至有的存储引擎比如Memory都不用磁盘来存储数据。由于InnoDB是MySQL的默认存储引擎，所以本章剖析InnoDB存储引擎的数据存储结构。

1.1 磁盘与内存交互的基本单位：页

InnoDB 将数据划分为若干个页，InnoDB中页的大小默认为 16KB。
以页作为磁盘和内存之间交豆的基本单位，也就是一次最少从磁盘中读取16KB的内容到内存中，一次最少把内存中的16KB内容刷新到磁盘中。也就是说，在数据库中，不论读一行，还是读多行，都是将这些行所在的页进行加载。也就是说，数据库管理存储空间的基本单位是页(Page)，数据库 I/0 操作的最小单位是页。 一个页中可以存储多个行记录

记录是按照行来存储的，但是数据库的读取并不以行为单位，否则一次读取（也就是依次I/O操作）只能处理一行数据，效率会非常低。

1.2 页结构概述

页a、页b、页c......页n，这些页可以不在物理结构上相连，只要通过双向链表相关联即可。每个数据页中的记录会按照主键值从小到大的顺序组成一个单向链表，每个数据页都会为存储在它里边的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录。

1.3 页的正常大小

不同的数据库管理系统(简称DBMS)的页大小不同。比如在 MySQL的InnoD8 存储引擎中，默认页的大小是16KB，我们可以通过下面的命令来进行查看:

mysql> show variables like '%innodb_page_size%';

SQL Server 中页的大小为8KB，而在 Oracle 中我们用术语“块”(Block)来代表“页”，0ralce 支持的块大小为2KB，4KB，8KB，16KB，32KB和64KB。

1.4 页的上层结构

另外在数据库中，还存在着区（Extent）、段（Segment）、和表空间（Tablespace）的概念。行、页、区、段、表空间的关系如图所示：

区(Extent)是比页大一级的存储结构，在InnoDB 存储引擎中，一个区会分配 64 个连续的页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB=1MB 。

段(Segment)由一个或多个区组成，区在文件系统是一个连续分配的空间(在 InnoDB 中是连续的 64 个页)当不过在段中不要求区与区之间是相邻的。 段是数据库中的分配单位，不同类型的数据库对象以不同的段形式存在。我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

表空间(Tablespace)是一个逻辑容器，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间、用户表空闻、撤销表空间、临时表空间 等。

二、 页的内部结构

页如果按类型划分的话，常见的有数据页(保存B+ 树节点),系统页、 Undo 页和事务数据页等。数据页是我们最常使用的页。
数据页的 16KB 大小的存储空间被划分为七个部分别是文件头(FileHeader)、页头(Page Header)、最大最小记录(Infimum+supremum)、用户记录(UserRecords)、空闲空间(Free Space)、页目录(Page Directory)和文件尾(File Tailer)。
页结构的示意图如下所示:

这7个部分作用分别如下，我们简单梳理如下表所示:

我们可以把这 7个结构分成3个部分。
具体见思维导图：

三、InnoDB行格式（或记录格式）

我们平时的数据以行为单位来向表中插入数据，这些记录在磁盘上存放的方式也被称为行格式或记录格式。
InnoDB存储引擎设计了4种不同类型的行格式，分别是Compact、Redundant、Dynamic和Compressed行格式。
查看MySQL8的默认行格式:

SELECT @@innodb default row format;

查看具体哪个表使用的行格式：

SHOW TABLE STATUS LIKE '表名'\G;

3.1 指定行格式的语法

在创建或者修改表的语句中指明行格式

CREATE TABLE 表名 (列的信息) ROW_FORMAT=行格式名称
ALTER TABLE 表名 ROW_FORMAT=行格式名称

例如：

CREATE TABLE record_test_table(
col1 VARCHAR(8),
col2 VARCHAR(8) NOT NULL,
col3 VCHAR(8),
col4 VARCHAR(8)
) CHARSET = ascii ROW_FORMAT=COMPACT

3.2 COMPACT行格式

在MvSOL 5.1版本中，默认设置为Compact行格式。一条完整的记录其实可以被分为记录的额外信息和记录的真实数据两大部分。

3.2.1 变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位从而形成一个变长字段长度列表。

注意:这里面存储的变长长度和字段顺序是反过来的。比如两个varchar字段在表结构的顺序是a(10)，b(15)。那么在变长字段长度列表中存储的长度顺序就是15，10，是反过来的。

以record test table表中的第一条记录举例:因为record test table表的col1、co12、co14列都是VARCHAR(8)类型的，所以这三个列的值的长度都需要保存在记录开头处，注意record test table表中的各个列都使用的是ascii字符集(每个字符只需要1个字节来进行编码)。

又因为这些长度值需要按照列的逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是(各个字节之间实际上没有空格，用空格隔开只是方便理解):

3.2.2 NULL值列表

Compact行格式会把可以为NULL的列统一管理起来，存在一个标记为NULL值列表中。
如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。
为什么定义NULL值列表?
之所以要存储NULL是因为数据都是需要对齐的，如果没有标注出来NULL值的位置，就有可能在查询数据的时候出现混乱。如果使用一个特定的符号放到相应的数据位表示空置的话，虽然能达到效果，但是这样很浪费空间，所以直接就在行数据得头部开辟出一块空间专门用来记录该行数据哪些是非空数据，哪些是空数据，格式如下:

1. 二进制位的值为1时，代表该列的值为NULL。
2. 二进制位的值为0时，代表该列的值不为NULL。

例如:字段 a、b、c，其中a是主键，在某一行中存储的数依次是 a=1、b=nul、c=2.那么Compact行格式中的NULL值列表中存储:01。第一个0表示c不为null，第二个1表示b是nul。这里之所以没有a是因为数据库会自动跳过主键，因为主键肯定是非NULL且唯一的，在NULL值列表的数据中就会自动跳过主键。

record test table的两条记录的NULL值列表就如下:

3.2.3 记录的真实数据

记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列：

实际上这几个列的真正名称其实是:DB_ROW_ID、DB_TRX_ ID、DB_ROLL_PTR。

• 一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。
• 事务ID和回滚指针在后面的《第14章 MySQL事务日志》章节中讲解。

四、区、段与碎片区

4.1 为什么要有区？

B+ 树的每一层中的页都会形成一个双向链表，如果是以页为单位来分配存储空间的话，双向链表相邻的两个页之间的物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围査询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远就是所谓的随机I/0。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/0是非常慢的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的顺序I/0。
引入区的概念，一个区就是在物理位置上连续的64个页。因为InnoDB 中的页大小默认是16KB，所以一个区的大小是 64*16KB=1MB 。在表中数据量大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成 一点点空间的浪费(数据不足以填充满整个区)，但是从性能角度看，可以消除很多的随机I0，功大于过!

4.2 为什么要有段？

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB 对 B+ 树的叶子节点和非叶子节点进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个段(segment)，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个一个叶子节点段,一个非叶子节点段。
除了索引的叶子节点段和非叶子节点段之外，InnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有数据段、索引段、回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。
在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不能也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。
段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

4.3 为什么要有碎片区？

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M(64*16Kb=1024Kb)存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么?以后每次添加一个索引都要多申请2M的存储空间么?这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常纯粹的，也就是一个区被整个分配给某一段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。
为了考虑以完整的区为单位分配给某个段对于 数据量较小 的表太浪费存储空间的这种情况，InnoDB提出了一个碎片(fragment)区的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间 ，并不属于任何一个段。
所以此后为某个段分配存储空间的策略是这样的:

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的。
• 当某个段已经占用了 32个碎片区页面之后，就会申请以完整的区为单位来分配存储空间。
  所以现在段不能仅定义为是某些区的集合，更精确的应该是某些零散的页面以及一些完整的区的集合。

4.4 区的分类

区大体上可以分为4种类型：

• 空闲的区(FREE):现在还没有用到这个区中的任何页面。
• 中还有可用的页面。(FREE_FRAG):表示碎片区还有可用的页面。
• 没有剩余空间的碎片区(FULL_FRAG):表示碎区中的所有页面都被使用，没有空闲页面。
• 附属于某个段的区(FSEG):每一个索引都可以分为叶子节点段和非叶子节点段。
  处于FREE、FREE_FRAG 以及 FULL_FRAG 这三种态的区都是独立的，直属于表空间。而处于FSEG状态的区是附属于某个段的。

如果把表空间比作是一个集团军，段就相当于师，区就相当于团。一般的团都是隶属于某个师的，就像是处于FSEG的区全都隶属于某个段，而处于FREE、 FREE_FRAG 以及 FULL_FRAG这三种状态的区却直接隶属于表空间，就像独立团直接听命于军部一样。

五、表空间

表空间可以看做是InnoDB存储引擎逻辑结构的最高层，所有的数据都存放在表空间中。
表空间是一个逻辑容器 ，表空间存储的对象是段，在一个表空间中可以有一个或多个段，但是一个段只能属于一个表空间。表空间数据库由一个或多个表空间组成，表空间从管理上可以划分为系统表空间(Systemtablespace)、独立表空间(File-per-table tablespace)、撤销表空间(Undo Tablespace)和 临时表空间(Temporary Tablespace)等

5.1 独立表空间

独立表空间，即每张表有一个独立的表空间，也就是数据和索引信息都会保存在自己的表空间中。独立的表空间(即:单表)可以在不同的数据库之间进行迁移。
空间可以回收(DROP TABLE 操作可自动回收表空间;其他情况，表空间不能自己回收)。如果对于统计分析或是日志表，删除大量数据后可以通过:alter table TableName engine=innodb;回收不用的空间。对于使用独立表空间的表，不管怎么删除，表空间的碎片不会太严重的影响性能，而且还有机会处理。
独立表空间结构
独立表空间由段、区、页组成。前面已经讲解过了。
真实表空间对应的大小
我们到数据目录里看，会发现一个新建的表对应的.ibd文件只占用了96K，才6个页面大小(MySQL5.7中)，这是因为一开始表空间占用的空间很小，因为表里边都没有数据。不过别忘了这些.ibd文件是自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大。
查看InnoDB的表空间类型

mysql> shwo variable like `innodb_file_per_table`

你能看到 innodb_file_per_table=ON，这就意味着每张表都会单独保存为一个.ibd 文件。

5.2 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。
InnoDB数据字典
每当我们向一个表中插入一条记录的时候，MySQL校验过程如下:
先要校验一下插入语句对应的表存不存在，插入的列和表中的列是否符合，如果语法没有问题的话，还需要知道该表的聚簇索引和所有二级索引对应的根页面是哪个表空间的哪个页面，然后把记录插入对应索引的B+树中。所以说，MySQL除了保存着我们插入的用户数据之外，还需要保存许多额外的信息，比方说:

• 某个表属于哪个表空间，表里边有多少列
• 表对应的每一个列的类型是什么
• 该表有多少索引，每个索引对应哪几个字段，该索引对应的根页面在哪个表空间的哪个页面
• 该表有哪些外键，外键对应哪个表的哪些列
• 某个表空间对应文件系统上文件路径是什么
• ......

上述这些数据并不是我们使用Insert语句插入的用户数据，实际上是为了更好的管理我们的这些用户数据而不得已引入的一些额外数据，这些数据也称为元数据。InnoDB存储引擎特意定义了一些列的内部系统表（Internal system table）来记录这些元数据。

这些系统表也被称为数据字典 ，它们都是以 B+树的形式保存在系统表空间的某些页面中，其中 SYS_TABLES、SYS_COLUMNS、SYS_INDEXES、 SYS_FIELDS这四个表尤其重要，称之为基本系统表(basic system tables)我们先看看这4个表的结构:
SYS_TABLES表结构

SYS_COLUMNS表结构

SYS_INDEXES表结构

SYS_FIELDS表结构

注意:用户是不能直接访问 InnoDB的这些内部系统表，除非你直接去解析系统表空间对应文件系统上的文件。不过考虑到査看这些表的内容可能有助于大家分析问题，所以在系统数据库 information_schema 中提供了一些以innodb_sys 开头的表:

在information_schema数据库中的这些以 INNODB_SYS开头的表并不是真正的内部系统表(内部系统表就是我们上边以 SYS 开头的那些表)，而是在存储引擎启动时读取这些以 SYS 开头的系统表，然后填充到这些以INNODB SYS 开头的表中。以 INNODB SYS 开头的表和以 SYS 开头的表中的字段并不完全一样，但供大家参考已经足矣。

附录：数据页加载的三种方式

InnoDB从磁盘中读取数据的 最小单位 是数据页。而你想得到的id =xxx的数据，就是这个数据页众多行中的一行,对于MySQL存放的数据，逻辑概念上我们称之为表，在磁盘等物理层面而言是按数据页形式进行存放的，当其加载到MySQL中我们称之为缓存页。
如果缓冲池中没有该页数据，那么缓冲池有以下三种读取数据的方式，每种方式的读取效率都是不同的!

1. 内存读取
   如果该数据存在于内存中，基本上执行时间在 1ms左右，效率还是很高的。
   
2. 随机读取
   如果数据没有在内存中，就需要在磁盘上对该页进行査找，整体时间预估在 10ms 左右，这 10ms 中有 6ms 是磁盘的实际繁忙时间(包括了 寻道和半圈旋转时间)，有 3ms 是对可能发生的排队时间的估计值，另外还有 1ms 的传输时间，将页从磁盘服务器缓冲区传输到数据库缓冲区中。这 10ms 看起来很快，但实际上对于数据库来说消耗的时间已经非常长了，因为这还只是一个页的读取时间。
   
3. 顺序读取
   顺序读取其实是一种批量读取的方式，因为我们请求的数据在磁盘上往往都是相邻存储的，顺序读取可以帮我们批量读取页面，这样的话，一次性加载到缓冲池中就不需要再对其他页面单独进行磁盘 I0 操作了。如果一个磁盘的吞吐量是 40MB/S，那么对于一个 16KB 大小的页来说，一次可以顺序读取 2560(40MB/16KB)个页，相当于一个页的读取时间为 0.4ms。采用批量读取的方式，即使是从磁盘上进行读取，效率也比从内存中只单独读取一个页的效率要高。