MySQL——各种索引结构的对比

MySQL——各种索引结构的对比

1、Hash结构

Hash 被称为散列函数，是把任意长度的输入通过散列算法变换成固定长度的输出，该输出就是散列值。

不同的输入可能会散列成相同的输出，所以不可能从散列值来确定唯一的输入值。

Hash 函数它可以帮助我们大幅提升检索数据的效率。

Hash 算法是通过某种确定性的算法（比如MD5、SHA1、SHA2、SHA3）将输入转变为输出。相同的输入永远可以得到相同的输出，假设输入内容有微小偏差，在输出中通常会有不同的结果。

Hash 结构 ，例如 HashMap，查询/插入/修改/删除的平均时间复杂度都是0(1)。

HashMap 底层结构图：

采用Hash进行检索效率非常高，基本上一次检索就可以找到数据，而B+树需要自顶向下依次查找，多次访问节点才能找到数据，中间需要多次I/O操作，从效率来说Hash比 B+树更快。

Hash索引适用于 Memory存储引擎，不适用于 MyISAM 和 InnoDB。

hash冲突

两个不同的关键字经过hash算法后可能被映射到相同的位置，这叫做冲突。在数据库中一般采用链接法来解决。在链接法中，将

经过hash算法计算后hash值相同的关键字放到同一个链表中，当链表超过8个节点时，并且数组长度大于64时，就会把链表转为红黑树，如上图。

hash结构做为索引的缺陷

1、Hash索引仅能满足 (=)，(<>)和 IN 查询。如果进行范围查询，哈希型的索引，时间复杂度会退化为O(n)；而树型索引的“有序”特性，依然能够保持O(log2N)的高效率。

2、Hash索引还有一个缺陷，数据的存储是没有顺序的，在ORDER BY的情况下，使用Hash索引还需要对数据重新排序。

3、对于联合索引的情况，Hash值是将联合索引键合并后一起来计算的，无法对单独的一个键或者几个索引键进行查询。

4、对于等值查询来说，通常Hash索引的效率更高，不过也存在一种情况，就是索引列的重复值如果很多，效率就会降低。这是因为遇到Hash冲突时，需要遍历桶中的行指针来进行比较，找到查询的关键字，非常耗时。所以,Hash索引通常不会用到重复值多的列上，比如列为性别、年龄的情况等。

hash结构做为索引适用的情况

Hash索引存在着很多限制，相比之下在数据库中B+树索引的使用面会更广，不过也有一些场景采用Hash 索引效率更高，比如在键值型(Key-Value）数据库中，Redis存储的核心就是 Hash表。

MySQL中的Memory存储引擎支持Hash存储，如果我们需要用到查询的临时表时，就可以选择Memory存储引擎，把某个字段设置为Hash索引，比如字符串类型的字段，进行Hash计算之后长度可以缩短到几个字节。当字段的重复度低，而且经常需要进行等值查询的时候，采用Hash索引是个不错的选择。

另外，InnoDB 本身不支持Hash索引，但是提供自适应 Hash索引(Adaptive Hash Index)。什么情况下才会使用自适应Hash索引呢?如果某个数据经常被访问，当满足一定条件的时候，就会将这个数据页的地址存放到Hash表中。这样下次查询的时候，就可以直接找到这个页面的所在位置。这样让B+树也具备了Hash索引的优点。

采用自适应hash索引的目的是方便根据sql的查询条件加速定位到叶子节点，特别是当B+树比较深的时候，通过自适应hash索引可以明显的提高数据检索效率。

查看是否开启 自适应hash索引

默认开启：

2、AVL 平衡二叉搜索树

AVL树本质还是一棵二叉查找树，只是在其基础上增加了“平衡”的要求。所谓平衡是指，对AVL树的任意结点来说，其左子树与右子树的高度之差的绝对值不超过，并且左右子树都是一颗二叉平衡树。

如下所示，就是一棵由{1，2，3，4，5，7，8}构建的AVL树：

如果我们采用二叉树的形式，即使通过平衡二叉搜索树进行了改进，树的深度也是O(log2n)，当n比较大时，深度也是比较大的。

每访问一次节点就需要进行一次磁盘I/O操作，对于上面的树来说，我们需要进行4次I/O操作。虽然平衡二叉树的效率高，但是树的深度也同样高，这就意味着磁盘I/O操作次数多，会影响整体数据查询的效率。

3、B-Tree

B-tree（多路平衡搜索树，并不是二叉的）是一种常见的数据结构。使用B-tree结构可以显著减少定位记录时所经历的中间过程，从而加快数据存取速度。

m阶B-Tree满足以下条件：

1、每个节点最多拥有m个子树

2、根节点至少有2个子树

3、分支节点至少拥有m/2颗子树（除根节点和叶子节点外都是分支节点）

4、所有叶子节点都在同一层、每个节点最多可以有m-1个key，并且以升序排列

B-Tree 的结构如下图：

1、B树在插入和删除节点的时候如果导致树不平衡，就通过自动调整节点的位置来保持树的自平衡。

2、关键字集合分布在整棵树中,即叶子节点和非叶子节点都存放数据。搜索有可能在非叶子节点结束

3、其搜索性能等价于在关键字全集内做一次二分查找。

4、B+Tree

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，主流的 DBMS 都支持 B+树的索引方式。

InnoDB存储引擎就是用B+Tree实现其索引结构。

相比 B-Tree，B+Tree 适合文件索引系统。

在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息，用于索引。
2. 非叶子节点的关键字也会同时存在子节点中，并且是子节点中所有关键字最大或最小的。
3. 所有叶子节点之间都有一个链指针。
4. 数据记录都存放在叶子节点中，叶子结点构成一个有序链表。
5. B+Tree查询效率更高更稳定，查询范围上，B+Tree也比B-Tree大。