InnoDB表聚集索引层高什么时候发生变化（1）

2021-10-31 08:45:51

导读

本文略长，主要解决以下几个疑问

1、聚集索引里都存储了什么宝贝2、什么时候索引层高会发生变化3、预留的1/16空闲空间做什么用的4、记录被删除后的空间能回收重复利用吗

1、背景信息

1.1 关于innodb_fill_factor

有个选项 innodb_fill_factor 用于定义InnoDB page的填充率，默认值是100，但其实最高只能填充约15KB的数据，因为InnoDB会预留1/16的空闲空间。在InnoDB文档中，有这么一段话

An innodb_fill_factor setting of 100 leaves 1/16 of the space in clustered index pages free for future index growth.

另外，文档中还有这样一段话

When new records are inserted into an InnoDB clustered index, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records. If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full.

上面这两段话，综合起来理解，就是

即便 innodb_fill_factor=100，也会预留1/16的空闲空间，用于现存记录长度扩展用
在最佳的顺序写入数据模式下，page填充率有可能可以达到15/16
在随机写入新数据模式下，page填充率约为 1/2 ~ 15/16
预留1/16这个规则，只针对聚集索引的叶子节点有效。对于聚集索引的非叶子节点以及辅助索引（叶子及非叶子）节点都没有这个规则
不过 innodb_fill_factor 选项对叶子节点及非叶子节点都有效，但对存储text/blob溢出列的page无效

1.2 关于innodb_ruby项目

innodb_ruby 项目是由Jeremy Cole 和 Davi Arnaut 两位大神开发的项目，可用于解析InnoDB数据结构，用ruby开发而成。他们还维护了另一个众所周知的项目叫 InnoDB Diagrams，相信稍微资深一点的MySQL DBA都应该知道这个项目。

1.3 关于innblock工具

由八怪开发，用于扫描和分析InnoDB page，详见 innblock | InnoDB page观察利器

1.4 阅读本文背景信息

需要假设您对InnoDB的数据结构已经有了一定了解，包括B+树、聚集索引、辅助索引，以及innodb page的一些简单结构。

如果不太肯定，请先阅读这些文档内容

Clustered and Secondary Indexes
The Physical Structure of an InnoDB Index
InnoDB Row Formats
InnoDB Record Structure
InnoDB Page Structure

2、测试验证：一层高的InnoDB表聚集索引，最多能存多少条数据

从上面我们知道，一个page最大约能存储15/16容量，扣掉用于存储page header、trailer信息，以及index header、File Segment Header、Infimum&Supremum（两条虚拟记录）等必要的固定消耗之后，实际大约只有15212字节可用于存储用户数据。

这样一来，我们就可以简单测算出一个page大约能存储多少条记录了。

本次用到的测试表，只有一个INT列，同时作为主键（建议横版观看，可左右滑动。或者复制链接到PC端打开观看，效果更佳。下同）

# MySQL的版本是Percona Server 5.7.22-22，我自己下载源码编译的
[root@yejr.me#] mysql -Smysql.sock innodb
...
Server version: 5.7.22-22-log Source distribution
...
[root@yejr.me]> \s
...
Server version:     5.7.22-22-log Source distribution

# 创建测试表
[root@yejr.me]> CREATE TABLE `t1` (
  `i` int(10) unsigned NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`i`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;

另外，我们知道每条记录都要几个额外存储的数据

DB_TRX_ID，6字节
DB_ROLL_PTR，7字节
Record Header，至少5字节（用上面这个测试表，只需要5字节，不同数据类型需要的header长度也不同，详见 浅析InnoDB Record Header及page overflow
因此，一条数据需要消耗 4(INT列) + 6 + 7 + 5 = 22字节
此外，大约每4条记录就需要一个directory slot，每个slot需要2字节
综上，假设可以存储N条记录，则 N*22 + N/4*2 = 15212，可求得N约等于676

接下来我们验证一下，往该表中持续插入 676 条数据

[root@yejr.me]> insert into t1 select 0;
...
# 逐次反复执行676次

然后，我们利用 innodb_ruby 工具查看其数据结构

2.1 查看聚集索引page结构

此时t1表的聚集索引树只有一层高，一个page即pageno=3

[root@yejr]# innodb_space -s ibdata1 -T innodb/t1 space-indexes

id   name     root fseg      fseg_id  used  allocated  fill_factor
128  PRIMARY  3    internal  1        1     1          100.00%
128  PRIMARY  3    leaf      2        0     0          0.00%

再用innblock工具扫描佐证一下

[root@yejr]# innblock innodb/t1.ibd scan 16
...
level0 total block is (1)
block_no:         3,level:   0|*|

2.2 查看其directory slot

可以看到170个slot，其中Infimum记录的owned=1，Supremum记录的owned=5

[root@yejr]# innodb_space -s ibdata1 -T innodb/t1 \
-p 3 page-directory-summary|grep -c -v slot

170

2.3 查看整个page的全览图

前面是一堆头信息

[root@yejr]# innodb_space -s ibdata1 -T innodb/t1 -p 3 page-illustrate

      Offset ╭────────────────────────────────────────────────────────────────╮
           0 │█████████████████████████████████████▋██████████████████████████│
          64 │█████████▋███████████████████▋████████████▋████████████▋████▋███│
# 大概从这里开始是第一条记录
         128 │█████████████▋████▋████████████████▋████▋████████████████▋████▋█│
         192 │███████████████▋████▋████████████████▋████▋████████████████▋████│
...
# 中间是用户数据
...
# 这里是预留的1/16空闲空间
       15872 │                                                                │
       15936 │                                                                │
# 这里是page directory slot，逆序存储
# trailer占用8字节，此后每个slot占用2字节
# 共170个slot
       16000 │                                      █▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋│
...
       16320 │█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋█▋███████▋│
             ╰────────────────────────────────────────────────────────────────╯

# 最后是统计汇总信息
Legend (█ = 1 byte):
  Region Type                         Bytes    Ratio
  █ FIL Header                           38    0.23%
  █ Index Header                         36    0.22%
  █ File Segment Header                  20    0.12%
  █ Infimum                              13    0.08%
  █ Supremum                             13    0.08%
  █ Record Header                      3380   20.63%
  █ Record Data                       11492   70.14%
  █ Page Directory                      340    2.08%
  █ FIL Trailer                           8    0.05%
  ░ Garbage                               0    0.00%
    Free                               1044    6.37%

可以得到几点信息

Record Data共占用11492字节，共676条记录，每条记录17字节（4+6+7）
Page Directory共340字节，170个slot，每个slot占用2字节
两条虚拟记录，均占用13字节（含5字节的record header）
Record Header共3380字节，共676条记录，每条记录需要5字节头信息（再次提醒，表里字段类型各异，Record Header也会随之不同，仅在本例中只需要5字节。详见 浅析InnoDB Record Header及page overflow）
提醒：本次测试是顺序写入，如果是随机写入或批量写入，可能就没办法把15/16的page空间填充的满满当当了

码农公寓