关于mysql:MySQL原理-InnoDB引擎-行记录存储-Offpage-列

3次阅读

共计 5614 个字符，预计需要花费 15 分钟才能阅读完成。

本文基于 MySQL 8

在后面的两篇文章，咱们剖析了 MySQL InnoDB 引擎的两种行记录存储格局：

Compact 格局
Redundant 格局

在这里简略总结下：

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 行格局中比拟长的列，只有 前 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 列存储一样，没有非凡的：

所有字段长度列表（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 页，其构造如下：

首先是数据列

所有字段长度列表（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 字节，它的构造是：

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

数据列 large_content（残余的 7332 字节）：61 7a ......

当字段再长一些呢，超过一页内数据的限度的时候呢？咱们来看第三行数据结构：

能够看出，过长的数据列，会以链表链接的模式存储在 overflow 页上。

由此可见 Redundant 行格局中，off-page 的构造其实是：

这样咱们会联想到三个问题：

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

首先咱们晓得一点，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 列。

overflow 页和表数据不同，不通过 B+ 树组织数据，同时不会做简单搜寻，它就是一个链表。所以咱们只有 保证数据大小不超过一页即可，即：

$$
overflow 页数据节点大小 \lt 16384
$$

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

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

Compact 中对于 off-page 的解决与 Redundant 根本一样，只是因为数据结构不一样：

导致列会变成 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 列解决和 Compact 不同以外，其余的根本和 Compact 一样。

Dynamic 对于 off-page 列解决的次要区别在于，所有的数据都存储在 overflow 页下面，在 off-page 列只存储 20 字节指针，这个指针的构造和 Redundant 格局中的 20 字节指针一样：

Compressed 行格局和 Dynamic 基本一致，包含对于 off-page 列解决，其实就是在 Dynamic 的根底上，减少了压缩解决。对于压缩解决，会在前面的压缩页章节详细分析。

微信搜寻“我的编程喵”关注公众号，每日一刷，轻松晋升技术，斩获各种 offer

正文完

mysql

发表至： mysql

2021-07-07

0

关于mysql:day4-体育馆的人流

关于mysql:关于MySQL参数这些你要知道

关于mysql:JNPF零代码开发平台之快速开发web表单页面

关于mysql:端到端的实时计算TiDB-Flink-最佳实践

关于后端:广州小公司你是熟悉Java-List集合是吧

关于mysql:MySQL原理-InnoDB引擎-行记录存储-Offpage-列

Redundant 中 off-page 列解决

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

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

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

Compact 中 off-page 列解决

Dynamic 中 off-page 列解决

Compressed 中 off-page 列解决

站内搜索