mysql之Innodb特性adaptive hash index

標籤：

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 mergesmerged operations: insert 0, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 553229, node heap has 17 buffer(s)0.00 hash searches/s, 0.00 non-hash searches/smysql> show variables like ‘%adaptive_hash%‘;   +----------------------------+-------+| Variable_name              | Value |+----------------------------+-------+| innodb_adaptive_hash_index | ON    |+----------------------------+-------+

不過我們可以通過參數innodb_adaptive_hash_index來禁用或啟動此特性，預設為開啟

 

參考文章

http://hedengcheng.com/?p=458

https://dev.mysql.com/doc/refman/5.0/en/innodb-adaptive-hash.html

https://dev.mysql.com/doc/innodb-plugin/1.0/en/innodb-performance-adaptive_hash_index.html

mysql之Innodb特性adaptive hash index