InnoDB utilizes hash indexes internally for its Adaptive Hash Index
Performance benefits are not limited to giant tables with long-running queries. When the same rows are accessed over and over from a table, a feature called the Adaptive
Hash Index takes over to make these lookups even faster, as if they came out of a hash table.
adaptive hash index
An optimization for InnoDB tables
that can speed up lookups using = and IN operators,
by constructing a hash index in
memory. MySQL monitors index searches for InnoDB tables, and if queries could benefit from a hash index, it builds one automatically for index pages that
are frequently accessed. In a sense, the adaptive hash index configures MySQL at runtime to take advantage of ample main memory, coming closer to the architecture of main-memory databases. This feature is controlled by the innodb_adaptive_hash_index configuration
option. Because this feature benefits some workloads and not others, and the memory used for the hash index is reserved in the buffer
pool, typically you should benchmark with this feature both enabled and disabled.
The hash index is always built based on an existing InnoDBsecondary
index, which is organized as a B-tree structure.
MySQL 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 against the index. A hash index can be partial; the whole B-tree index does not need to be cached in the buffer pool.