Mysql存储引擎篇：InnoDB数据文件存储（表空间、数据页、行记录）

type

status

date

slug

summary

1、Mysql的文件存储

Mysql 总体的物理文件可以分为下面三类

数据文件：数据文件用来存储数据库中的表文件数据，通常会在 /var/lib/mysql/ 目录下面建立一个以数据库为名的目录，另外还有四个系统数据库 mysql、 information_schema、 performance_schema 和 sys。不同的数据库引擎，目录下表对应的文件扩展名也不一样。可以使用 SHOW VARIABLES LIKE '%datadir%'; 命令查看数据目录所在位置。

日志文件：日志文件中记录着 MySQL 数据库运行期间发生的变化，可以通过 SHOW VARIABLES LIKE '%\_log_%'; 查询当前 MySQL 日志使用情况。日志分类有以下类型：

错误日志（Error Log）：格式为 *.err，主要记录 MySQL 服务器启动和停止过程中的信息、服务器在运行过程中发生的故障和异常情况等。错误日志中记录的并非全是错误的信息，例如MySQL如何启动InnoDB的表空间文件，如何初始化自己的存储引擎等等，这些也记录在错误日志文件中。
事务日志（redo Log & undo Log）：事务日志是 InnoDB 独有的日志，使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到硬盘上持久的事务日志中，事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。事务日志又分为重做日志(redo Log)和回滚日志(undo Log)，当事务对数据库进行修改，InnoDB会同时生成redo log 和 undo log日志。

redo Log：记录事务操作引起数据的变化，目的是用于重做事务。如果事务执行完成后内存数据还没有刷回磁盘数据库就宕机了，redo log 可以保证这些内存数据不会被丢失。格式为 ib_logfile0 等。redo log 用于保证事务特性的持久性。
undo log：记录事务操作前数据的信息，目的是用于恢复事务前的状态，如果事务执行失败或者调用了rollback，导致事务需要回滚，undo log 可以保证数据恢复到事务前的状态。undo 文件默认记录在表空间文件里面例如 .ibdata。undo log 用于保证事务特性的原子性和实现MVCC。

修改后的数据最终还是慢慢刷回硬盘，为什么中间要增加写事务日志这一步呢？
因为如果修改后的内存中的数据如果在还没有写回磁盘前数据库就宕机了，会造成数据丢失；但如果每次写内存的同时立刻刷回磁盘性能又太低，因为数据刷回磁盘文件要找到正确落盘位置的过程是随机 IO，而写事务采用文件追加的方式，性能比随机 IO会高很多。采用事务日志的方式在提高安全性的同时性能也不会太差）。

二进制日志（Binary Log）：用于记录数据库的变化情况，即 SQL 语句的 DDL 和 DML 语句，不包含数据记录查询操作，用于数据库恢复和主从同步。格式为 mysql-bin.000001 等。
查询日志（Query Log）：用来记录建立的客户端连接和执行的语句。
慢查询日志（Show Query Log）：用来记录在MySQL中响应时间超过阀值的语句。 long_query_time 的默认值为 10（即10秒，通常设置为1秒），开启慢查询日志会对性能造成一定影响，默认情况下，MySQL数据库是不开启慢查询日志的。格式为 *show.log。
中继日志（Relay log）：用于主从复制的从服务器上。从服务器 I/O 线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后从服务器SQL线程会读取 relay-log 日志的内容并应用到从服务器，从而使从服务器和主服务器的数据保持一致。

配置文件：用于存放 MySQL 所有的配置信息的文件。

系统配置文件：命名为 my.ini 或 my.cnf，可以使用 mysql --help|grep my.cnf 查看MySQL配置文件的位置。
socket 文件：命名为 mysql.sock，MySQL服务器启动后 socket 文件自动生成，该文件主要用来连接客户端。

2、InnoDB的表空间文件

MySQL 存储的行为是由存储引擎实现的，MySQL 支持多种存储引擎，不同的存储引擎保存的文件自然也不同。InnoDB 是我们常用的存储引擎，也是 MySQL 默认的存储引擎。所以，本文主要以 InnoDB 存储引擎展开讨论。

先来看看 MySQL 数据库的文件存放在哪个目录？

我们每创建一个 database（数据库）都会在 /var/lib/mysql/ 目录里面创建一个以 database 为名的目录，然后保存表结构和表数据的文件都会存放在这个目录里。

