MySQL查看Innodb数据文件

 

• bcview

#https://blog.youkuaiyun.com/weixin_34409741/article/details/90253307
#https://www.jianshu.com/p/719f1bbb21e8

 

 

• innblock

 

#https://github.com/gaopengcarl/innblock
工具有2个功能：
scan功能用于查找ibd文件中所有的索引页
analyze功能用于扫描数据块里的row data

scan功能
[root@test test]# ./innblock  testblock.ibd scan 16
analyze功能
[root@test test]# ./innblock  testblock.ibd 3 16

innblock的限制：
不支持REDUNDANT行格式的数据文件.
只支持LINUX x64平台.
本工具直接读取物理文件，部分dirty page可能延时刷盘而未能被读取到，可以让InnoDB及时刷盘再重新读取.
最好在MySQL 5.6/5.7版本下测试.
只能解析索引页，不支持inode page、undo log等类型的page.
scan功能会包含delete后的索引块和drop了的索引块.
不能读取详细的row data.
建议采用独立表空间模式，更便于观察.

 

 

• 测试

 

create table testblock (
id1 int primary key,
name varchar(30),
id3 int,
key(name),
key(id3));
insert into testblock values(1,'gao',1),(2,'gao',2),
(3,'gao',3),(4,'gao',4);
delete from testblock where id1=1;

 

 

• scan

 

#测试scan功能，扫描所有index page
[root@k8s-master01 tmp]# ./innblock  /data/mysql/data/test/testblock.ibd scan 16
----------------------------------------------------------------------------------------------------
Welcome to use this block analyze tool:
[Author]:gaopeng [Blog]:blog.itpub.net/7728585/abstract/1/ [QQ]:22389860
[Review]:yejinrong@zhishutang [Blog]:imysql.com [QQ]:4700963
----------------------------------------------------------------------------------------------------
Datafile Total Size:131072
===INDEX_ID:41
level0 total block is (1)
block_no:         3,level:   0|*|
===INDEX_ID:42
level0 total block is (1)
block_no:         4,level:   0|*|
===INDEX_ID:43
level0 total block is (1)
block_no:         5,level:   0|*|

#我们发现有3个索引，INDEX_ID 41 42 43

#查看数据字典
use information_schema;
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 INNODB_SYS_TABLES A  LEFT JOIN INNODB_SYS_INDEXES B ON A.TABLE_ID =B.TABLE_ID  WHERE A.NAME = 'test/testblock';

 

• analyze

 

./innblock  /data/mysql/data/test/testblock.ibd 3 16

 

 

• Block base info

 

block_no	FIL_PAGE_OFFSET 索引页码(index page no)，该页相对于表空间的偏移量，从0开始计数。如果page no = 3，则实际上是第4个index page
space_id	FIL_PAGE_SPACE_ID 索引页所属的表空间ID，可以在 INNODB_SYS_TABLES、INNODB_SYS_TABLESPACES、INNODB_SYS_DATAFILES 等系统视图中查看
index_id	PAGE_INDEX_ID 本索引页所属的索引ID，可以在 INNODB_SYS_INDEXES 系统视图中查看
slot_nums	PAGE_N_DIR_SLOTS  本索引页中所包含的slot(槽)的数量
heaps_rows	PAGE_N_HEAP 本索引页中的全部记录数量，这其中包含了已经deleted且已被purged的记录（这种记录会被放到索引页的garbage队列中），以及两个伪记录INFIMUM/SUPREMUM
n_rows	本索引页中的记录数，不含deleted且已被purged的记录，以及两个伪记录INFIMUM、SUPREMUM
heap_top	PAGE_HEAP_TOP 指向本索引页已分配的最大物理存储空间的偏移量
del_bytes	PAGE_GARBAGE 本索引页中所有deleted了的且已被purged的记录的总大小
last_ins_offset	PAGE_LAST_INSERT 指向本索引页最后插入记录的位置偏移量，如果最后操作是delete，则这个偏移量为空。通过判断索引页内数据最后插入的方向，用于索引分裂判断
page_dir	PAGE_DIRECTION 本索引页中数据最后插入的方向，同样用于索引分裂判断
page_n_dir	PAGE_N_DIRECTION 向同一个方向插入数据的行数，同样用于索引分裂中进行判断
leaf_inode_space leaf_inode_pag_no leaf_inode_offset	leaf segment postion and in inode block offset,only root block(PAGE_BTR_SEG_LEAF开始 10字节)
no_leaf_inode_space no_leaf_inode_pag_no no_leaf_inode_offset	(取自PAGE_BTR_SEG_TOP 开始 10字节) 这6个值只在root节点会有信息，分别表示了叶子段和非叶子段的inode的位置和在inode块中的偏移量，其他块都为0
last_modify_lsn	(FIL_PAGE_LSN) 本块最后一次修改的LSN
page_type	(FIL_PAGE_TYPE) 对于本工具而言始终为B+ TREE，因为不支持其它page type
level	(PAGE_LEVEL) 本索引页所处的B+ TREE的层级。注意，叶子结点的PAGE LEVEL为0

 

 

