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.
has a mechanism that monitors index searches. If
InnoDB notices that queries could benefit from
building a hash index, it does so automatically.
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.
In MySQL 5.7, 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. In earlier releases, 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 8.3.8, “Comparison of B-Tree and Hash Indexes”.