接着上篇文章Mysql InnoDB&MyISAM 反对Hash么,咱们晓得InnoDB是不反对HASH的,然而有个Adaptive Hash的概念让InnoDB在

原文如下adaptive hash index:

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 B-tree index on the table. 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 15.20, “InnoDB memcached Plugin” for details.

See Also B-tree, buffer pool, hash index, memcached, page, secondary index.

翻译:

自适应HASH,是一种通过在内存中构建HASH索引,实现对应用InnoDB引擎的表进行=和IN查问操作减速的优化. MySQL监控InnoDB表的数据搜寻操作,当发现如果应用HASH索引查问某条数据能产生性能效益时,那么MySQL会主动的对这个频繁查问的数据在index page上建设一个索引. 从某种意义上说,自适应哈希索引是一种利用短缺的内存资源在运行期产生的非凡的应用了HASH构造的减速索引,它的将InnoDB频繁拜访的数据缓存在主内存中,让数据更加凑近IO性能好的内存并远离IO性能差的磁盘,这个个性由innodb_adaptive_hash_index配置选项管制。因为该个性只对某些工作负载(=,IN)有益处，而对其余工作负载没有益处，而且用于散列索引的内存保留在缓冲池中,无奈体现MySQL随机查问的理论性能,因而通常您应该启用和禁用该个性进行基准测试.

哈希索引总是基于InnoDB表中现有的B-tree索引构建的,MySQL能够在为B-tree定义的键的任意长度的前缀上构建散列索引，这取决于针对索引的搜寻模式.哈希索引能够是局部索引;整个B-tree索引不须要缓存在缓冲池中。

在MySQL 5.6及更高版本中，利用InnoDB表疾速单值查找的另一种形式是应用InnoDB memcached插件.

集体解读:

  • Adaptive Hash实际上是在在运行期根据理论的数据查问状况动静产生的,它产生后寄存在内存中,以便充分利用内存的随机IO劣势
  • Adaptive Hash只保留这张表的热点查问数据,不是对整个BTREE索引或者数据进行全副缓存,这样太费内存而且不事实
  • Adaptive Hash的保留在内存中的设计决定的它缓存的数据总量无限,如果你的机器内存足够那么应用innodb_adaptive_hash_index关上Adaptive Hash并分配内存到适合的大小会有性能收益.
  • 如果MySQL服务重启或者解体,那么Adaptive Hash在MySQL服务从新失常工作后须要从新动静的创立,以前的热点数据会隐没.
  • Adaptive Hash实际上是对BTREE索引做缓存优化,减速单值等值查问.为了节约内存,能够对BTREE索引的局部索引内容做HASH而不是非得要做全部内容的HASH.

上面是查问MySQL无关Adaptive Hash的属性配置的演示

mysql> show variables like "%innodb_adaptive%";

本文原创链接:

  • MySQL InnoDB中的Adaptive Hash