目录结构

之前整篇文章太长，阅读体验不好，将其拆分为几个子篇章。

本篇章讲解 InnoDB 行格式。

InnoDB 行格式

InnoDB 一行记录是如何存储的？

这个问题是本文的重点，也是面试中经常问到的问题，所以就引出了下文的 InnoDB 行格式内容。

InnoDB 指定行格式语法

先看下指定行格式的简单语法

#创建表指定行格式
create table table_name(列信息) row_format = 行格式名称

#修改表行格式
alter table table_name row_format = 行格式名称

Compact__25">Compact 行格式

Compact__27">Compact 行数据存储结构

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

**举例：**采用 Compact 行格式创建一张表 page_demo

create table page_demo (
  c1 int,
  c2 int,
  c3 varchar(10000),
  primary key(c1)
) CAHRSET=ascii ROW_FORMAT=Compact

• 字符集：ascii
• 行格式：Compact

表中的每一行记录的行格式如下所示：

这些记录头信息中的各个属性如下（主要 6 个属性）：

其中有两个预留位置没有使用，我们简化之后的行格式如下所示：

向库中插入 4 条数据：

insert into page_demo 
values
(1, 100, 'song'),
(2, 200, 'tong'),
(3, 300, 'zhan'),
(4, 400, 'lisi');

这 4 条记录的行格式如下所示：

上图各方块属性：

• 蓝色方块为记录头信息
• 绿色方块为 数据信息，这里为了展示方便，写的是 10 进制 ，实际上底层存储的是 2 进制

变长字段长度列表

创建一张表 record_test_table：

create table record_test_table(
  col1 varchar(8),
  col2 varchar(8) not null,
  col3 vhar(8),
  col4 varchar(8)
) charset=ascii row_format=Compact

向表里面插入两个数据：

insert into record_test_table(col1, col2, col3, col4)
values
('zhangsan', 'lisi', 'wangwu', 'songhk'),
('tong', 'chen', NULL, NULL);

MySQL 支持一些变成的数据类型，比如 varchar(M)、varbinary(M)、text、blob 等类型，这些数据类型修饰的列被称为 变成字段。边长字段中存储多少个字节的数据是不固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存储起来。

在 Compact 行格式中，把所有变长字段的真实数据占用的字节长度存放在记录的开头部位，从而形成一个变长字段长度列表。

注意：

这里存储的变长字段的长度的顺序和表字段创建时的真实顺序是翻过来的，比如：两个 varchar 字段在表中的顺序是 a(10)，b(15)。那么在变长字段长度列表中的顺序是 15,10，翻过来存储的。

根据上面插入的两条真实数据，分析一下各个变长字段真实数据占用的字节长度：

NULL 值列表

Compact 行格式会把可以为 NULL 值的列统一管理起来，存在一个标记为 NULL 值列表中。

如果表中没有可以为 NULL 值的列，那这个 NULL 值列表也就不存在。

为什么要定义 NULL 值列表？

之所以要存储 NULL值，是因为数据都是需要对齐的。如果没有标注出 NULL 值的位置，就有可能在查询数据的时候出现混乱 的情况。如果 使用一个特殊符号代替 NULL 值放到对应的位置，虽然可以达到效果，但是大量为 NULL 值的列会严重 浪费空间，所以直接在 行数据的头部开辟出一块空间 专门用来存储该行数据有哪些是非空数据，哪些是空数据， 格式如下：

• 二进制位为 1：代表列值为 NULL
• 二进制为为 0：代表列值不为 NULL

这样我们回答一个问题，MySQL 中的 NULL 值是怎么存储的？

答：NULL 值是由 NULL 列表记录的，用二进制逆序表示每一行记录中的每一列是否为 NULL 值，0 代表不为 NULL，1 代表为 NULL 值。

假设有一张表有 4 个字段，col1、col2、col3、col4

插入一条记录：‘a’, NULL, NULL, ‘dd’

那 NULL 值列表用二进制表示为：0 1 1 0，转化为 10 进制就是 06。

记录头信息（5 字节）

delete_mask（删除标记）

这个属性标记着当前记录是否被删除，占用 1 个 bit：

