mysql自适应哈希索引（AHI）,40%性能提升，为何有人却选择关闭？

自适应哈希索引（Adaptive Hash Index, AHI）是InnoDB存储引擎的核心优化机制，​自动为高频访问的索引页构建内存级哈希表。其核心是绕过B+树遍历​，将多层索引搜索优化为哈希直接定位。

一、最佳实践：什么场景下建议开启？

• ​读密集型业务​：电商商品查询、用户信息检索等高频点查；
• ​深层次B+树​：索引高度≥4时；
• ​MySQL ≥8.0.23环境​：官方修复了关键缺陷；
• ​内存充足时​：确保Buffer Pool富余（建议>总数据量的30%）；

典型场景​
主键点查（WHERE id=‘n’）、二级索引回表（WHERE name=‘xxx’）、IN列表查询
简言之，如果数据库存在高频大表join，且被连接表索引效率较低时，AHI性能提升非常明显。此时建议评估cpu、内存资源，开启AHI。

分享一个案例
执行计划如下，sql略。

+----+--------------------+-------+------------+--------+--------------------------------------------------------------------------------+-------------------------+---------+--------------------------------+--------+----------+-------------------------------------------+
| id | select_type        | table | partitions | type   | possible_keys                                                                  | key                     | key_len | ref                            | rows   | filtered | Extra                                     |
+----+--------------------+-------+------------+--------+--------------------------------------------------------------------------------+-------------------------+---------+--------------------------------+--------+----------+-------------------------------------------+
|  1 | PRIMARY            | ci    | NULL       | ref    | PRIMARY,interview_process_id,resume_screen_init_search,apply_count_union_index | apply_count_union_index | 82      | const                          | 351201 |     0.91 | Using where                               |
|  1 | PRIMARY            | rcl   | NULL       | eq_ref | PRIMARY,idx_code_flag                                                          | PRIMARY                 | 82      | rm_prd.ci.interview_process_id |      1 |    55.14 | Using where                               |
|  1 | PRIMARY            | rrda  | NULL       | ref    | PRIMARY                                                                        | PRIMARY                 | 82      | rm_prd.ci.id                   |      1 |    10.00 | Using where; FirstMatch(rcl)              |
|  4 | DEPENDENT SUBQUERY | rpm   | NULL       | ref    | PRIMARY,manage_index,project_id_index                                          | project_id_index        | 8       | func                           |      3 |    12.70 | Using where                               |
|  4 | DEPENDENT SUBQUERY | rpml  | NULL       | ref    | link                                                                           | link                    | 131     | const,func,rm_prd.rpm.id       |      1 |   100.00 | Using where; Using index; FirstMatch(rpm) |
|  3 | DEPENDENT SUBQUERY | t     | NULL       | ref    | PRIMARY,data_role_find_index,idx_level_link                                    | PRIMARY                 | 50      | rm_prd.ci.dept_id,const        |      1 |     5.00 | Using where                               |
+----+--------------------+-------+------------+--------+--------------------------------------------------------------------------------+-------------------------+---------+--------------------------------+--------+----------+-------------------------------------------+
6 rows in set, 16 warnings (0.01 sec)

开启AHI后测试

root@localhost: 09:58:  [rm_prd]> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
1 row in set (0.00 sec)
# 关闭输出
root@localhost: 10:01:  [rm_prd]> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'

执行sql耗时：6.38

关闭AHI后测试

root@localhost: 10:07:  [rm_prd]> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF   |
+----------------------------+-------+
1 row in set (0.00 sec)
# 关闭输出
root@localhost: 10:09:  [rm_prd]> pager cat >>/dev/null
PAGER set to 'cat >>/dev/null'

执行sql耗时：12.29

开启与关闭AHI时，explain输出的执行计划完全一致，但sql耗时差了2倍。

二、副作用

<1> DDL成本升高
删除超过32GB缓冲池的大表时，AHI清理需扫描BP的flush_list链表，导致实例Hang住。

规避方法：

• MySQL ≥8.0.23版本已修复此问题
• 5.7版本需手动在删表前关闭AHI

SET GLOBAL innodb_adaptive_hash_index=OFF;  
-- 执行DROP TABLE操作  
SET GLOBAL innodb_adaptive_hash_index=ON;  

<2> 高并发下的锁竞争
旧版MySQL（<5.7）使用全局锁btr_search_latch，高并发时CPU争用飙升。

规避方法：
升级到≥5.7版本，启用分区锁（innodb_adaptive_hash_index_parts，默认8分区）。另外监控信号量，若SHOW ENGINE INNODB STATUS出现大量btr0sea.c的RW-latch等待，需调高分区数或关闭AHI。

诊断innodb状态：

show engine innodb status\G;

调大分区数：

# 经实测，调大分区后，cpu使用率下降明显。
SET GLOBAL innodb_adaptive_hash_index_parts = 32;  -- 如将分区数从8提升至32

<3> AHI收益小于开销

• 当数据库低频查询较多，热点数据较少时，AHI的构建成本高于受益；
• 索引页频繁变动时，数据修改触发AHI维护开销（如页分裂时重构哈希）。

三、结论：开还是不开？​​

MySQL 在 ​8.4 LTS 版本中已将自适应哈希索引（AHI）的默认状态从 ON 改为 OFF。可见官方态度更倾向提供可预测的性能基线，而非依赖动态优化带来的不确定性。

由于在写操作频繁或高负载环境下可能出现的性能瓶颈，使得AHI功过参半，所以开与不开需要DBA根据实际环境做出决策。

• ​推荐开启的场景​：MySQL≥8.0.23、读多写少、内存充足、B+树层次深
• ​谨慎使用的场景​：5.7版本且需频繁删大表、超高频写操作、内存紧张