MySQL各种“Buffer”之Adaptive Hash Index

如图所示，在InnoDB体系架构图的内存结构中，还有一块区域（已经用红框标出）名为：Adaptive Hash Index，翻译成中文：自适应哈希索引，缩写：AHI。它是一个纯内存结构，今天就来盘它。

Adaptive Hash Index是什么

• 说InnoDB不支持Hash Index一派的说辞：

CREATE TABLE `student` (  `student_id` int NOT NULL AUTO_INCREMENT COMMENT '学生编号',  `student_name` varchar(20) NOT NULL COMMENT '学生姓名',  `address` varchar(100) DEFAULT '北京市' COMMENT '家庭住址',  `extra` varchar(50) DEFAULT NULL COMMENT '额外信息',  `remark` tinytext COMMENT '备注',  PRIMARY KEY (`student_id`),  UNIQUE KEY `uniq_extra` (`extra`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

然后，我们为address字段添加一个Hash Index，再顺便看一下创建完哈希索引后的表结构：

/* 给address字段创建Hash Index */ALTER TABLE `student` ADD INDEX `idx_address`(`address`) USING HASH;/* 查看表结构 */SHOW CREATE TABLE `student`;

SHOW INDEXES FROM `student`;

• 说InnoDB支持Hash Index一派的说辞：

在MySQL运行的过程中，如果InnoDB发现，有很多寻路很长（比如B+树层数太多、回表次数多等情况）的SQL，并且有很多SQL会命中相同的页面（Page）的话，InnoDB会在自己的内存缓冲区（Buffer Pool）里，开辟一块区域，建立自适应哈希索引（Adaptive Hash Index，AHI），以加速查询。

官档地址：https://dev.mysql.com/doc/refman/5.7/en/innodb-adaptive-hash.html

SHOW VARIABLES LIKE '%adaptive%';

• 参数：innodb_adaptive_hash_index

介绍：是否启用或禁用InnoDB 自适应哈希索引。默认情况下启用此变量。可以在线生效，无需重新启动服务器。禁用自适应哈希索引会立即清空哈希表。当哈希表被清空时，正常操作可以继续，并且执行使用哈希表的查询直接访问索引 B 树。当重新启用自适应散列索引时，在正常操作期间会再次填充散列表。

• 参数：innodb_adaptive_flushing

介绍：指定是否动态调整冲洗速度 脏页在 InnoDB 缓冲池中，根据工作负载。动态调整刷新率旨在避免I/O活动的爆发。默认情况下启用此设置。

• 参数：innodb_adaptive_flushing_lwm

• 参数：innodb_adaptive_hash_index_parts

• 参数：innodb_adaptive_max_sleep_delay

Adaptive Hash Index工作原理

ALTER TABLE `student` DROP INDEX `uniq_extra`,DROP INDEX `idx_address`,ADD INDEX `idx_student_name` (`student_name`);

我们删除了之前创建的唯一索引uniq_extra，删除了并没有实际用处的“Hash”索引idx_address，为student_name学生姓名字段添加了一个普通二级索引。

ALTER TABLE `student` DROP COLUMN extra,DROP COLUMN remark;

同时把extra额外信息字段、remark备注字段DROP掉。

• 最后，我们看一下表结构和表中的数据：

/* 查看student表结构 */SHOW CREATE TABLE `student`;/* 查看student表数据 */SELECT * FROM student;

student学生表中的数据我们可以简易画一个B+树的结构图（真实的B+树结构要复杂的多，后期会开辟单独的文章详细讲解），如下所示：

InnoDB会在主键student_id上建立聚集索引（Clustered Index），Leaf叶子节点存储记录本身，在student_name上会建立普通索引（Secondary Index），叶子节点存储主键值（聚集索引就是数据的完整记录，普通索引也是单独的物理结构，两者均存放在.ibd文件中）。发起主键student_id查询时，能够通过聚集索引，直接定位到行记录。

SELECT * FROM `student` WHERE student_id = 6;

此时的过程如下图所示：

SELECT * FROM `student` WHERE student_name = '褚壬';

通过普通索引查询记录时，和通过聚簇索引查询记录有所不同，分为两步：

步骤1：查询会先访问普通索引，定位到主键值9；

步骤2：再通过步骤1得到的主键值，回表到聚集索引上经过二次遍历定位到具体的完整记录。

从上面的流程图可以看出，不管是聚集索引还是普通索引，记录定位的寻路路径（Search Path）都很长。回到Adaptive Hash Index的概念上：在MySQL运行的过程中，如果InnoDB发现：

• 有很多寻路很长（比如B+树层数太多、回表次数多等情况）的SQL；

• 有很多SQL会命中相同的页面（Page）。

InnoDB会在自己的内存缓冲区（Buffer Pool）里，开辟一块区域，建立自适应哈希索引（Adaptive Hash Index，AHI），以加速查询。

通过上面的流程图，我们可以得出以下结论：

• MySQL InnoDB的Adaptive Hash Index，更像“索引的索引”，以此来缩短寻路路径（Search Path）。

• 我们都知道，Hash数据结构都是包含键（Key）、值（Value）的，在Adaptive Hash Index，Key就是经常访问到的索引键值，Value就是该索引键值匹配的完整记录所在页面（Page）的位置。

• 因为是MySQL InnoDB自己维护创建的，所以称之为“自适应”哈希索引，但系统也有误判的时候，也不能起到加速查询的效果。

知识补充

SHOW ENGINE INNODB STATUS\G

• 很多单行记录查询，比如用户登录系统时密码的校验。

• 索引范围查询，此时AHI可以快速定位首行记录。

• 所有记录内存能放得下，这时AHI往往是有效的。

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

• 设置innodb_adaptive_hash_index_parts使用AHI分区/分片降低竞争提高并发，个别场景下，开了AHI后，可能导致spin_wait lock比较大，此时也可以关闭AHI功能。

1、AHI目的：缓存索引中的热点数据，提高检索效率，时间复杂度O(1) VS O(N)的差异；

2、基于主键的搜索，几乎都是hash searches；

3、基于普通索引的搜索，大部分是non-hash searches，小部分是hash searches；

4、无序，没有树高，对热点Buffer Pool建立AHI，非持久化；

5、初始化为innodb_buffer_pool_size的1/64，会随着InnoDB Buffer Pool动态调整；

6、只支持等值查询（基于主键的等值查询AHI效果更好）：

e.g.：

• idx_a_b(a,b)

• WHERE a=?

• WHERE a=? and b=?

• WHERE a IN (?)

• WHERE a!=?

7、AHI很可能是部分长度索引，并非所有的查询都能有效果。

1、只能用于等值比较，例如=、<=>、IN、AND等

2、无法用于排序

3、有冲突可能

4、MySQL自动（“自适应”）管理，人为无法干预。

小结

今天理论的知识不是很多，下面简单做一下总结：

4、Adaptive Hash Index只能用于等值比较，例如=、<=>、IN、AND等，无法用于排序；

5、Adaptive Hash Index是MySQL InnoDB自己维护创建的，人为无法干预。初始化为innodb_buffer_pool_size的1/64，会随着InnoDB Buffer Pool动态调整。

今天主要讲解了MySQL InnoDB Adaptive Hash Index的工作原理，通过一些例子来说明整个过程，偏理论的知识，内容比较少也很好理解，大家理解记忆即可，面试中有被问到的可能。

end