怎么看innodb的B+TREE层数?,下面以sysbench_testdata.sbtest2为例查看索引层数:

- 查看相关系统

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

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

#hexdump -s 49216 -n 10 ./sbtest2.ibd000c040 0300 0000 0000 0000 3300               000c04a#hexdump -s 622656 -n 10 ./sbtest2.ibd0098040 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.