• 值为 0：代表记录没有被删除
• 值为 1：代表记录被删除了

被删除的记录为什么还在页中存储？

这些被删除的记录之所以不立即从磁盘的页中移除，是因为移除他们之后，紧跟着他们的记录需要 重新排列，特别是对 聚簇索引的叶子节点，假设移除的是主键值为 1的记录， 那整个聚簇索引的叶子节点会因为这一条记录的删除全部重新排序，导致性能消耗。所以只是将这些删除的记录做一个删除标记和正常记录做个区分，实际上这些被删除的记录会组成一个 垃圾链表，它们所占用的空间被称为 可重用空间，之后再插入的数据，可能会把这些被删除记录占用的空间直接 覆盖掉（复用）。

min_rec_mask（最小记录标记）

B+Tree 的每层非叶子节点中的最小记录都会添加该标记，并且 min_rec_mask的值为 1。

我们自己插入的数据记录的 min_rec_mask的值为 0，所以它们都不是 B+Tree 的非叶子节点中的最小记录（这句话自己理解就行，不要纠结）。

record_type（记录类型）

这个属性代表当前记录的类型，一共有 4 种类型的记录：

• 0：表示普通记录
• 1：表示 B+Tree 非叶子节点记录
• 2：表示最小记录
• 3：表示最大记录

从图中可以看出，我们自己插入的记录的 record_type的值为 0，最大最小记录的 record_type的值分别为 2和 3：

非叶子节点记录 record_type的值为 1的情况（索引的数据结构一文中讲述的内容）：

heap_no（记录位置）

这个属性代表代表当前记录在当前页中的下标位置。

下标为 0、1 的两条记录分别为最大和最小记录，在上文【Infimum + Supremum（最大记录和最小记录）】中已经提到了，因为这两个记录不是我们插入的，所以有时候也称为 伪记录或 虚拟记录。

n_owned（每组记录数）

页目录（有多个组）中每个组中最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned字段的值。

next_record（下一条记录的地址偏移量，非指针）

记录头中该属性非常重要，它表示从 当前记录的真实数据 到 下一条记录的真实数据 之间的 地址偏移量。

比如：第一条记录中的 next_record值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节，便是下一条记录的真实数据。

**注意：**下一记录并不是按照我们插入顺序的下一条记录，而是按照主键值顺序排列的下一条记录。

InnoDB 底层规定 Infimum 记录（最小记录）的下一条记录就是当前页中主键值最小的记录，而当前页中主键值最大的记录指向的下一条记录就是 Supremum 记录（最大记录）。

下图用箭头指向代替地址偏移量，来表示 next_record。

演示：删除一条记录的操作

根据上图所示，假设删除上图第 2 条记录：

# 删除主键值为2的记录
delete from page_demo where c1 = 2;

删除之后，整个链表也会跟着变化，第一条记录的 next_record就会直接指向第 3 条记录，但是第 2 条记录并没有被真实删除，只是将 delete_mask值变成了 1。下图所示：

变化内容如下：

• 第 2 条记录的 delete_mask变为 1

• 第 2 条记录的 next_record变为 0，代表不再指向真实数据了

• 最大记录的 n_owned的值从 5=> 4，因为当前组少了一条记录

  • 原本当期页算上最大最小记录，总共 6 条记录，分为两个组，最小记录为一个组
  • 四条真实记录和最大记录为一组，所以最大记录中的n_owned的值为 5
  • 现在第二组中删除了一条记录，所以n_owned的值从 5=> 4

演示：增加一条记录的操作

上述主键值为 2 的记录被删除后（变成了垃圾链表），但是存储空间并没有被收回，如果再次把这条记录插入表中，会发生什么？

insert into page_demo values(2, 200, 'tong');

如下图所示：

变化内容如下：

• 新插入的数据，因为指定了主键值为 2，所以按照聚簇索引结构这条记录会按照顺序插入原来第 2 条记录的位置
• 因为原来被删除的第 2 条记录并没有被真实删除，仍然占有空间，所以这次新插入的数据会复用原有的空间
• 第 2 条记录的 delete_mask的值变为 0
• 第 2 条记录的 next_record的值变为 32
• 第 1 条记录的 next_record指向第 2 条记录，第 2 条记录的next_record指向第 3 条记录
• 最大记录的 n_owned的值从 4 => 5

