如何使用列索引一键加速慢查询？PolarDB AutoIndex大揭秘

阿里云瑶池数据库 2025-02-12

255

1. 导读

阿里云瑶池旗下的云原生关系型数据库PolarDB MySQL版的列存索引（IMCI）技术专为 OLAP 场景下的大数据量复杂查询而设计。通过列存索引，PolarDB MySQL版可以实现实时事务处理与实时数据分析的一体化能力，成为一站式的 HTAP（Hybrid Transactional/Analytical Processing）数据库解决方案。

在原来的使用方式中，如果要对慢查询涉及的表进行加速，需要手动为这些表添加列索引。一般做法是通过 CREATE TABLE 或 ALTER TABLE 在表的 COMMENT 字段中添加 COLUMNAR=1 ，列索引就绪后，优化器会根据查询代价自动决定是否使用列索引。但如果用户的SQL模板数量众多、SQL逻辑复杂，这种手动方式在实际使用中并不友好。

为此，PolarDB 现推出自动建列索引功能（AutoIndex）：它可根据慢查询情况自动对目标表添加列索引，从而加速查询。本篇文章将介绍该功能的技术背景、实现原理与使用方法，帮助大家深入了解如何通过自动化手段提升数据库查询性能。

2. AutoIndex技术背景

AutoIndex是一种基于自动添加索引来调优 SQL 性能的技术手段。对于行存索引而言，业界已有充分的研究和商业落地案例（如Oracle、SQL Server等），通常是从谓词（Predicate）、排序（Order By）或连接（Join）等角度，分析可能需要添加的二级索引，并在假设创建该索引后重新计算代价，再选择可显著降低查询代价的索引进行创建。

近年来，随着列索引技术在分析型数据库中的发展，AutoIndex 也逐渐被应用到 Redshift、Snowflake、Databricks、Heatwave 等系统中。典型的自动化手段包括：

1. 自动 Load/Unload：根据查询执行情况自动创建列索引并加速慢查询；对于不常用或占用资源过多的列索引会自动删除，减少空间消耗。

2. 自动 Encode：根据每列数据分布自动选择最优的编码算法，进一步压缩存储空间。

3. 自动选取 Distribute Key：使数据在 MPP 场景下分布更均衡，减少数据倾斜，同时优化分布式 GroupBy 与 Join 的数据 Shuffle。

4. 自动选取 Sort Key：结合常用谓词或连接、排序列来加速查询过滤或排序。

本篇文章主要介绍的是IMCI 在PolarDB MySQL版上实现的自动创建列索引（AutoIndex）功能。值得关注的是，在 IMCI 中自动创建列索引比创建行索引的代价更低，容错性也更高。这主要是因为：

1. 写入影响更小：行存索引过多会显著影响主节点写入性能，而 IMCI 在主节点上并不物化列存数据；新增列索引后，只需通过 Redo 将写入操作同步到列存只读节点，对写入性能几乎不造成影响。

2. 更高压缩率：列索引一般能达到 3～5 倍的压缩率，因此新增索引在存储空间上的开销也相对更小。

3. 对业务影响有限：包括无锁高效的数据结构采集 SQL 跟踪信息，以及使用 Nonblock DDL（非阻塞 DDL）降低对业务的干扰，这些都会在后续的实现原理章节中详细介绍。

3. AutoIndex实现原理

3.1 慢查跟踪与 DDL 语句生成

PolarDB MySQL 版集群各节点使用 SQL Trace 功能采集慢查询，对符合条件的 SQL 自动生成创建列索引的 DDL 语句。具体流程如下：

1. SQL Trace：以 SQL 模板为键，采用 MySQL 官方的无锁哈希表跟踪和记录 SQL 执行信息，可适配高并发、海量 SQL 模板场景。在 Sysbench 测试场景下，SQL Trace 对数据库性能的影响不超过 3%。

2. 触发条件：当某个慢查询的扫描行数达到设定阈值时，AutoIndex 会生成给相应表添加列索引的 DDL 语句。

3. 低开销设计：为避免重复解析 SQL、打开表（open table）以及获取 MDL 锁，AutoIndex 会直接使用已缓存的 THD（Thread Handler）的table list，因此几乎不额外增加系统负担。

4. 查看生成记录：可通过直连至各节点，查询 information_schema.imci_autoindex 系统表，了解已追踪并生成 DDL 语句集合的慢查询信息。例如：

mysql> SELECT * FROM information_schema.imci_autoindex\G

也可结合 information_schema.sql_sharing 获取更详细的 SQL 执行信息：

mysql> SELECT * 
       FROM information_schema.imci_autoindex autoindex, information_schema.sql_sharing share
       WHERE autoindex.sql_id = share.sql_id 
         AND share.type = 'SQL'\G

