mysql InnoDB引擎支持hash索引吗

mysql InnoDB引擎支持hash索引吗

    查一下mysql官方文档:https://dev.mysql.com/doc/refman/5.7/en/create-index.html




从上面的图中可以得知,mysql 是支持hash索引的,但支持和不支持又和具体的存储引擎有关系。从图中,
看到InnoDB是支持Btree索引,这是我们众所周知的。
我们具体查一下InnoDB文档那一部分:https://dev.mysql.com/doc/refman/5.7/en/innodb-introduction.html







表中表明,InnoDB不支持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存储引擎.姜承尧.扫描版】也提到了这点,【对不起作者,一向喜欢看电子书,实体书实在买不起,因为电子书携带也比较方便,搬家也不用乱丢书了。】



      

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


©️2020 CSDN 皮肤主题: 编程工作室 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值