As we all know, the index structure used by InnoDB is B + tree, but in fact, it also supports another index: adaptive hash index.

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

It can be seen from the above that in the optimal case of hash table lookup, the lookup is once, while InnoDB uses B + tree, and the number of lookups in the optimal case is determined by the number of layers. Therefore, in order to improve query efficiency, InnoDB allows the use of adaptive hashes to improve performance.

The parameter InnoDB adaptive hash index can be used to determine whether to turn it on. It is on by default.

mysql> show variables like "innodb_adaptive_hash_index";
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+

The storage engine will automatically monitor the queries on index pages. If it can improve the query efficiency by using adaptive hash index, it will automatically create adaptive hash index, without any setting operation by developers or operation and maintenance personnel.

Adaptive hash index is to create the B + tree page of innodb buffer pool, not the whole table, so it is very fast.

You can view the usage of the adaptive hash index by viewing the status of innodb.

mysql> show engine innodb  status \G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2019-03-07 23:37:23 0x7f1f2d34c700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
------------------------------------------------------
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 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
Hash table size 34679, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
-------------------------------
END OF INNODB MONITOR OUTPUT
============================

You can see the adaptive hash index size, usage per second.

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

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