比如，我这里有一个名为 my_test 的 database，该 database 里有一张名为 t_order 数据库表。

然后，我们进入 /var/lib/mysql/my_test 目录，看看里面有什么文件

可以看到共有三个文件：

db.opt：用来存储当前数据库的默认字符集和字符校验规则。

t_order.frm：保存 t_order 的表结构。在 MySQL 8.0 之前，每个表都有一个对应的 .frm 文件存储表结构定义。MySQL 8.0 开始，表结构信息存储在 mysql.ibd 文件，不再使用 .frm 文件。

t_order.ibd：保存 t_order 的表数据和索引。InnoDB 的所有数据都存储在表空间中，有两种表空间格式（由参数 innodb_file_per_table 控制）：

系统表空间：文件名为 ibdata1。某一个数据库的所有表的数据和索引全部放在一个文件（ibdata1 ）或者多个文件（ibdata1，ibdata2 ），可自行配置。
独立表空间：文件名为 表名.ibd。每一个表都会生成一个 .ibd文件，用来存储该表的数据和索引。需要设置参数 innodb_file_per_table 为 1，从 MySQL 5.6.6 版本开始该参数的默认值是 1，即 MySQL 5.6.6+ 默认每个表有自己独立的 .ibd 文件。

InnoDB 的表空间文件由段（segment）、区（extent）、页（page）、行（row）组成。他们的关系是：

逻辑存储结构大致如下图：

2.4 段（segment）

表空间是由各个段（segment）组成的，段是一个逻辑上的概念，并不对应表空间中某一个连续的物理区域，它由若干个完整的区组成（还会包含一些碎片页）。段的类型有：

索引段：存放 B + 树的非叶子节点的区的集合。

数据段：存放 B + 树的叶子节点的区的集合。

回滚段：存放 undo log 的区的集合，MVCC 利用了回滚段实现了多版本查询数据。

存放叶子节点的区的集合就是数据段，存放非叶子节点的区的集合就是索引段。也就是说一个索引会生成 2 个段，一个叶子节点段（数据段），一个非叶子节点段（索引段）。

2.2 区（extent）

区（extent）由连续的页组成，作用就是把多个页连成连续的物理空间。一个区的大小为 1MB，对于 16KB 的页来说，连续的 64 个页会被划为一个区。

对于小表可以按页为单位来分配空间。当表中数据量很大的时候，为某个索引分配空间的时候就不再按照页为单位分配了，而是按照区为单位分配。这样就使得链表中相邻的页的物理位置也相邻，就能使用顺序 I/O 了，那么在范围查询（扫描叶子节点）的时候性能就会很高。

2.3 页（page）

页（page）是 InnoDB 磁盘管理的最小单位，默认 16 KB (可通过 innodb_page_size 调整)。意味着数据库每次读写都是以 16KB 为单位的，一次最少从磁盘中读取 16K 的内容到内存中，一次最少把内存中的 16K 内容刷新到磁盘中。

一个页（数据页）包含多条行记录，当需要读一条记录的时候，并不是将这个行记录从磁盘读出来，而是以页为单位，将其整体读入内存。

InnoDB 有多种页类型，主要包括：

数据页（INDEX）：最常见的类型，存储表数据和 B+树索引

Undo 日志页（UNDO_LOG）：存储 Undo 日志

系统页（SYS）：存储系统信息

事务系统页（TRX_SYS）：存储事务系统信息

表空间头部页（FSP_HDR）：存储表空间和区的管理信息

扩展描述页（XDES）：存储区的描述信息

插入缓冲位图页（IBUF_BITMAP）：存储插入缓冲的位图信息

BLOB 页（BLOB）：存储 BLOB 类型的大数据

2.4 行（row）

行（row）是 InnoDB 中数据存储的最小单位，数据库表中的记录都是按行进行存放的。行记录支持多种行格式（row_format），可通过 ROW_FORMAT 指定，不同的行格式对应不同的存储结构。

Compact (MySQL 5.0 默认)

Redundant

Dynamic (MySQL 8.0 默认)

Compressed

3、InnoDB的数据页

数据页包括七个部分，结构如下图：

这 7 个部分的作用如下图：

页结构整体上可以分为三大部分

通用部分(文件头、文件尾)

存储记录空间

索引部分

3.1 通用部分(文件头、文件尾)

