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

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

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

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

本文内容基于 MySQL 8.0.29 源码。

目录

[TOC]

正文

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

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 语句的,这时,建表就会失败,并且报如下错误:

11118 - Row size too large (> 8126).
2Changing some columns to TEXT or BLOB may help.
3In 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 记录,溢出字段的全部内容都存放到溢出页。




欢迎扫码关注公众号,我们一起学习更多 MySQL 知识: