【MySQL索引与优化篇】InnoDB数据存储结构

1. 数据库的存储结构:页

索引结构给我们提供了高效的索引方式，不过索引信息以及数据记录都是保存在文件上的，确切说是存储在页结构中。另一方面，索引是在存储引擎中实现的，MySQL服务器上的 存储引擎 负责对表中数据的读取和写入工作。不同存储引擎中 存放的格式 一般是不同的。

由于 InnoDB 是MySOL的 默认存储引擎，所以本章剖析InnoDB存储引擎的数据存储结构。

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

InnoDB 将数据划分为若干个页，InnoDB中页的大小默认为 16KB（可通过show variables命令看innodb_page_size参数）。页 是磁盘与内存交互的 基本单位。

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

1.2 页结构概述

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

1.3 页的上层结构

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

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

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

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

2. 页的内部结构

页如果按类型划分的话，常见的有 数据页(保存 B+ 节点)、系统页、Undo 页和 事务数据页 等。数据页是我们最常使用的页。

数据页的 16KB 大小的存储空间被划分为七个部分，分别是文件头 (File Header) 、页头(Page Header) 、最大最小记录 (Infimum+supremum) 、用户记录 (User Records)、空闲空间 (Free Space) 、页目录 (Page Directory) 和文件尾 (File Tailer) 。

这7个部分的作用说明分别如下：

B+ 树是如何进行记录检索的?

通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽(sot)采用 二分查找 的方式先找到一个粗略的记录分组（一个分组有8条以下的记录，页目录结构查找图如下），然后再在分组中通过 链表遍历 的方式查找记录。

3. InnoDB行格式(或记录格式)

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

查看MySQL8.0默认的行格式或具体表使用的行格式：

SELECT @@innodb_default_row_format; -- 默认dynamic

SHOW TABLE STATUS LIKE '表名'; -- 查看具体表使用的行格式Row_format列

3.1 Compact行格式

1. 变长字段长度列表：存储所有变长字段占用字节数，存储顺序是反的

2. NULL值列表：1代表NULL，0代表非NULL，如果表中没有允许存储NULL的值，则NULL值列表也不存在了，故可考虑尽可能设置表字段为非NULL，注意：存储的顺序也是反的，下面有示意图

3. 记录头信息(5字节)

   • 预留位1和2：各占1bit，没有使用

   • delete_mask：占1bit，删除标识

   • min_rec_mask：占1bit，B+树的每层非叶子节点中的最小记录都会添加该标记

   • n_owned：占4bit，表示当前记录槽拥有的记录数，只有组中最大的会记录

   • heap_no：占13bit，表示当前记录在记录堆的位置信息

   • record_type：占3bit，表示当前记录的类型，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录

   • next_record：占16bit，表示下一条记录的相对位置

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

   • DB_ROW_ID：6字节，行ID，唯一标识一条记录，一个表即没有主键又没有唯一索引则会默认添加该项隐藏列作为主键
   • DB_TRX_ID：事务ID
   • DB_ROLL_PTR：回滚指针

NULL值列表示意图：

记录头信息示意图：

3.2 Dynamic和Compressed行格式

Dynamic和Compressed相比Compact行格式只是对于行溢出的处理方式不同。

行溢出：一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节（2个字节存储变长的长度，1个存储NULL标识），这样就可能出现一个页存放不了一条记录，这种现象称为行溢出

• Compressed和Dynamic两种记录格式对于存放在BLOB中的数据采用了完全的行溢出的方式。如图，在数据页中只存放20个字节的指针(溢出页的地址)，实际的数据都存放在Off Page(溢出页) 中
  • Compressed行记录格式的另一个功能就是，存储在其中的行数据会以zlib的算法进行压缩，因此对于BLOB、TEXT、VARCHAR这类大长度类型的数据能够进行非常有效的存储
• Compact和Redundant两种格式会在记录的真实数据处存储一部分数据(存放768个前缀字节)，把剩余的数据分散存储在几个其他的页中进行分页存储然后记录的真实数据处用20个字节存储指向这些页的地址(当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数)，从而可以找到剩余数据所在的页

Compact和Redundant页的扩展：

Compressed和Dynamic：

3.3 Redundant行格式

支持该格式是为了兼容MySQL 5.0之前版本的页格式，实际开发中不使用，故不作过多分析

4. 区、段与碎片区

4.1 为什么要有区？

