假设表存在N行数据，每个索引节点平均有M个索引key，这时候索引的树高度则为logN/logM。由于索引页大小固定，KEY越小，M值就会越大，索引高度就越小，遍历树的效率就更高。
mysql> select * from information_schema.innodb_sys_tables a left join information_schema.innodb_sys_indexes b on a.table_id=b.table_id where a.name='employees/employees';
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+----------+---------+----------+------+----------+---------+-------+-----------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+----------+---------+----------+------+----------+---------+-------+-----------------+
| 50 | employees/employees | 41 | 9 | 37 | Barracuda | Compressed | 8192 | Single | 57 | PRIMARY | 50 | 3 | 1 | 3 | 37 | 50 |
+----------+---------------------+------+--------+-------+-------------+------------+---------------+------------+----------+---------+----------+------+----------+---------+-------+-----------------+
mysql> select * from information_schema.innodb_sys_tables a left join information_schema.innodb_sys_indexes b on a.table_id=b.table_id where a.name='employees/titles';
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+----------+----------+----------+------+----------+---------+-------+-----------------+
| TABLE_ID | NAME | FLAG | N_COLS | SPACE | FILE_FORMAT | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INDEX_ID | NAME | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+----------+----------+----------+------+----------+---------+-------+-----------------+
| 62 | employees/titles | 33 | 7 | 49 | Barracuda | Dynamic | 0 | Single | 73 | PRIMARY | 62 | 3 | 3 | 3 | 49 | 50 |
| 62 | employees/titles | 33 | 7 | 49 | Barracuda | Dynamic | 0 | Single | 74 | idx_date | 62 | 0 | 1 | 12 | 49 | 50 |
+----------+------------------+------+--------+-------+-------------+------------+---------------+------------+----------+----------+----------+------+----------+---------+-------+-----------------+
|
hexdump
主键索引B+树的根页节点从表空间文件的第三个页开始，所以它在表空间文件的偏移量为16384*3=49152(16384为block size)，而page level保存在根页节点在的64位偏移量的前两个字节，因此我们可以通过hexdump来查看表空间文件上位于49152+64位置偏移量的数据，就能计算出索引树高
# block size=8K
[root@t-luhx02-v-szzb employees]# hexdump -C -s 24640 -n 10 employees.ibd
00006040 00 02 00 00 00 00 00 00 00 39 |.........9|
0000604a
# block size=16k
[root@t-luhx02-v-szzb employees]# hexdump -C -s 49216 -n 10 titles.ibd
0000c040 00 01 00 00 00 00 00 00 00 49 |.........I|
0000c04a
|
在这里employees的主键索引树高为3，titles树高为2
innblock
我们也可以借助innodb_ruby或者innblock这类的工具来查看相关页信息，这里就以innblock为例进行说明
[root@t-luhx02-v-szzb employees]# /media/mysql/innblock titles.ibd 3 16
----------------------------------------------------------------------------------------------------
Welcome to use this block analyze tool:
[Author]:gaopeng [Blog]:blog.itpub.net/7728585/abstract/1/ [QQ]:22389860
[Review]:yejinrong@zhishutang [Blog]:imysql.com [QQ]:4700963
----------------------------------------------------------------------------------------------------
==== Block base info ====
block_no:3 space_id:49 index_id:73
slot_nums:3 heaps_rows:10 n_rows:8
heap_top:333 del_bytes:0 last_ins_offset:317
page_dir:2 page_n_dir:7
leaf_inode_space:49 leaf_inode_pag_no:2
leaf_inode_offset:242
no_leaf_inode_space:49 no_leaf_inode_pag_no:2
no_leaf_inode_offset:50
last_modify_lsn:294095967
page_type:B+_TREE level:1
==== Block list info ====
-----Total used rows:10 used rows list(logic):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) normal record offset:127 heapno:2 n_owned 0,delflag:N minflag:1 rectype:1
(3) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0 rectype:1
(4) normal record offset:179 heapno:4 n_owned 0,delflag:N minflag:0 rectype:1
(5) normal record offset:209 heapno:5 n_owned 4,delflag:N minflag:0 rectype:1
(6) normal record offset:235 heapno:6 n_owned 0,delflag:N minflag:0 rectype:1
(7) normal record offset:261 heapno:7 n_owned 0,delflag:N minflag:0 rectype:1
(8) normal record offset:291 heapno:8 n_owned 0,delflag:N minflag:0 rectype:1
(9) normal record offset:317 heapno:9 n_owned 0,delflag:N minflag:0 rectype:1
(10) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
-----Total used rows:10 used rows list(phy):
(1) INFIMUM record offset:99 heapno:0 n_owned 1,delflag:N minflag:0 rectype:2
(2) SUPREMUM record offset:112 heapno:1 n_owned 5,delflag:N minflag:0 rectype:3
(3) normal record offset:127 heapno:2 n_owned 0,delflag:N minflag:1 rectype:1
(4) normal record offset:153 heapno:3 n_owned 0,delflag:N minflag:0 rectype:1
(5) normal record offset:179 heapno:4 n_owned 0,delflag:N minflag:0 rectype:1
(6) normal record offset:209 heapno:5 n_owned 4,delflag:N minflag:0 rectype:1
(7) normal record offset:235 heapno:6 n_owned 0,delflag:N minflag:0 rectype:1
(8) normal record offset:261 heapno:7 n_owned 0,delflag:N minflag:0 rectype:1
(9) normal record offset:291 heapno:8 n_owned 0,delflag:N minflag:0 rectype:1
(10) normal record offset:317 heapno:9 n_owned 0,delflag:N minflag:0 rectype:1
-----Total del rows:0 del rows list(logic):
-----Total slot:3 slot list:
(1) SUPREMUM slot offset:112 n_owned:5
(2) normal record offset:209 n_owned:4
(3) INFIMUM slot offset:99 n_owned:1
|
Tips：innblock
)