Adaptive Hash Index, AHI

scenario

For example, every time we query the primary key of the corresponding record from the secondary index, we need to use the primary key as the search key to search the primary key B+tree to find the record.

The innoDB engine optimizes when this kind of search becomes more frequent.

Maintain the Hash mapping between index key values (or key prefix) of all records in the index leaf page and the position of the index leaf page, and quickly locate the Offset of records meeting the conditions according to the index key values (prefix), reducing the cost of B+ tree Search Path. Locate the B+ tree path from the Root page to the Leaf page and optimize the Hash Index for quick query.

Adaptive Hash Index is optimized for the Search Path of B+ trees. Therefore, all operations involving the Search Path can be optimized using this Hash Index.

AHI also requires that the sequential access pattern to the page be the same. For example, for a federated index page such as (a,b), the access mode could be as follows:

  1. where a=xxx
  2. where a =xxx and b=xxx

The same access mode means that the query conditions are the same. If the above two queries are performed interchangeably, InnoDB storage engine will not construct AHI for the page. Of course, the access pattern is consistent, and there must be a certain number, such as N times the page has been accessed through this pattern, where N= records on the page *1/16.

By some estimates, with AHI enabled, read and write speeds are up to two times faster, and join performance for secondary indexes is up to five times better.

AHI is designed with the idea of database self-tuning, which does not require the DBA to manually adjust the database and can only be turned on or off. We can check the usage of Innodb AHI by using the following command.

mysql> show engine innodb status
Copy the code

reference

hedengcheng.com/?p=458

Blog.csdn.net/bohu83/arti…

Dev.mysql.com/doc/refman/…