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

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

发布时间：2020-08-16 20:07:32

来源：ITPUB博客

阅读：79

作者：gaopengtttt

关于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也是有的。

免责声明：本站发布的内容(图片、视频和文字)以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。