MySQL 验证 InnoDB 存储引擎可能出现的浪费存储空间的问题

在阅读 小孩子4919 的《MySQL 是怎样运行的》过程中，为了搞懂第 9 章 ( 表中的页是如何存储的 ) 就去分析了一个表的 ibd 文件。但是在过程中，李守中发现在使用 InnoDB 与 COMPACT 行结构时，聚簇索引的非叶子节点的行记录之前总会有一段全 0 值，在与作者确认过结论之后，有了这篇记录验证过程的笔记。

读懂这篇笔记需要理解这本书的 4 到 9 章。

InnoDB 使用的 COMPACT 行格式为:

• 变长字段长度表。
• NULL 值列表。
• 记录头信息。
• 主键。如果表没有主键则为 row_id。
• trx_id 事务 ID。
• roll_pointer 回滚指针。
• 除主键外的数据。

InnoDB 索引树的非叶子节点的行信息中，只存储:

• 与叶子节点中的一个 NULL 值列的表大小相同的全 0 段。( 也可能不存在 )
• 记录头信息。
• 页内最小主键。
• 页号。

这里验证的就是这个全 0 段浪费空间的问题。

一旦索引树的叶子节点中出现 NULL 值列表，那么在非叶子节点中，每行数据之前，也会出现与 NULL 值列表长度相当的全 0 段。

这其实是 InnoDB 存储引擎的设计缺陷。

为了验证索引树非叶子节点中的行数据可能存在被浪费的空间，需要在数据库中开启独立表空间。

还需要 3 张表:

-- CREATE DATABASE ibd_analysis_demo;
-- USE ibd_analysis_demo;

