MySQL进阶之（四）InnoDB数据存储结构之行格式

EijoaHoaiQ:D

于 2024-03-06 22:54:51 发布

阅读量911

点赞数 13

分类专栏： MySQL进阶文章标签：行格式 Dynamic Compact REDUNDANT COMPRESSED

本文链接：https://blog.csdn.net/qq_50994235/article/details/136356658

版权

MySQL进阶专栏收录该内容

8 篇文章 1 订阅

订阅专栏

四、InnoDB数据存储结构之行格式

4.1 行格式的语法
4.2 COMPACT 行格式
4.3 Dynamic 和 Compressed 行格式
4.4 Redundant 行格式

我们平时的数据都是以行记录为单位向表中插入数据的，这些记录在磁盘上的存放形式也被称为 行格式或者 记录格式。设计 InnoDB 存储引擎的大叔到现在为止设计了 4 中不同类型的行格式，分别是 COMPACT、REDUNDANT、DYNAMIC 和 COMPRESSED。随着时间的推移，它们可能会设计出更多的行格式，但是不管怎么变，这些行格式在原理上大体都是相同的。
——摘自《MySQL 是怎样运行的》

4.1 行格式的语法

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

# 创建表时指定行格式
CREATE TABLE 表名(列的信息) ROW_FORMAT = 行格式名称
# 修改表的行格式
ALTER TABLE 表名 ROW_FORMAT = 行格式名称

4.2 COMPACT 行格式

COMPACT 表示紧凑的，在 MySQL 5.1 版本中，默认设置为 COMPACT 行格式。

一条完整的记录可以分为记录的额外信息和记录的真实数据两大部分：
在这里插入图片描述

4.2.1 记录的额外信息

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

01、变长字段长度列表

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

也就是说，这些变长字段占用的存储空间分为两部分：

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

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

比如，创建一张表：

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);

因为 record_test_table 表中 col1、col2、col4 列都是 varchar(8) 类型的，所以这三个列的值的长度都会被存储在记录开头处。由于使用的是 ascii 字符集，所以每个字符只需要 1 个字节来进行编码：
在这里插入图片描述
又因为这些长度值需要按照列的顺序逆序存放，所以最后变长字段长度列表的字节串用十六进制表示的效果就是：06 04 08。

在这里插入图片描述

小贴士：并不是所有记录都有这个变长字段长度列表部分，如果表中所有的列都不是变长的数据类型或者所有列的值都是 NULL 的话，就不需要有变长字段长度列表（不冗余存储，节省存储空间）了。

02、NULL 值列表

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

为什么要定义 NULL 值列表呢？

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

⭐ 规定

将每个允许存储 NULL 的列对应一个二进制位，二进制位按照列的顺序逆序存放，格式如下：

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

举个例子：字段 a、b、c，其中 a 是主键，在某一行中存储的数依次是 a = 1，b = null，c = 2。那么 COMPACT 行格式中的 NULL 值列表中存储的是：01。第一个 0 表示 c 的值不为 null，第二个 1 表示 b 是 null。这里需要注意以下，之所以没有 a 的值，是因为数据库会自动跳过主键，因为主键肯定是非 null 且唯一的，在 null 值列表的数据中就会自动跳过主键。

record_test_table 的两条记录的 NULL 值列表为：

# 这里是上文中插入的两条数据
insert into record_test_table(col1, col2, col3, col4)
values
('zhangsan', 'lisi', 'wangwu', 'songhk'),
('tong', 'chen', NULL, NULL);

在这里插入图片描述

这样我们就可以回答问题了：MySQL 中的 NULL 值是怎么存储的？
答：NULL 值是由 NULL 值列表记录的，用列的二进制位逆序表示每行记录中的每一列是否为 NULL 值，0 代表不为 NULL，1 代表为 NULL 值。

假设现有一张表，其中有 4 个字段：col1、col2、col3、col4。向其插入一条记录：‘a’, NULL, NULL, ‘dd’，那么 NULL 值列表使用二进制位表示为：0 1 1 0，转化成十进制就是 06。

03、记录头信息

除了变长字段长度列表、NULL 值列表之外，还有一个记录头信息的部分，它是由固定的 5 字节组成，用于描述一些属性的。5 个字节也就是 40 个二进制位，不同的位代表不同的属性：
在这里插入图片描述

其中，重点来说几个属性。

属性一、delete_mask（删除标记）

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

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

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

