MySQL—InnoDB记录结构

一、InnoDB页概述

InnoDB是用来把内存中表的数据存储到磁盘上、把磁盘上的数据加载到内存中供用户使用的存储引擎。因为磁盘与内存的速度差了几个量级，所以InnoDB存储引擎在进行IO操作时，不可能一条一条记录进行读取或写入，如果一条一条记录进行IO的话，用户可能会骂娘。所以为了解决内存与磁盘速度不匹配问题，InnoDB采用的方式是：将数据划分为若干个页，这个页就是内存与磁盘交互的基本单位，即内存读取或写入一次磁盘，就是读取或写入若干个页，InnoDB中每个页的大小一般为16KB。

二、InnoDB行格式

平时我们操作数据库表，都是按照一条一条记录进行CRUD，这些记录在磁盘上的存放方式被称为行格式或者记录格式。

到目前为止，InnoDB存储引擎有4中行格式，分别是：Compact、Redundant、Dynamic和Compressed。

2.1 指定行格式的语法

创建表时指定行格式

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

修改表时切换行格式

ALTER TABLE 表名 ROW_FORMAT=行格式名称

2.2 行格式

InnoDB存储引擎中的4种行格式从宏观上来看，都是分为记录的额外信息和真实数据两大部分。如下

比如我们在数据库里创建一个演示用的表test，可以这样指定它的行格式：

