怎么看innodb的B+TREE层数

2022-10-27 15:08:39 浏览数 (2)

原文链接：怎么看innodb的B TREE层数? - 2森林 - 博客园

怎么看innodb的B TREE层数?,下面以sysbench_testdata.sbtest2为例查看索引层数: - 查看相关系统

代码语言：javascript复制
root@localhost [sysbench_testdata]>show create table sbtest2;
| sbtest2 | CREATE TABLE `sbtest2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `k` int(11) NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_2` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=67840915 DEFAULT CHARSET=utf8 |
1 row in set (0.00 sec)

root@localhost [sysbench_testdata]>select count(id) from sbtest2;
 ----------- 
| count(id) |
 ----------- 
|  67840914 |
 ----------- 
1 row in set (56.87 sec)
  • 查看information_schema中相关表信息,注意索引的PAGE_NO和:index_id
代码语言：javascript复制
root@localhost [sysbench_testdata]>SELECT b.name, a.name, index_id, type, a.space, a.PAGE_NO FROM information_schema.INNODB_SYS_INDEXES a, information_schema.INNODB_SYS_TABLES b WHERE a.table_id = b.table_id AND a.space <> 0 and b.name='sysbench_testdata/sbtest2';
 --------------------------- --------- ---------- ------ ------- --------- 
| name                      | name    | index_id | type | space | PAGE_NO |
 --------------------------- --------- ---------- ------ ------- --------- 
| sysbench_testdata/sbtest2 | PRIMARY |       51 |    3 |    33 |       3 |
| sysbench_testdata/sbtest2 | k_2     |       58 |    0 |    33 |      38 |
 --------------------------- --------- ---------- ------ ------- --------- 
2 rows in set (0.00 sec)

root@localhost [sysbench_testdata]>show global variables like 'innodb_page_size';
 ------------------ ------- 
| Variable_name    | Value |
 ------------------ ------- 
| innodb_page_size | 16384 |
 ------------------ ------- 
1 row in set (0.00 sec)
  • 到表的文件系统目录中:cd /data/57mysql/mysql3508/data/sysbench_testdata
代码语言：javascript复制
#hexdump -s 49216 -n 10 ./sbtest2.ibd
000c040 0300 0000 0000 0000 3300               
000c04a

#hexdump -s 622656 -n 10 ./sbtest2.ibd
0098040 0200 0000 0000 0000 3a00               
009804a
  • 注:hexdump中49216和622656是怎么算出来的?这个数分别对应sbtest2表的两个索引,公式是 page_no * innodb_page_size 64。PRIMARY:316384 64=49216 k_2:3816384 64=622656 ,同时可以观察hexdump结果中的3300和3a00,此数十六进制为33和3a,转换成十进制为:51和58,分别和information_schema中的index_id对应上了.
  • 可以发现 主键索引(PRIMARY)的PAGE_LEVEL 为 0300，表示这棵二级索引树的高度为 4,k_2索引的PAGE_LEVEL 为 0200,表示这棵二级索引树的高度为 3.
data im innodb schema

0 人点赞