CREATE TABLE ibd_analysis_demo_a (
  c1 varchar(20),
  c2 varchar(20),
  c3 varchar(20),
  c4 varchar(20),
  c5 varchar(20),
  c6 varchar(20),
  c7 varchar(20),
  c8 varchar(20),
  c9 varchar(20),
  c10 varchar(20)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ibd_analysis_demo_b (
  c1 varchar(20),
  c2 varchar(20),
  c3 varchar(20),
  c4 varchar(20),
  c5 varchar(20),
  c6 varchar(20),
  c7 varchar(20),
  c8 varchar(20),
  c9 varchar(20) NOT NULL,
  c10 varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE ibd_analysis_demo_c (
  c1 varchar(20) NOT NULL,
  c2 varchar(20) NOT NULL,
  c3 varchar(20) NOT NULL,
  c4 varchar(20) NOT NULL,
  c5 varchar(20) NOT NULL,
  c6 varchar(20) NOT NULL,
  c7 varchar(20) NOT NULL,
  c8 varchar(20) NOT NULL,
  c9 varchar(20) NOT NULL,
  c10 varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

还需要从这里 insert_for_ibd_analysis_demo.zip 下载已经生成好的 sql 文件。

或者用这段 Python 代码自行生成插入数据的语句:

insert_data = ""
for i in range (1,2001):
    insert_data += ("("+
        "\"row" + str(i) + "col1" + "\"" + ", " +
        "\"row" + str(i) + "col2" + "\"" + ", " +
        "\"row" + str(i) + "col3" + "\"" + ", " +
        "\"row" + str(i) + "col4" + "\"" + ", " +
        "\"row" + str(i) + "col5" + "\"" + ", " +
        "\"row" + str(i) + "col6" + "\"" + ", " +
        "\"row" + str(i) + "col7" + "\"" + ", " +
        "\"row" + str(i) + "col8" + "\"" + ", " +
        "\"row" + str(i) + "col9" + "\"" + ", " +
        "\"row" + str(i) + "col10" + "\"" + "),\n")

print("INSERT INTO ibd_analysis_demo_a " +
      "(c1, c2, c3, c4, c5, c6, c7, c8, c9, c10) VALUES " +
      insert_data[:-1] + ";")

注意: 将 ibd_analysis_demo_a 中的 a 改为 b, c 可以生成另外两张表的数据。

在数据库中执行生成的语句。

以上做完之后，可以在 ibd_analysis_demo 数据库目录下找到存储表数据的三个文件 ( 文件名即为表名 ):

• ibd_analysis_demo_a.ibd
• ibd_analysis_demo_b.ibd
• ibd_analysis_demo_c.ibd

接下来要分析的就是这三个文件。以 16 进制模式打开上面三个文件:

• emacs 用户可以使用 hexl-mode 打开这三个文件。
• 需要更多外挂来帮助理解的读者，可以使用 010 editor 打开 ibd 文件并执行这个 010editor_ibd_analysis_script.zip 脚本。

ibd_analysis_demo_a 简称表 a，以此类推。

验证: 如果索引树叶子节点的行数据中有 NULL 值列表段，那么在索引树非叶子节点中的行数据之前也存在一个同样规模且无意义的全 0 段。如果索引树叶子节点的行数据中没有 NULL 值列表段，那么在索引树非叶子节点中的行数据之前也没有无意义的全 0 段。

由于使用了独立的表空间，所以所有 ibd 文件的前 4 个页分别是:

• 页 0: FSP_HDR。
• 页 1: IBUF_BITMAP。
• 页 2: INODE。
• 页 3: 聚簇索引根节点。

又由于一个页面的大小为 16k，所以得到聚簇索引根节点页 3 的起始地址为 0xC000，由此可以定位:

• infimum 记录起始地址为 0xC05E，第一条 目录项记录 的地址被记录在 0xC061 和 0xC062 两个字节中。
• supremum 记录起始地址为 0xC06B，结束地址为 0xC077。

下面是分析过程。

分析表 a

贴一部分表 a ibd 文件内的数据:

          0011 2233 4455 6677 8899 aabb ccdd eeff  0123456789abcdef
...
0000c050: 0002 00f2 0000 002b 0000 0002 0032 0100  .......+.....2..
0000c060: 0200 1c69 6e66 696d 756d 0005 000b 0000  ...infimum......
0000c070: 7375 7072 656d 756d 0000 1000 1100 1100  supremum........
...

可以看到，表 a 的 ibd 文件中，0xC061 和 0xC062 两个字节中存储的十进制数据为 28。即，下一条数据 ( 第一行数据 ) 的起始地址是 0xC07E。

第一条数据的地址 0xC07E - supremum 的结束地址 0xC077 = 0x7 = 7

可以得出，第一行数据的记录部分距离 supremum 结束有 7 个字节。

再减去记录头信息所占的 5 个字节，那就得到了一个结论: supremum 的尾部到第一行数据的开始部分间隔了 2 个字节。

并且观察 0xC078 和 0xC079 这两个字节可以发现它们都是 0 值。

而文档说明，supremum 记录后紧跟的就是第一行数据，并不存在任何的间隔符。

又注意到，表 a 的 c1 - c10 这 10 个字段都可以存储 NULL。这意味着，聚簇索引叶子节点的行记录中，NULL 值列表共占 2 字节。

分析表 b

贴一部分表 b ibd 文件内的数据:

          0011 2233 4455 6677 8899 aabb ccdd eeff  0123456789abcdef
...
0000c050: 0002 00f2 0000 002c 0000 0002 0032 0100  .......,.....2..
0000c060: 0200 1b69 6e66 696d 756d 0005 000b 0000  ...infimum......
0000c070: 7375 7072 656d 756d 0010 0011 0010 0000  supremum........
...

重复分析表 a 的思路。

0xC061 和 0xC062 两个字节中存储的十进制数据为 27。即，下一条数据 ( 第一行数据 ) 的起始地址是 0xC07D。

第一条数据的地址 0xC07D - supremum 的结束地址 0xC077 = 0x6 = 6

可以得出，第一行数据的记录部分距离 supremum 结束有 6 个字节。

再减去记录头信息所占的 5 个字节，那就得到了一个结论: supremum 的尾部到第一行数据的开始部分间隔了 1 个字节。

并且观察 0xC078 字节可以发现它存储的也是 0 值。

又注意到，表 b 的 10 个字段中可以存储 NULL 的字段有 8 个。这意味着，聚簇索引叶子结点的行记录中，NULL 值列表共占 1 字节。

分析表 c

贴一部分表 c ibd 文件内的数据:

          0011 2233 4455 6677 8899 aabb ccdd eeff  0123456789abcdef
...
0000c050: 0002 00f2 0000 002d 0000 0002 0032 0100  .......-.....2..
0000c060: 0200 1a69 6e66 696d 756d 0008 000b 0000  ...infimum......
0000c070: 7375 7072 656d 756d 1000 1100 0f00 0000  supremum........
...

重复分析表 a 的思路。

0xC061 和 0xC062 两个字节中存储的十进制数据为 26。即，下一条数据 ( 第一行数据 ) 的起始地址是 0xC07C。

第一条数据的地址 0xC07C - supremum 的结束地址 0xC077 = 0x5 = 5

可以得出，第一行数据的记录部分距离 supremum 结束有 5 个字节。

而记录头信息正好占 5 个字节，那就得到了一个结论: supremum 的尾部到第一行数据的开始部分没有间隔。

又注意到，表 b 的 10 个字段中，没有可以存储 NULL 的字段。这意味着，聚簇索引叶子结点的行记录中，不存在 NULL 值列表。

至此，验证结束。