The adaptive hash
index (AHI) lets
InnoDB perform more
like an in-memory database on systems with appropriate
combinations of workload and ample memory for the
buffer pool, without
sacrificing any transactional features or reliability. This
feature is enabled by the
option, or turned off by
--skip-innodb_adaptive_hash_index at server
Based on the observed pattern of searches, MySQL builds a hash index using a prefix of the index key. The prefix of the key can be any length, and it may be that only some of the values in the B-tree appear in the hash index. Hash indexes are built on demand for those pages of the index that are often accessed.
If a table fits almost entirely in main memory, a hash index can
speed 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
InnoDB notices that queries
could benefit from building a hash index, it does so
With some workloads, the
speedup from hash index lookups greatly outweighs the extra work
to monitor index lookups and maintain the hash index structure.
Sometimes, the read/write lock that guards access to the
adaptive hash index can become a source of contention under
heavy workloads, such as multiple concurrent joins. Queries with
LIKE operators and
wildcards also tend not to benefit from the AHI. For workloads
where the adaptive hash index is not needed, turning it off
reduces unnecessary performance overhead. Because it is
difficult to predict in advance whether this feature is
appropriate for a particular system, consider running benchmarks
with it both enabled and disabled, using a realistic workload.
The architectural changes in MySQL 5.6 and higher make more
workloads suitable for disabling the adaptive hash index than in
earlier releases, although it is still enabled by default.
As of MySQL 5.7.8, the adaptive hash index search system is
partitioned. Each index is bound to a specific partition, and
each partition is protected by a separate latch. Partitioning is
controlled by the
configuration option. Prior to MySQL 5.7.8, the adaptive hash
index search system was protected by a single latch which could
become a point of contention under heavy workloads. The
option is set to 8 by default. The maximum setting is 512.
The hash index is always built based on an existing
B-tree index on the table.
InnoDB can build a hash index on a prefix of
any length of the key defined for the B-tree, depending on the
pattern of searches that
InnoDB observes for
the B-tree index. A hash index can be partial, covering only
those pages of the index that are often accessed.
You can monitor the use of the adaptive hash index and the
contention for its use in the
section of the output of the
SHOW ENGINE INNODB
STATUS command. If you see many threads waiting on an
RW-latch created in
btr0sea.c, then it
might be useful to disable adaptive hash indexing.
For more information about the performance characteristics of hash indexes, see Section 9.3.8, “Comparison of B-Tree and Hash Indexes”.