自适应哈希索引使 InnoDB 在具有适当工作负载组合和足够缓冲池内存的系统上表现得更像内存数据库，同时不牺牲事务功能或可靠性。自适应哈希索引可以通过 innodb_adaptive_hash_index 变量禁用，或者在服务器启动时通过 --innodb-adaptive-hash-index 启用。The adaptive hash index enables InnoDB to perform more like an in-memory database on systems with appropriate combinations of workload and sufficient memory for the buffer pool without sacrificing transactional features or reliability. The adaptive hash index is disabled by the innodb_adaptive_hash_index variable, or turned on at server startup by --innodb-adaptive-hash-index.

根据观察到的搜索模式，使用索引键的前缀构建哈希索引。前缀可以是任意长度，并且可能只有 B 树中的部分值出现在哈希索引中。哈希索引是针对经常访问的索引页按需构建的。Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often.

如果一个表几乎完全适合放在主内存中，哈希索引通过允许直接查找任何元素来加快查询速度，将索引值变成一种指针。InnoDB有一种机制来监控索引搜索。如果InnoDB发现查询可以通过构建哈希索引受益，它会自动执行此操作。If a table fits almost entirely in main memory, a hash index speeds up queries by enabling direct lookup of any element, turning the index value into a sort of pointer. InnoDB has a mechanism that monitors index searches. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.

对于某些工作负载来说，哈希索引查找带来的加速效果远远超过了监控索引查找和维护哈希索引结构的额外工作。在高负载情况下，例如多个并发连接，访问自适应哈希索引有时可能成为争用的来源。带有LIKE操作符和%通配符的查询通常也不会受益。对于不受自适应哈希索引影响的工作负载，关闭它可以减少不必要的性能开销。由于难以预先预测自适应哈希索引是否适合特定系统和工作负载，因此建议考虑在启用和禁用它的情况下运行基准测试。With some workloads, the speedup from hash index lookups greatly outweighs the extra work to monitor index lookups and maintain the hash index structure. Access to the adaptive hash index can sometimes become a source of contention under heavy workloads, such as multiple concurrent joins. Queries with LIKE operators and % wildcards also tend not to benefit. For workloads that do not benefit from the adaptive hash index, turning it off reduces unnecessary performance overhead. Because it is difficult to predict in advance whether the adaptive hash index is appropriate for a particular system and workload, consider running benchmarks with it enabled and disabled.

自适应哈希索引功能是分区的。每个索引绑定到特定分区，每个分区由单独的闩锁保护。分区由 innodb_adaptive_hash_index_parts 变量控制。innodb_adaptive_hash_index_parts 变量的默认值为 8。最大设置值为 512。The adaptive hash index feature is partitioned. Each index is bound to a specific partition, and each partition is protected by a separate latch. Partitioning is controlled by the innodb_adaptive_hash_index_parts variable. The innodb_adaptive_hash_index_parts variable is set to 8 by default. The maximum setting is 512.

您可以在 SEMAPHORES 输出的 SHOW ENGINE INNODB STATUS 部分监控自适应哈希索引的使用情况和竞争情况。如果有大量线程在 btr0sea.c 中创建的读写锁等待，考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引。You can monitor adaptive hash index use and contention in the SEMAPHORES section of SHOW ENGINE INNODB STATUS output. If there are numerous threads waiting on rw-latches created in btr0sea.c, consider increasing the number of adaptive hash index partitions or disabling the adaptive hash index.

有关哈希索引性能特性的详细信息，请参见 第 10.3.9 节，“B-Tree 与哈希索引的比较”。For information about the performance characteristics of hash indexes, see Section 10.3.9, “Comparison of B-Tree and Hash Indexes”.