MySQL各种索引算法

Mysql各种索引算法

​ --洱涷

---

​ 首先梳理一下当我们在数据库中输入一个SQL进行查询时，DBMS（数据库管理系统）所经历的流程：

1. 对sql语法进行校验，看齐是否符合sql语法规则
2. 对合法的语句进行语义检查，即根据数据字典中有关模式定义检查数据库对应是否有效
3. 查询优化，在DBMS中每一个查询都有许多可以选择的执行策略和操作算法，查询优化即选择其中一个较高效的去执行
4. 根据上一步优化器得到的执行策略生成查询执行计划，有代码生成器生成执行这个查询计划的代码，然后执行代码，回送查询结果

​ 在上述步骤的3中的查询算法分为以下两种：

• 全表扫描算法
• 索引扫描算法

全表扫描算法

​ 全表扫描就是对数据库服务器用来搜寻表的每一条记录的过程，直到所有符合给定条件的记录都被返回为止（基本没人用），因为查询一笔数据还是查询多笔数据，查询的数据成本是不变的，如果只想要一条数据，如果表小那还好，如果数据量很大，那么效率将会非常低，所以日常业务不会考虑，针对上述情况，索引查询应运而生

索引扫描算法

​ 索引的思想就是根据表中的某一条属性建立一套算法，每次查询时，在内存中根据该算法得到所需数据的物理存储地址，根据地址直接去内存中获取结果数据，就像查字典时根据偏旁或拼音获得该字的页码然后获取结果

​ 索引的扫描算法有很多种，常见的是B-Tree，B+Tree，hash。MySQL默认建表查询索引为B+tree索引

B-Tree

​ B-树,这里的 B 表示 balance( 平衡的意思),B-树是一种多路自平衡的搜索树（B树是一颗多路平衡查找树），它类似普通的平衡二叉树，不同的一点是B-树允许每个节点有更多的子节点。下图是 B-树的简化图.

​ B-树有如下特点:

1. 所有键值分布在整颗树中（索引值和具体data都在每个节点里）；
2. 任何一个关键字出现且只出现在一个结点中；
3. 搜索有可能在非叶子结点结束（最好情况O(1)就能找到数据）；
4. 在关键字全集内做一次查找,性能逼近二分查找；

传统用来搜索的平衡二叉树有很多，如 AVL 树，红黑树等。这些树在一般情况下查询性能非常好，但当数据非常大的时候它们就无能为力了。原因当数据量非常大时，内存不够用，大部分数据只能存放在磁盘上，只有需要的数据才加载到内存中。一般而言内存访问的时间约为 50 ns，而磁盘在 10 ms 左右。速度相差了近 5 个数量级，磁盘读取时间远远超过了数据在内存中比较的时间。这说明程序大部分时间会阻塞在磁盘 IO 上。那么我们如何提高程序性能？减少磁盘 IO 次数，像 AVL 树，红黑树这类平衡二叉树从设计上无法“迎合”磁盘。

​ 平衡二叉树是通过旋转来保持平衡的，而旋转是对整棵树的操作，若部分加载到内存中则无法完成旋转操作。其次平衡二叉树的高度相对较大为 log n（底数为2），这样逻辑上很近的节点实际可能非常远，无法很好的利用磁盘预读（局部性原理），所以这类平衡二叉树在数据库和文件系统上的选择就被 pass 了。

B-树的设计原理

​ 索引的效率依赖于磁盘 IO 的次数，快速索引需要有效的减少磁盘 IO 次数，如何快速索引呢？索引的原理其实是不断的缩小查找范围，就如我们平时用字典查单词一样，先找首字母缩小范围，再第二个字母等等。平衡二叉树是每次将范围分割为两个区间。为了更快，B-树每次将范围分割为多个区间，区间越多，定位数据越快越精确。那么如果节点为区间范围，每个节点就较大了。所以新建节点时，直接申请页大小的空间（磁盘存储单位是按 block 分的，一般为 512 Byte。磁盘 IO 一次读取若干个 block，我们称为一页，具体大小和操作系统有关，一般为 4 k，8 k或 16 k），计算机内存分配是按页对齐的，这样就实现了一个节点只需要一次 IO。

B-树的查找策略

​ 我们来看看B-树的查找，假设每个节点有 n 个 key值，被分割为 n+1 个区间，注意，每个 key 值紧跟着 data 域，这说明B-树的 key 和 data 是聚合在一起的。一般而言，根节点都在内存中，B-树以每个节点为一次磁盘 IO，比如上图中，若搜索 key 为15 节点的 data，首先在根节点进行二分查找（因为 keys 有序，二分最快），判断 key 15 小于 key 25，所以定位到最左侧的节点，此时进行一次磁盘 IO，将该节点从磁盘读入内存，接着继续进行上述过程，直到找到该 key 为止。

B+Tree

​ B+树是B-树的变体，也是一种多路搜索树，将相关数据构建成一棵B+树，通过索引获取查询想要的的结果。

​ B+树举例：

​ B+树不是二叉树，其特征大概分为以下几点：

• B+树不是二叉树，m阶的B+树其子树最多分m叉

• 所有的叶子节点位于同一层

• 所有的叶子节点包含了全部的元素信息，即包含指向这些元素的指针

• 叶子节点本身依靠关键字的大小从小到大依次顺序链接

• 所有的中间节点只保存索引信息，不保存数据信息（也就是说想找到数据，必须从根节点找到叶子节点）

• 所有中间结点的元素都存在于子节点上，在子节点元素中是最大或者最小的元素

  以上特征在图中的体现：