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

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之间。