B+树的每一层中的页都会形成一个双向链表，如果是以 页为单位 来分配存储空间的话，双向链表相邻的两个页之间的 物理位置可能离得非常远。我们介绍B+树索引的适用场景的时候特别提到范围查询只需要定位到最左边的记录和最右边的记录，然后沿着双向链表一直扫描就可以了，而如果链表中相邻的两个页物理位置离得非常远就是所谓的 随机I/O。再一次强调，磁盘的速度和内存的速度差了好几个数量级，随机I/O是非常慢 的，所以我们应该尽量让链表中相邻的页的物理位置也相邻，这样进行范围查询的时候才可以使用所谓的 顺序I/O。

引入区的概念，一个区就是在物理位置上连续的 64个页。因为 InnoDB 中的页大小默认是 16KB，所以一个区的大小是 64*16KB= 1MB 。在表中 数据量大 的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照 区为单位分配，甚至在表中的数据特别多的时候，可以一次性分配多个连续的区。虽然可能造成 一点点空间的浪费 (数据不足以填充满整个区)，但是从性能角度看，可以消除很多的随机I/O，功大于过!

4.2 为什么要有段？

对于范围查询，其实是对B+树叶子节点中的记录进行顺序扫描，而如果不区分叶子节点和非叶子节点，统统把节点代表的页面放到申请到的区中的话，进行范围扫描的效果就大打折扣了。所以InnoDB 对B+树的 叶子节点 和 非叶子节点 进行了区别对待，也就是说叶子节点有自己独有的区，非叶子节点也有自己独有的区。存放叶子节点的区的集合就算是一个 段( segment )，存放非叶子节点的区的集合也算是一个段。也就是说一个索引会生成2个段，一个 叶子节点段，一个 非叶子节点段。

除了索引的叶子节点段和非叶子节点段之外，lnnoDB中还有为存储一些特殊的数据而定义的段，比如回滚段。所以，常见的段有 数据段、索引段、 回滚段。数据段即为B+树的叶子节点，索引段即为B+树的非叶子节点。

在InnoDB存储引擎中，对段的管理都是由引擎自身所完成，DBA不也没有必要对其进行控制。这从一定程度上简化了DBA对于段的管理。

段其实不对应表空间中某一个连续的物理区域，而是一个逻辑上的概念，由若干个零散的页面以及一些完整的区组成。

4.3 为什么有碎片区

默认情况下，一个使用InnoDB存储引擎的表只有一个聚簇索引，一个索引会生成2个段，而段是以区为单位申请存储空间的，一个区默认占用1M (64*16Kb = 1024Kb)存储空间，所以默认情况下一个只存了几条记录的小表也需要2M的存储空间么?以后每次添加一个索引都要多申请2M的存储空间么? 这对于存储记录比较少的表简直是天大的浪费。这个问题的症结在于到现在为止我们介绍的区都是非常 纯粹 的，也就是一个区被整个分配给某一个段，或者说区中的所有页面都是为了存储同一个段的数据而存在的，即使段的数据填不满区中所有的页面，那余下的页面也不能挪作他用。

为了考虑以完整的区为单位分配给某个段对于 数据量较小 的表太浪费存储空间的这种情况，lnnoDB提出了一个 碎片(fragment)区 的概念。在一个碎片区中，并不是所有的页都是为了存储同一个段的数据而存在的，而是碎片区中的页可以用于不同的目的，比如有些页用于段A，有些页用于段B，有些页甚至哪个段都不属于。碎片区直属于表空间，并不属于任何一个段。

所以此后为某个段分配存储空间的策略是这样的：

• 在刚开始向表中插入数据的时候，段是从某个碎片区以单个页面为单位来分配存储空间的
• 当某个段已经占用了 32个碎片区 页面之后，就会申请以完整的区为单位来分配存储空间

所以现在段不能仅定义为是某些区的集合，更精确的应该是 某些零散的页面 以及 一些完整的区 的集合

4.4 区的分类

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

处于 FREE、FREE_FRAG 以及 FULL_FRAG 这三种状态的区都是独立的，直属于表空间。而处于 FSEG 状态的区是附属于某个段的

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

5. 表空间

表空间可以看做是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文件是 自扩展的，随着表中数据的增多，表空间对应的文件也逐渐增大

5.2 系统表空间

系统表空间的结构和独立表空间基本类似，只不过由于整个MySQL进程只有一个系统表空间，在系统表空间中会额外记录一些有关整个系统信息的页面，这部分是独立表空间中没有的。

InnoDB数据字典，SYS_TABLES等SYS开头的部分表，但用户并不能直接访问这些表，而是存储引擎在启动时会把SYS_等表的数据填充到information_schema数据库中以INNODB_SYS开头的表。