记录的真实数据

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

实际上这几个列的真实名称是：

• db_row_id
• db_trx_id
• db_roll_ptr

其中 row_id 字段的含义，如果一个表没有手动定义主键，则会选取一个 Unique 键（值唯一的列）作为主键，如果连 Unique 键都没有定义的话，则会为表默认添加一个名为 row_id 的隐藏列作为主键。所以 row_id 是在没有手动定义主键以及不存在 Unique 键的情况下才会存在。

transaction_id 和 roll_pointer 涉及到事务，后面学到再讲解。

举例：创建一张表 mytest：

create table mytest(
  col1 varchar(10),
  col2 varchar(10),
  col3 char(10),
  col4 varchar(10)
)engine=innodb charset=latin1 row_format=compact

插入三条数据：

insert into mytest values
('a', 'bb', 'bb', 'ccc'),
('d', 'ee', 'ee', 'fff'),
('d', NULL, NULL, 'fff');

找到存储表文件 mytest.ibd 的位置，用 notepad++打开，

刚打开可能会乱码，可以安装一个解析插件（自行解决），解析为十进制的数据格式。

格式化之后，二进制文件如下，只需要看真实数据存储的二进制即可：

我们对照下插入的三行记录：

('a', 'bb', 'bb', 'ccc'),
('d', 'ee', 'ee', 'fff'),
('d', NULL, NULL, 'fff');

解析上面的二进制文件，因为 col3 列是定长，不计入变长字段列表，下面解析第一行记录：

• 【变长字段区域】：03 02 01 对照 col3 列 ccc 长度为 03，col2 列 bb 长度为 02，col1 列 a 长度为 01
• 【NULL 值列表区域】：00 代表都是非空的字段，实际上是按照字段的逆序组成的二进制 0 0 0 0 ，转化为十进制就是 00
• 【记录头信息】：00 00 10 00 2c 对照记录头信息（5 个字节），其中 2c对应 next_record，偏移 2c 个字节到下一条记录的位置
• 【row_id】：00 00 00 2b 68 00 对照隐藏主键（6 字节），当没有手动指定主键，且没有 Unique 建时，InnoDB 会默认创建 row_id
• 【transaction_id】：00 00 00 00 06 05 对照事务id（6 字节）
• 【roll_pointer】：80 00 00 00 32 01 10 对照回滚指针（7 字节）
• 【真实记录】：61 对照第一行记录 col1 的值 a
• 【真实记录】：62 62 对照第一行记录 col2 的值 bb
• 【真实记录】：62 62 20 20 20 20 20 20 20 20 对照第一行记录 col3 的值 bb，后面的 20 作为一个空值，因为 col3 字段是定长 char(10)10 个字节，而一个字符 b 只占 1 个字节，所以用 8 个 20 填充 8 个空字节位
• 【真实记录】：63 63 63 对照第一行记录 col3 的值 ccc

根据上面的分析我们大致知道了，一行完整数据底层二进制文件的存储格式是怎样的。

第二行记录和第一行内容想通，根据行格式自行推断。

我们重点来看第三行记录是如何存储的？

• 【变长字段列表】：03 01 对照字段 col4 和 col1，col3 和 col2 为 NULL 值不记录
• 【NULL 值列表】：06 对照四个字段是否为 NULL 值的二进制 0 1 1 0，转化为十进制就是 06
• 【记录头信息】：00 00 20 ff 98 对照记录头信息（5 个字节），其中 98 是 next_record
• 【row_id】：00 00 00 2b 68 02 对照 row_id（6 字节）
• 【transaction_id】：00 00 00 00 06 07 对照事务 id（6 字节）
• 【roll_pointer】：80 00 00 00 32 01 10 对照回滚指针（7 字节）
• 【真实记录】：64 对照第三行记录的 col1 字段的值 d
• 【真实记录】：66 66 66 对照第三行记录的 col4 字段的值 fff，因为 col2 和 col3 都是 NULL值所以没有记录

到这我们就分析完了，应该对底层二进制文件的存储有了一定的认知吧。

