一周浅谈：InnoDB记录存储结构

前言

之前的文章，mysql 对于我们来说还只是一个 “黑盒”，我们只负责使用客户端发送请求，并且等待服务器返回结果。但是我们现在需要去搞清楚三个问题：

表中的数据存到哪里（where）
存放格式（what）
mysql 以书面方式来访问数据（how）

接下来，我就重点总结一下 mysql 默认的存储引擎 InnoDB，其他存储引擎的设计思路也大差不差，只不过特性不同罢了，设计思路差不多

InnoDB 页简介

InnoDB 是一个将表中的数据存储到磁盘上的存储引擎，即使我们关闭并且重启服务器，数据还是存在的。而真正处理数据的过程发生在内存中，所以是要把磁盘中的数据加载到内存中。如果是处理写入或修改请求，就还需要把内存中的内容刷新到磁盘上。

InnoDB 行格式

我们平时都是以记录为单位向表中插入数据的，这些记录在磁盘中的存放形式也被称为行格式或者记录格式。InnoDB 的设计者设计了 4 中不同类型的行格式：

COMPACT
REDUNDANT
DYNAMIC
COMPRESSED

指定行格式的语法

可以在创建或者修改表的语句中指定记录所使用的的行格式：

1
2
3

CREATE TABLE 表名 （列的信息） ROW_FORMAT=行格式名称;
ALTER TABLE 表名 ROW_FORMAT=行格式名称;

为了探究接下来各个行格式下的数据结构到底有啥不同，所以假设在 test_db 中创建一张表 record_format_demo，同时指定它的行格式