3.2 慢查收集聚合与 DDL 语句执行

集群中的列存只读节点（若有多个，则其中一个担任 Leader 角色）会收集并汇总各节点上存在列索引推荐的慢查询，并按执行时间倒序排序，依次执行推荐的 DDL 语句，直至达到单轮可执行的 DDL 数量阈值。此过程要点如下：

1. Instant DDL

添加列索引的操作在主节点是 Instant DDL，只需修改数据字典，并不会在主库做实际数据改动。
对应的 Redo 会复制到列存只读节点，由后者在后台构建列索引。

2. Nonblock DDL（非阻塞 DDL）

普通 DDL 在等待获取 MDL-X 锁期间会阻塞新事务进入；而 Nonblock DDL 在获取锁失败时，会先允许新事务进入，再不断重试获取锁。
对于 AutoIndex 场景而言，DDL 并非“紧急”操作，一旦本轮失败，可在下轮自动调度中继续尝试，因此 Nonblock DDL 能够很好地与自动建索引需求相结合。
可通过集群地址查询 information_schema.imci_autoindex_executed 系统表，查看最近执行成功的列索引推荐语句及关联慢查询（最多保留 128 条记录）。

3. 调度与执行限制

默认情况下，轮次间隔为 1 分钟，每轮最多执行 5 条 DDL 语句。
未在本轮被覆盖的列索引需要等待后续慢查询再次命中才能被重试。
列存只读节点在构建列索引时会受资源管控，虽然会引起资源指标上升，但不会打满节点资源。
本轮次列索引在列存节点上构建完成后，才会开始调度下一轮次。

3.3 列索引生效与使用

为充分发挥 AutoIndex 效果，推荐将列存只读节点挂在集群地址下，开启行列自动分流。这样在真实业务流量回放过程中，慢查询会触发 AutoIndex 创建列索引；当列存节点完成索引构建后，行列自动分流机制可根据查询代价将相关查询路由到列存节点，从而获得加速效果。

如果业务流量直连普通只读节点，则在列索引创建完成后，可手动将流量切换到列存只读节点，享受列索引带来的性能提升。列索引的状态查询、分流策略等常见使用问题可参考相关文档中的列存索引 FAQ。

列存索引常见问题：

🔗https://help.aliyun.com/zh/polardb/polardb-for-mysql/user-guide/faq-8

4. 测试效果

要使用 IMCI AutoIndex，只需在控制台开启“自动列存索引提速”开关即可。若当前集群没有列存只读节点，系统将引导至列存只读节点购买页面。

以下为基于 tpch100g 测试集的示例：

环境准备：每隔 1 分钟执行一遍 22 条 tpch 测试查询；集群地址开启 HTAP 配置、行列自动分流，行存并行度设为 8（主要用于缩短测试时间）。
测试结果：如下表所示，后续轮次的查询速度提升非常明显。在第一轮执行后半部分查询时（约第 12 条开始），AutoIndex 已经完成了对大多数表的列索引创建，优化器自动将这部分查询路由至列存节点。

执行轮次	总时间 (s)	行存计划/列存计划	提速效果
1	8293	10/12	-
2	118	1/21	约 70 倍
3	110	0/22	约 75 倍

从测试数据可见，AutoIndex 在 HTAP 场景下能够非常直观地提升查询性能，大幅缩短执行时间。

5. 用户价值

PolarDB MySQL版的列存索引特性提供了一系列优势，使其在兼容性、性能和成本方面都有显著提升：

100%兼容MySQL：支持MySQL所有数据类型，完全兼容MySQL协议。
优秀的HTAP性能：分析场景性能普遍可以达到1-2个数量级提升。
行列混合存储，降低成本：行列存储保证事务一致性。列索引在某些查询场景性能更优，成本更低。

列存索引的AutoIndex功能进一步增强了用户体验和成本效益：

自动提速操作简单：一键开启自动性能提升，无需复杂配置和调整。
节约运维工作量：自动为慢查询创建列索引，减少手动慢查询优化工作。
节约IT成本：无需为所有库和表创建列索引，只针对慢查询优化，节省内存和存储资源。

总体来说，这些特性使PolarDB在增强可用性和性能的同时，显著降低了运维和IT成本。

总结

本篇文章介绍了PolarDB MySQL版 IMCI AutoIndex 功能的技术背景、实现原理、使用方法和核心优势。通过自动化的列索引创建与管理机制，AutoIndex 在 HTAP 场景下能有效提升慢查询的执行效率，且对数据库正常事务性能影响极小。对于日常需要分析大量业务数据的场景，这是一种降本增效的实用方案。

希望本文能帮助您快速了解并上手使用 AutoIndex，加速PolarDB中的慢查询。如果对具体实现细节或使用中遇到问题，欢迎参考官方文档或与我们进一步交流。