关于MYSQL INNODB index page header学习和实验总结

千家信息网最后更新 2024年11月18日关于MYSQL INNODB index page header学习和实验总结关于INNODB index header


所用到的工具是自己写的mysqlblock和bcview，
我放到了百度云盘
http://pan.baidu.com/s/1num76RJ
供大家下载和使用


普通表空间(及设置了innodb_file_per_table每个表都对应一个idb文件)从第4个块开始通常是innodb的数据页。
前38字节为FILE HEADER
从38字节到74字节为INDEX HEADER，如下：


number of directory slot 2bytes 槽的个数，
heap top position 2bytes 块的最高数据记录的偏移地址
number of heap records/format flag 2bytes 行的记录数量但是第15位为行模式的标示如果15位为1，就是COMPACT模式
first garbage record offset 2bytes 第一行删除记录的偏移量
garbage space 2bytes 删除的空间大小单位bytes
last insert position 2bytes 最后一个插入的位置偏移量
page direction 2bytes 行的插入方向，取值为左，右或者无顺序 0x02 右 0x01 左 0x05 无序
number of inerts in page direction 2bytes 同一方向插入数据的行数，如果方向改变则重置
number of record 2bytes 总的行数
maximum transaction id 8bytes 最大事物的ID
page level 2bytes 页的级别，页节点为0，然后网上加1，如果3层，根节点为 2，分支节点为1，页节点为0
index ID 8bytes 索引的ID这个在INNODB_SYS_INDEXES也是有的


接下来分析我设置了innodb_file_per_table
create table km1(id int ,name varchar(20));
insert into km1 values(1,'gaopeng');
insert into km1 values(2,'gaopeng');
insert into km1 values(3,'gaopeng');
insert into km1 values(4,'gaopeng');


分析文件：
[root@hadoop1 test]# mysqlblock km1.ibd -d|more
***************************************************
USEAGE: mysqlblock datafile -t/-d
This small tool used in study and test database,not
uesd on online database!
This tool is used to find how many blocks and types
in specified datafile,Exp:how many undo block in d
ata file!
QQ:2238980
***************************************************
-t Only Total blocks types in ibdata!
-d Blocks types detail in ibdata!
***************************************************
FILE SIZE IS : 98304
current read blocks is : 0 --This Block is file space header blocks!
current read blocks is : 1 --This Block is insert buffer bitmap blocks!
current read blocks is : 2 --This Block is inode blocks!
current read blocks is : 3 --This Block is data blocks( index pages)!
current read blocks is : 4 --This Block is new allocate blocks!
current read blocks is : 5 --This Block is new allocate blocks!
Total Block Status :
Total block : 6,Total size is: 0.093750 MB
Total undo block : 0,Total size is: 0.000000 MB
Total inode block : 1,Total size is: 0.015625 MB
Total insert buffer free blocks: 0,Total size is: 0.000000 MB
Total data(index pages) block : 1,Total size is: 0.015625 MB
Total new allocate blocks : 2,Total size is: 0.031250 MB
Total insert buf bitmap blocks : 1,Total size is: 0.015625 MB
Total system blocks : 0,Total size is: 0.000000 MB
Total transaction system blocks: 0,Total size is: 0.000000 MB
Total file space header blocks : 1,Total size is: 0.015625 MB
Total extrenl disc blocks : 0,Total size is: 0.000000 MB
Total LOB blocks : 0,Total size is: 0.000000 MB
Total Unkown blocks : 0,Total size is: 0.000000 MB


我这里数据很少，值有3条，所以一共就只有6个块，块3就是数据块(0 开始)使用

1、number of directory slot
bcview km1.ibd 16 38 2|more
current block:00000003--Offset:00038--cnt bytes:02--data is:0002
这里有一个槽的概念，他实际上是用于更快的定位数据，以后的文章会更加细节的研究学习

2、heap top position
bcview km1.ibd 16 40 2|more
current block:00000003--Offset:00040--cnt bytes:02--data is:010c
表明记录的最高为出现在偏移量10c，及十六进制的268

3、number of heap records/format flag

bcview km1.ibd 16 42 2|more
current block:00000003--Offset:00042--cnt bytes:02--data is:8006
这里8006十六进制，就说明是compact模式的行记录，如果使用Redundant我们看看
create table km3 (id int,name varchar(20)) ROW_FORMAT = Redundant;
current block:00000003--Offset:00042--cnt bytes:02--data is:0002
可以看到这里第15为为0，说明是Redundant格式
而我们的行记录是6，明明只是插入了4条记录为什么是6呢，其实MYSQL的每个块里面有
2个虚拟列infimum 和supremum，他们都在固定的位置，而 infimum指向了第一条记录的
offset，而最后一条记录的offset表示为supremum的offset，这样形成了行记录的一个
单项链表。
4、first garbage record offset
bcview km1.ibd 16 44 2|more
current block:00000003--Offset:00044--cnt bytes:02--data is:0000
为0，如果说明没有删除的记录，如果此时我们来delete 2条记录
delete from km1 where id in (1,3);
(由于bcview为基于文件的工具，修改的数据脏数据卸载buffer中，所以我重启了一次数据才能看到)
再次查看
current block:00000003--Offset:00044--cnt bytes:02--data is:00c9
看到这里为c9及offset 201