这些被删除的记录不会从磁盘上移除，是因为一旦移除，其他的记录还需要在磁盘上重新排列，这会带来性能消耗。

尤其是对聚簇索引的叶子节点来说，假设移除的是主键值为 1 的记录，那么整个聚簇索引的叶子节点都会因为这一条记录的删除全部重新排序，显然这样是不合适的。

所以，只是将这些删除的记录打一个删除标记，以区分正常记录和被删除的记录，所有被删除的记录会组成一个垃圾链表，它们所占用的空间被称为可重用空间，之后如果有新记录插入到表中时，可能会覆盖掉（复用）被删除的记录占用的存储空间。

这里需要注意：将 delete_flag 属性设置为 1 和将被删除的记录加入到垃圾链表中其实是分为两个阶段。

属性二、min_rec_mask（非叶子节点最小记录标记）

B+ 树的每层非叶子节点中的最小记录都会添加该标记，min_rec_mask 值为 1。如果我们自己插入的四条记录的 min_rec_mask 值都是 0，意味着它们都不是 B+ 树的非叶子节点中的最小记录。

属性三、record_type（记录类型）

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

0：普通记录
1：B+ 树非叶节点记录
2：最小记录
3：最大记录

再回过头来看：数据页结构和索引结构就可以理解当时的图中为什么表示的 record_type 值不一样了。

heap_no（在页中的相对位置）

这个属性表示当前记录在本页中的位置（设计 InnoDB 的大叔把记录一条一条亲密无间排列的结构称为堆，这个属性也表示在堆中的相对位置）。

其中，值为 0 和 1 的记录分别代表最小和最大记录，这两条记录并不是我们自己插入的，所以有时候也称为伪记录或虚拟记录。

n_owned（每行记录数）

页目录中会将所有的记录分成若干个组，每个组中的最后一条记录的头信息中会存储该组一共有多少条记录，来作为 n_owned 字段的值，而其他记录的 n_owned 值都是 0。

next_record（记录的相对位置）

它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。

假设第一条记录的 next_record 值为 32，意味着从第一条记录的真实数据的地址处向后找 32 个字节就是下一条记录的真实数据。

注意：下一条记录指得并不是按照我们插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。

并且，InnoDB 底层规定 Infimum 记录（最小记录）的下一条记录就是当前页中主键值最小的记录，而当前页中主键值最大的记录指向的下一条记录就是 Supremum 记录（最大记录）。用箭头指向代替地址偏移量来表示 next_record：
在这里插入图片描述
下面来分别演示一下删除一条记录的操作和增加一条记录的操作。

⭐ 删除一条记录的操作

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

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

删除后，整个链表也会跟着变化：

把第二条记录的 delete_mask 值设置为 1，而并没有从存储空间中移除；
把第二条记录的 next_record 值设置为 0，意味着没有下一条记录了；
第一条记录的 next_record 指向第三条记录；
最大记录的 n_owned 值从 5 变成了 4。

⭐ 增加一条记录的操作

在 “删除一条记录的操作” 中，主键值为 2 的记录被删除了（变成了垃圾链表），但是存储空间并没有被回收。现在要把这条数据再次插入：

# 新增主键值为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。

所以，不论我们怎么对页中的记录做增删改操作，InnoDB 始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。

4.2.2 记录的真实数据

记录的真实数据，除了我们自定义的列的数据之外，MySQL 会为每个记录默认地添加一些列（隐藏列）：

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

为了方便看，就把它们都写成小写的：row_id、transaction_id、roll_pointer。

在 InnoDB 表中，InnoDB 表的主键生成策略是这样的：

优先使用用户自定义的主键作为主键；
如果用户没有定义主键，则选取一个不允许存储 NULL 值的 UNIQUE 键作为主键；
如果表中连不允许存储 NULL 值的 UNIQUE 键都没有定义，则会为表默认添加一个名为 row_id 的隐藏列作为主键。

举个例子：

现创建一张表 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.idb 的位置，用 notepad++ dakai，建议安装一个解析插件，将乱码文件解析成十进制的数据格式：
在这里插入图片描述

解析第一行记录如下：
由于 col3 列是定长，所以不计入变长字段长度列表中。

第二行的数据同第一行，这里就不一一列举了。

第三行的数据中有 NULL 值，所以在存储上与第一行、第二行有些差异：
在这里插入图片描述

至于 transaction_id 和 roll_pointer，暂时还没学到哩，等学到时候会做笔记的呀~

4.3 Dynamic 和 Compressed 行格式

4.3.1 字段的长度限制

char 与 varchar 的区别如下：
在这里插入图片描述
也就是说，一个 varchar 类型的字段，最大容量为 65535 个字节。

现在已经存在了一张表，分别查看 MySQL 8.0.26 版本和 MySQL 5.7.34 版本的默认字符集：
在这里插入图片描述

由此可见，MySQL 8.0.26 字符集默认采用 utf8mb4，MySQL 5.7.34 字符集默认采用 utf8。这两者的区别就是：

utf8：使用 3 个字节表示字符；
utf8mb4：使用 4 个字节表示字符，可以存储一些 emoji 表情等。

说明：统一采用 MySQL 8.0.26 版本来进行接下来的操作。

⭐ 采用默认字符集 utf8mb4
在这里插入图片描述

报错提示：字段长度最大不能超过 16383，因为 8.0.26 版本默认字符集为 utf8mb4，也就是说，一个字符等于 4 个字节，那么，16383 * 4 = 65532，还差 3 个字节才等于 65535。反过来，65535 除以 4 结果等于 1633.75，由于字段长度不能带小数，将其四舍五入改为 16384：
在这里插入图片描述
显然还是不能超过 16383 的，所以字符集 utf8mb4 允许的最大长度为 16382。

那么，那 3 个字节跑哪里了呢？

16383 * 4 = 65532，65535 - 65532 = 3。实际上，每一行记录除了存储真实数据之外，还有记录的额外信息中默认会有变长字段长度列表（2 字节）和 NULL 值列表（1 字节）。所以，如果该 varchar 类型的的列没有 NOT NULL 属性，每一行记录都会默认空出来 3 个字节：存储变长字段长度列表和 NULL 值的标识，实际最多只能存储 65532 个字节的数据。

⭐ 采用字符集 utf8

由上文可知，需要预留 3 个字节：65535 - 3 = 65532，65532 / 3 = 21844。

重复上述验证步骤：
在这里插入图片描述
由此可知，字符集 utf8 字段的最大长度限制为 21844。

⭐ 采用 ascii 字符集

由上述推断可知，ascii 字符集允许存储字段的最大长度为 65532。

4.3.2 行溢出

根据上文所说的单个字段的最大长度根据不同的字符集，会有不同的限制，8.0.26 默认采用 utf8mb4字符集（4 个字节表示一个字符），最大容量为 65532 字节。

而 InnoDB 中一个数据页的大小是 16 KB，16 * 1024 = 16386 个字节，也就是说，一个 varchar 的容量远远大于一个数据页的大小，这样就可能出现一个页存储不下一行记录的情况，这种情况就叫做行溢出。

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

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

4.3.3 Dynamic 和 Compressed 行格式

在MySQL 8.0中，默认行格式就是 Dynamic，Dynamic、Compressed 行格式和 Compact 行格式类似，只不过在处理行溢出数据时方式不同：

Compact 和 Redundant 两种格式会在记录的真实数据处存储一部分数据（存放768个前缀字节）。
Compressed 和 Dynamic 两种行格式不会在记录的真实数据处存储列真实数据的前 768 字节，而是把所有的数据都存储到溢出页中，只在记录的真实数据处存储指向这些溢出页的地址(20 字节），实际的数据都存放在 Off Page（溢出页）中:

Compressed 和 Dynamic 行格式的区别：Compressed 行格式在 Dynamic 的基础上优化了一层，存储在其中的行数据会以 zlib 的算法进行压缩，因此对于 BLOB、TEXT、VARCHAR 这类大长度类型的数据能够进行非常有效的存储。

4.4 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 值列表。

4.4.1 字段长度偏移列表

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

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

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

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

2B 25 1F 1B 13 0C 06

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

06 0C 13 1B 1F 25 2B

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

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

在这里插入图片描述

4.4.2 记录头信息（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 个字节（使用 127 作为分界点是因为：127 二进制表示为 01111111，第一位为 NULL 比特位，用来标记是否为 NULL）。
当记录的真实数据占用的字节数不大于 127 时，占用 1 字节；
当记录的真实数据占用的字节数大于 127，但不大于 32767 时，占用 2 字节；
当记录的真实数据大于 32767 时，这部分的数据被存放到溢出页中，使用 2 字节来存储梅格列对应的偏移量。