Adaptive Hash Index of key features of Innodb

InnoDB is known to use the B+ tree index structure, but it also supports another index: adaptive hash index.

The hash table is in the form of an array + linked list. The hash bucket position corresponding to the key in each node data is calculated by hash function. If hash conflict occurs, zipper method is used to solve it. For more information, please refer to Baidu Encyclopedia-Hash Table

As you can see from the above, hash table lookups are optimally performed once. InnoDB uses a B+ tree, and the number of searches in the optimal case depends on the number of layers. So to improve query efficiency, InnoDB allows adaptive hashing to improve performance.

The parameter innodb_adaptive_hash_index can be used to determine whether it is enabled. Aliyun is closed by default.

mysql>show variables like '%innodb_adaptive_hash_index%'+----------------------------------+-----------------+| Variable_name | Value |+----------------------------------+-----------------+| innodb_adaptive_hash_index | OFF || innodb_adaptive_hash_index_parts | 8 |+----------------------------------+-----------------+

The storage engine automatically monitors queries on each index page, and if it can improve query efficiency by using an adaptive hash index, it automatically creates an adaptive hash index without requiring any setup by developers or operations personnel.

Adaptive hash indexing is fast because it is created on the B+ tree pages of innodb's buffer pool, not on the entire table.

You can see how adaptive hash indexes are used by looking at innodb's status.

INSERT BUFFER AND ADAPTIVE HASH INDEX-------------------------------------Ibuf: size 1, free list len 6236, seg size 6238, 50367801 mergesmerged operations: insert 78512159, delete mark 0, delete 0discarded operations: insert 0, delete mark 0, delete 0Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)Hash table size 13148407, node heap has 0 buffer(s)0.00 hash searches/s, 67793.48 non-hash searches/s

You can see the size of the adaptive hash index and how it is used.

Note: According to the characteristics of hash table, adaptive hash index can only be used for equivalent query, range or size is not allowed.

Equivalent query: select * from xx where name = "xxx";