5、garbage space
bcview km1.ibd 16 46 2|more
current block:00000003--Offset:00046--cnt bytes:02--data is:004a
这里删除的字节为4a及74个字节，为什么我删除了2行数据却有这么多字节呢？
其实每行的数据除了数据本身还有很还有24个字节左右的开销，并且我们这里
没有主键的表MYSQL INNODB会自动生成一个6字节的48位的ROWID，那么加上就是
大约30个字节的开销，如果有建表的时候加上了主键ROWID 6字节开销就没了，同样
实验一下：
create table km4 (id int primary key,name varchar(1000));
insert into km4 values(1,'gaopeng');
insert into km4 values(2,'gaopeng');
insert into km4 values(3,'gaopeng');
insert into km4 values(4,'gaopeng');
delete from km4 where id in (1,3);
再次查看km4
bcview km4.ibd 16 46 2|more
current block:00000003--Offset:00046--cnt bytes:02--data is:003
这里3e是十进制62,74-62=12 刚好6字节证明了我的说法。
还要注意一点：如果建表的时候没有加入主键，插入数据后加入，这每行6字节的开销也是有的，所以建表的时候尽量要加主键。
如果没有删除的行或者所有删除的空间从用了这个值减少为0.

6、last insert position
bcview km1.ibd 16 48 2|more
current block:00000003--Offset:00048--cnt bytes:02--data is:0000

这个取值在没有删除重用空间的时候都0，但是随后的测试中这个值代表的
是最后一次插入的偏移量。如果使用的是删除的空间，那么这个值会出现
指向小于当前偏移量的情况，因为删除的数据的空间在当前行的物理偏移量
以前

7、page direction

bcview km1.ibd 16 50 2|more
current block:00000003--Offset:00050--cnt bytes:02--data is:0002

这里代表的插入的顺序为0x02 右

8、number of inerts in page direction
bcview km1.ibd 16 52 2|more
current block:00000003--Offset:00052--cnt bytes:02--data is:0003

这里代表以0x02 这个顺序插入数据的行数为4-1，因为我插入了4行它为3

9、number of record
bcview km1.ibd 16 54 2|more
current block:00000003--Offset:00054--cnt bytes:02--data is:0002

可以看到这里的行数实际为2了因为我删除了2行，而
number of heap records/format flag
的记录还是8006,可以看到记录还是6，除掉infimum 和supremum还有4行。
那么我们可以得到一个结论
number of heap records 是本block中 delete的行数+未delete的行数+infimum 和supremum=6
而number of record 是本block中 未delete的行数

我们可以做下实验：
现在表中有2行，我们增加一行数据看看变化
mysql> insert into km1 values(5,'gaopeng');
number of record
current block:00000003--Offset:00054--cnt bytes:02--data is:0003
number of heap records/format flag
current block:00000003--Offset:00042--cnt bytes:02--data is:8006
可以看到我们的空闲的空间重用了，因为number of heap records还是6，而number of record变成了3

那么还可以看看page direction和number of inerts in page direction
按照理论既然是重用了空间，那会插入的顺序是相反的，那么这两个值会有所变化
不出所料
page direction
current block:00000003--Offset:00050--cnt bytes:02--data is:0005
值为5
number of inerts in page direction
current block:00000003--Offset:00052--cnt bytes:02--data is:0000
变为了0，因为顺序改变了0X02改变为0X05那么这个值被重置了

同时我们这个时候来看
last insert position
bcview km1.ibd 16 48 2|more
current block:00000003--Offset:00048--cnt bytes:02--data is:00c9
以前这值是
current block:00000003--Offset:00048--cnt bytes:02--data is:0000
而这个值刚好也是 刚才 删除数据后first garbage record offset 的值
current block:00000003--Offset:00044--cnt bytes:02--data is:00c9
而此时值first garbage record offset
bcview km1.ibd 16 44 2|more
变为了
current block:00000003--Offset:00044--cnt bytes:02--data is:007f
这里7f变得更小，这个就是我们id=1的地址，而id=3的地址已经重用了

10、maximum transaction id
bcview km1.ibd 16 56 8|more
current block:00000003--Offset:00056--cnt bytes:08--data is:0000000000000000

这个值应该代表是所有行中最高的事物ID，但是没有测试出来


11、page level
bcview km1.ibd 16 64 2|more
current block:00000003--Offset:00064--cnt bytes:02--data is:0000

代表是索引的层次，这里根节点是页节点是一个节点所以是0

12、index ID

bcview km1.ibd 16 66 8|more
current block:00000003--Offset:00066--cnt bytes:08--data is:0000000000000255
这是索引的ID这个在INNODB_SYS_INDEXES也是有的。

学习了这些内容，我们有了一些对INDEX PAGE结构的了解。
总结一下：
1、heap top position 是块的高水位，就是索引页曾经达到的最高点。
2、last insert position 为最后一次索引页插入的行的偏移量，如果删除了数据会进行从用，那么这个值和heap top position并没有什么关系。
3、number of record 是块中未删除的数据量行数，每次删除和插入数据一定变化
4、number of heap records/format flag 记录的是 未删除行+删除行+infimum 和supremum的行数 ，未删除行可以重用，所以这个值可能在你插入数据后不会变化。
5、format flag是第15位的值1为compact格式，0为Redundant
6、如果不加入主键，那么会自动生成一个6字节48位的ROWID，它会加大你的存储空间，所以尽可能加入主键吧再建表的时候，建表插入数据后加入主键也不会改善
7、garbage space 就是删除数据DELETE后剩余的空间，随着不断的重用这个空间不断减少，如果没有delete后可以从用的空间为0
8、index ID 这是索引的ID这个在INNODB_SYS_INDEXES也是有的。