Innodb三大特性之a(chǎn)daptivehashindex_MySQL

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）是一種非常快的查找方法，一般情況下查找的時(shí)間復(fù)雜度為O（1），常用于連接（join）操作，如SQL Server和Oracle中的哈希連接（hash join）。但是SQL Server和Oracle等常見的數(shù)據(jù)庫(kù)并不支持哈希索引（hash index）。MySQL的Heap存儲(chǔ)引擎默認(rèn)的索引類型為哈希，而InnoDB存儲(chǔ)引擎提出了另一種實(shí)現(xiàn)方法，自適應(yīng)哈希索引（adaptive hash index）

3、自適應(yīng)哈希

InnoDB存儲(chǔ)引擎會(huì)監(jiān)控對(duì)表上索引的查找，如果觀察到建立哈希索引可以帶來速度的提升，則建立哈希索引，所以稱之為自適應(yīng)（adaptive） 的。自適應(yīng)哈希索引通過緩沖池的B+樹構(gòu)造而來，因此建立的速度很快。而且不需要將整個(gè)表都建哈希索引，InnoDB存儲(chǔ)引擎會(huì)自動(dòng)根據(jù)訪問的頻率和模式 來為某些頁(yè)建立哈希索引。

根據(jù)InnoDB的官方文檔顯示，啟用自適應(yīng)哈希索引后，讀取和寫入速度可以提高2倍；對(duì)于輔助索引的連接操作，性能可以提高5倍。在我看來，自適應(yīng)哈希索引是非常好的優(yōu)化模式，其設(shè)計(jì)思想是數(shù)據(jù)庫(kù)自優(yōu)化（self-tuning），即無需DBA對(duì)數(shù)據(jù)庫(kù)進(jìn)行調(diào)整。

Adaptive Hash Index是針對(duì)B+樹Search Path的優(yōu)化，因此所有會(huì)涉及到Search Path的操作，均可使用此Hash索引進(jìn)行優(yōu)化，這些可優(yōu)化的操作包括：Unique Scan/Range Scan(Locate First Key Page)/Insert/Delete/Purge等等，幾乎涵蓋InnoDB所有的操作類型

Adaptive，意味著不是所有的葉頁(yè)面都會(huì)以Hash索引維護(hù)，葉頁(yè)面進(jìn)入Hash 索引的條件是：同種類型的操作(Scan/Insert…)，命中同一葉頁(yè)面的次數(shù)，超過此頁(yè)面記錄數(shù)量的1/16，則可將當(dāng)前葉頁(yè)面加入Hash索引， 用以優(yōu)化后續(xù)可能的相同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 |
+----------------------------+-------+

不過我們可以通過參數(shù)innodb_adaptive_hash_index來禁用或啟動(dòng)此特性，默認(rèn)為開啟

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com