Mysql索引探究

1. 哈希表

也称散列表，通过将任意值作为key通过哈希函数变换为固定长度的地址。

根据上图，假设有个用户信息表为user_info，当我们想查询John Smith的信息，sql语句如下

select * from user_info where name = 'John Smith';

哈希算法首先计算存储John Smith 这个key的哈希code，为02，与之对应的是磁盘中的521-1234这个地址，通过该地址就可以找到对应的这条数据的位置，从而查询出整条信息。这就是哈希算法检索数据的计算过程。

使用哈希算法时需要注意一个问题：哈希碰撞。是指不同的key通过哈希函数计算出来的结果是相同的，举个例子，John Smith 和 Bob Smith计算的结果都为02，这样不同的key就映射到同一个结果了。解决这个问题的常见处理方式就是链表，用链表将碰撞的数据连接起来。当计算完哈希函数后，需要遍历链表，直至找到真正对应key数据为止。

从算法的时间复杂度来看，哈希算法的复杂度为O（1），检索很快，但是mysql并没有将其作为底层算法，其原因是由于这种算法对查询范围数据太不友好了。举个例子：

select * from user_type where name != 'John Smith'

针对这条语句，我希望查出除去John Smith以外的所有数据，如果用哈希算法去实现，那么我需要将所有数据查出来加载到内存中，然后在内存去筛选符合条件的数据。这种方法过于笨重，且没有任何效率。

2.二叉树

这是一种支持数据快速查找的数据结构，二叉树的时间复杂度是O（logn）,它可以用来解决范围查找的问题。在二叉树中，节点的左子树一般比节点的值小，右子树比节点的值大，查询范围时，如果是大于，则取右子树的值即可，如果是不等于，则取除节点意外的左右子树，这很容易去实现。

但是普通的二叉树有个致命的缺点：极端情况下会退化成线性链表，二分查找也会退化为遍历查询，时间复杂度变为O（n），检索性能急剧下降，此时已经不能够提供快速的服务了。在数据库中，主键自增是个很常见的行为，如果采用二叉树作为索引，那么成为一个线性链表就是必然结果，所以无法作为索引的底层算法。

为了解决二叉树带来的问题，开发者们提出通过树节点的自旋和调整，让二叉树始终保持基本平衡的状态，这样就可以保证二叉树查找的最佳性能了。红黑树与平衡二叉树就是基于这个思路产生的。

3.红黑树

这是一颗会自动调整树节点形态的树结构，举个例子，当二叉树处于一个不平衡状态时，红黑树就会自动左旋或者右旋以及节点变色，调整树的形态，使其保持基本的平衡状态（时间复杂度为O（lgn）），也就保证了查找效率不会明显减低。

如上图所示，如果想要查找id=7的值，需要比较的节点数为4，依然保持着不错的查找效率。

但是即便是红黑树，也存在着一些极端的O（n）情况，举个例子，红黑树顺序插入1~7个节点，查找id=7的计算节点数为4，那么顺序插入1～16个节点，查找id=16需要计算的节点数为6，通过观察下图中红黑树的形态，当顺序插入时，树一直处于一种右倾的趋势。所以从根本上来说，红黑树并没有完全解决二叉树退化为线性链表的问题，如果将红黑树作为数据库的索引，自增的主键可能是成千上万的，这种问题会消耗巨大的查询性能，并且我们也无法忍受数据库响应这么慢的。

4.平衡二叉树

这是一种绝对平衡的二叉树，因此它在调整二叉树形态时会消耗更多的性能。

平衡二叉树顺序插入1～16个节点，查找id=16需要计算的节点数为4。从查找效率来说，平衡二叉树的速度要高于红黑树的查找效率，而且在形态上也不存在右倾的问题，也就是说，从根本上解决了红黑树的问题。

看起来平衡二叉树作为数据查找的数据结构是个不错的选择，但是它并不适合作为Mysql数据库的索引数据结构。是因为数据库查询数据的瓶颈在于磁盘IO，如果使用的是平衡二叉树，那么每个树节点只存一个数据，我们一次磁盘IO只能取出一个节点加载到内存中，这无疑是很消耗时间的，所以在我们设计数据库时，需要尽可能的去减少磁盘IO的次数。

磁盘IO有个特点：无论是读取1B还是1KB的数据，消耗的时间基本上是一样的，顺着这个思路，我们可以在一个节点上尽可能多的存储数据，这样每进行一次磁盘IO就可以多加载一些数据到内存中，这就是B树与B+树的设计原理了。

5.B树

下图中的这个B树，每个节点限制最多存储2个key，一个节点如果超过两个key就会自动分裂。举个例子，这个存储了7个数据的B树，只需要查询两个节点接可以找到id=7这个数据的具体位置，也就是两次磁盘IO就可以拿到想要的结果，这种查询效率要优于平衡二叉树。

下图是一个存储了16个数据的B树，同样每个节点限制最多存储2个key，查询ID=16需要比较四个节点，也就是四次磁盘IO，性能与平衡二叉树相似。

但是考虑到磁盘IO的特点，我们可以进行优化：尽可能在一次磁盘IO中多读一点数据到内存，即增加每个节点存储的key。

当我们把单个节点限制的key个数设置为16时，一个存储了7个数据的B树，查询id=7这个数据仅需要磁盘IO2次，与平衡二叉树相比，效率提升了一倍。

这里总结一下B树作为数据库索引的优点：

1. 具有优秀的检索速度与时间复杂度
2. 尽可能的减少了磁盘IO，加快检索次数
3. 支持范围查找

那么，B树与B+树有什么区别呢？

6.B+树

首先，B树一个节点里存的是数据，而B+树存储的是索引，在B树里一个节点存不了很多个数据，但是B+树里一个节点可以存储很多索引，B+树的叶子结点存所有数据。其次，B+树的叶子结点是数据节点用了一个链表串联起来，便于范围查找。

最后，由于B+树的叶子节点是通过链表串联起来的，这个链表本身就是有序的，在范围查找时具有更高的效率。

因此Mysql的索引就是用的B+树数据结构。