InnoDB 行超长时怎么选择溢出字段？

InnoDB 索引页的大小默认为 16K，然而，varchar、text、blob 类型的单个字段内容长度就有可能超过 16K，这种情况下，整个索引页都存不下一个字段的内容了。

解决这个问题的办法，是找到那些内容比较长的字段作为溢出字段，把它们的内容存放到溢出页中，减少留在索引页记录中的内容。

接下来，我们来聊聊 InnoDB 选择溢出字段的逻辑。

本文内容基于 MySQL 8.0.29 源码。

目录

• 1. 建表时的限制
• 2. 索引页长什么样？
• 3. 怎么判断行超长了？
• 4. 选择溢出字段的逻辑
• 5. 溢出页地址
• 6. 回答文章开头的问题
• 7. 总结

正文

进入正题之前，大家可以思考一个问题：一个表中每条记录的溢出字段都是一样的吗？

1 . 建表时的限制

单从字段数量看，MySQL 的 server 层限制一个表最多只能创建 1024 个字段。

InnoDB 则限制最多只能创建 1023 个字段，但是，如果我们创建表时，真要创建 1023 个字段，会很荣幸的收到这个错误：1117 - Too many columns。

因为 InnoDB 会往表中增加 2 ~ 3 个隐藏字段：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。

只有表中没有主键，并且在建表时也没有创建所有字段都不允许为 NULL 的唯一索引时，才会增加 DB_ROW_ID 字段。

创建表时，能定义 1023 - 3 = 1020 个字段吗？

依然不能，因为崩溃恢复过程中，解析 Redo 日志时，REDUNDANT 记录还会往表的内存对象（dict_table_t）中加入 3 个字段。

从以上介绍可知，InnoDB 需要保留 6 个字段自用，所以，我们建表时，最多能创建的字段数量是：1023 - 3 * 2 = 1017。

表中实际能创建多少个字段，除了受限于 server 层和存储引擎的字段数量限制，还会受到字段长度的限制。

创建表时，InnoDB 会问自己一个问题：

如果我放过这条 DDL 语句，让它建表成功，以后对这个表进行插入、更新操作时，有没有可能因为记录超长导致操作失败？

要回答这个问题，总不能凭空想象，随心而动吧？

所以，得有一个规则，要按规则办事，规则是这样的：

假定有资格被选择成为溢出字段的那些字段，都已经被当作溢出字段了，它们的字段内容都部分或全部存放到溢出页中了。

溢出字段内容是部分还是全部存放到溢出页，这取决于记录的格式。 REDUNDANT、COMPACT 记录只会把溢出字段的部分内容存放到溢出页。 DYNAMIC、COMPRESSED 记录会把溢出字段的全部内容存放到溢出页。

在这个规则之下，再来计算留在索引页中的记录内容长度，看看是不是还会超长？

如果还会超长，InnoDB 是不会放过这条 DDL 语句的，这时，建表就会失败，并且报如下错误：

1118 - Row size too large (> 8126).
Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

为啥判断超长的条件是大于 8126 字节呢？先别急，后面会有介绍。

如果不会超长，自然就会建表成功了。

2 . 索引页长什么样？

InnoDB 的索引页，不管是 B+ 树的非叶子结点，还是叶子结点，初始化完成之后，未插入记录之前，都包含以下几个部分：

• 38 字节的 File Header
• 56 字节的索引页头信息
• 13 字节的 Infimum 记录
• 13 字节的 Supremum 记录
• 2 字节的 Supremum Slot
• 2 字节的 Infimum Slot
• 8 字节的 File Trailer

总共占用 132 字节，如下图所示：

3 . 怎么判断行超长了？

通过上一小节，我们知道一个索引页初始化完成之后，会占用 132 字节的空间。

索引页默认大小为 16K，初始化之后，索引页中剩余空间为：16 * 1024 - 132 = 16252 字节。

InnoDB 规定：一个索引页中最少要存放 2 条记录。所以，索引页中一条记录的最大长度就是：16252 / 2 = 8126 字节。

插入或者更新记录时，如果插入记录的长度，或者更新之后记录的长度大于 8126 字节，就会选择记录中的部分字段作为溢出字段。

一条记录的长度为下几个部分的长度之和：

