获取 MySQL innodb B+tree 的高度的方法

2023-07-13 11:32:03 258

前言

MySQL的innodb引擎之所以使用B+tree来存储索引，就是想尽量减少数据查询时磁盘IO次数。树的高度直接影响了查询的性能。一般树的高度在3~4层较为适宜。数据库分表的目的也是为了控制树的高度。那么如何获取树的高度呢？下面使用一个示例来说明如何获取树的高度。

示例数据准备

建表语句如下：

CREATETABLE`user`(
  `id`int(11)NOTNULLAUTO_INCREMENT,
  `name`varchar(100)CHARACTERSETlatin1DEFAULTNULL,
  `age`int(11)DEFAULTNULL,
  PRIMARYKEY(`id`),
  KEY`name`(`name`),
  KEY`age`(`age`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8

表中插入100万条数据。数据如下：

mysql>select*fromuserlimit2\G
***************************1.row***************************
  id:110000
name:ab
 age:100
***************************2.row***************************
  id:110001
name:ab
 age:100
2rowsinset(0.00sec)

通过查询相关数据表获取树的高度

以MySQL5.6版本为例说明如何获取树的高度。

首先获取page_no

mysql>SELECTb.name,a.name,index_id,type,a.space,a.PAGE_NOFROMinformation_schema.INNODB_SYS_INDEXESa,information_schema.INNODB_SYS_TABLESbWHEREa.table_id=b.table_idANDa.space<>0andb.name='test/user';
+-----------+---------+----------+------+-------+---------+
|name     |name   |index_id|type|space|PAGE_NO|
+-----------+---------+----------+------+-------+---------+
|test/user|PRIMARY|      22|   3|    6|      3|
|test/user|name   |      23|   0|    6|      4|
|test/user|age    |      24|   0|    6|      5|
+-----------+---------+----------+------+-------+---------+
3rowsinset(0.00sec)

page_no是索引树中Root页的序列号。其它各项的含义可以参照：
https://dev.mysql.com/doc/refman/5.6/en/innodb-sys-indexes-table.html

再读取页的大小

mysql>showglobalvariableslike'innodb_page_size';
+------------------+-------+
|Variable_name   |Value|
+------------------+-------+
|innodb_page_size|16384|
+------------------+-------+
1rowinset(0.00sec)

最后读取索引树的高度

$hexdump-s49216-n10./user.ibd
000c04002000000000000001600
000c04a

可以发现PAGE_LEVEL为0200，表示这棵二级索引树的高度为3。后面的1600是索引的index_id值。十六进制的16转换为十进制数字是22。这个22正好就是上面主键的index_id。
上面hexdump命令中49216是怎么算出来的？公式是page_no*innodb_page_size+64。
3*16384+64=49216

我们在用这个方式查看下其他两个索引的高度。

$hexdump-s65600-n10./user.ibd
001004001000000000000001700
001004a
$hexdump-s81984-n10./user.ibd
001404002000000000000001800
001404a

可见，name索引的高度是2，age索引的高度是3。

根据索引的结构估算

如果你没有数据库服务器的权限。自己也可以根据数据库索引结构进行估算树的高度。
根据B+Tree结构，非叶子节点存储的是索引数据，叶子节点存储的是每行的所有数据。
非叶子节点每个索引项的大小是，数据大小+指针大小。假设指针大小为8个字节。每页不会被占满，预留1/5的空隙。下面我们估算下name和age两个索引的高度。

name索引高度估算

非叶子节点每页存放的索引项数量。每页大小是16k。name的值为ab。占2个字节。每项数据大小是2+8=10字节。每页能存放的索引项数量是16384*0.8/10=1310个。
叶子节点每页存放的索引数量。每页大小是16k。每项数据大小是4+2+8=14个字节。没页能存放的索引数量是16384*0.8/14=936个。
两层能存放1310*936=1226160个数据记录。可见120万条记录以下，树的高度为2。

age索引高度估算

非叶子节点每页存放的索引项数量。每页大小是16k。age的类型为int。占4个字节。每项数据大小是4+8=12字节。每页能存放的索引项数量是16384*0.8/12=1092个。
叶子节点每页存放的索引数量。每页大小是16k。每项数据大小是4+4+8=16个字节。没页能存放的索引数量是16384*0.8/16=819个。
两层能存放1092*819=894348个数据记录。可见90万条记录以下，树的高度为2。100万条为3层。

其它工具

还有一个小工具可以查看。InnoDB表空间可视化工具innodb_ruby

以上就是获取MySQLinnodb的B+tree的高度的示例的详细内容，更多关于MySQLinnodb的B+tree的资料请关注毛票票其它相关文章！

获取 MySQL innodb B+tree 的高度的方法

热门推荐

随机推荐