MySQL支持的两大索引类型,hash索引和B-tree索引的区别和优缺点

B-Tree索引

B-Tree索引是一个平衡查找树，叶子到根部的节点距离相等。所有的记录都是按照键值的大小排列，叶子结点由指针连接。

B-Tree索引的优点：

①B-tree索引更适合进行范围查询

②存储引擎不需要进行全表扫描来获得需要的数据，取而代之的是从索引的根节点开始进行搜索。然后根据指针逐层向下查找，通过比较节点页的值和有目标值就可以找到合适的指针进入下层节点，而这些指针实际上定义了子节点页中值的上限和下限

B-Tree索引的限制：

1、若不是按照索引的最左列开始查找，则无法使用该索引

比如建立联合索引（name 、phone_num），若搜索phone_num则无法使用该索引

2、使用索引时，不能跳过索引中的列

比如建立联合索引（name 、phone_num 、addr），若搜索name和addr 则无法使用该索引只能使用那么过滤

3、not in 和 <> 操作无法使用该索引

4、若查询中有某个列的范围查询，则其右边的所有列都无法使用索引

HASH索引

在MySQL的存储引擎中，MyISAM不支持哈希索引，而InnoDB中的hash索引是存储引擎根据B-Tree索引自建的，后面会对其做具体说明。

hash索引的特点

1、hash索引是基于hash表实现的，只有查询条件精确匹配hash索引中的所有列的时候，才能用到hash索引。

2、对于hash索引中的所有列，存储引擎都会为每一行计算一个hash码，hash索引中存储的就是hash码。

3、hash索引包括键值、hash码和指针 。

因为hash索引本身只需要存储对应的hash值，所以索引的结构十分紧凑，这也让hash索引查找的速度非常快。

Hash索引的限制：

1、Hash索引必须进行二次查找

使用哈市索引两次查找，第一次找到相应的行，第二次读取数据，但是被频繁访问到的行一般会缓存在内存中，这点对数据库性能的影响不大。

2、hash索引不能用于外排序

hash索引存储的是hash码而不是键值，所以无法用于外排序

3、hash索引不支持部分索引查找也不支持范围查找

只能用到等值查询，不能范围和模糊查询

4、hash索引中的hash码的计算可能存在hash冲突

当出现hash冲突的时候，存储引擎必须遍历整个链表中的所有行指针，逐行比较，直到找到所有的符合条件的行，若hash冲突很多的话，一些索引的维护代价机会很高，所以说hash索引不适用于选择性很差的列上（重复值很多）。姓名、性别、身份证（合适）

上面说到InnoDB的“自适应hash索引”。就是当InnoDB注意到某些索引值被使用的非常频繁时，它会在内存中基于B-Tree索引上在创建一个hash索引，这样就让B-tree索引也具有hash索引的一些优点。这是一个完全自动的内部的行为，用户无法控制或配置，不过，如果有需要，完全可以关闭该功能。