Mysql性能优化之自适应索引

自适应索引

是什么

自适应哈希索引是 InnoDB 存储引擎为了提高特定类型查询性能而自动构建的一种内存中的哈希索引结构。它可以根据查询模式和数据访问频率自动调整，以优化数据库的性能。

与B+Tree的区别

与传统的 B-tree 索引不同，哈希索引使用哈希函数将索引键值映射到哈希表中，从而实现了 O(1) 时间复杂度的快速查找。这意味着在等值查询场景下，自适应哈希索引能够提供比 B-tree 索引更快的查找速度。

原理

自动和动态的：InnoDB 会根据查询模式和数据访问频率自动决定是否构建哈希索引，并且会根据数据的变化和查询模式的变化动态地调整哈希索引。经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。

局限

哈希索引并不适用于所有查询场景。由于哈希索引不存储数据的物理顺序信息，因此它无法支持范围查询和排序操作。

成本

• 内存消耗： 自适应哈希索引完全在内存中构建，因此需要足够的内存资源。在高负载下，它可能会消耗大量的内存。为了平衡性能和资源消耗，InnoDB 存储引擎会根据查询模式和数据访问频率动态地构建和维护自适应哈希索引。
• 不可预测性：由于是基于运行时查询模式的，所以哈希索引的存在和组成是不可预测的。
• 不适用于所有查询：自适应哈希索引主要优化等值查询，对于范围查询或排序操作没有帮助。
• hash自适应索引会占用innodb buffer pool；

优点

• 自动优化：自适应哈希索引会自动构建和维护，不需要用户显式创建或管理。
• 性能提升：对于某些等值查询，自适应哈希索引可以显著减少查找时间，哈希索引，查询消耗 O(1）
• 降低对二级索引树的频繁访问资源。

利用自适应索引优化性能

• 监控自适应哈希索引的使用情况 通过执行SHOWENGINEINNODBSTATUS命令，可以查看自适应哈希索引的使用情况，包括索引的大小、构建速度以及查询性能
  等。这些信息可以帮助你了解自适应哈希索引在实际应用中的效果，并根据需要进行调整。
  在输出的SEMAPHORES部分中
  1
  mysql> show engine innodb status\G
  2
  Hash table size 34673, node heap has 0 buffers)
  4 0.00 hash searches/s, 0.00 non-hash searches / S
  字节为单位，占用内存空间总量，通过hash searches/s、non-hash searches/s算自适应hash索引带来的收益以及付出，确定是否开启自适应hash索引
• 调整内存配置 根据系统的实际情况和查询需求，合理调整InnoDB存储引擎的内存配置参数，如innodb_buffer_poolsize和innodb_adaptive_hash_index_partitions等。这些参数的设置将直接影响自适应哈希索引的构建和维护效果。
• 定期维护数据库 定期对数据库进行维护操作，如优化表(OPTIMIZETABLE)、重建索引(REBUILDINDEX)等，可以保持数据库的良好状态，提高自适应哈希索引的使用效果。

欢迎技术交流