MySQL自适应哈希索引

一、什么是自适应哈希索引?自适应哈希索引 是 InnoDB 在内存中基于 B+Tree 索引的常用搜索模式，自动创建并维护的一个哈希索引，可以理解为索引的索引。它使用哈希表数据结构。对于等值查询，哈希表的查询时间复杂度是 O(1)，而 B+Tree 是 O(log n)。因此正常情况下，它的速度会更优，并且仅支持等值查询，范围、模糊、排序场景仍走原 B+ 树 。如下是自适应哈希索引的核心知识点：自适应： 哈希索引不是由 DBA 或开发者手动创建的，而是由 InnoDB 存储引擎自己决定是否创建、为哪些索引页创建。只缓存热数据：哈希表本身占用的是 Buffer Pool 空间，只为那些热点查询的索引页自动创建hash索引;创建哈希索引规则：当某页被命中次数达到内部阈值，只要同一个索引页被等值查询命中 ≥15次，且页里记录数≥4条，InnoDB 就会立即给这一页建立自适应哈希条目;哈希索引内容：立即在 Buffer Pool 的“哈希索引区”插入一条记录：key → 索引键值，value → 叶子节点页地址(它存的是内存中的地址，用于直接跳到 Buffer Pool 里的那一页)。基于 B+Tree： AHI 并不是替代 B+Tree 的，而是作为 B+Tree 的一个补充缓存。数据最终仍然存储在 B+Tree 中。内存中：哈希索引是在内存中为热点等值查询自动创建的“加速哈希表”，如果某个b+tree的叶子节点没有在内存中，那么就不会为其创建hash索引;哈希索引数据淘汰规则：页不再热点、对应记录被修改/删除或内存紧张时，哈希条目被自动回收，全程无 DBA 干预AHI的优势：主要减少的是 CPU开销和锁竞争，以及内存中的B+TREE的多层遍历，而不是随机I/O。 二、什么场景需要 AHI?1、背景：1)数据必须在内存中才能创建AHI，AHI 只能缓解内存中的 B+Tree 遍历，哈希表的查询时间复杂度是 O(1)，而 B+Tree 是 O(log n);2)数据检索的过程：InnoDB 的主键索引(聚簇索引)和数据是存储在一起的。当根据主键进行查询时，只需要遍历主键 B+Tree 即可在叶子节点找到数据。但是，对于二级索引，情况就复杂了：在二级索引的 B+Tree 的叶子节点中查找到目标记录的主键。再用这个主键去主键索引的 B+Tree 中查找，最终拿到完整的数据行。这个过程被称为回表。2、AHI的适合场景：1)频繁执行的等值查询，同时修改的不频繁，否则频繁删除和重建AHI条目会代理额外的CPU负担;2)频繁的回表等值查询场景，同时修改的不频繁，AHI的性能收益会更优;3)大量并发等值查询场景，内存页热点争用严重，同时需要回表查询，但是修改不频繁的，AHI的性能收益会最优 三、怎么开启AHIinnodb_adaptive_hash_index： 全局开关，默认是 ON。你可以通过 SET GLOBAL innodb_adaptive_hash_index = OFF; 来禁用它。innodb_adaptive_hash_index_parts： 将 AHI 分区数，默认为 8。增加分区数可以将 AHI 的锁争用分散到多个分区中，在高并发场景下有助于提升性能。最大可设置为 512 四、AHI使用情况监控：方式1：使用 Performance Schema 监控：-- 启用AHI监控UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE '%adaptive_hash%';-- 查询AHI统计信息SELECT * FROM performance_schema.events_waits_summary_global_by_event_name WHERE EVENT_NAME LIKE '%adaptive_hash%';方式2：你可以通过 SHOW ENGINE INNODB STATUS\G 命令在 SEMAPHORES 部分查看 AHI 的使用和争用情况：SHOW ENGINE INNODB STATUS查看 “Hash table size XXXX, hash searches/s YYYY” 评估命中率。备注：hash_table_size = 276671 # 哈希表的大小(槽位数)约 27 万槽，对应占用内存 ≈ 276 671 × 8 B ≈ 2.1 MB(在 buffer pool 内)hash_searches/s = 0.00 # 每秒通过哈希索引查找的次数 ，通过哈希索引查找的次数non_hash_searches/s = 0.00 # 每秒走传统 B+ 树搜索的次数node_heap_buffers = [10, 13, 5, 12] # 各分区的节点堆缓冲区数量，为哈希节点额外向 buffer pool 申请的 16 KB 页数量 | 10 / 13 / 5 / 12 | 四段合计 ~40×16 KB ≈ 640 KB 真正存了哈希记录 五、何时考虑关闭 AHI?1、业务主要是范围查询或排序，AHI 几乎无用。2、系统有大量高并发的写入，维护 AHI 的成本超过了其带来的读取收益。3、AHI 的锁争用(在 SHOW ENGINE INNODB STATUS 中看到大量 btr0sea.c 的等待)非常严重，成为了系统瓶颈。4、内存非常紧张;5、SHOW ENGINE INNODB STATUS查看 “Hash table size XXXX, hash searches/s YYYY” AHI查询命中率低。