第一部分通用部分，主要指文件头和文件尾。将页的内容进行封装，通过文件头和文件尾校验的 CheckSum 方式来确保页的传输是完整的。在 File Header 中有两个指针，分别指向上一个数据页和下一个数据页，连接起来的页就形成了一个双向链表。让数据页之间不需要是物理上的连续，而是逻辑上的连续。

3.2 存储记录空间

第二个部分是记录部分。页的主要作用是存储记录。

页面头、最小记录和最大记录这三部分大小是固定的，用于描述整个页面的基本信息。

用户记录，占了页结构的主要空间。对于不同功能的页，用户记录存储的内容不同。例如数据页的每行用户记录存储实际的数据，而索引页的每行用户记录存储子页的最小主键和页地址。

空闲空间，是个灵活的部分，当有新的记录插入时，会从空闲空间中进行分配用于存储新记录。

3.3 索引部分

第三部分是索引部分，指的是页目录（Page Directory）。页目录提供类似"目录"的功能，实现页内记录的二分查找。不管是什么类型的数据页（索引页、数据页）都会包含页目录。

因为 B+ 树索引本身并不能找到具体的一条记录，只能找到该记录所在的页，然后需要把页读入内存里面，遍历页内数据才能定位到具体的数据行。如果页内的数据量比较大的话，这种方式的性能较差。页目录就是用来解决这个问题的，利用页目录可以在页内快速定位到目标数据。页目录的设计思路类似于给页面数据创建一个目录，利用二分查找的方式快速查找。

页目录包含的内容：

页内所有记录被逻辑上分成若干组（通常 4-8 条记录一组），包括最小记录和最大记录。

每个记录组的最后一条记录就是组内最大的那条记录，并且最后一条记录的头信息中会存储该组的记录数（下图中粉红色字段）。

页目录由多个槽（Slot）组成，每个槽指向了不同组的最后一个记录。

从图可以看到，页目录就是由多个槽组成的，槽相当于分组记录的索引。我们通过槽查找记录时，可以使用二分法快速定位要查询的记录在哪个槽，再在槽对应的记录组内进行线性查找，找到对应的记录。这样我们就无需从最小记录开始遍历整个页中的记录链表。

以上面那张图举个例子，5 个槽的编号分别为 0，1，2，3，4，我想查找主键为 11 的用户记录：

先二分得出槽中间位是 (0+4)/2=2 ，2号槽里最大的记录为 8。因为 11 > 8，所以需要从 2 号槽后继续搜索记录；

再使用二分搜索出 2 号和 4 槽的中间位是 (2+4)/2= 3，3 号槽里最大的记录为 12。因为 11 < 12，所以主键为 11 的记录在 3 号槽里；

这里有个问题，「槽对应的值都是这个组的主键最大的记录，如何找到组里最小的记录」？比如槽 3 对应最大主键是 12 的记录，那如何找到最小记录 9。解决办法是：通过槽 3 找到槽 2 对应的记录，也就是主键为 8 的记录。主键为 8 的记录的下一条记录就是槽 3 当中主键最小的 9 记录，然后开始向下搜索 2 次，定位到主键为 11 的记录，取出该条记录的信息即为我们想要查找的内容。

注意：

页目录初始时只有两个槽，分别指向最小和最大记录。随着记录插入，槽数量会动态增加。

InnoDB 对每个记录分组中的记录数规定：

第一个分组中的记录只能有 1 条记录。
最后一个分组中的记录条数范围只能在 1-8 条之间。
剩下的分组中记录条数范围只能在 4-8 条之间。

4、InnoDB的行格式

行格式（row_format）就是一条记录的存储结构，决定了数据如何物理存储在磁盘上。InnoDB 提供了四种行格式：

Redundant：MySQL5.0 版本之前 InnoDB 的行记录存储方式，兼容性最好但存储效率较低，现在基本也不再使用这种格式

Compact：MySQL 5.0 引入，紧凑型行格式，比 Redundant 减少了大约 20% 的行存储空间，并把将 NULL 值存储在行头而不是列数据中。

Dynamic：MySQL 5.7 后的默认格式，基于 Compact 改进，优化了对 BLOB、TEXT 等大字段的行溢出处理。

Compressed：基于 Dynamic 改进，增加了表和索引数据的压缩，可以减少存储空间但会增加 CPU 开销

