MYSQL的B+Tree索引树高度如何计算

前一段被问到一个平时没有关注到有关于MYSQL索引相关的问题点,被问到一个表有3000万记录,假如有一列占8位字节的字段,根据这一列建索引的话索引树的高度是多少?

这一问当时就被问蒙了,平时这也只关注MySQL索引一般都是都是用B+Tree来存储维护索引的,还有一些复合索引的最左匹配原则等等,还真没有实际关注过始即然用到索引能提升

查询的效率,那么这个索引树高是多少,给定表和索引字段后怎么计算出索引树的高度?下面将用举例的形式来说明如何计算索引树的高度。

在举例之前,先给出一个千万级记录表的索引的高度大概在3-5的样,当时我看到这个数字时也是很惊讶的!

举例前先做一下举例时用到的公式的一些维度的说明

假设:

 表的记录数是N

 每一个BTREE节点平均有B个索引KEY

那么B+TREE索引树的高度就是logNB(等价于logN/logB)

由于索引树每个节点的大小固定,所以索引KEY越小,B值就越大,那么每个BTREE节点上可以保存更多的索引KEY,也就是B值越大,索引树的高度就越小,那么基于索引的查询的性能就越高。所以相同表记录数的情况下,索引KEY越小,索引树的高度就越小。

现在我们假设表3000W条记录(因为2^25=33554432),如果每个节点保存64个索引KEY,那么索引的高度就是(log2^25)/log64≈ 25/6 ≈ 4.17

通过上面的计算可知,要计一张表索引树的高度,只需要知道一个节点有多,从而就能知道每个节点能存储多少个索引KEY。现代数据库经过不断的探索和优化,并结合磁盘的预读特点,每个索引节点一般都是操作系统页的整数倍,操作系统页可通过命令得到该值得大小,且一般是4094,即4k。而InnoDB的pageSize可以通过命令得到,默认值是16k。

以BIGINT为例,存储大小为8个字节。INT存储大小为4个字节(32位)。索引树上每个节点除了存储KEY,还需要存储指针。所以每个节点保存的KEY的数量为pagesize/(keysize+pointsize)(如果是B-TREE索引结构,则是pagesize/(keysize+datasize+pointsize))。

假设平均指针大小是4个字节,那么索引树的每个节点可以存储16k/((8+4)*8)≈171。那么:一个拥有3000w数据,且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log171 ≈ 25/7.4 ≈ 3.38。

假设平均指针大小是8个字节,那么索引树的每个节点可以存储16k/((8+8)*8)≈128。那么:一个拥有3000w数据,且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log128 ≈ 25/7 ≈ 3.57

由上面的计算可知:一个千万量级,且存储引擎是MyISAM或者InnoDB的表,其索引树的高度在3~5之间

 

参专文章

 

posted @ 2019-04-18 15:58  songpingyi  阅读(20019)  评论(7编辑  收藏  举报