14.2.15.6 Adaptive Hash Indexes

The feature known as 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
innodb_adaptive_hash_index
option, or turned off by the
--skip-innodb_adaptive_hash_index at server
startup.

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
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.

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 SEMAPHORES
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.