行格式相关命令

以 Compact 行格式为例，因为 Dynamic 和 Compressed 这两个行格式和 Compact 格式非常类似，都是基于 Compact 格式的改进。

COMPACT 格式下一条完整的记录包含以下部分：

记录的额外信息：包含 3 个部分

变长字段长度列表
NULL 值列表
记录头信息

记录的真实数据

4.1 记录的额外信息

4.1.1 变长字段长度列表

变长字段长度列表（Variable-Length Field Length List）是数据库中用于存储变长字段（如VARCHAR、VARBINARY、TEXT、BLOB 等）的一种数据结构，它记录了每个变长字段的实际长度。通常使用 1-2 个字节表示每个变长字段的长度，通过这个列表可以快速定位每个变长字段在记录中的位置

为了展示「变长字段长度列表」具体是怎么保存「变长字段的真实数据占用的字节数」，我们先创建这样一张表，字符集是 ascii（所以每一个字符占用的 1 字节），行格式是 Compact，t_user 表中 name 和 phone 字段都是变长字段：

现在 t_user 表里有这三条记录：

接下来，我们看看看看这三条记录的行格式中的「变长字段长度列表」是怎样存储的。

先来看第一条记录：

name 列的值为 a，真实数据占用的字节数是 1 字节，十六进制 0x01；

phone 列的值为 123，真实数据占用的字节数是 3 字节，十六进制 0x03；

age 列和 id 列不是变长字段，所以这里不用管。

这些变长字段的真实数据占用的字节数会按照列的顺序逆序存放（等下会说为什么要这么设计），所以「变长字段长度列表」里的内容是「 03 01」，而不是「01 03」。

同样的道理，我们也可以得出第二条记录的行格式中，「变长字段长度列表」里的内容是「 04 02」，如下图：

第三条记录中 phone 列的值是 NULL，NULL 是不会存放在行格式中记录的真实数据部分里的，所以「变长字段长度列表」里不需要保存值为 NULL 的变长字段的长度。

为什么「变长字段长度列表」的信息要按照逆序存放？

主要是因为「记录头信息」中指向下一个记录的指针，指向的是下一条记录的「记录头信息」和「真实数据」之间的位置，这样的好处是向左读就是记录头信息，向右读就是真实数据，这样可以使得位置靠前的记录的真实数据和数据对应的字段长度信息可以同时在一个 CPU Cache Line 中，这样就可以提高 CPU Cache 的命中率。

同样的道理， NULL 值列表的信息也需要逆序存放。

每个数据库表的行格式都有「变长字段字节数列表」吗？

其实变长字段字节数列表不是必须的。当数据表没有变长字段的时候，比如全部都是 int 类型的字段，这时候表里的行格式就不会有「变长字段长度列表」了，因为没必要，不如去掉以节省空间。

所以「变长字段长度列表」只出现在数据表有变长字段的时候。

4.1.2 NULL 值列表

表中的某些列可能会存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中会比较浪费空间，所以 Compact 行格式把这些值为 NULL 的列存储到 NULL值列表中。InnoDB 使用一个位图（bitmap）来标识哪些列的值是 NULL，每个列对应一个二进制位（bit），二进制位按照列的顺序逆序排列。

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

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

另外，NULL 值列表占用的字节数 = ceil(允许为 NULL 的列数 / 8)。例如，表中有 9 个允许为 NULL 的列，则需要 2 个字节（16 位）的 NULL 值列表。

还是以 t_user 表的这三条记录作为例子，我们看看这三条记录的行格式中的 NULL 值列表是怎样存储的。

先来看第一条记录，第一条记录所有列都有值，不存在 NULL 值，所以用二进制来表示是酱紫的：

但是 InnoDB 是用整数字节的二进制位来表示 NULL 值列表的，现在不足 8 位，所以要在高位补 0，最终用二进制来表示是酱紫的：

所以，对于第一条数据，NULL 值列表用十六进制表示是 0x00。

接下来看第二条记录，第二条记录 age 列是 NULL 值，所以，对于第二条数据，NULL值列表用十六进制表示是 0x04。

最后第三条记录，第三条记录 phone 列和 age 列是 NULL 值，所以，对于第三条数据，NULL 值列表用十六进制表示是 0x06。

我们把三条记录的 NULL 值列表都填充完毕后，它们的行格式是这样的：

