目录
一、分析表空间
创建表,并插入数据,如下所示。
CREATE TABLE `test_clustered_index` (
`a` int(11) NOT NULL,
`b` varchar(8000) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`c` int(11) NOT NULL,
PRIMARY KEY (`a`) USING BTREE,
INDEX `index_c`(`c`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
insert into test_clustered_index select 1,REPEAT('a',7000),-1;
insert into test_clustered_index select 2,REPEAT('a',7000),-2;
insert into test_clustered_index select 3,REPEAT('a',7000),-3;
insert into test_clustered_index select 4,REPEAT('a',7000),-4;
使用工具py_innodb_page_info.py(详细见:py_innodb_page_info.py工具使用_爱我所爱0505的博客-CSDN博客)来分析表空间。
如下所示,test_clustered_index的空间共9页,其中B+树节点页有5页,空闲页有1页。而B+树当前高度是2层,最高层page level <0001>,属于非叶子节点;page level <0000>是叶子节点。
[root@488c1daa7967 py_innodb_page_info]# python py_innodb_page_info.py -v /home/MySQL5.7/mysql-5.7.35/data/test_mysql/test_clustered_index.ibd
page offset 00000000, page type <File Space Header>
page offset 00000001, page type <Insert Buffer Bitmap>
page offset 00000002, page type <File Segment inode>
page offset 00000003, page type <B-tree Node>, page level <0001>
page offset 00000004, page type <B-tree Node>, page level <0000>
page offset 00000005, page type <B-tree Node>, page level <0000>
page offset 00000006, page type <B-tree Node>, page level <0000>
page offset 00000007, page type <B-tree Node>, page level <0000>
page offset 00000000, page type <Freshly Allocated Page>
Total number of page: 9:
Freshly Allocated Page: 1
Insert Buffer Bitmap: 1
File Space Header: 1
B-tree Node: 5
File Segment inode: 1
二、分析聚集索引页00000003页
page offset 00000003表示00000003页在表空间的偏移量(页的位置),如下计算:3 * 16KB = 49152 = 0xc000。所以通过FileViewPro软件打开t_activity_detail.ibd,找到0x0000c000,就是00000003页的起始位置。
hexdump -C -s 49152 -n 16384 test_clustered_index.ibd
其中:
-n length 只格式化输入文件的前length个字节
-C 输出规范的十六进制和ASCII码
-b 单字节八进制显示
-c 单字节字符显示
-d 双字节十进制显示
-o 双字节八进制显示
-x 双字节十六进制显示
-s 从偏移量开始输出
[root@488c1daa7967 test_mysql]# hexdump -C -s 49152 -n 16384 test_clustered_index.ibd
0000c000 99 25 a2 6a 00 00 00 03 ff ff ff ff ff ff ff ff |.%.j............|
0000c010 00 00 00 00 a8 b1 bd 17 45 bf 00 00 00 00 00 00 |........E.......|
0000c020 00 00 00 00 01 47 00 02 00 a2 80 05 00 00 00 00 |.....G..........|
0000c030 00 9a 00 02 00 02 00 03 00 00 00 00 00 00 00 00 |................|
0000c040 00 01 00 00 00 00 00 00 01 f7 00 00 01 47 00 00 |.............G..|
0000c050 00 02 00 f2 00 00 01 47 00 00 00 02 00 32 01 00 |.......G.....2..|
0000c060 02 00 1b 69 6e 66 69 6d 75 6d 00 04 00 0b 00 00 |...infimum......|
0000c070 73 75 70 72 65 6d 75 6d 00 10 00 11 00 0e 80 00 |supremum........|
0000c080 00 01 00 00 00 05 00 00 00 19 00 0e 80 00 00 02 |................|
0000c090 00 00 00 06 00 00 00 21 ff d6 80 00 00 04 00 00 |.......!........|
0000c0a0 00 07 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0000c0b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
0000fff0 00 00 00 00 00 70 00 63 99 25 a2 6a a8 b1 bd 17 |.....p.c.%.j....|
如上代码所示,Page Directory有:00 70 00 63,记录的相对位置是逆序存放的。0x63是infimum行的位置,即:0xc063;0x0070是supremum行的位置,即:0xc070。
0xc063的记录头后的最后两位字节00 1b是下条记录的相对位置,即:0xc063 + 0xc01b = 0xc07e。0xc07e读取主键键值是80 00 00 01,就是主键为1的键值。注意,表定义主键是无符号的INT型,因此二进制是0x80 00 00 01,而不是0x00 00 00 01。而后四个字节00 00 00 05是数据页的物理偏移量,即:page offset 00000005,如下表所示。
位置 | 记录长度 (1或2字节) | 记录头信息 (5字节) | Key值 (主键值) | 数据页偏移量 (page offset) |
0000c078 | 00 | 10 00 11 00 0e | 80 00 00 01 | 00 00 00 05 |
0000c086 | 00 | 00 00 19 00 0e | 80 00 00 02 | 00 00 00 06 |
0000c094 | 00 | 00 00 21 ff d6 | 80 00 00 04 | 00 00 00 07 |
如下所以,是数据页00 00 00 06的二进制内容。看出0x80 00 00 02、0x80 00 00 03分别对应主键2、3的行记录。聚集索引页保存的是行的完整数据。
[root@488c1daa7967 test_mysql]# hexdump -C -s 98304 -n 16384 test_clustered_index.ibd
00018000 84 a8 90 8c 00 00 00 06 00 00 00 05 00 00 00 07 |................|
00018010 00 00 00 00 a8 b1 bd 17 45 bf 00 00 00 00 00 00 |........E.......|
00018020 00 00 00 00 01 47 00 02 37 62 80 04 00 00 00 00 |.....G..7b......|
00018030 1b f5 00 05 00 00 00 02 00 00 00 00 00 00 00 00 |................|
00018040 00 00 00 00 00 00 00 00 01 f7 00 00 00 00 00 00 |................|
00018050 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 00 |................|
00018060 02 00 1d 69 6e 66 69 6d 75 6d 00 03 00 0b 00 00 |...infimum......|
00018070 73 75 70 72 65 6d 75 6d 58 9b 00 00 00 10 1b 75 |supremumX......u|
00018080 80 00 00 02 00 00 00 00 61 e0 b3 00 00 01 27 01 |........a.....'.|
00018090 10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |.aaaaaaaaaaaaaaa|
000180a0 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
*
00019be0 61 61 61 61 61 61 61 61 61 7f ff ff fe 58 9b 00 |aaaaaaaaa....X..|
00019bf0 00 00 18 e4 7b 80 00 00 03 00 00 00 00 61 e5 b6 |....{........a..|
00019c00 00 00 01 2a 01 10 61 61 61 61 61 61 61 61 61 61 |...*..aaaaaaaaaa|
00019c10 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 61 |aaaaaaaaaaaaaaaa|
*
0001b750 61 61 61 61 61 61 61 61 61 61 61 61 61 61 7f ff |aaaaaaaaaaaaaa..|
0001b760 ff fd 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
0001b770 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
0001bff0 00 00 00 00 00 70 00 63 84 a8 90 8c a8 b1 bd 17 |.....p.c........|
0001c000
根据以上对聚集索引页的分析,可以整理如下图所示的聚集索引结构。看出叶子节点存放行记录的完整数据,聚集索引下的数据即索引,索引即数据。
三、分析辅助索引页00000004页
根据第二章节,聚集索引的根页是page offset 00000003,存放键值指向数据页page offset 00000005、page offset 00000006、page offset 00000007。
page offset 00000004是辅助索引所在的页,如下计算:4 * 16KB = 65536 = 0x10000。
hexdump -C -s 65536 -n 16384 test_clustered_index.ibd
[root@488c1daa7967 test_mysql]# hexdump -C -s 65536 -n 16384 test_clustered_index.ibd
00010000 54 95 92 c8 00 00 00 04 ff ff ff ff ff ff ff ff |T...............|
00010010 00 00 00 00 a8 b1 bd 3d 45 bf 00 00 00 00 00 00 |.......=E.......|
00010020 00 00 00 00 01 47 00 02 00 ac 80 06 00 00 00 00 |.....G..........|
00010030 00 a4 00 01 00 03 00 04 00 00 00 00 00 00 61 e6 |..............a.|
00010040 00 00 00 00 00 00 00 00 01 f8 00 00 01 47 00 00 |.............G..|
00010050 00 02 02 72 00 00 01 47 00 00 00 02 01 b2 01 00 |...r...G........|
00010060 02 00 41 69 6e 66 69 6d 75 6d 00 05 00 0b 00 00 |..Ainfimum......|
00010070 73 75 70 72 65 6d 75 6d 00 00 10 ff f3 7f ff ff |supremum........|
00010080 ff 80 00 00 01 00 00 18 ff f3 7f ff ff fe 80 00 |................|
00010090 00 02 00 00 20 ff f3 7f ff ff fd 80 00 00 03 00 |.... ...........|
000100a0 00 28 ff f3 7f ff ff fc 80 00 00 04 00 00 00 00 |.(..............|
000100b0 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 |................|
*
00013ff0 00 00 00 00 00 70 00 63 54 95 92 c8 a8 b1 bd 3d |.....p.cT......=|
00014000
如下表所示。-1以7f ff ff ff形式存放,7(0111)最高位0,代表负值,实际值应该取反后加1,即得:-1。辅助索引叶子节点存放辅助索引对应的列值,及该列值对应的主键值。
Key值(c列值) | 主键值 |
7f ff ff ff | 80 00 00 01 |
7f ff ff fe | 80 00 00 02 |
7f ff ff fd | 80 00 00 03 |
7f ff ff fc | 80 00 00 04 |
根据以上对辅助索引页的分析,可以整理如下图所示的辅助索引结构。看出叶子节点存放行记录的辅助索引的列值与主键的映射关系。
四、参考资料
py_innodb_page_info.py工具使用_爱我所爱0505的博客-CSDN博客_py_innodb_page_info