mysql> CREATE TABLE record_format_demo (
    ->     c1 VARCHAR(10),
    ->		 c2 VARCHAR(10) NOT NULL,
    ->		 c3 CHAR(10),
    ->     c4 VARCHAR(10),
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;
    Query OK, 0 rows affected (0. 03 sec)

这张表的行格式是 COMPACT，林外，我们还显式指定了字符集为 ascii（因为 ascii 字符集只包含空格、标点符号、数字、大小写字母和一些不可见字符，所以汉字不能存到这张表中）现在我向表中插入两条记录：

mysql> INSERT INTO record_format_demo(c1, c2, c3, c4) VALUES ('aaaa', 'bbb', 'cc', 'd'), ('eeee', 'fff', NULL, NULL);
Query ok, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0

现在表中的记录是这个样子的：

mysql> SELECT * FROM record_format_demo;
+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

现在可以看看每个行格式下的存储结构到底有啥不同

COMPACT 行格式

>COMPACT 行格式示意图

记录的额外信息

这部分信息是服务器为了更好地管理记录而不得不额外添加的一些信息。这些额外信息分为 3 个部分，分别是变长字段长度列表、NULL 值列表和记录头信息。

变长字段长度列表

mysql 支持一些变长的数据类型，比如 VARCHAR (M)、VARBINARY (M)、各种 TEXT 类型、各种 BLOB 类型。这些数据类型的列称为变长字段。因为变长字段中存储多少字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来，这样才不至于吧 mysql 服务器高懵逼，所以总结来说这些变长字段占用的存储空间分为两部分：

真正的数据内容
该数据占用的字节数

在 COMPACT 行格式中，所有变长字段的真实数据占用的字节数都存放在记录的开头位置，从而形成一个变长字段长度列表，各边长字段的真实数据占用的字节数按照列的顺序逆序存放。以 record_format_demo 表中第一条记录来举个例子，因为 record_format_demo 表中的 c1、c2、c4 列都是 VARCHAR (10) 类型的，也就是变长的数据类型所以，所以这 3 个列的值占用的存储空间按字节数都需要保存在记录开头处。看下第一条记录各变长字段内容的长度

列名存储内容内容长度（十进制表示）内容长度（十六进制表示）c1’aaaa’40x04c2’bbb’30x03c4’d’10x01因为这些长度值需要按照列的顺序逆序存放，所以最后变长字段长度列表的字节串用十六进制的表示的效果就是：01 03 04需要说明的是，这里各个字节之间实际上没有空格，只是为了更好理解罢了。把这个字节串组成的变长字段长度列表填入到** COMPACT 行格式示意图**的效果如下图所示。>第一条记录的存储格式由于第一条记录中 c1、c2、c4 列中的字符串都比较短，也就是说占用的字节数比较小（c1 列的内容是 ‘aaaa’，占用 4 字节；c2 列内容 ‘bbb’，占用 3 字节；c4 列内容 ‘d’，占用 1 字节），每个变长字段的内容占用的字节数用 1 字节就可以表示（也就是 4、3、1 这 3 个数字可以分别用字节 0x04、0x03、0x01 表示）。但是，如果变长字段的内容占用的字节数比较多，可能就需要用 2 字节来表示。至于用 1 字节还是 2 字节来表示变长字段的真实数据占用的字节数，InnoDB 是有它的一套规则的。为了更好的表述清楚这个规则，我们引入 W、M 和 L 这几个符号，先分别看看这些符号的意思。

- 假设某个字符集中最多需要W字节来表示一个字符（也就执行SHOW CHARSET语句后结果中的Maxlen列）。比如utf8mb4字符集中的W就是4，utf8是3，gbk是2，ascii是1
- 对于变长类型VARCHAR(M)来说，这种类型表示能存储最多M个字符（注意是字符不是字节哦），所以这种类型能表示的字符串最多占用的字节数就是 M*W
- 假设该变长字段实际存储的字符串占用的字节数是L

确定使用 1 字节还是 2 字节来表示一个变长字段的真实数据占用的字节数的规则就是这样：

- 如果 M*W <= 255，那么使用1字节来表示真实数据占用的字节数
- 如果 M*W > 255，则分为下面两种情况：
   - 如果 L <= 127，则用1字节来表示真实数据占用的字节数
   - 如果 L > 127，则用2字节来表示真实数据占用的字节数

总结：如果该变长字段允许存储的最大字节数（M*W）超过 255 字节，并且真实数据占用的字节数（L）超过 127 字节，则使用 2 字节来表示真实数据占用的字节数，否则使用 1 字节。

NULL 值列表

一条记录中的某些列可能存储 NULL 值，如果把这些 NULL 值都放到记录的真实数据中存储会很占地方，所以 COMPACT 行格式把一条记录中值为 NULL 的列统一管理起来，存储到 NULL 值列表中。处理过程如下：

1 2	1. 统计表中允许存储NULL的列有哪些

主键列以及使用 NOT NULL 修饰的列都是不可以存储 NULL 值的，所以在统计的时候不会吧这些列算进去。比如表 record_format_demo 的 3 个列 c1、c3、c4 都允许存储 NULL 值，而 c2 列使用 NOT NULL 进行了修饰，不允许存储 NULL 值

2. 如果表中没有允许存储NULL的列，则NULL值列表也就不存在了，否则将每个允许存储NULL的列对应一个二进制位，二进制位按照列的顺序逆序排列。二进制位表示的意义如下：
   1. 二进制位的值为1时，代表该列的值为 NULL
   1. 二进制位的值为0时，代表该列的值不为 NULL

因为表 record_format_demo 有 3 个值允许为 NULL 的列，所以这 3 个列的二进制位的对应关系如下图所示。再次强调一次，二进制位按照列的顺序逆序排序，所以第一个列 c1 和最后一个二进制位对应>

MySQL 规定 NULL 值列表必须用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，所以在字节的高位补 0，效果图如下

>以此类推，如果一个表中有 9 个值允许为 NULL 的列，则这个记录的 NULL 值列表部分就需要 2 字节来表示了。知道了规则以后，我们再返回头看看表 record_format_demo 中两条记录中的 NULL 值列表应该怎么存储。因为只有 c1、c3、c4 这 3 个列的值都允许存储 NULL 值，所以记录的 NULL 值列表处只需要一个字节

1 2	- 对于第一条记录来说，c1、c3、c4这3个列的值都不为 NULL，所以它们对应的二进制位都是0，如下图所示

>所以第一条记录的 NULL 值列表用十六进制表示就是 0x00

1 2	- 对于第二条记录来说，c1、c3、c4这3个列中c3和c4的值都为 NULL，所以这3个列对应的二进制位的情况如下图所示

>所以第二条记录的 NULL 值列表用十六进制表示就是 0x06这两条记录在填充了 NULL 值列表后的示意图如下所示：>

记录头信息

除了变长字段长度列表、NULL 值列表之外，还有一个称之为记录头信息的部分。记录头信息由固定的 5 字节组成，用于描述记录的一些属性。5 字节也就是 40 个二进制位，不同的位代表不同的意思，如下图所示：>这些二进制位代表的详情信息如表所示：记录头信息中各二进制位代表的详细信息

名称大小（位）描述预留位 11没有使用预留位 21没有使用deleted_flag1标记该记录是否被删除min_rec_flag1B + 树的每层非叶子节点中最小的项目项记录都会添加该标记n_owned4一个页面中的记录会被分成若干个组，每个组中有一个记录是 “带头大哥”，其余的记录都是 “小弟”。“带头大哥” 记录 n_owned 值都为 0heap_no13表示当前记录在页面堆中的相对位置record_type3表示当前记录的类型，0 表示普通记录，1 表示 B + 树飞叶子节点的目录项记录，2 表示 Infimum 记录，3 表示 Supremum 记录next_record16表示下一条记录的相对位置- 记录的真实数据

对于 record_format_demo 表来说，记录的真实数据除了 c1、c2、c3、c4 这几个我们自己定义的列的数据外，MySQL 会为每个记录默认地添加一些列（也称为隐藏列），具体的列如下表所示MySQL 为每个记录默认添加的列

列名是否必需占用空间描述row_id否6 字节行 ID，唯一标识一条记录trx_id是6 字节事务 IDroll_pointer是7 字节回滚指针这里提一下 InnoDB 表的主键生成策略：优先使用用户自定义的主键作为主键；如果用户没有定义主键，则选取一个不允许存储 NULL 值的 UNIQUE 键作为主键：如果表中连不允许存储 NULL 值的 UNIQUE 键都没有定义，则 InnoDB 会为表默认添加一个名为 row_id 的隐藏列作为主键

这几个真正的列的真正名称是 DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，但是后面的描述中都用小写描述