程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> Innodb三大特性之adaptive hash index

Innodb三大特性之adaptive hash index

編輯: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 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來禁用或啟動此特性,默認為開啟


  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved