MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

本文基于 MySQL 8

在前面的两篇文章,我们分析了 MySQL InnoDB 引擎的两种行记录存储格式:

在这里简单总结下:

  • Compact 格式结构:
    • 变长字段长度表:包括数据不为NULL的每个可变长度字段的长度,并按照列的顺序逆序排列
    • NULL 值列表:针对可以为 NULL 的字段,用一个 BitMap 来标识哪些字段为 NULL
    • 记录头信息:固定 5 字节,包括:
      • 无用位:2 bits,目前没用
      • deleted_flag:1 bits,标识记录是否被删除
      • min_rec_flag:1 bits,是否是 B+ 树中非叶子节点最小记录标记
      • n_owned:4 bits,记录对应的 slot 中拥有的记录数量
      • heap_no:13 bits,该记录在堆中的序号,也可以理解为在堆中的位置信息
      • record_type:3 bits,记录类型,普通数据记录为000,节点指针类型为 001,伪记录首记录 infimum 行为 010,伪记录最后一个记录 supremum 行为 011,1xx 的为保留的
      • next_record 指针:16 bits,页中下一条记录的相对位置
    • 隐藏列
      • DB_ROW_ID:6 字节,这个列不一定会生成。优先使用用户自定义主键作为主键,如果用户没有定义主键,则选取一个 Unique 键作为主键,如果表中连 Unique 键都没有定义的话,则会为表默认添加一个名为 DB_ROW_ID 的隐藏列作为主键
      • DB_TRX_ID:6 字节,产生当前记录项的事务 id,每开始一个新的事务时,系统版本号会自动递增,而事务开始时刻的系统版本号会作为事务 id,事务 commit 的话,就会更新这里的 DB_TRX_ID
      • DB_ROLL_PTR:7 字节,undo log 指针,指向当前记录项的 undo log,找之前版本的数据需通过此指针。如果事务回滚的话,则从 undo Log 中把原始值读取出来再放到记录中去
    • 数据列
      • bigint:如果不为 NULL,则占用8字节,首位为符号位,剩余位存储数字,数字范围是 -2^63 ~ 2^63 - 1 = -9223372036854775808 ~ 9223372036854775807。如果为 NULL,则不占用任何存储空间
      • double:非 NULL 的列,符合 IEEE 754 floating-point "double format" bit layout 这个统一标准,如果为 NULL,则不占用任何存储空间
      • 对于定长字段,不需要存长度信息直接存储数据即可如果不足设定的长度则补充。例如 char 类型,补充 0x20, 对应的就是空格。
      • varchar 存储:因为数据开头有可变长度字段长度列表,所以 varchar 只需要保存实际的数据即可,不需要填充额外的数据。但是我们还没有考虑存储特别长数据的情况
  • Redundant 格式结构与 Compact 格式的区别:
    • 所有字段长度列表:不同于 Compact 行格式,Redundant 的开头是所有字段长度列表:记录所有字段的长度偏移,包括隐藏列。偏移就是,第一个字段长度为 a,第二个字段长度为 b,那么列表中第一个字段就是 a,第二个字段就是 a + b。所有字段倒序排列
    • 记录头信息:固定 6 字节
      • 无用位:2 bits,目前没用
      • deleted_flag:1 bits,标识记录是否被删除
      • min_rec_flag:1 bits,是否是 B+ 树中非叶子节点最小记录标记
      • n_owned:4 bits,记录对应的 slot 中拥有的记录数量
      • heap_no:13 bits,该记录在堆中的序号,也可以理解为在堆中的位置信息
      • n_field:10 bits,该记录的列数量,范围从1到1023
      • 1byte_offs_flag:1 bit,1 代表每个字段长度的存储为 1 字节,0 代表 2 字节
      • next_record 指针:16 bits,页中下一条记录的相对位置
    • 数据列
      • CHAR 类型存储:无论字段是否为 NULL,或者长度是多少,char(M) 都会占用 M * 字节编码最大长度那么多字节。为 NULL 的话,填充的是 0x00,不为 NULL,长度不够的情况下,末尾补充 0x20.

之前并没有分析当字段比较长的时候会怎么存储,在本篇文章会详细分析。

在此再回顾下之前提到的。因为每条数据都是一个硬盘寻址读取,我们要减少这个硬盘寻址读取的次数,可以考虑一块一块的读取数据,这样,我们很可能下次请求需要的数据就已经在内存中了,就省去了从硬盘读取。基于这个思想,InnoDB 将一个表的数据划分成了若干pages),这些页通过 B-Tree 索引联系起来。每一页大小默认为 16384 Bytes 也就是 16KB(配置为 innodb_page_size)。

对于比较大的字段,例如 Text 类型的字段,如果也存在于这个聚簇索引上,那这个节点数据就会过大,会一下子读取很多页出来,这样读取效率会降低(例如在我们没有想读取这个 Text 列的请求情况下)。所以,InnoDB 对于比较长的变长字段,一般倾向于将他们存储在其他地方,这就涉及到了 Off-page 列的设计模式。不同的 行格式 处理不同。

在开始讨论不同的 行格式 的处理之前,我们先回顾一下 InnoDB 的页大小,InnoDB是一个持久化的存储引擎,也就是数据都是保存在磁盘上面的。但是读写数据,对数据处理,这些是发生在内存中。也就是数据需要从磁盘读取到内存。那么这个读取是如何读取呢?如果处理哪条数据,就读取哪一条到内存中,这样效率也太低了。因为每条数据都是一个硬盘寻址读取,我们要减少这个硬盘寻址读取的次数,可以考虑一块一块的读取数据,这样,我们很可能下次请求需要的数据就已经在内存中了,就省去了从硬盘读取。基于这个思想,InnoDB 将一个表的数据划分成了若干页(pages),这些页通过 B-Tree 索引联系起来。每一页大小默认为 16384 Bytes 也就是 16KB(配置为 innodb_page_size)。在 MySQL 启动的时候可以修改,只能是 4096,8192,16384 其中的一个。

Redundant 中 off-page 列处理

对于 Redundant 行格式中比较长的列,只有前 768 字节会被存储在数据行上,剩下的数据会被放入其他页。我们来看一个实例,运行以下 SQL,创建一个测试表,插入测试数据:

drop table if exists long_column_test;
CREATE TABLE `long_column_test` (
`large_content` varchar(32768) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=REDUNDANT;

##长度为 768 字节
insert into long_column_test values (repeat("az", 384));
##长度为 8100 字节
insert into long_column_test values (repeat("az", 4050));
##长度为 32768 字节
insert into long_column_test values (repeat("az", 16384));

我们使用 64 进制编码器查看表文件 long_column_test.ibd,可以看到第一条数据是一条正常的数据,其存储和之前我们讲的 Redundant 列存储一样,没有特殊的:

MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

所有字段长度列表(8字节,4列,一个数据列,三个隐藏列):03 13(768+7+6+6),00 13(7+6+6),00 0c(6+6), 00 06(6)
记录头(6字节):00 00 10 08 03 ac
隐藏列 DB_ROW_ID(6字节):00 00 00 00 02 22 
隐藏列 DB_TRX_ID(6字节):00 00 00 00 58 b7
隐藏列 DB_ROLL_PTR(7字节):82 00 00 01 0c 01 10 
数据列 large_content(768字节):61 7a ......

对于第二行,我们发现这一行的 large_content 列的数据并没有完全存储在这一行,而是一部分存储在这一行,另一部分存储在了其他地方,这种列就被称为 off-page 列,存储到的其他地方被称为 overflow 页,其结构如下:
MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

首先是数据列

所有字段长度列表(8字节,4列,一个数据列,三个隐藏列):43 27(第一字节的头两位不代表长度,最高位还是标记字段是否为NULL,第二位标记这条记录是否在同一页,由于不为 NULL,所以最高位为 0,由于存在 overflow 页所以不在同一页,所以第二位为1,后面的 3 27 代表长度,即 20+768+7+6+6),00 13(7+6+6),00 0c(6+6), 00 06(6)
记录头(6字节):00 00 10 08 03 ac
隐藏列 DB_ROW_ID(6字节):00 00 00 00 02 22 
隐藏列 DB_TRX_ID(6字节):00 00 00 00 58 b7
隐藏列 DB_ROLL_PTR(7字节):82 00 00 01 0c 01 10 
数据列 large_content(768字节):61 7a ......
指向剩余数据所在地址的指针(20字节):00 00 05 23 00 00 00 05 00 00 00 01 00 00 00 00 00 00 1c a4

对于 off-page 列,列数据末尾会存在指向剩余数据所在地址的指针,这个指针占用 20 字节,它的结构是:

MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

然后是列剩下的数据存储到的 overflow 页

数据列 large_content(剩余的 7332 字节):61 7a ......

当字段再长一些呢,超过一页内数据的限制的时候呢?我们来看第三行数据结构:

MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

可以看出,过长的数据列,会以链表链接的形式存储在 overflow 页上。

由此可见 Redundant 行格式中,off-page 的结构其实是:
MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

这样我们会联想到三个问题:

  1. 什么时候列会变成 off-page 列?
  2. 什么时候 overflow 页会分成一个个链表节点存储?
  3. 对于哪些列类型会这么存储?

1. 什么时候列会变成 off-page 列?

首先我们知道一点,innodb 引擎的页大小默认是 16KB,也就是 16384 字节,而且 innodb 的数据是按页加载的。然后,组织 innoDB 引擎数据的数据结构是 B+ 树。扫描 B+ 树寻找数据,也是一页一页加载搜索的。如果一页内能包含的数据行越多,那么很明显,搜索效率越高。但是如果一页中只有一条数据,那么这个 B+ 树其实和链表的效率差不多了。所以,为了效率,需要保证一页内至少有两条数据。所以有:

\[2 * 行数据大小 \lt 16384 \rightarrow 行数据大小 \lt 8192 \]

同时,一行数据并不是只有列数据,还有隐藏列,记录头,列长度列表等等,并且,innoDB 页也有自己的一些元数据(占用 132 字节,我们在以后的章节会详细分析),在这里我们拿 long_column_test 作为例子,则有:

\[page 元数据大小 + 2 * `long\_column\_test` 行数据大小 \lt 16384 \rightarrow 132 + 2 * (字段长度列表长度 + 记录头长度 + 三个隐藏列长度 + large\_content 长度) \lt 16384 \]

可以推导出:

\[large\_content 长度 \lt 8093 \]

在实际使用中,可能不止一列数据比较长。还有,由于数据不存储在行数据一起,搜索读取效率会比较低,所以,redundant 行格式会尽可能不把列变为 off-page 列,并尽量少的将列变为 off-page 列。

2. 什么时候 overflow 页会分成一个个链表节点存储?

overflow 页和表数据不同,不通过 B+ 树组织数据,同时不会做复杂搜索,它就是一个链表。所以我们只要保证数据大小不超过一页即可,即:

\[overflow 页数据节点大小 \lt 16384 \]

这个数据节点也是有一些额外信息的,同时,页也是有自己的额外信息的,这些会在之后的文章中看到。所以,真正承载的数据大小,会需要刨除这些额外信息,也就是小于 16384。如果不够,就会分成多页存储,这些节点会通过一个链表链接起来。

3. 对于哪些列类型会这么存储?

对于可变长度字段,例如 varchar,varbinary,text,blob 等,会利用这种机制存储。对于定长字段,例如 char,如果超长,也会像 varchar 一样存储,在这种情况下,char 末尾就不会填充空白字符了。但是这种情况不常见,char 最长只能 255 个字符,字符编码必须是大于三字节的时候,才会大于 768,例如 uf8mb4 并且每个字符都是大于 3 字节的字符。

Compact 中 off-page 列处理

Compact 中对于 off-page 的处理与 Redundant 基本一样,只是由于数据结构不一样:
MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

导致列会变成 off-page 列的临界点不一样,在这里我们拿 long_column_test 作为例子,则有:

\[page 元数据大小 + 2 * `long\_column\_test` 行数据大小 \lt 16384 \rightarrow 132 + 2 * (变长长度列表 2 字节 + NULL 值列表 1 字节 + 记录头长度 5 字节 + 三个隐藏列长度(6+6+7 字节) + large\_content 长度) \lt 16384 \]

可以推导出:

\[large\_content 长度 \lt 8099 \]

Dynamic 中 off-page 列处理

Dynamic 除了 off-page 列处理和 Compact 不同以外,其他的基本和 Compact 一样

Dynamic 对于 off-page 列处理的主要区别在于,所有的数据都存储在 overflow 页上面,在 off-page 列只存储 20 字节指针,这个指针的结构和 Redundant 格式中的 20 字节指针一样:
MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

Compressed 中 off-page 列处理

Compressed 行格式和 Dynamic 基本一致,包括对于 off-page 列处理,其实就是在 Dynamic 的基础上,增加了压缩处理。对于压缩处理,会在后面的压缩页章节详细分析。

微信搜索“我的编程喵”关注公众号,每日一刷,轻松提升技术,斩获各种offer
MySQL原理 - InnoDB引擎 - 行记录存储 - Off-page 列

上一篇:宋宝华: 用off-cpu火焰图进行Linux性能分析


下一篇:MongoDB数据库配置文件详解