每个数据库表的行格式都有「NULL 值列表」吗？

NULL 值列表也不是必须的。

当数据表的字段都定义成 NOT NULL 的时候，这时候表里的行格式就不会有 NULL 值列表了。

所以在设计数据库表的时候，通常都是建议将字段设置为 NOT NULL，这样可以至少节省 1 字节的空间（NULL 值列表至少占用 1 字节空间）。

「NULL 值列表」是固定 1 字节空间吗？如果这样的话，一条记录有 9 个字段值都是 NULL，这时候怎么表示？

「NULL 值列表」的空间不是固定 1 字节的。

当一条记录有 9 个字段值都是 NULL，那么就会创建 2 字节空间的「NULL 值列表」，以此类推。

4.1.3 记录头信息

以 COMPACT 为例，记录头信息如下图：

名称	大小(Bit)	说明
预留位1	1	没有使用
预留位2	1	没有使用
deleted_flag	1	记录删除标记
min_rec_flag	1	B+树非叶子节点的最小目录项标记
n_owned	4	同一页内同一组里最大的记录会记录组里的记录数量，其余记录该值为0
heap_no	13	当前记录在页面堆里的相对位置
record_type	3	记录类型： 000 - 普通记录 001 - B+树非叶子节点记录 010 - 最小记录(Infimum) 011 - 最大记录(Supremum) 1xx - 保留未用
next_record	16	下一条记录的相对位置

记录头信息的最后 2 字节用来连接下一条记录，将页内所有记录串联成一个单向链表。

记录头信息里的 record_type 表示页记录类型。其中 Infimum 和 Supremum 是索引页内的两条虚拟记录，InnoDB 规定所有索引页都会有这两条记录，而且所有的用户记录都比 Infimum 大，都比Supremum 小。记录头信息里的 heap_no 代表记录在堆里的相对位置，该值越小代表记录越靠前。Infimum 和 Supremum 的 heap_no 值分别是 0 和 1，它俩在所有用户记录的最前面。用户记录 heap_no 值是从 2 开始的。

Infimum 和 Supremum 结构非常的简单，和用户记录一样也有头信息，真实数据部分是固定的字符串，如下图所示：

我们把这两条虚拟记录也加入到记录里面，完整的结构就是下面这样的。其中 Supremum 记录的 next_record 属性为0，代表它已经没有下一条记录了。

4.2 记录的真实数据

记录真实数据部分除了我们定义的字段，还有三个隐藏字段，用户通常无法直接访问。

row_id：InnoDB 表的隐式主键，如果在建表的时候没有指定主键和唯一约束，那么 InnoDB 就会为记录添加 row_id 隐藏字段。这个值对用户不可见，由系统内部维护，所有没有主键的表共享同一个隐式 Row ID 计数器。InnoDB 使用主键（或隐式 row_id）作为聚簇索引来组织数据存储。row_id 不是必需的，如果我们建表的时候指定了主键或者唯一约束列，那么就没有 row_id 隐藏字段了。占用 6 个字节。

trx_id：事务 id，表示这个数据是由哪个事务生成的。 trx_id 是必需的，占用 6 个字节。

roll_pointer：回滚指针，记录上一个版本的指针。roll_pointer 是必需的，占用 7 个字节。

其中 trx_id 和 roll_pointer 字段用于实现 MVCC 机制。

4.3 行溢出

行溢出是 MySQL 中当一行数据的大小超过存储引擎规定的行大小。InnoDB 引擎默认每行最大为 8KB（页大小的一半，默认页 16KB），当一行数据超过这个限制时，就会发生行溢出。例如 TEXT、BLOB、VARCHAR（最多可以存储 65532 字节）等变长字段存储大量数据。

不同行格式对行溢出的处理如下：

REDUNDANT 和 COMPACT：将溢出列的部分数据存储在溢出页。将前 768 个字节的可变长度列值（VARCHAR、VARBINARY 以及 BLOB 和 TEXT 类型）存储在原始行中（inline 存储），其余部分存储在溢出页上，并在原始行中保留 20 字节的指针指向溢出页。

DYNAMIC：将溢出列的所有数据存储在溢出页，只在原始行中存储 20 个字节的指针来指向溢出页，实际的数据都存储在溢出页中。

COMPRESSED：类似于 DYNAMIC，但是增加压缩功能。