怎么看innodb的B+TREE层数?,下面以sysbench_testdata.sbtest2为例查看索引层数:
- 查看相关系统
[email protected] [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)
[email protected] [sysbench_testdata]>select count(id) from sbtest2;
+-----------+
| count(id) |
+-----------+
| 67840914 |
+-----------+
1 row in set (56.87 sec)
- 查看information_schema中相关表信息,注意索引的PAGE_NO和:index_id
[email protected] [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)
[email protected] [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.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.