文章目录
- 1 问题背景
- 2 回顾
- 3 自适应Hash索引
1 问题背景
前面研究了InnoDB内存架构之更改缓冲区，今天了解下InnoDB内存架构之自适应Hash索引。
参考自：MySQL官方文档之InnoDB的自适应Hash索引
2 回顾
InnoDB的架构如下图所示：
3 自适应Hash索引
原文
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 enabled by the innodb_adaptive_hash_index variable, or turned off at server startup by --skip-innodb-adaptive-hash-index.
自适应hash索引使InnoDB能够像一个在系统上的内存数据库一样，具有适当的工作负载组合以及足够的内存给缓冲池，而不会牺牲事务性的特点以及可靠性。自适应hash索引由innodb_adaptive_hash_index变量来启用，或者在启动机器时以--skip-innodb-adaptive-hash-index方式来关闭。
原文
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.
根据观察到的搜索模式，一个hash索引是由索引的key的一些前缀构建成的。前缀可以是任意长度，并且它可以仅仅是出现在索引上的B-树的某些值。hash索引是针对经常访问的索引页面区按需构建的。
原文
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.
如果一个表几乎完全装进主内存，hash索引通过启用任何元素的直接查找来加速查询，将索引值转换为某种指针。InnoDB有一个机制会监控索引搜索。如果InnoDB发现查询可以从构建hash索引中受益，它会自动这样做。
原文
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.
对于某些工作负载，hash索引查找的加速远远超过监控索引查找和维持hash索引结构的额外工作。在高度工作负载的情况下，访问自适应hash索引有时候会成为一个争议的源头，比如多个并发连接。LIKE的查询操作或%通配符也不会受益。对于无法从hash自适应索引有益的工作负载，关闭自适应索引以减少不必要的性能开销。 因为很难提前预测自适应索引是否适合一个特定的系统和工作负载，所以要考虑启用和禁用它的情况下运行基准测试。
原文
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.
自适应hash索引的特性是分区的。每一个索引与一个特定的区域绑定在一起，每一个区域被一个独立的门闩保护。分区由innodb_adaptive_hash_index_parts变量控制，默认值是8，最大设置值是512。
原文
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.
您可以在输出SEMAPHORES部分 监控自适应哈希索引的使用和争用 SHOW ENGINE INNODB STATUS。如果有许多线程在等待 rw-latch 中创建的btr0sea.c，请考虑增加自适应哈希索引分区的数量或禁用自适应哈希索引。
