MySQL（七）：InnoDB 自适应Hash索引（Adaptive Hash Index）-蒲公英云

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

文章目录

- 1、简述
- 2、AHI（Adaptive Hash index）创建条件及注意事项
- 3、AHI（Adaptive Hash index）监控
- - 3.1、通过 *show engine innodb status* 命令查看AHI状态
  - 3.2、通过 information_schema.innodb_metrics 来监控AHI运行状态
- 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    |
+----------------------------+-------+

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

3.2、通过 information_schema.innodb_metrics 来监控AHI运行状态

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

mysql> set global innodb_monitor_enable = module_adaptive_hash;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT STATUS, NAME, subsystem FROM information_schema.INNODB_METRICS WHERE subsystem LIKE '%adaptive_hash%';
+---------+------------------------------------------+---------------------+
| STATUS  | NAME                                     | subsystem           |
+---------+------------------------------------------+---------------------+
| enabled | adaptive_hash_searches                   | adaptive_hash_index |
| enabled | adaptive_hash_searches_btree             | adaptive_hash_index |
| enabled | adaptive_hash_pages_added                | adaptive_hash_index |
| enabled | adaptive_hash_pages_removed              | adaptive_hash_index |
| enabled | adaptive_hash_rows_added                 | adaptive_hash_index |
| enabled | adaptive_hash_rows_removed               | adaptive_hash_index |
| enabled | adaptive_hash_rows_deleted_no_hash_entry | adaptive_hash_index |
| enabled | adaptive_hash_rows_updated               | adaptive_hash_index |
+---------+------------------------------------------+---------------------+
8 rows in set (0.00 sec)