MySQL（七）：InnoDB 自适应Hash索引（Adaptive Hash Index）

心已赠人 2023-10-05 18:27 2阅读 0赞

#### 文章目录 ####

*   *  [1、简述][1]
     *  [2、AHI（Adaptive Hash index）创建条件及注意事项][2_AHI_Adaptive Hash index]
     *  [3、AHI（Adaptive Hash index）监控][3_AHI_Adaptive Hash index]
     *   *  [3.1、通过 \*show engine innodb status\* 命令查看AHI状态][3.1_ _show engine innodb status_ _AHI]
         *  [3.2、通过 information\_schema.innodb\_metrics 来监控AHI运行状态][3.2_ information_schema.innodb_metrics _AHI]
     *  [4、参考文献][4]

### 1、简述 ###

哈希（hash）查找非常快，一般情况下时间复杂度为 O(1)，一般仅需要一次查找就能定位。而 B+ 树的查找次数，取决于 B+ 树的高度，B+ 树的高度一般为 34层，因此便需要查询34次。

InnoDB 存储引擎通过观察表上索引页的查询，如果发现建立哈希索引可以提升查询效率，则会自动建立哈希索引，称之为自适应哈希索引（Adaptive Hash Index）。

AHI 是基于缓冲池的B+树构造的索引，因此速度非常快，而且不需要对整张表建立索引。InnoDB 存储引擎会自动根据访问的频率和模式来自动为某些热点页建立哈希索引。

考虑到不同系统的差异，有些系统开启自适应哈希索引可能会导致性能提升不明显，而且为监控索引页查询次数增加了多余的性能损耗， MySQL5.7 更改了 AHI 实现机制，每个 AHI 都分配了特定分区，每个索引都绑定到特定分区，并且每个分区均受单独的锁存器保护。分区由 innodb\_adaptive\_hash\_index\_parts 变量控制。默认情况下，innodb\_adaptive\_hash\_index\_parts 变量值为8，最大值为 512。

自适应哈希索引功能由 innodb\_adaptive\_hash\_index 变量启用，或者在服务器启动时由–skip-innodb-adaptive-hash-index关闭。

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

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

### 2、AHI（Adaptive Hash index）创建条件及注意事项 ###

1、重复访问某一特定查询模式达到一定数量才会创建，如WHERE a = XXX,WHERE a = xxx and b = xxx。

2、AHI 只支持等值查询=和IN，不支持LIKE, REGEXP等。

3、AHI 存在内存中，占用缓冲池资源(Buffer Pool)。

4、AHI 无法人为干预，只能配置开关：set global innodb\_adaptive\_hash\_index=off/on。

### 3、AHI（Adaptive Hash index）监控 ###

#### 3.1、通过 *show engine innodb status* 命令查看AHI状态 ####

-------------------------------------
    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 1 buffer(s)
    Hash table size 34679, node heap has 3 buffer(s)
    0.00 hash searches/s, 0.00 non-hash searches/s

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18

#### 3.2、通过 information\_schema.innodb\_metrics 来监控AHI运行状态 ####

该表搜集了AHI 查询次数，更新次数等信息，可以很好的监控其运行状态，在某些负载下，AHI并不适合打开，关闭AHI可以避免额外的维护开销。

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13
 *  14
 *  15
 *  16
 *  17
 *  18

### 4、参考文献 ###

1.  [《高性能MySQL(第3版)》][MySQL_3]
2.  [《MySQL技术内幕：InnoDB存储引擎(第2版)》][MySQL_InnoDB_2]
3.  [《MySQL源码库》][MySQL]
4.  [《MySQL参考手册》][MySQL 1]
5.  [《MySQL实战45讲》][MySQL_45]
6.  [《数据库内核月报》][Link 1]

来源：[https://blog.csdn.net/u010647035/article/details/104729686][https_blog.csdn.net_u010647035_article_details_104729686]

[1]: https://blog.csdn.net/u010647035/article/details/104729686#1_1
[2_AHI_Adaptive Hash index]: https://blog.csdn.net/u010647035/article/details/104729686#2AHIAdaptive_Hash_index_21
[3_AHI_Adaptive Hash index]: https://blog.csdn.net/u010647035/article/details/104729686#3AHIAdaptive_Hash_index_29
[3.1_ _show engine innodb status_ _AHI]: https://blog.csdn.net/u010647035/article/details/104729686#31_show_engine_innodb_status_AHI_30
[3.2_ information_schema.innodb_metrics _AHI]: https://blog.csdn.net/u010647035/article/details/104729686#32_information_schemainnodb_metrics_AHI_52
[4]: https://blog.csdn.net/u010647035/article/details/104729686#4_74
[MySQL_3]: http://e.dangdang.com/products/1900716972.html
[MySQL_InnoDB_2]: http://e.dangdang.com/products/1900432045.html
[MySQL]: https://dev.mysql.com/doc/dev/mysql-server/latest/
[MySQL 1]: https://dev.mysql.com/doc/refman/8.0/en/
[MySQL_45]: https://time.geekbang.org/column/intro/139
[Link 1]: http://mysql.taobao.org/monthly/2020/02/02/
[https_blog.csdn.net_u010647035_article_details_104729686]: https://blog.csdn.net/u010647035/article/details/104729686