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

朴灿烈づ我的快乐病毒、 2022-10-13 12:50 84阅读 0赞

> 本文基于 MySQL 8

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

*  [Compact 格式][Compact]
 *  [Redundant 格式][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 中 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 列存储一样，没有特殊的：

![image][]

所有字段长度列表（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 页**，其结构如下：  
![image][image 1]

**首先是数据列**

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

![image][image 2]

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

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

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

![image][image 3]

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

由此可见 Redundant 行格式中，off-page 的结构其实是：  
![image][image 4]

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

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

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

首先我们知道一点，**innodb 引擎的页大小默认是 16KB**，也就是 16384 字节，而且 **innodb 的数据是按页加载的**。然后，**组织 innoDB 引擎数据的数据结构是 B+ 树**。扫描 B+ 树寻找数据，也是一页一页加载搜索的。如果一页内能包含的数据行越多，那么很明显，搜索效率越高。但是如果**一页中只有一条数据，那么这个 B+ 树其实和链表的效率差不多了**。所以，为了效率，需要保证**一页内至少有两条数据**。所以有：  
 2 ∗ 行 数 据 大 小 < 16384 → 行 数 据 大 小 < 8192 2 \* 行数据大小 \\lt 16384 \\rightarrow 行数据大小 \\lt 8192 2∗行数据大小<16384→行数据大小<8192  
同时，一行数据并不是只有列数据，还有隐藏列，记录头，列长度列表等等，并且，innoDB 页也有自己的一些元数据(占用 132 字节，我们在以后的章节会详细分析)，在这里我们拿 `long_column_test` 作为例子，则有：  
 p a g e 元 数 据 大 小 + 2 ∗ ‘ l o n g \_ c o l u m n \_ t e s t ‘ 行 数 据 大 小 < 16384 → 132 + 2 ∗ ( 字 段 长 度 列 表 长 度 + 记 录 头 长 度 + 三 个 隐 藏 列 长 度 + l a r g e \_ c o n t e n t 长 度 ) < 16384 page 元数据大小 + 2 \* \`long\\\_column\\\_test\` 行数据大小 \\lt 16384 \\rightarrow 132 + 2 \* (字段长度列表长度 + 记录头长度 + 三个隐藏列长度 + large\\\_content 长度) \\lt 16384 page元数据大小\+2∗‘long\_column\_test‘行数据大小<16384→132\+2∗(字段长度列表长度\+记录头长度\+三个隐藏列长度\+large\_content长度)<16384  
可以推导出：  
 l a r g e \_ c o n t e n t 长 度 < 8093 large\\\_content 长度 \\lt 8093 large\_content长度<8093

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

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

overflow 页和表数据不同，不通过 B+ 树组织数据，同时不会做复杂搜索，它就是一个链表。所以我们只要**保证数据大小不超过一页即可**，即：  
 o v e r f l o w 页 数 据 节 点 大 小 < 16384 overflow 页数据节点大小 \\lt 16384 overflow页数据节点大小<16384  
这个数据节点也是有一些额外信息的，同时，页也是有自己的额外信息的，这些会在之后的文章中看到。所以，真正承载的数据大小，会需要刨除这些额外信息，也就是小于 16384。如果不够，就会分成多页存储，这些节点会通过一个链表链接起来。

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

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

## Compact 中 off-page 列处理 ##

**Compact 中对于 off-page 的处理与 Redundant 基本一样**，只是由于数据结构不一样：  
![image][image 5]

导致列会变成 off-page 列的临界点不一样，在这里我们拿 `long_column_test` 作为例子，则有：  
 p a g e 元 数 据 大 小 + 2 ∗ ‘ l o n g \_ c o l u m n \_ t e s t ‘ 行 数 据 大 小 < 16384 → 132 + 2 ∗ ( 变 长 长 度 列 表 2 字 节 + N U L L 值 列 表 1 字 节 + 记 录 头 长 度 5 字 节 + 三 个 隐 藏 列 长 度 （ 6 + 6 + 7 字 节 ） + l a r g e \_ c o n t e n t 长 度 ) < 16384 page 元数据大小 + 2 \* \`long\\\_column\\\_test\` 行数据大小 \\lt 16384 \\rightarrow 132 + 2 \* (变长长度列表 2 字节 + NULL 值列表 1 字节 + 记录头长度 5 字节 + 三个隐藏列长度（6+6+7 字节） + large\\\_content 长度) \\lt 16384 page元数据大小\+2∗‘long\_column\_test‘行数据大小<16384→132\+2∗(变长长度列表2字节\+NULL值列表1字节\+记录头长度5字节\+三个隐藏列长度（6\+6\+7字节）\+large\_content长度)<16384  
可以推导出：  
 l a r g e \_ c o n t e n t 长 度 < 8099 large\\\_content 长度 \\lt 8099 large\_content长度<8099

## Dynamic 中 off-page 列处理 ##

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

Dynamic 对于 off-page 列处理的主要区别在于，**所有的数据都存储在 overflow 页上面，在 off-page 列只存储 20 字节指针**，这个指针的结构和 Redundant 格式中的 20 字节指针一样：  
![image][image 6]

## Compressed 中 off-page 列处理 ##

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

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

[Compact]: https://juejin.cn/post/6844903470860861454
[Redundant]: https://juejin.cn/post/6847902223817506829
[image]: /images/20221005/f591fd7de5c443468841f68efca0bfc3.png
[image 1]: /images/20221005/b36d15137b284aa6b642957732129c45.png
[image 2]: /images/20221005/8082d7f083c54463b9361a30a9846a3c.png
[image 3]: /images/20221005/52be4c1bd8f54a56887e1bddb39f99c8.png
[image 4]: /images/20221005/4c4f262ad0f24442b23288eff25a942f.png
[image 5]: /images/20221005/2001d5213a944ed3a1066b0f4c9549fd.png
[image 6]: /images/20221005/0d5fc5ecfbec4347898dfacb96d63c3a.png