mysql InnoDB引擎支持hash索引吗
-
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 indexAn optimization for
InnoDBtables that can speed up lookups using=andINoperators, by constructing a hash index in memory. MySQL monitors index searches forInnoDBtables, 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 theinnodb_adaptive_hash_indexconfiguration 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.In MySQL 5.6 and higher, another way to take advantage of fast single-value lookups with
InnoDBtables is to use theInnoDBmemcached plugin. See Section 14.20, “InnoDB memcached Plugin” for details.See Also B-tree, buffer pool, hash index, memcached, page, secondary index.