Dynamic__Compressed__410">Dynamic 和 Compressed 行格式

字段的长度限制

在了解行溢出之前我们要先了解下一个字段的最大长度。

回顾一下，char 和 varchar 的区别

一个 varchar 类型的字段，最大容量为 65535 个字节。

我们创建一张表，验证一下是否真的可以指定为 65535 个字节？

首先我们查看一下 MySQL8.0.26 默认字符集

说明默认字符集采用 utf8mb4。

再查看一下 MySQL5.7.34 默认字符集

说明默认字符集采用 utf8。

这里我们统一采用 8.0.26 版本去实践验证。

首先我们明确一点，不同字符集字符和字节的对等关系：

• **utf8 字符集：**1 个字符等于 3 个字节
• **utf8mb4 字符集：**1 个字符等于 4 个字节
• **ascii 字符集：**1 个字符等于 1个字节

第一步我们采用默认字符集创建一张表 varchar_size_demo，行格式统一采用 Compact。

• **utf8mb4 字符集：**1 个字符等于 4 个字节

create table varchar_size_demo  (
  c varchar(65535)
) row_format=COMPACT;

报错提示，字段长度最大不能超过 16383，因为 8.0.26 版本默认字符集为utf8mb4，也就是一个字符等于 4 个字节，但是16383 * 4 = 65532，65532 还差了 3 个字节到 65535，按理论我们应该用 65535 除以 4 等于 16383.75，但是字段长度不能带小数，那我们字舍五入将字段长度改为 16384再试下：

显示还是不能超过 16383，那我们将字段长度改为 16383，再次尝试：

创建成功！！！

思考一下，那 3 个字节跑哪去了？

16383 * 4 = 65532

65535 - 65532 = 3

原因是：每一行记录的头信息中都会默认有 变长字段长度列表（2 字节）和 NULL 值列表 （1 字节），所以每一行记录都会默认空出 3 个字节，用户存储变长字段和 NULL 值的标识。

上述我们采用的是 8.0.26 默认的字符集 utf8mb4，下面我们验证一下指定字符集采用 utf8。

• **utf8 字符集：**1 个字符等于 3 个字节

根据上述所知要预留 3 个字节，65535 - 3 = 65532，65532 / 3 = 21844。

也就是说字符集 utf8 字段的最大长度限制为 21844。

那我们假设长度为 21845，创建表 varchar_size_demo1

-- utf8字符集，1个字符等于3个字节
create table varchar_size_demo1 (
  c varchar(21845)
)charset=utf8;

创建报错，显示字段过长。

那我们指定字段长度为 21884再次创建：

-- utf8字符集，1个字符等于3个字节
CREATE TABLE varchar_size_demo1 (
  c VARCHAR(21844)
)CHARSET=utf8;

创建成功，那就说明我们上述的逻辑是对的。

再指定字符集为 ASCII创建表 varchar_size_demo2：

• **ascii 字符集：**1 个字符等于 1个字节

预留 3 个自己，那字段长度最大为 65532，如果指定长度为 65533看下效果：

-- ascii字符集，1个字符等于1个字节
create table varchar_size_demo2 (
  c varchar(65533)
)charset=ascii;

创建失败，将字段长度改为 65532再次创建：

-- ascii字符集，1个字符等于1个字节
create table varchar_size_demo2 (
  c varchar(65532)
)charset=ascii;

OK 创建成功，撒花！！！

行溢出

根据上文所说的单个字段的最大长度根据不同的字符集，会有不同的限制，8.0.26 默认采用 utf8mb4字符集

• **utf8mb4 字符集：**1 个字符等于 4 个字节

varchar 类型最大为 65535 个字节，预留 3 个字节，一个 varchar 字段最大的容量为 65533 字节，而 InnoDB 的一个数据页的大小为 16KB，16 * 1024 = 16384个字节，一个 varchar 的容量远远大于一个数据页的大小，这样就可能出现一个页存不下一行记录，这种现象成为 行溢出。

在 Compact 和 Redundant 行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据（768 个前缀字节），把剩余的数据分散存储在其他的页中，这叫作 分页存储。

