MySQL InnoDB features Adaptive hash Index

1, Adaptive Hash Indexes definition

If a table fits almost entirely in main memory, the fastest-to-perform queries on it's to use hash indexes. Has InnoDB a mechanism this 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 all built based on a existing B-tree index on the table. InnoDBcan build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of sear Ches that InnoDB observes for the B-tree index. A Hash index can be partial:it are not required that the whole B-tree index is cached in the buffer pool. InnoDBbuilds hash indexes on demand for those pages of the index that is often accessed.

In a sense, InnoDB tailors itself through the adaptive Hash Index mechanism to ample main memory, coming closer to the arch Itecture of Main-memory databases.

The configuration parameter innodb_adaptive_hash_index can set to disable or enable the Adaptive hash index. See sections 8.3.4, "dynamically changing innodb_adaptive_hash_index " for details.

2. Hash index

Hashing (hash) is a very fast way to find, in general the time Complexity is O (1), commonly used for connection (join) operations, such as SQL Server and the hash connection in Oracle (hash join). However, common databases such as SQL Server and Oracle do not support hash indexes. MySQL's heap storage engine has a default index type of hash, while the InnoDB storage Engine presents an alternative implementation method, an adaptive hash Index (adaptive hash)

3. Adaptive hashing

The InnoDB storage engine monitors the lookup of indexes on a table, and if it observes that a hash index can lead to an increase in speed, a hash index is established, so it is called adaptive (adaptive). The adaptive Hash index is constructed from the B + Tree of the buffer pool and is therefore fast to build. And without having to hash the entire table, the InnoDB storage engine automatically hashes the pages based on the frequency and pattern of access.

According to the official document of InnoDB, when the Adaptive hash index is enabled, the read and write speeds can be increased by twice times, and the performance can be increased by 5 times times for secondary index connection operations. In my opinion, Adaptive Hash index is a very good optimization mode, its design idea is database self-optimization (self-tuning), that is, no DBA to adjust the database.

Adaptive Hash index is optimized for B + Tree search path, so all operations that involve search path can be optimized using this Hash index, which includes: Unique Scan/range Scan ( Locate first Key Page)/insert/delete/purge and so on, covering almost all of the InnoDB operation types

Adaptive, meaning that not all leaf pages will be maintained in hash index, leaf page into the hash index condition is: the same type of operation (Scan/insert ...), hit the same leaf page, more than the number of records on this page 1/ 16, the current leaf page can be added to the hash index to optimize the subsequent possible same 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 BUF Fer (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    |+----------------------------+-------+

However, we can disable or start this feature by using the parameter Innodb_adaptive_hash_index, which is enabled by default

Reference articles

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 features Adaptive hash Index