首页 >数据库 >mysql教程 >Innodb三大特性之adaptive hash index_MySQL

Innodb三大特性之adaptive hash index_MySQL

WBOY
WBOY原创
2016-05-30 17:11:011196浏览

1、Adaptive Hash Indexes 定义

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit from building a hash index, it does so automatically.

 

The hash index is always built based on an existing B-tree index on the table. InnoDB 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 that InnoDB observes for the B-tree index. A hash index can be partial: It is not required that the whole B-tree index is cached in the buffer pool. InnoDB builds hash indexes on demand for those pages of the index that are often accessed.

 

In a sense, InnoDB tailors itself through the adaptive hash index mechanism to ample main memory, coming closer to the architecture of main-memory databases.

 

The configuration parameter innodb_adaptive_hash_index can be set to disable or enable the adaptive hash index. See Section 8.3.4, “Dynamically Changing innodb_adaptive_hash_index” for details.

 

2、hash index

 

哈希(hash)是一种非常快的查找方法,一般情况下查找的时间复杂度为O(1),常用于连接(join)操作,如SQL Server和Oracle中的哈希连接(hash join)。但是SQL Server和Oracle等常见的数据库并不支持哈希索引(hash index)。MySQL的Heap存储引擎默认的索引类型为哈希,而InnoDB存储引擎提出了另一种实现方法,自适应哈希索引(adaptive hash index)

 

3、自适应哈希

 

InnoDB存储引擎会监控对表上索引的查找,如果观察到建立哈希索引可以带来速度的提升,则建立哈希索引,所以称之为自适应(adaptive) 的。自适应哈希索引通过缓冲池的B+树构造而来,因此建立的速度很快。而且不需要将整个表都建哈希索引,InnoDB存储引擎会自动根据访问的频率和模式 来为某些页建立哈希索引。

 

根据InnoDB的官方文档显示,启用自适应哈希索引后,读取和写入速度可以提高2倍;对于辅助索引的连接操作,性能可以提高5倍。在我看来,自适应哈希索引是非常好的优化模式,其设计思想是数据库自优化(self-tuning),即无需DBA对数据库进行调整。

 

Adaptive Hash Index是针对B+树Search Path的优化,因此所有会涉及到Search Path的操作,均可使用此Hash索引进行优化,这些可优化的操作包括:Unique Scan/Range Scan(Locate First Key Page)/Insert/Delete/Purge等等,几乎涵盖InnoDB所有的操作类型

 

Adaptive,意味着不是所有的叶页面都会以Hash索引维护,叶页面进入Hash 索引的条件是:同种类型的操作(Scan/Insert…),命中同一叶页面的次数,超过此页面记录数量的1/16,则可将当前叶页面加入Hash索引, 用以优化后续可能的相同Search Path。

mysql> show engine innodb status \G
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 553229, node heap has 17 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s


mysql> show variables like '%adaptive_hash%';  
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

不过我们可以通过参数innodb_adaptive_hash_index来禁用或启动此特性,默认为开启


声明:
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn