innodb_adaptive_hash_index 关掉后写入 P99 降了 40%：高频 UPDATE 下 AHI 的反噬

一、现象：写入 QPS 没变，P99 却从 6ms 飙到 11ms

去年 Q3 给一套支付清结算系统做性能治理。核心表 t_settle_record：

• 规模：单表 2.8 亿行、InnoDB 16KB 页、MySQL 8.0.32、innodb_buffer_pool_size=64GB
• 负载特征：峰值 12000 TPS 的 UPDATE（批量回写 settle_status、clear_time、fee_amount 三列），同一秒内热点集中在最近 2 小时内的约 40 万行
• 监控指标：innodb_buffer_pool_reads/s 稳定在 0（全命中 buffer pool），但 UPDATE P99 从 6.2ms 逐步爬到 10.8ms，持续了约 3 周

最反直觉的一点：buffer pool 命中率 100%、没有磁盘 IO、没有锁等待（performance_schema.events_waits_summary_global_by_event_name 里 wait/io/file/innodb/data_file 为 0），但写入就是变慢了。

二、根因：AHI 在高频更新下的三笔"隐藏税"

InnoDB 的 Adaptive Hash Index（AHI）机制：当 B+Tree 某条路径被反复访问时，AHI 在 B+Tree 之上额外维护一张哈希表，让"主键/唯一索引点查"从树形 O(log N) 降到 O(1)。

但 AHI 的设计初衷是加速读，它对写操作有三笔代价：

1. 维护税（maintenance cost）：每次 UPDATE/DELETE 命中 AHI 记录的 B+Tree 页，InnoDB 需要同步更新或删除 AHI 中对应的哈希桶。高并发下（12000 TPS 打到同一批 40 万行），AHI 桶的更新本身成为热点。
2. 锁竞争税（hash partition lock）：AHI 内部按桶分 8 个 mutex（hash_mutex），并发线程在同一个桶上排队。B+Tree 的 latch 粒度是页级（16KB），而 AHI 的 mutex 粒度是桶级——并发越集中，桶级锁的排队越长。
3. 失效重建税（invalidate & rebuild）：当 B+Tree 页发生分裂（page split）或合并时，AHI 中指向该页的所有哈希记录被批量失效。失效后 AHI 需要等后续访问重新学习——这中间有一段"冷窗口"，点查退回 B+Tree，延迟抖动。

我们抓到 SHOW ENGINE INNODB STATUS 里的 AHI 统计：

INDEX STATISTICS
------------
Hash table size: 32768
Used cells: 18412        ← 利用率 56%，说明 AHI 确实在"学习"
Node hash: 9203          ← B+Tree 节点级哈希
Record hash: 9209        ← 行级哈希

同时 SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%' 显示：

Innodb_adaptive_hash_queries          48231  ← 累计被 AHI 加速的查询数
Innodb_adaptive_hash_waits            12037  ← 等 AHI 锁的次数
Innodb_adaptive_hash_translations     48231  ← 哈希翻译次数

12037 次 AHI 锁等待 / 48231 次查询 = 25% 的查询在等 AHI 锁——这就是 P99 从 6ms 爬到 11ms 的那 4.6ms 增量。

三、具体做法：灰度关闭 AHI 的完整步骤

3.1 先确认 AHI 确实是瓶颈（别盲关）

-- 1) 看 AHI 命中率与锁等待比例
SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%';

-- 2) 看 performance_schema 里 AHI 相关的 wait event
SELECT event_name, count_star, avg_timer_wait/1e9 AS avg_us
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE '%adaptive_hash%'
ORDER BY count_star DESC;

-- 3) 对比：把 UPDATE 的 P99 按"是否命中 AHI 学习窗口"分桶
--    方法：开启 general_log 30 秒，抓 UPDATE 耗时，
--    同时 grep InnoDB status 里的 AHI 统计快照，对齐时间线

判据：如果 Innodb_adaptive_hash_waits / Innodb_adaptive_hash_queries > 15%，且你的负载以高频 UPDATE 同一批行为主（而非大量随机点查），AHI 大概率是负资产。

3.2 灰度关闭（不要直接 SET GLOBAL 全关）

-- Step 1: 先在从库/预发环境验证
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Step 2: 观察 10 分钟关键指标
--   a) UPDATE P99 是否下降
--   b) SELECT 点查 P99 是否上升（AHI 关闭后点查退回 B+Tree）
--   c) CPU 是否下降（少了 AHI 维护的开销）

-- Step 3: 如果 SELECT 点查 P99 上升 < 20% 且 UPDATE P99 下降 > 30%
--         → 主库也关
SET GLOBAL innodb_adaptive_hash_index = OFF;

-- Step 4: 固化到 my.cnf
-- [mysqld]
-- innodb_adaptive_hash_index = OFF

3.3 我们的实测数据（主库，关闭 AHI 后 24h）

| 指标 | AHI ON | AHI OFF | 变化 | |------|--------|---------|------| | UPDATE P50 | 3.8ms | 3.2ms | -16% | | UPDATE P99 | 10.8ms | 6.4ms | -41% | | SELECT by PK P99 | 0.8ms | 1.1ms | +38%（可接受） | | CPU（该实例） | 72% | 61% | -11pp | | buffer pool 命中率 | 100% | 100% | 不变 |

收益/代价比：写入 P99 降 41%，点查 P99 升 38%（从 0.8ms 到 1.1ms，绝对值仅 +0.3ms）。对清结算场景（写多读少、点查走应用层缓存），这笔账非常划算。

四、踩过的坑与反直觉之处

1. AHI 是"自适应"的，不是"自动调优"的。它只学习"被反复访问的路径"，不区分读多写多。如果你的 40 万热点行每秒被 UPDATE 20 次，AHI 会拼命学习这些路径——然后每次 UPDATE 都要拆掉它刚学的哈希桶。学习频率越高，维护成本越高，形成正反馈恶性循环。

2. innodb_adaptive_hash_index=OFF 不需要重启，是动态参数。但已经建立的 AHI 哈希表不会立即释放，需要等相关 B+Tree 页被 evict 或重启后彻底消失。我们观察到关闭后 5~8 分钟 AHI 内存才降到接近 0（SHOW ENGINE INNODB STATUS 里 Hash table size 从 32768 降到 0）。

3. 别和 innodb_change_buffering 搞混。Change Buffer 是合并后台对二级索引的写入，AHI 是加速前台对聚簇/唯一索引的点查。两者作用层面不同。我们关闭 AHI 后 Change Buffer 仍然开着（innodb_change_buffering=all），没有冲突。

4. MySQL 8.0.28+ 的 AHI 实现和 5.7 不同。8.0 把 AHI 锁从全局一把 mutex 改成了 8 个 partition mutex（hash_mutex 数组），并发比 5.7 好了很多，但在超高频更新同一批行的场景下，8 个桶仍然不够用。如果你的系统 QPS < 2000，大概率 AHI 是正收益，别关。

五、结论与可复用清单

核心判断：AHI 是"读多写少 + 随机点查"场景的加速器，是"写多读少 + 热点集中"场景的减速器。你的负载画像决定它是朋友还是敌人。

可复用检查清单（按顺序执行）：

1. SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%' → 算 waits/queries 比例，>15% 进下一步
2. 确认负载画像：performance_schema 里 UPDATE/DELETE QPS 是否 > SELECT by PK QPS？热点行是否集中在 < 100 万行？
3. 是 → 从库/预发 SET GLOBAL innodb_adaptive_hash_index = OFF，压测 30 分钟
4. 对比 UPDATE P99 与 SELECT by PK P99 的绝对增量（不是百分比），业务能否接受
5. 能接受 → 主库关闭 + my.cnf 固化 + 监控 AHI 相关 wait event 归零
6. 不能接受 → 保持 ON，但检查 innodb_page_size 是否合理（16KB 页在 2.8 亿行 B+Tree 上树高 4 层，如果降到 8KB 页树高 5 层，AHI 的收益更明显——但改 page_size 需要重建表，成本极高，慎动）

一句话：AHI 不是"开了就有用"的免费午餐，它是一笔"用写入延迟换读取延迟"的交易。先算你的账，再决定签不签。