MySql的Btree与Hash如何选择，以及Innodb的伪Hash和自适应索引

前言

在mysql的常见的三种引擎中：InnoDB、MyISAM、MEMORY

这三个引擎中，仅有Memory 引擎显式支持哈希索引，其他的引擎只支持Btree索引

Hash索引（Adaptive Hash Index）

但是InnoDB 支持自适应Hash索引（Adaptive Hash Index）

mysql> show variables like "innodb_adaptive_hash_index";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

AHI（Adaptive Hash index）创建条件及注意事项

1. 重复访问某一特定查询模式达到一定数量才会创建，如WHERE a = XXX,WHERE a = xxx and b = xxx。
2. AHI 只支持等值查询=和IN，不支持LIKE, REGEXP等。
3. AHI 存在内存中，占用缓冲池资源(Buffer Pool)。
4. AHI 无法人为干预，只能配置开关：set global innodb_adaptive_hash_index=off/on。配置完开关也仅仅是允许他可以变成AHI，但是变不变是MySQL自己的事
5. 详细的自适应Hash索引请参考MySQL（七）：InnoDB 自适应Hash索引（Adaptive Hash Index）https://blog.csdn.net/u010647035/article/details/104729686

MySQL的BTREE索引和HASH索引

B-TREE索引

特点：

• B-TREEB-TREE以B+树结构存储数据，大大加快了数据的查询速度
• B-TREE索引在范围查找的SQL语句中更加适合（顺序存储）

B-TREE索引使用场景：

• 全值匹配的查询SQL，如 where user_id= '1111'（Hash更适合，因为只需要计算一次Hash然后找到Hash所在的地址，如果是链表再查一次，如果不是链表，直接查出来就可以）
• 联合索引汇中匹配到最左前缀查询，如联合索引 KEY idx_user_name(user_id,user_name) USING BTREE，只要条件中使用到了联合索引的第一列，就会用到该索引，但如果查询使用到的是联合索引的第二列act_name，该SQL则便无法使用到该联合索引（注：覆盖索引除外）
• 匹配模糊查询的前匹配，如where user_name like '11_Li%'
• 匹配范围值的SQL查询，如where user_creatdate > '9865123547215'（not in和<>无法使用索引）
• 覆盖索引的SQL查询，就是说select出来的字段都建立了索引

HASH索引

HASH的特点&使用场景 ：

• Hash索引基于Hash表实现，只有查询条件精确匹配Hash索引中的所有列才会用到hash索引比如select * from table where no=1;
• 存储引擎会为Hash索引中的每一列都计算hash码，Hash索引中存储的即hash码，所以每次读取都会进行两次查询
• Hash索引无法用于排序
• Hash不适用于区分度小的列上，如性别字段
• hash索引中的hash码的计算可能存在hash冲突，如果出现hash冲突，则需要遍历这条hash值上面的所有链表，如果hash冲突过多时，就不如使用Btree

如果存储引擎（innodb）不支持Hash索引，我们可以实现一个伪hash

基于Btree的伪Hash

思路也比较简单，就是在B-tree基础上创建一个伪哈希索引。这和真正的hash索引不是一回事，因为还是采用B-Tree进行查找，但是它使用的是hash值而不是键本身进行查找。只需要在查询的where子句中手动指定使用hash函数即可。下面举个简单的例子：

比如：当我们需要存储大量的文件名字，图片名字等字符串的信息时，并需要根据字符串进行搜索查找。若用B-Tree来存储字符串，存储的内容就会很大。此时的查询语句就是

select id from url where fileName= "2022-01-02-ps-learn.jpg";

若删除原来的fileName列上的索引，而新增一个被索引的fileName_crc32列，使用crc32做hash函数，则可以使用如下方式查询：

select id from url where fileName= "2022-01-02-ps-learn.jpg" and fileName_crc=CRC32("2022-01-02-ps-learn.jpg");

这样做的话，性能就会有很大提升，因为mysql优化器会使用这个选择性高而体积很小的基于fileName_crc32列的多音来完成查找。即使有多个记录相同的索引值，查找仍然很快，只需要根据hash值做快速的整数比较就能找到索引条目，然后一一返回对应的行。

缺点

• 需要维护hash值，可以手动维护，也可以使用触发器实现。
• 若数据表非常大的话，CRC32（）会出现大量hash冲突，则可以自己实现一个64位的hash函数，这个自定义的hash函数要返回整数而不是字符串，因为范围整数，对此效率更高。一个简单的办法就是使用MD5（）函数返回值的一部分来作为自定义的hash函数。但是这可能比自己写一个hash算法性能要差一些。