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

发布于 2022-7-19 16:35
浏览
0收藏

作者 | 操盛春
来源 | 一树一溪(ID:lovespring52)
转载请联系授权(微信ID:csch52)

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

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

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

本文内容基于 MySQL 8.0.29 源码。

正文

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

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 字节,如下图所示:

InnoDB 行超长时怎么选择溢出字段?-开源基础软件社区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 字节来存储溢出页的内容长度之和,如下图所示:
    InnoDB 行超长时怎么选择溢出字段?-开源基础软件社区溢出字段留在索引页记录中的内容根据记录格式的不同而不同:

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

  • 768 字节的字段内容
  • 20 字节的溢出页地址
    溢出字段中 768 字节之后的内容,会存放到溢出页中。

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

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

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

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

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

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

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

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

标签
已于2022-7-19 16:35:17修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