B+树结构与索引＜二＞ _ 索引页示例

目录

一、分析表空间

二、分析聚集索引页00000003页

三、分析辅助索引页00000004页

四、参考资料

---

一、分析表空间

        创建表，并插入数据，如下所示。

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 
page offset 00000001, page type 
page offset 00000002, page type 
page offset 00000003, page type , page level <0001>
page offset 00000004, page type , page level <0000>
page offset 00000005, page type , page level <0000>
page offset 00000006, page type , page level <0000>
page offset 00000007, page type , page level <0000>
page offset 00000000, page type 
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，如下表所示。

        如下所以，是数据页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。辅助索引叶子节点存放辅助索引对应的列值，及该列值对应的主键值。

        根据以上对辅助索引页的分析，可以整理如下图所示的辅助索引结构。看出叶子节点存放行记录的辅助索引的列值与主键的映射关系。

四、参考资料

py_innodb_page_info.py工具使用_爱我所爱0505的博客-CSDN博客_py_innodb_page_info

InnoDB数据页结构示例_爱我所爱0505的博客-CSDN博客

MySQL InnoDB Engine--主键索引非叶子节点数据存储 - 走看看