开发者社区> xaubllxwtvaqiu> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

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

简介: InnoDB表聚集索引层高什么时候发生变化
+关注继续查看

导读

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


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.

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

  1. 即便 innodb_fill_factor=100,也会预留1/16的空闲空间,用于现存记录长度扩展用
  2. 在最佳的顺序写入数据模式下,page填充率有可能可以达到15/16
  3. 在随机写入新数据模式下,page填充率约为 1/2 ~ 15/16
  4. 预留1/16这个规则,只针对聚集索引的叶子节点有效。对于聚集索引的非叶子节点以及辅助索引(叶子及非叶子)节点都没有这个规则
  5. 不过 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空间填充的满满当当了


            </div>

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
10 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
10 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
9 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
8 0
InnoDB表聚集索引层高什么时候发生变化(1)
InnoDB表聚集索引层高什么时候发生变化
9 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
17 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
23 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
29 0
InnoDB表聚集索引层高什么时候发生变化(1)
InnoDB表聚集索引层高什么时候发生变化
96 0
InnoDB表聚集索引层高什么时候发生变化(2)
InnoDB表聚集索引层高什么时候发生变化
40 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载