• 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 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.
delflag	this record is delete will Y,if not purge in list 1,if purge in list 3.
rectype	[REC_STATUS_ORDINARY=0(B+ leaf record) [REC_STATUS_NODE_PTR=1(not B+ leaf record)] [REC_STATUS_INFIMUM=2] [REC_STATUS_SUPREMUM=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.

 

 

 

• 测试

 

create table testblock (
id1 int primary key,
name varchar(30),
id3 int,
key(name),
key(id3)
);
insert into testblock values(1,'gao',1),(2,'gao',2),
(3,'gao',3),(4,'gao',4);

发起事务，先执行delete，暂不commit
mysql> begin; delete from testblock where id1=1;
#
./innblock  /data/mysql/data/test/testblock.ibd 3 16

 

• 执行delete后还未commit的记录只打 delete 标记

 

其 delflag = Y，offset = 127，这条记录只是delete，但还没 commit，也还没被 purged，因此不会出现在 del rows list链表中。
同时注意到几个信息：
del_bytes:0
n_rows:4
heaps_rows:6
三个信息结合起来看，表示还没有真正被清除的数据

 

• 执行delete后commit的记录，被purged后真正被清除，进入删除链表

 

#提交
mysql> commit;

 

执行commit，这条偏移量为127的记录被purged后入了del rows list链表
delflag = Y，同时我们观察到
del_bytes:31 上一次看到的值是 0
n_rows:3 上一次看到的值是 4
heaps_rows:6 和上一次的值一样，因为这里计算的是物理记录数
可见，commit且被purged的数据才是真正的删除（清除）

 

• 先删除后insert更大新记录，旧的heap no不会重用

 

insert into testblock values(5,'gaopeng',1);

 

这条记录的heapno = 6，而删除的旧记录 heapno=2，这表明它没有重用del rows list中的空间，因为删除记录的空间根本放不下这条新记录，所以只能重新分配。同时我们注意到 heap_top = 279 ，这里也发生了变化，体现了实际为这行数据分配了新的heapno

 

• delete后，再insert更小或者相同大小记录，heap no会重用

 

insert into testblock values(6,'gao',1);

 

我们发现heapno=2的记录 delflag 不再是 Y了，同时 heap_top = 279 也没有变化，del_bytes:31 变成了 del_bytes:0，都充分说明了这块空间得到重用

 

• 测试del list中的空间重用只会检测第一个条删除的记录

 

delete from testblock;
insert into testblock values(1,'gao',1),(2,'gao',2),(3,'gao',3),(4,'gaopeng',4);
delete from testblock where id1=4;
delete from testblock where id1=3;
insert into testblock values(5,'gaopeng',5);

 

在这里，我们先删除 [id1=4] 记录，后删除 [id1=3] 记录。 由于del list是头插法，所以后删除的 [id1=3] 的记录会放在del list链表的最头部，也就是 [del list header] => [id1=3] => [id1=4]。虽然 [id=4] 的记录空间足以容下新记录 (5,'gaopeng’,5)，但并没被重用。因为InnoDB只检测第一个 del list 中的第一个空位 [id1=3]，显然这个记录空间不足以容下新记录 (5,’gaopeng',5)，所以还是新开辟了heap。

我们看到 del list 中共有2条记录（没被重用），却新增加了 heapno = 6 的记录

 

• del_bytes(PAGE_GARBAGE)是否包含碎片空间

 

delete from testblock;
insert into testblock values(1,'gao',1),(2,'gao',2),(3,'gao',3),(4,'gaopeng',4);
delete from testblock where id1=4;

 

注意这里 del_bytes:35 就是删除这条记录的空间的使用量

 

insert into testblock values(5,'gao',5);

 

注意到 del_bytes:4，这个刚好就是 'gaopeng' 7字节减去 'gao' 3字节剩下的4字节，我们也看到了 [heapno=5] MySQL查看Innodb数据文件这个记录被重用了（del list为空，heaono=5的记录 delflag 不为 Y）。

 

#文档参考https://mp.weixin.qq.com/s/yfi5XikDJlh6-nS-eoJbcA