暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片

MySQL工具之innblock:一文搞懂表空间碎片是如何产生的

GrowthDBA 2021-11-25
1369
上篇文章我们学习了解析.ibd文件小工具innodb_ruby的安装与使用,作者是外国大佬Jeremy Cole,听说现任于FaceBook。今天我们继续来学习一款由国内大佬高鹏(网名:八怪)开发的分析InnoDB块结构的小工具 innblock,现任中亦安图科技股份有限公司数据库专家。
下载的工具安装包中,有一个帮助文档,大家可以看下图,出现了很多库圈大佬的名字,看看大家有认识的吗。

GitHub地址:https://github.com/gaopengcarl/innblock


01 INSTALLATION


环境介绍 & 工具安装.



测试环境介绍

  • Linux操作系统版本:CentOS Linux release 7.5.1804 (Core)

这个工具是直接编译好的,我们可以直接下载即用。

innblock下载

因为GitHub上没有介绍安装教程,所以从网上找了一篇资料,提供了一个编译好的工具下载地址:http://pan.baidu.com/s/1qYnyVWo

同时我也把下载好的软件放在了百度网盘,为防止原地址丢失,大家也可以从这个地址下载。(链接: https://pan.baidu.com/s/1Yftl1wqFuUhYlcU-CFYKkA,提取码: 38i0

1、赋予工具可执行权限(我把工具放在了测试环境的/root目录下):
cd mysql_innblockchmod +x innblock

2、确认工具是否可以使用:
/root/mysql_innblock/innblock --help

'安装'就是这么简单,下面我们开始使用。



02 DATA PREPARE


测试数据准备.



MySQL环境要求

  • 不支持REDUNDANT行格式的数据文件;

  • 只支持LINUX x64平台;

  • 本工具直接读取物理文件,部分Dirty Page可能延时刷盘而未能被读取到,可以让InnoDB及时刷盘再重新读取;

  • 最好在MySQL 5.6/5.7版本下测试;

  • 只能解析索引页,不支持INODE Page、UNDO LOG等类型的Page;

  • SCAN功能会包含DELETE后的索引块和DROP了的索引块.

  • 不能读取详细的ROW Data;

  • 建议采用独立表空间模式,更便于观察;

  • 建议仅在测试环境下学习和研究使用

测试数据准备

--Create DatabaseCREATE DATABASE test_innblock;--Create TableUSE test_innblock;CREATE TABLE `test_innblock` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(30) DEFAULT NULL,    `age` INT(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY(`name`),KEY(`age`)) ENGINE=InnoDB;--Insert Test DataINSERT INTO `test_innblock` VALUES(1,'zhangsan',20),(2,'lisi',21),(3,'wangwu',22),(4,'zhaoliu',19);--Delete Test DataDELETE FROM `test_innblock` WHERE `id`=1;--Query DataSELECT * FROM `test_innblock`;

查看一下对应的物理文件:




03 INTRODUCE


工具用法介绍.



两大功能

  • 第一个scan功能用于查找ibd文件中所有的索引页
  • 第二个analyze功能用于扫描数据块里的ROW Data

扫描所有Index Page

/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/test_innblock.ibd scan 16

总共扫描出3个索引,索引ID(INDEX_ID)分别是107108109。再通过查看数据字典确认:

SELECT A.SPACE AS TBL_SPACEID, A.TABLE_ID, A.NAME AS TABLE_NAME, FILE_FORMAT, ROW_FORMAT, SPACE_TYPE, B.INDEX_ID , B.NAME AS INDEX_NAME, PAGE_NO, B.TYPE AS INDEX_TYPE   FROM information_schema.INNODB_SYS_TABLES A   LEFT JOIN information_schema.INNODB_SYS_INDEXES B   ON A.TABLE_ID =B.TABLE_ID   WHERE A.NAME = 'test_innblock/test_innblock';

没问题,GO ON。

扫描指定PAGE_NO的索引页

/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/test_innblock.ibd 3 16

这个图里面的内容大家看起来就很眼熟了,我们再来看一下。

输出信息详解

详解的话还是对照一下这个图看比较方便:

  • ==== Block base info ====(块基本信息)

block_no:索引页码(Index Page No),该Page相对于表空间的偏移量,从0开始计数。如果page no = 3,则实际上是第4个Index Page。该值取自File Header的FIL_PAGE_OFFSET【页号,4字节】。
space_id:本索引页所属的表空间ID,可以在INNODB_SYS_TABLES、INNODB_SYS_TABLESPACES、INNODB_SYS_DATAFILES等系统视图中查看。该值取自File Header的FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID【页属于哪个表空间,4字节】。
index_id:本索引页所属的索引ID,可以在INNODB_SYS_INDEXES系统视图中查看。该值取自Page Header的PAGE_INDEX_ID【索引ID,表示当前页属于哪个索引,8字节】。
slot_nums:本索引页中所包含的slot(槽)的数量。该值取自Page Header的PAGE_N_DIR_SLOTS【页目录的插槽数,2字节】。
heaps_rows:本索引页中的全部记录数量,这其中包含了已经deleted且已被purged的记录(这种记录会被放到索引页的garbage队列中),以及两个伪记录INFIMUM/SUPREMUM。该值取自Page Header的PAGE_N_HEAP【本页中的记录的数量(包括最小和最大记录以及标记为删除的记录),2字节】。

n_rows:本索引页中的记录数,不含deleted且已被purged的记录,以及两个伪记录INFIMUM、SUPREMUM。该值取自Page Header的PAGE_N_RECS【该页中记录的数量(不包括最小和最大记录以及被标记为删除的记录),2字节】。

heap_top:指向本索引页已分配的最大物理存储空间的偏移量。该值取自Page Header的PAGE_HEAP_TOP【还未使用的空间最小地址,也就是说从该地址之后就是Free Space,2字节】。

del_bytes:本索引页中所有deleted了的且已被purged的记录的总大小。该值取自Page Header的PAGE_GARBAGE【已删除记录占用的字节数,2字节】。

last_ins_offset:指向本索引页最后插入记录的位置偏移量,如果最后操作是delete,则这个偏移量为空。通过判断索引页内数据最后插入的方向,用于索引分裂判断。该值取自Page Header的PAGE_LAST_INSERT【最后插入记录的位置,2字节】。

page_dir:本索引页中数据最后插入的方向,同样用于索引分裂判断。该值取自Page Header的PAGE_DIRECTION【记录插入的方向,2字节】。

page_n_dir:向同一个方向插入数据的行数,同样用于索引分裂中进行判断。该值取自Page Header的PAGE_N_DIRECTION【一个方向连续插入的记录数量,假如新插入的一条记录的主键值比上一条记录的主键值大,我们说这条记录的插入方向是右边,反之则是左边,2字节】。

leaf_inode_space leaf_inode_pag_no leaf_inode_offset & no_leaf_inode_space no_leaf_inode_pag_no no_leaf_inode_offset:这6个值只在root节点会有信息,分别表示了叶子段和非叶子段的inode的位置和在inode块中的偏移量,其他块都为0。分别取自Page Header的PAGE_BTR_SEG_LEAF【B+树叶子段的头部信息,仅在B+树的Root页定义,10字节】、PAGE_BTR_SEG_TOP【B+树非叶子段的头部信息,仅在B+树的Root页定义,10字节】。

last_modify_lsn:本块最后一次修改的LSN。该值取自File Header的FIL_PAGE_LSN【页面被最后修改时对应的日志序列位置(英文名是:Log Sequence Number),8字节】。

page_type:对于本工具而言始终为B+TREE,因为不支持其它Page Type。该值取自File Header的FIL_PAGE_TYPE【该页的类型,2字节】。

level:本索引页所处的B+TREE的层级。注意,叶子结点的PAGE LEVEL为0。该值取自Page Header的PAGE_LEVEL【当前页在B+树中所处的层级,2字节】。

  • ==== Block list info ====(块链表信息)

-----Total used rows:5 used rows list(logic):这个链表是逻辑有序链表,也是我们平时所说的块内数据有序的展示。它的顺序当然按照主键或者ROWID进行排列,因为是通过物理偏移量链表实现的,实际上就是逻辑上有序。作者在实现的时候实际上是取了INFIMUM的偏移量开始进行扫描直到最后,但是注意被deleted且已经被purged的记录不在其中。

-----Total used rows:5 used rows list(phy):这个链表是物理上的顺序,实际上就是heap no的顺序,我在实现的时候实际上就是将上面的逻辑链表按照heap no进行排序完成的,所以块内部是逻辑有序物理无序的,同样注意被deleted且已被purged的记录不在其中。

-----Total del rows:1 del rows list(logic):这个链表是逻辑上的,也就是被deleted且被purged后的记录都存在于这个链表中,通过读取块的PAGE_FREE获取链表信息。(就是我们一开始删除的那条数据就加入到了这个链表)

-----Total slot:2 slot list:这是slot(槽的)信息,通过扫描块尾部8字节以前信息进行分析得到,我们可以发现在slot中存储的是记录的偏移量。

  • 链表中包含的属性信息(取自记录头信息Record Header)
record offset:real offset in block of this record.【记录在块中的真实偏移量】
heapno:physics heapno of this record.【当前记录在物理记录堆的位置信息】
n_owned:if this record is slot record n_owned is how many this slot include,other is 0.【如果是槽记录,记录槽包含的记录数,非槽记录该值为0】
delflag:this record is delete will Y,if not purge in list 1,if purge in list 3.【标记着当前记录是否被删除。删除为Y,未删除为N】
rectype:【当前记录类型】
  REC_STATUS_ORDINARY=0(B+ leaf record)【0表示普通记录】
  REC_STATUS_NODE_PTR=1(not B+ leaf record)【1表示B+树非叶子节点记录】
  REC_STATUS_INFIMUM=2【2表示最小记录】
  REC_STATUS_SUPREMUM=3【3表示最大记录】
slot offset:where(offset) this slot point,this is a record offset.no purge delete record.【槽指针,槽记录的偏移量,不包括被标记为删除的记录】
n_owned:how many this slot include recorods.no purge delete record.【对应槽包含的记录数,不包括被标记为删除的记录】



04 EXAMPLE


使用示例.



知道了各个输出信息的含义,现在来做一些测试案例:
先准备一张测试数据表:
--Create TableUSE test_innblock;CREATE TABLE `example_innblock` (    `id` INT(11) NOT NULL AUTO_INCREMENT,    `name` VARCHAR(30) DEFAULT NULL,    `age` INT(11) DEFAULT NULL,PRIMARY KEY (`id`),KEY(`name`),KEY(`age`)) ENGINE=InnoDB;--Insert Test DataINSERT INTO `example_innblock` VALUES(1,'zhangsan',20),(2,'lisi',21),(3,'wangwu',22),(4,'zhaoliu',19);

查看一下当前记录:

SELECT * FROM `test_innblock`.`example_innblock`;

E.g.1:发起事务,先执行delete,暂不commit(执行delete后还未commit的记录只打delete标记)
BEGIN;DELETE FROM `test_innblock`.`example_innblock` WHERE id=1;

分析结果:
/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/example_innblock.ibd 3 16

如图可得:
  • '(2) normal record offset:127 heapno:2 n_owned 0,delflag:Y minflag:0 rectype:0':偏移量为127的普通记录,被标记delete了。
  • '-----Total del rows:0 del rows list(logic):'显示为空:被标记delete的偏移量为127的记录所在事务没有COMMIT,也还没被purged(还没有被放到garbage队列),所以del rows List链表显示为空。
  • 'heaps_rows:6':表示该文件中加上最大、最小记录和真实记录,一共6行记录。
  • 'del_bytes:0':已删除并加入到garbage队列记录占用的字节数为0,就是没有加入garbage队列的记录。
  • 'n_rows:4':该页中真实用户记录的数量(不包括最小和最大记录以及被标记为删除的记录)为4。
综上,delete但还未commit的记录没有真正被清除,只是打了一个delete_mask的标记(将值从0变为1)
E.g.2:接着上面的事务,继续执行commit(执行delete后commit的记录,被purged后真正被清除,进入删除链表)
COMMIT;

分析结果:
/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/example_innblock.ibd 3 16

如图可得:

  • '-----Total del rows:1 del rows list(logic): (1) normal record offset:127 heapno:2 n_owned 0,delflag:Y minflag:0  rectype:0':执行commit,偏移量为127的普通记录被purged后加入了del rows List链表。

  • 'heaps_rows:6':和上次看到的值一样,一共还是6行记录,这里计算的是物理记录数,虽然数据被delete且被purged,但只是逻辑上加入了del rows List链表,物理上这行记录仍在。

  • 'del_bytes:36':上一次看到的值是0,表示删除的数据占用36字节大小。

  • 'n_rows:3':上一次看到的值是4,表示真实用户记录被删除了1条。

综上,commit且被purged的记录才是真正的删除(清除)

E.g.3E.g.2删除了heapno为2的记录,接着插入新记录(先删除后insert更大新记录,旧的heapno不会重用
INSERT INTO `example_innblock` VALUES(5,'zhangsanfeng',28);

分析结果:
/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/example_innblock.ibd 3 16

如图可得:(我们只关注发生变化的信息,重复的信息大家看上面哈)
  • '(5) normal record offset:264 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0'、'-----Total del rows:1 del rows list(logic):(1) normal record offset:127 heapno:2 n_owned 0,delflag:Y minflag:0  rectype:0':新增这条记录heapno=6。而删除的旧记录是heapno=2且还在del rows List链表中,这表明新增heapno=6的记录没有重用del rows List中的空间,因为删除记录的空间根本放不下这条新记录,所以只能重新分配。
  • 'heap_top:297':上一次看到的值是257,这也实际表明为这行新增数据分配了新的heapno。

综上,删除原记录后insert更大的新记录,旧的heapno(空间)不会重用
E.g.4:在上面步骤基础上,接着插入新记录(再insert更小或者相同大小记录,旧的heapno会重用)
INSERT INTO `example_innblock` VALUES(6,'zhangli',20);

分析结果:

如图可得:

  • '(6) normal record offset:127 heapno:2 n_owned 0,delflag:N minflag:0 rectype:0'、'-----Total del rows:0 del rows list(logic):':我们这次新写入的数据长度较删除的数据长度较短,heapno=2的位置被重用,同时heapno=2的记录delflag不再是Y了,del rows List链表中的记录没有了,而在数据逻辑顺序中多了一条。

  • 'heap_top:297':与E.g.3的值一致。

  • 'del_bytes:1':也减少到1。

综上,删除原记录后再insert数据长度更小或者相同大小记录,旧的heapno会重用

E.g.5:测试del rows List链表中的空间重用只会检测第一条删除的记录
--先清空旧表TRUNCATE TABLE `example_innblock`;--按顺序执行以下操作INSERT INTO `example_innblock` VALUES(1,'zhangsan',20),(2,'lisi',21),(3,'wangwu',22),(4,'zhaoliu',19);DELETE FROM  `example_innblock` WHERE id=4;DELETE FROM  `example_innblock` WHERE id=3;INSERT INTO `example_innblock` VALUES(5,'zhangsa',20);

本例中,我们先删除id=4的记录,后删除id=3的记录。

由于del rows List链表是头插法(类似于栈的数据结构),所以后删除的id=3的记录会放在del rows List链表的最头部,也就是del list header → id=3 → id=4。虽然id=4的记录空间足以容下新记录(5,'zhangsa',20),但并没被重用。因为InnoDB只检测第一个del rows List链表中的第一个空位id=3的记录,显然这个记录(3,'wangwu',22)空间不足以容下新记录(5,'zhangsa',20),所以还是开辟了新的heap

分析结果:
/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/example_innblock.ibd 3 16

如图可得:
  • '-----Total del rows:2 del rows list(logic):(1) normal record offset:195 heapno:4 n_owned 0,delflag:Y minflag:0  rectype:0(2) normal record offset:229 heapno:5 n_owned 0,delflag:Y minflag:0  rectype:0':del rows List链表中共有2条记录,证明我们删除的记录位置没有被重用。
  • '(4) normal record offset:264 heapno:6 n_owned 0,delflag:N minflag:0 rectype:0':新增加了heapno=6的记录。
E.g.6:DELETE操作,del_bytes(PAGE_GARBAGE)是否包含碎片空间
--先清空旧表TRUNCATE TABLE `example_innblock`;--按顺序执行以下操作INSERT INTO `example_innblock` VALUES(1,'lulu',20),(2,'lulu',21),(3,'lulu',22),(4,'luluzhang',19);DELETE FROM  `example_innblock` WHERE id=4;

分析结果:
/root/mysql_innblock/innblock /mysql/mysql3306/test_innblock/example_innblock.ibd 3 16

如图可得,这里del_bytes:37就是删除这条记录的空间的使用量。
再执行一条插入语句(新增数据较上面删除数据长度短):
INSERT INTO `example_innblock` VALUES(5,'lulu',20);

再次分析结果:

如图可得,虽然空间被复用了,但是del_bytes:5表示PAGE_GARBAGE的空间占用为5字节,这5字节就是传说中的碎片【浪费不可再被利用的空间】(注意这里的碎片和之前MySQL之InnoDB表空间说过的碎片(fragment)区不是一回事儿,大家一定要区分开来)。这里的del_bytes:5是怎么得出来的:删除的'luluzhang'占用9字节,插入的'lulu'占用4字节,虽然空间被复用,但是没有被用完,所以就浪费掉了9-4=5字节的空间

E.g.7:UPDATE操作,del_bytes(PAGE_GARBAGE)是否包含碎片空间

基于上面的步骤,我们继续插入一条数据:

INSERT INTO `example_innblock` VALUES(6,'luluzhang',21);

分析结果:

由图可得:del_bytes=5,del rows List链表为空。
修改新插入的数据(新值较原值数据长度较短)
UPDATE `example_innblock` SET name='lulu' WHERE id=6;

分析结果:

由图可得:del_bytes=10,较上一次的值涨了5字节(翻译一下就是:碎片又多了5字节)。del rows List链表为空,原值heapno=6的空间被复用了。至此,得到结论,UPDATE更新的新记录较原记录数据长度短的情况,原空间会被复用,但会生成新旧两条数据相差长度的碎片
测试继续,我们再来修改此值(新值较原值数据长度较长):
UPDATE `example_innblock` SET name='lululuzhang' WHERE id=6;

分析结果:

由图可得:heapno=6的原纪录被加入到了del rows List链表,但是delflag=N,申请了新的heapno=7的空间来存放新值'lululuzhang',del_bytes=42,较上一次的值涨了32字节,由此可以证明heapno=6的原纪录占用空间32字节。至此,得到结论,UPDATE更新的新记录较原记录数据长度长的情况,原空间不会被复用且加入到del rows List链表,不会打delete_mask标记,会申请新的空间来存放新数据
继续测试,我们再插入一条数据,看看del rows List链表中heapno=6的空间是否会被复用:
INSERT INTO `example_innblock` VALUES(7,'luluzhang',21);

分析结果:

由图可得:heapno=6的空间没有被复用,新增数据申请了heapno=8的新空间,del_bytes的值停留在了42字节。至此,得到结论,频繁的DML操作确实会产生表空间碎片




05 END


小结.




感谢高鹏(八怪)大佬开源出来的这么优秀的工具,可以帮助我们分析InnoDB块(Page)的内容和工作原理。通过对工具的学习和测试,我们也得到了频繁DML操作会产生表空间碎片的结论,工具很小,但引申出来的知识点却很多,下面总结一下:
  • delete但还未commit的记录没有真正被清除,只是打了一个delete_mask的标记(将值从0变为1),即delflag → Y。
  • commit且被purged的记录(加入到del rows List链表中的记录)才是真正的删除(清除)。
  • 删除原记录后insert数据长度更长的新记录,加入到del rows List链表中旧的heapno(空间)不会复用,会申请新的空间来存放。
  • 删除原记录后再insert数据长度更短或者数据长度相同的记录,加入到del rows List链表中旧的heapno会复用。
  • 加入到del rows List链表中旧的heapno(空间)复用机制是InnoDB只检测第一个del rows List链表中的第一个空位(头插法,最近操作加入到del rows List链表的空间)是否满足空间要求,满足则复用,不满足则放弃并申请新空间。
  • 删除原记录后再insert数据长度较短的数据,加入到del rows List链表中旧的heapno会复用,但会生成新旧两条数据相差长度的碎片。
  • UPDATE更新的新记录较原记录数据长度短的情况,原空间会被复用,但会生成新旧两条数据相差长度的碎片;UPDATE更新的新记录较原记录数据长度长的情况,原空间不会被复用且加入到del rows List链表,不会打delete_mask标记,会申请新的空间来存放新数据。
  • 频繁的DML操作确实会产生表空间碎片
通过测试的结果,我自己又整理了如下结论:
1、我对碎片的理解就是加入到del rows List链表中旧的heapno(空间)是允许被复用的,但是需要满足2个条件:①InnoDB头插法只检测第一个del rows List链表中的第一个空位会被复用,其余历史的多个旧heapno(空间)则不可复用会变为碎片。②del rows List链表中记录空间的delflag为Y且空间满足新数据时才可以被复用(即DELETE并COMMIT的数据),在del rows List链表中delflag为N的记录空间不会被复用。。
2、重复对某行记录UPDATE比原值数据长度相等的情况会复用Total used logic List链表原heapno(空间),不会产生碎片;UPDATE比原值数据长度短的情况也会复用Total used logic List链表原heapno(空间),会产生新旧两条数据相差长度的碎片。
3、可复用的空间大小是固定的,就像一个固定大小的盒子,不会因为要使用这部分空间的数据大小而改变,只能放下小于等于空间大小的数据,当然数据和空间大小相等的情况是最理想的,不会产生碎片。数据小于空间的情况,没有被数据填满的空闲空间就会成为碎片,会被加入到del_bytes中
又是站在巨人肩膀上,收获满满的一天。知其然,知其所以然,才能站得更高,行得更远!与君共勉!~



end


文章转载自 GrowthDBA,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论