然后记录的真实数据处用 20 个字节存储指向这些分散页的地址（这 20 个字节中还包括存储了分散在各个页中的真实数据占用的字节数），从而可以找打剩余数据所在的页，这称为页的扩展，如下图所示：

Dynamic__Compressed__589">Dynamic 和 Compressed 行格式

在 MySQL8.0 中，默认的行格式为 Dynamic，Dynamic 和 Compressed 这两种行格式和 Compact 行格式类似，只不过在处理行溢出数据时方式不同，区别如下：

• Compact 和 Redundant 两种行格式会在记录的真实数据处存储一部分数据（768 个前缀字节）。
• Dynamic 和 Compressed 两种行格式对于存放在 Blob 中的数据采用了完全的行溢出存储方式。如下图所示，如果一行记录数据溢出了，在数据页中只存储 20 个字节的指针地址（存储真实数据的溢出页的地址），实际的数据都存储在 Off Page（溢出页）中。

Compressed 和 Dynamic 是什么区别呢？

Compressed 是在 Dynamic 的基础上优化了一层，存储在其中的行数据会以 zlib 算法进行压缩存储，因此对于 Blob、Text、Varchar 这类大长度类型的数据能够进行非常有效的存储。

Redundant 行格式

Redundant 是 MySQL5.0 版本之前 InnoDB 的行记录存储格式，MySQL 5.0 支持 Redundant 是为了兼容之前版本的页格式。

比如直接修改表的行格式为 Redundant：

alter table record_test_table row_rormat=Redundant;

Redundant 行格式存储格式如下所示：

对比 Compact 行格式主要有两大处不同：

• Compact 是 变长字段长度 列表，Redundant 是 字段长度偏移 列表
• Compact 有 NULL 值列表，Redundant 没有 NULL 值列表

字段长度偏移列表

为什么说 Redundant 行格式会有冗余说法？

因为 Redundant 行格式的字段长度便宜列表会将该行记录中所有列（包括隐藏列）的长度信息都按照逆序存储起来。

偏移 两字，意味着 Redundant 行格式计算列值的长度的方式不想 Compact 行格式那么直观，它是采用两个相邻数值的差值来计算各个列值的长度。

比如第一行记录的字段长度偏移列表（逆序）是：

• 2B 25 1F 1B 13 0C 06

因为它是按照逆序排列的，所以按照顺序排列就是：

• 06 0C 13 1B 1F 25 2B

可以看出有三个隐藏列和四个字段列。

按照两个相邻数值的差值来计算各个字段列值的长度的如下表所示：

列名	十六进制字节数	十进制字节数
row_id	0x06	6
transaction_id	0x0C - 0x06	6
roll_pointer	0x13 - 0x0C	7
col1	0x1B - 0x13	8
col2	0x1F - 0x1B	4
col3	0x25 - 0x1F	6
col4	0x2B - 0x25	6

记录头信息（record header）

不同于 Compact 行格式，Redundant 行格式中的记录头信息固定占用 6 个字节（48 位），每位的含义如下：

与 Compact 行格式的记录头信息对比来看，有两处不同：

• Redundant 行格式多了 n_field和 1byte_offs_flag这两个属性
• Redundant 行格式没有 record_type这个属性

其中两个属性的含义：

• n_field代表一行中列的数量，占用 10 位，所以 MySQL5.0 之前的版本最多只能包含 1023 个列。

• 1byte_offs_flags该属性定义了字段长度偏移列表占用 1 个字节，还是 2 个字节。

  • 当值为 1 时，表示占用 1 个字节；
  • 当值为 2 时，表示占用 2 个字节。

小结

到这我们就把 MySQL 的行格式了解的差不多了，当然更底层的知识点我们也用不到，也不会去用它，了解到这个层面其实在工作中也已经足够用了。

本文内容总结借鉴于康师傅的 MySQL 视频课:https://www.bilibili.com/video/BV1iq4y1u7vj

---

一起学编程，让生活更随和！

如果你觉得是个同道中人，欢迎关注博主gzh：【随和的皮蛋桑】。

专注于Java基础、进阶、面试以及计算机基础知识分享🐳。偶尔认知思考、日常水文🐌。

---