社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
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 InnoDB secondary
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.
In MySQL 5.6 and higher, another way to take advantage of fast single-value lookups with InnoDB tables is to use the InnoDB memcached plugin.
See Section 14.20,
“InnoDB memcached Plugin” for details.
See Also B-tree, buffer pool, hash index, memcached, page, secondary index.
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
