What are the characteristics and defects of MySQL adaptive hash indexing?

This article mainly explains "what are the characteristics and defects of MySQL adaptive hash indexing". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn what are the characteristics and defects of MySQL adaptive hash indexing.

InnoDB buffer pool is used to store all kinds of data cache, including index page, data page, undo page, insert buffer, adaptive hash index, lock information stored by innodb, data dictionary information, etc. Adaptive hash index is a part of mysql innodb buffer pool.

The Innodb storage engine will monitor the lookup of the secondary index on the table. If it is found that a secondary index is accessed frequently, the secondary index becomes hot data, and the speed can be improved by establishing a hash index.

View the parameters in which the adaptive hash indexing function is enabled:

Mysql > show variables like'% ap%hash_index'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_adaptive_hash_index | ON |

+-+ +

1 row in set (0.01 sec)

Frequently accessed secondary index data is automatically generated into the hash index (data recently accessed three times in a row), and adaptive hash indexing is constructed through the B + tree of the buffer pool, so the speed of establishment is very fast.

Features:

1), disorder, no tree height

2) reduce the frequent access to resources on the secondary index tree

Index tree height