• 字段 NULL 标记区域，标记每个字段内容是否为 NULL，如果表中所有字段都定义为 NOT NULL，记录中没有此区域。
• 字段内容长度区域，存储每个变长字段的内容长度，如果表中所有字段都不是变长字段，记录中没有此区域。
• 记录的头信息，REDUNDANT 格式：6 字节；COMPACT、DYNAMIC、COMPRESSED 格式：5 字节。
• 用户字段内容。
• DB_ROW_ID，6 字节，创建表时，表中既没有主键，也没有创建所有字段都定义为 NOT NULL 的唯一索引时，InnoDB 才会添加这个列，作为表的主键。
• DB_TRX_ID，8 字节，最后修改记录的事务 ID。
• DB_ROLL_PTR，7 字节，指向上一个事务产生的 undo 日志。

4 . 选择溢出字段的逻辑

选择溢出字段环节可能会进行一轮或多轮循环，每轮循环从表中选择一个字段作为溢出字段，直到留在索引页中的记录长度小于等于 8126 字节，选择溢出字段环节也就结束了。

选择溢出字段时，有一些字段是会被排除在外的，命中以下规则的字段都不会被选为溢出字段：

• 主键字段。
• 固定长度字段（char、binary 字段除外）。
• 内容为 NULL 的字段。
• REDUNDANT、COMPACT 记录，字段内容长度 <= 788 字节。
• DYNAMIC、COMPRESSED 记录，字段内容长度 <= 40 字节，且字段类型是 BLOB、GEOMETRY、VAR_POINT。
• DYNAMIC、COMPRESSED 记录，字段内容长度 <= 255 字节，且字段类型不是 BLOB、GEOMETRY、VAR_POINT。

没有命中以上规则的字段，都有资格被选为溢出字段。

每轮循环都会遍历表中的所有字段，并根据以上规则，从有资格被选为溢出字段的那些字段中，找到内容最长的字段，就是溢出字段了。

5 . 溢出页地址

字段被选为溢出字段之后，该字段的部分或全部内容会存放到溢出页，然后，索引页记录中，该字段的末尾，会有一个 20 字节的区域，保存着溢出页地址。

20 字节的溢出页地址由以下 4 个部分构成：

• 表空间 ID，4 字节，溢出页所在表空间 ID。
• 页号，4 字节，第 1 个溢出页的页号。一个溢出页存不下字段的溢出内容时，会有多个溢出页，组成溢出页链表。
• 字段内容 Offset，4 字节，第 1 个溢出页中，字段内容在页中的 Offset。 根据是否启用了压缩页，字段内容在溢出页中的 Offset 会不一样，所以需要记下来。
• 溢出页内容长度，当前字段存放到溢出页中的内容长度，8 字节，实际只使用了最后 4 字节来存储溢出页的内容长度之和，如下图所示：

溢出字段留在索引页记录中的内容根据记录格式的不同而不同：

REDUNDANT、COMPACT 记录，溢出字段在索引页记录中的长度为 788 字节，由以下两部分组成：

• 768 字节的字段内容
• 20 字节的溢出页地址

溢出字段中 768 字节之后的内容，会存放到溢出页中。

DYNAMIC、COMPRESSED 记录，溢出字段的全部内容都会存放到溢出页中，索引页记录中只保存 20 字节的溢出页地址。

6 . 回答文章开头的问题

经过前面的介绍，相信大家对于本文开头的那个问题已经有了答案，回到问题：

问：一个表中每条记录的溢出字段都是一样的吗？

答：每条记录的溢出字段，可能一样，也可能不一样，记录中哪些字段会成为溢出字段，取决于每条记录中，所有有资格被选为溢出字段的内容长度。

7 . 总结

一条记录中，所有字段内容长度之和超过 8126 字节时，就会有部分字段被选择成为溢出字段。

选择溢出字段可能会进行多轮循环，每轮循环都会从有资格被选为溢出字段的那些字段中，选择内容最长的字段作为溢出字段，直到留在索引页中的记录长度小于等于 8126 字节。

REDUNDANT、COMPACT 记录，溢出字段内容的前 768 字节存放在索引页记录中，剩余内容存放到溢出页。

DYNAMIC、COMPRESSED 记录，溢出字段的全部内容都存放到溢出页。

本文由哈喽比特于2年以前收录，如有侵权请联系我们。
文章来源：https://mp.weixin.qq.com/s/_7IuF5N9W5BodiwcQ1UTBQ