mysql InnoDB引擎支持hash索引吗

mysql InnoDB引擎支持hash索引吗


从上面的图中可以得知,mysql 是支持hash索引的,但支持和不支持又和具体的存储引擎有关系。从图中,


InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature。
  • 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-treebuffer poolhash indexmemcachedpagesecondary index.

看到了吗。mysqlInnoDB存储引擎 是支持hash索引的,不过,我们必须启用,hash索引的创建由InnoDB存储引擎引擎自动优化创建,我们干预不了。



所以,当有人问你mysql InnoDB存储引擎是否支持hash索引的时候,你知道怎么回答了吧。

©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
钱包余额 0