Do What You Want To Be

0%

MySQL获取索引树高度

发表于 更新于 分类于
本文字数： 5.2k 阅读时长 ≈ 5 分钟

假设表存在N行数据，每个索引节点平均有M个索引key，这时候索引的树高度则为logN/logM。由于索引页大小固定，KEY越小，M值就会越大，索引高度就越小，遍历树的效率就更高。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
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位置偏移量的数据，就能计算出索引树高

1
2
3
4
5
6
7
8
9
# 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为例进行说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
[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