mysql（三）InnoDB之自适应hash索引

前言

InnoDB 用户无法手动创建哈希索引，如果从这一层面来说，InnoDB 不支持哈希索引，但是InnoDB 会自调优 (self-tuning)，如果判定建立自适应哈希索引 (Adaptive Hash Index, AHI)，能够提升查询效率，InnoDB 自己会建立相关哈希索引，如果从这一层来说，InnoDB 是支持哈希索引的

自适应哈希索引 (Adaptive Hash Index, AHI)

新建一张表

CREATE TABLE `user`  (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NULL,
  `sex` char(4) NULL,
  `flag` char(4) NULL,
  PRIMARY KEY (`id`),
  INDEX `name`(`name`) USING BTREE
);

id 是主键，name 建了普通索引。

在表中插入四条记录：

• 1, shenjian, m, A

• 3, zhangsan, m, A

• 5, lisi, m, A

• 9, wangwu, f, B

如上图，通过前序知识，容易知道 InnoDB 在主键 id 上会建立聚集索引 (Clustered Index)，叶子存储记录本身，在 name 上会建立普通索引 (Secondary Index)，叶子存储主键值。

发起主键 id 查询时，能够通过聚集索引，直接定位到行记录。

select * from t where name='ls'

发起普通索引查询时：

1. 会先从普通索引查询出主键（上图右边）；

2. 再由主键，从聚集索引上二次遍历定位到记录（上图左边）。

不管聚集索引还是普通索引，记录定位的寻路路径 (Search Path) 都很长。

在 MySQL 运行的过程中，如果 InnoDB 发现，有很多 SQL 存在这类很长的寻路，并且有很多 SQL 会命中相同的页面 (page)，InnoDB 会在自己的内存缓冲区 (Buffer) 里，开辟一块区域，建立自适应哈希所有 AHI，以加速查询。

从这个层面上来说，InnoDB 的自使用哈希索引，更像 “索引的索引”，毕竟其目的是为了加速索引寻路。

既然是哈希，key 是什么，value 是什么？

key 是索引键值（或者键值前缀），value 是索引记录页面位置。

为啥叫 “自适应 (adaptive)****” 哈希索引？

系统自己判断 “应该可以加速查询” 而建立的，不需要用户手动建立，故称“自适应”。

系统会不会判断失误，是不是一定能加速？

不是一定能加速，有时候会误判。

当业务场景为下面几种情况时：

• 很多单行记录查询（例如 passport，用户中心等业务）
• 索引范围查询（此时 AHI 可以快速定位首行记录）
• 所有记录内存能放得下

AHI 往往是有效的。当业务有大量 like 或者 join，AHI 的维护反而可能成为负担，降低系统效率，此时可以手动关闭 AHI 功能

创建自定义的hash索引

如果存储引擎不支持hash索引，则可以模拟像InnoDB一样创建hash索引，这样可以得到hash索引带来的便利，如只需要 很小的索引就可以为超长的键创建索引

思路

在B-Tree的基础上创建一个伪hash索引，这和真正的hash索引不是一回事，因为还是使用B-Tree进行查找，但是它使用hash值而不是键本身进行索引查找。我们需要做的就是在查询的WHERE字句中手动指定使用hash函数

示例

此时我么新建一张表，需要存储大量的URL,并且根据URL进行搜索查找。如果使用B-tree来存储URL，存储的内容就会很大，因为URL本身都很长。

新建一张表

create table pseudohash(
id int UNSIGNED not null auto_increment,
url VARCHAR(255) not null,
url_crc int UNSIGNED not null DEFAULT 0,
PRIMARY KEY(id)
) 

然后创建触发器

CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW
BEGIN	
	SET NEW.url_crc = CRC32(
	NEW.url);
	end;
	
	
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW
BEGIN	
	SET NEW.url_crc = CRC32(
	NEW.url);
	end;

插入数据

一般情况，是在url建索引查询

select id from pseudohash where url ='www.baidu.com123217'

但是我们删除了url上面的索引，对url_crc使用CRC32做hash，就可以使用下面的方式进行查询

select id from pseudohash where url='www.baidu.com123217' and url_crc = CRC32('www.baidu.com123217')

这样做的性能就会很高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找。即使有多个记录有相同的索引值，朝招仍然很快，只需要根据hash值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。但是这样实现的缺陷就是需要维护hash值。可以手动维护，也可选择使用触发器

如果采用这种方式，最好不要使用SHA1()和MD5()作为hash函数。因为这两个函数计算出来的hash值是非常长的字符串，会浪费大量空间，比较时也会更慢。SHA1()和MD5()是强加密函数，设计的目标是最大限度的消除冲突，但这里并不需要这样高德要求。简单的hash函数的冲突在一个可以接受的范围，同时又能提供更好的性能。

如果表非常大，CRC32()会出现大量的hash冲突，则可以考虑自己实现一个简单的64位hash函数

如何处理hash冲突

查询的时候，必须得是如下

select id from pseudohash where url='www.baidu.com123217' and url_crc = CRC32('www.baidu.com123217')

仅仅是如下的查询

select id from pseudohash where  url_crc = CRC32('www.baidu.com123217')

当发生hash冲突的时候，是无法正确工作的

参考：

https://zhuanlan.zhihu.com/p/339957841