B-tree 索引提高 MySQL 查询效率的原理

谁借莪１个温暖的怀抱￠ 2022-05-30 02:22 200阅读 0赞

在MySQL中，我们常用的存储引擎 InnoDB 和 MyISAM 的索引都是B-Tree 索引。大家都知道，建立索引的目的便是优化慢查询，那么慢查询究竟慢在哪里呢？

### 查询时间 ###

一个sql查询的时间分为 **等待时间** 和**执行时间**。

等待时间，即sql执行之前所等待的时间。对于mysql数据库来说，在执行某些操作时，InnoDB会锁行，MyISAM会锁表。当即将开始一个新查询时，如果查询所操作的表或者行正被锁定，那么就会有等待时间（一般时间相当短，可以忽略。如果过长，应检查数据库）。

执行时间，sql查询的执行时间分为2个步骤：  
查询：找到符合要求的行。  
取出：将找到的行的数据取回。

时间分布如下图：

![这里写图片描述][SouthEast]

建立索引的目的便是为了减少执行时间。

### B-tree索引 ###

B-tree是一种常见的数据结构。对于 InnoDB 和 MyISAM 来说，在未加索引的情况下，查询一条数据只能进行全表扫描。效率十分低下，添加索引后，通过索引检索数据，效率则会提高千倍万倍。

那么b-tree的查询为何如此快速呢？  
B-tree 可以说是多阶的二叉树，下面我们以二叉树查找来演示，原理与b-tree基本相同：

二叉树具有以下性质：  
\- 任意节点的左子树不空，则左子树上所有结点的值均小于它的根结点的值；  
\- 任意节点的右子树不空，则右子树上所有结点的值均大于它的根结点的值；  
\- 任意节点的左、右子树也分别为二叉查找树；  
\- 没有键值相等的节点。

假设将主键索引存入一颗二叉树，那么在查找主键为27的数据时，二叉树查找与队列查找对比如下：  
![这里写图片描述][SouthEast 1]  
（图中step数忽略了顶层比较）

使用二叉树后，比较次数由11次降到了4次。

在大数据时这一点更加明显：如图，27所在二叉树的第四层，需要进行4次比较操作。  
假设数据量够大，要查找的目标id在第32层存储，那么则需要比较32次便可找到目标记录的id。那么11层二叉树存储了多少数据呢？二叉树每层存储量都会翻倍，那么第32层则存储了 2^32个数据，再加上前10层的数据，总数据量达到了 2^(33)-2 个，大约40多亿条。假设没有索引，直接全表扫描，那么平均则需要检索20亿次，才能找到目标数据。

### 索引排序 ###

建立索引，不仅能够大大提高查询的效率。对排序效率也有很大提高。  
在进行排序操作时，索引树由根节点开始展开，左子树排在父节点之前，右子树排在父节点之后，组成一个整体，再与更上一层父节点重复操作。如图：

![这里写图片描述][SouthEast 2]

[SouthEast]: /images/20220530/1f8de0d9a03c4f42a9ce4c4c61deded3.png
[SouthEast 1]: /images/20220530/3b82cebddcc44443ba2e69659e2ad94d.png
[SouthEast 2]: /images/20220530/9e6938ca9d384a39b1426d3605377d31.png