CREATE TABLE test (
    ->     c1 VARCHAR(10),
    ->     c2 VARCHAR(10) NOT NULL,
    ->     c3 CHAR(10),
    ->     c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;	#指定行格式

可以看到我们刚刚创建的这个表的行格式就是Compact，另外，我们还显式指定了这个表的字符集为ascii，因为ascii字符集只包括空格、标点符号、数字、大小写字母和一些不可见字符，所以我们的汉字是不能存到这个表里的。我们现在向这个表中插入两条记录：

INSERT INTO test(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd');
INSERT INTO test(c1, c2, c3, c4) VALUES('eeee', 'fff', NULL, NULL);

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

+------+-----+------+------+
| c1   | c2  | c3   | c4   |
+------+-----+------+------+
| aaaa | bbb | cc   | d    |
| eeee | fff | NULL | NULL |
+------+-----+------+------+
2 rows in set (0.00 sec)

2.2.1 Compact行格式

记录的额外信息

额外信息被服务器用来描述这条记录。额外信息被分为3部分：变长字段长度列表、Null值列表和记录头信息

变长字段长度列表

MySQL中有很多可变长度的字符类型，比如varchar(M)， varbinary(M)，各种text类型(text，tinytext，longtext……)，各种blob类型(blob，tinyblob，longblob……)。因为上述类型是可变长度，所以在实际存储时不能确定其具体占用的空间大小，所以为了解决这个问题，引入了变长字段长度列表的概念。

如果当前记录中的可变长度类型的字段为Null时，则变长字段长度列表为空。那什么时候不为空呢？当我们向表中插入数据时，如果插入数据的可变长度类型的字段不为空，那么此时变长字段长度列表也就随之更新为此次插入数据所占用的字节长度。下面举个例子应该就明白了。

在Compact行格式中，所有可变长度类型字段在存放数据时，其具体所需要占用的字节长度全都存放在变长字段长度列表中。

所有可变长度类型字段所需要占用的字节长度按照字段顺序逆序存放。

以上面test表中的数据为例，请看c1、c2、c4列的数据类型为varchar(10)，即可变长度类型，所以这3个字段的值的 长度（所占空间字节数） 需要存放进变长字段长度列表中。还可以看出，test表使用的字符集为ASCII，即记录中每个字符仅需要占用 1 个字节的空间。test表中第一条记录中c1、c2、c4字段分别占用空间大小如下：

INSERT INTO test(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd');

列名	存储内容	内容长度（十进制表示）	内容长度（十六进制表示）
c1	'aaaa'	4字节	0x04
c2	'bbb'	3字节	0x03
c4	'd'	1字节	0x01

此时，还有一个必须满足的条件，那就是：所有可变长度类型字段所需要占用的字节长度按照字段顺序逆序存放。

即变长字段长度列表的实际存放顺序为（各个字节之间实际上没有空格，用空格隔开只是方便理解）

01	03	04	#字节长度存放顺序正好与字段的顺序相反

把上面这3个字节长度存入变长字段长度列表中的样子如下图：

上面的案例是变长字段长度列表占用 1 个字节长度的情况，那什么时候占用 2 个字节呢？其实InnoDB有它自己的一套规则，我们首先声明一下W、M和L的意思

• W：代表当前字符集的一个字符最大占用字节数

  • ASCII字符集的一个字符最大占用 1 个字节，即 W=1；
  • gbk字符集的一个字符最大占用 2 个字节，即 W=2;
  • utf8字符集的一个字符最大占用 3个字节，即 W=3;

• M：代表一个字段存储多少个字符

  • 如果某字段类型为 varchar(10) ，则表示该字段存储 10 个字符，即 M=10；

• L：代表一个字段实际存储的数据所占用的字节数

通过公式：W*M 可以得出字段最大可占用的字节数。

具体规则如下：

• 当 W*M <=255 时，用 1 个字节来表示实际数据所占多少字节

  • 可以理解为，InnoDB 在读取变长字段长度列表的信息时，首先查看表结构，计算一下变长字段的最大占用字节数，如果计算结果小于等于 255 ，则使用 1 个字节来表示实际数据所占多少字节

• 当 W*M >255 时，则分为两种情况：

  • 如果 L<=127 ，使用 1 个字节来表示实际数据所占多少字节；
  • 如果 L>127 ，使用 2 个字节来表示实际数据所占多少字节；

当 W*M >255 时，InnoDB 怎么区分它正在读取的字节代表单独的一个字段长度还是代表半个字段长度？

• 当该字节二进制位的第一位是 0 时，说明该字节代表的是一个单独字段长度；
• 当该字节二进制位的第一位是 1 时，说明该字节代表的是半个字段长度

综上所述，可以得出一个结论：只有当可变字段允许存储最大字节数大于255 (W*M >255) 且 实际存储数据的字节数大于127时 (L>127)，才使用 2 个字节来表示实际数据所占多少字节，否则使用 1 个字节。

特殊说明：

变长字段长度列表只会存储 非Null 字段的数据所占用的字节大小，如果字段为 Null的话，其所占用的字节大小是不会存储到变长字段长度列表中的。

我们来看一下第二条记录

INSERT INTO test(c1, c2, c3, c4) VALUES('eeee', 'fff', NULL, NULL);

字段c1和c2非Null，c4为Null，所以变长字段长度列表中只需要存储c1和c2的长度即可，还可以看到 c1 存储的数据为 eeee ，占用 4 个字节，c2 存储的数据为 fff 占用 3 个字节，因为4 和 3 都没有大于255，所以c1和c2分别占用 1 个字节来存储数据长度，如下图

PS：并不是所有记录都有这个 变长字段长度列表 部分，比方说表中所有的列都不是变长的数据类型的话，这一部分就不需要有。

NULL值列表

因为表中会存在 Null 值的情况，如果把 Null 值存放在真实数据中，并且表中有大量的 Null 值时，此时就会占用很大一部分空间。为了避免浪费存储空间，InnoDB 中的 Compact行格式 就会把这些值为 Null 的字段进行统一管理，放到 Null 值列表中。

Null 值列表规则如下

• 首先会统计当前记录中哪些字段是允许为 Null

• 将每个允许存储 Null 值的字段对应一个二进制位，且 二进制位按照字段顺序逆序存放（与变长字段长度列表存放顺序一致）

  • 如果字段值为 Null ，则对应的二进制位用 1 表示；
  • 如果字段值非 Null ，则对应的二进制位用 0 表示；

  因为 test 表中字段 c1, c3, c4 允许为 Null ，所以其与二进制位的对应关系（逆序）如下图

• MySQL 规定 Null值列表 必须使用整数个字节的位表示，如果使用的二进制位个数不是整数个字节，则字节二进制的高位补 0

  • 什么是整数个字节？

    我们都知道，1 个字节是由 8 个二进制位组成，如果此时二进制位不够 8 位，也就代表其不是一个字节，进而也就说明不是整数个字节。

  因为 test 表中字段 c1, c3, c4 允许为 Null ，但是只有 3 个字段，所以对应 3 个二进制位，很明显不满足 1 个字节的长度（即8位），所以高位需要补 0。如下图

通过上面的规则可以得出一个结论：表中每个允许为 Null 值的字段都会在 Null值列表 中对应一个二进制位，字段对应的二进制位是根据字段顺序逆序存放，如果二进制位的个数不是 8 的倍数（即整数个字节），则高位补 0 来保证是 8 的倍数。

如果允许为 Null 值的字段个数有 9 个，则使用16个二进制位来表示（即2个字节），高位补 0。以此类推

现在已经得出了上面这个结论，接下来我们就看一下 test 表的两条记录的 Null值列表 是怎么存放的。因为只有c1、c3、c4这3个列允许存储NULL值，所以所有记录的NULL值列表只需要 1 个字节。

首先看一下第一条记录

INSERT INTO test(c1, c2, c3, c4) VALUES('aaaa', 'bbb', 'cc', 'd');

可以看出，第一条记录没有为 Null 值的字段，所以其 Null值列表 的二进制位都为 0 ，如下图（牢记逆序存放）

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

接下来看一下第二条记录

INSERT INTO test(c1, c2, c3, c4) VALUES('eeee', 'fff', NULL, NULL);

可以看出，c3和c4的值为NULL，所以这 2 个字段对应的二进制位为 1，而 c1 字段对应的二进制位为 0 。如下图（牢记逆序存放）

所以第二条记录的 NULL值列表 用十六进制表示就是：0x06。

所以这两条记录在填充了NULL值列表后的示意图就是这样：

记录头信息

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

这些二进制位代表的详细信息如下表：

名称	大小（单位：bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

看一下test中的第一条记录的头信息分别是什么：

记录的真实数据

它用来存储我们自定义字段的数据，比如 test 表中c1、c2、c3、c4就是自定义字段。但是除了存放自定义字段的数据外，还会存放一些 隐藏字段 ， MySQL 默认为每条记录都会添加这些 隐藏字段。具体 隐藏字段 如下：

列名	是否必须	占用空间	描述
row_id	否	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointer	是	7字节	回滚指针

PS：实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR，为了美观才写成了row_id、transaction_id和roll_pointer。

InnoDB主键生成策略

• 如果用户自定义了主键，则使用用户定义的
• 如果用户没有自定义主键，则选取一个Unique键作为主键
• 如果连Unique键也没有的话，那么InnoDB会帮我们生成一个默认名为row_id的隐藏列作为主键列

通过上表和主键生成策略，我们得出一个结论：

InnoDB 存储引擎会为每条记录都添加 transaction_id 和 roll_pointer 这两个列，但是 row_id 是可选的（在没有自定义主键 以及Unique 键的情况下才会添加该列）

test 的建表语句

CREATE TABLE test (
    ->     c1 VARCHAR(10),
    ->     c2 VARCHAR(10) NOT NULL,
    ->     c3 CHAR(10),
    ->     c4 VARCHAR(10)
    -> ) CHARSET=ascii ROW_FORMAT=COMPACT;	#指定行格式

现在我们来看一下 test 表，通过建表语句可以看出建表时并没有指定主键，所以根据InnoDB主键生成策略，我们知道MySQL会帮我们添加一个名为row_id的默认主键列，并且也会添加另外两个列，即transaction_id和roll_pointer，如下图

看这个图的时候我们需要注意几点：

1. 表test使用的是ASCII字符集，所以0x61616161就表示字符串'aaaa'，0x626262就表示字符串'bbb'，以此类推。

2. 注意第1条记录中c3列的值，它是CHAR(10)类型的，它实际存储的字符串是：'cc'，而ASCII字符集中的字节表示是'0x6363'，虽然表示这个字符串只占用了2个字节，但整个c3列仍然占用了10个字节的空间，除真实数据以外的8个字节的统统都用空格字符填充，空格字符在ASCII字符集的表示就是0x20。

3. 注意第2条记录中c3和c4列的值都为NULL，它们被存储在了前边的NULL值列表处，在记录的真实数据处就不再冗余存储，从而节省存储空间。

CHAR(M)列的存储格式

test表的c1、c2、c4列的类型是VARCHAR(10)，而c3列的类型是CHAR(10)，我们说在Compact行格式下只会把变长类型的列的长度逆序存到变长字段长度列表中，如下图

这是因为 test 表采用的是定长字符集 ASCII 字符集，每个字符固定占用 1 个字节。如果把字符集修改为变长的，例如修改为 gbk 字符集表示一个字符占用 1~2 个字节、 utf8 字符集表示一个字符占用 1~3 个字节，这时候c3列的类型依旧是CHAR(10)，那么它到底占用多少字节呢，是不是就变成了一个范围（10~20或10~30）。

所以得出一个结论：当采用变长字符集的时候，即使是所谓的固定长度类型CHAR(M)也就变成了可变长度类型，也需要按照上述相应的规则把该字段的长度存放到 变长字段长度列表 中。

如果把 test 表的字符集修改为 utf8，则 test 表的第一条记录会发生什么变化呢？

特别强调一点

如果使用变长字符集，则 CHAR(M)类型的字段要求至少占用 M 个字节，而 VARCHAR(M)类型的字段没有要求。

如果当前使用 utf8 字符集，并且某个字段是采用 CHAR(10) 类型，该字段可存放的实际数据的长度范围是10～30个字节。即使我们向该字段中存储一个空字符串也会占用 10 个字节，这是怕将来更新该列的值的字节长度大于原有值的字节长度而小于10个字节时，可以在该记录处直接更新，而不是在存储空间中重新分配一个新的记录空间，导致原有的记录空间成为所谓的碎片。

2.2.2 Redundant行格式

这个是最古老的，最简单粗暴的行格式了，现在基本上已经不用了，因为占用空间最多，从而导致内存碎片化最严重，是最低效的行格式了（针对现在varchar字段使用的更多，而对于 varchar 字段改变长度的更新大部分情况下就是将原有行的数据标记为已删除，然后在其他空间足够的地方新建记录，Redundant 顾名思义，占用空间更多，所以碎片化，空间浪费会更严重）。

不同于 Compact 行格式，Redundant 的开头是所有字段长度列表，而不是变长字段列表 + NULL 值列表。这个字段长度列表的格式是：

• 记录所有字段的长度偏移，包括隐藏列。偏移就是，第一个字段长度为 a，第二个字段长度为 b，那么列表中第一个字段就是 a，第二个字段就是 a + b。
• 所有字段倒序排列

记录头信息

Redundant行格式的记录头信息占用6字节，48个二进制位，这些二进制位代表的意思如下：

名称	大小（单位：bit）	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在页面堆的位置信息
n_field	10	表示记录中列的数量
1byte_offs_flag	1	标记字段长度偏移列表中每个列对应的偏移量是使用1字节还是2字节表示的
next_record	16	表示下一条记录的相对位置

对于长度存储，是一字节还是两字节，以及存储的内容，Redundant 的规则比较特殊：

根据整行记录的长度决定，到底每个字段用一个字节还是两个字节，每个字段用一个字节还是两个字节，在记录头信息里面有标记

• 如果整行长度小于 128，则用一字节存储
• 如果大于等于128，但不大于32767，则每个字段用两个字节

对于一字节存储，最高位标记字段是否为 NULL，如果为 NULL，则最高位为1，否则为0. 剩下的 7 位用来存储长度，所以最多是 127

对于两字节存储，最高位还是标记字段是否为NULL。第二位标记这条记录是否在同一页，如果在则为0，如果不在则为1

这也正好说明了：一个字节能表示的范围是0～255，但是在记录的真实数据占用的存储空间大于127时就采用2个字节表示各个列的偏移量的原因。最高位用来表示是否为Null了。

有没有记录的真实数据大于32767的情况呢？ 有，不过此时的记录已经存放到了溢出页中，在本页中只保留前768个字节和 20 个字节的溢出页面地址（当然这20个字节中还记录了一些别的信息）。因为字段长度偏移列表处只需要记录每个列在本页面中的偏移就好了，所以每个列使用2个字节来存储偏移量就够了。

CHAR(M)列的存储格式

我们知道Compact行格式在CHAR(M)类型的列中存储数据的时候还挺麻烦，分变长字符集和定长字符集的情况，而在Redundant行格式中十分干脆，不管该列使用的字符集是啥，只要是使用CHAR(M)类型，占用的真实数据空间就是该字符集表示一个字符最多需要的字节数和M的乘积。比方说使用utf8字符集的CHAR(10)类型的列占用的真实数据空间始终为30个字节，使用gbk字符集的CHAR(10)类型的列占用的真实数据空间始终为20个字节。由此可以看出来，使用Redundant行格式的CHAR(M)类型的列是不会产生碎片的。

2.2.3 行溢出数据

VARCHAR(M)最多能存储的数据

大家应该知道varchar(M) 类型最大可以存储 65535 个字节。其中 M 表示可以可以最大存放多少个 字符。

varchar(M) 类型字段，实际上需要占用 3 部分的存储空间

• 真实数据
• 真实数据占用多少字节的长度，需要占用 2 个字节空间
• NULL 值标识，如果该字段有NOT NULL属性则可以没有这部分存储空间，反之则需要 1 个字节空间

按照上述规则，下面三种字符集最大可存储字符数如下表

字符集	没有 NOT NULL 属性	有 NOT NULL 属性
ASCII	65532	65533
gbk	32766	32766
utf8	21844	21844

当使用 ASCII 字符集时，如果有NOT NULL 属性，最大存放字符数是 65533；如果没有，则最大字符数为65532

CREATE TABLE varchar_size_demo (c VARCHAR(65533) ) CHARSET = ASCII ROW_FORMAT = Compact;

[SQL]CREATE TABLE varchar_size_demo (c VARCHAR(65533) ) CHARSET = ASCII ROW_FORMAT = Compact;
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

当使用 gbk 字符集时，无论varchar(M) 类型字段有没有NOT NULL属性，最大存放字符数都是32766

CREATE TABLE varchar_size_demo (c VARCHAR(32767) not NULL) CHARSET = gbk ROW_FORMAT = Compact;

[SQL]CREATE TABLE varchar_size_demo (c VARCHAR(32767) not NULL) CHARSET = gbk ROW_FORMAT = Compact;
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

当使用 utf8 字符集时，无论varchar(M) 类型字段有没有NOT NULL属性，最大存放字符数都是21844

CREATE TABLE varchar_size_demo (c VARCHAR(21845) not NULL) CHARSET = utf8 ROW_FORMAT = Compact;

[SQL]CREATE TABLE varchar_size_demo (c VARCHAR(21845) not NULL) CHARSET = utf8 ROW_FORMAT = Compact;
[Err] 1118 - Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. You have to change some columns to TEXT or BLOBs

记录中的数据太多产生的溢出

文章开头处提到了MySQL数据库中是以 页 来作为基本的存储单位，一般情况下，一个 页 的大小是 16KB ，也就是16384字节。可是VARCHAR(M)类型字段没有NOT NULL属性时，最大可以存放 65532个字节。这样一条记录所占用的字节数远远超过一个页可以存放的最大字节数。这样就会造成 行溢出。

那什么是行溢出呢？

在Compact和Reduntant行格式中，如果出现一个页存放不下一个大字段的情况，在记录的真实数据处只会存放字段的一部分数据（768个字节），把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处再用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。如下图

简图：

详细图：

如果发生 行溢出 的情况，本 记录的真实数据 只会存放该字段的前 768个字节 数据部分和一个占用 20字节 空间的指向其它页的地址，然后把多出来的数据分散存放到其它页中，这个过程就被称为 行溢出 。

存储多出来的数据的页也被称为溢出页。

强调一点：不光是 VARCHAR(M) 类型的字段，其他的 TEXT、BLOB 类型的字段也会造成行溢出的情况。

行溢出的临界点（了解即可）

MySQL中规定一个页中至少存放两行记录。假如一个表它只有一个列c，我们往这个表中插入两条记录，每条记录最少插入多少字节的数据才会行溢出的现象呢？这得分析一下页中的空间都是如何利用的。

• 每个页除了存放我们的记录以外，也需要存储一些额外的信息，乱七八糟的额外信息加起来需要136个字节的空间（现在只要知道这个数字就好了），其他的空间都可以被用来存储记录。

• 每个记录需要的额外信息是27字节。

  这27个字节包括下边这些部分：

  • 2个字节用于存储真实数据的长度
  • 1个字节用于存储列是否是NULL值
  • 5个字节大小的头信息
  • 6个字节的row_id列
  • 6个字节的transaction_id列
  • 7个字节的roll_pointer列

假设一个列中存储的数据字节数为n，那么发生行溢出现象时需要满足这个式子：

136 + 2×(27 + n) > 16384

求解这个式子得出的解是：n > 8098。也就是说如果一个列中存储的数据不大于8098个字节，那就不会发生行溢出，否则就会发生行溢出。不过这个8098个字节的结论只是针对只有一个列的表来说的，如果表中有多个列，那上边的式子和结论都需要改一改了，所以重点就是：你不用关注这个临界点是什么，只要知道如果我们想一个行中存储了很大的数据时，可能发生行溢出的现象。

2.2.4 Dynamic和Compressed行格式

在 msyql 5.7.9 及以后版本，它的行格式默认值是Dynamic。

Dynamic和Compressed行格式和Compact行格式很像，但是在处理行溢出时有不同

Dynamic和Compressed行格式，它们不会在记录的真实数据处存储字段真实数据的前768个字节，而是把所有的字节都存储到其他页面中，只在记录的真实数据处使用20个字节来存储其他页面的地址。如下图

Compressed行格式和Dynamic不同的一点是，Compressed行格式会采用压缩算法对页面进行压缩，以节省空间。

来源：掘金小册子《MySQL 是怎样运行的：从根儿上理解 MySQL》