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

本文涉及的产品
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
简介: 如何使用列索引一键加速慢查询?PolarDB AutoIndex大揭秘

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中的慢查询。如果对具体实现细节或使用中遇到问题,欢迎参考官方文档或与我们进一步交流。

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
存储 关系型数据库 MySQL
PolarDB-X 存储引擎核心技术 | 索引回表优化
数据库系统为了高效地存储、检索和维护数据,采用了多种不同的数据组织结构。不同的组织结构有其特定的用途和优化点,比如提高查询速度、优化写入性能、减少存储空间等,目前 PolarDB-X 采用了 B-Tree 的索引组织结构。
|
SQL 关系型数据库 MySQL
关系型数据库选择唯一性索引
【5月更文挑战第19天】
230 2
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
PolarDB产品使用问题之相同的SQL语句在不同时间执行EXPLAIN计划显示出不同的索引类型,是什么原因
|
存储 缓存 负载均衡
【PolarDB-X 技术揭秘】Lizard B+tree:揭秘分布式数据库索引优化的终极奥秘!
【8月更文挑战第25天】PolarDB-X是阿里云的一款分布式数据库产品,其核心组件Lizard B+tree针对分布式环境优化,解决了传统B+tree面临的数据分片与跨节点查询等问题。Lizard B+tree通过一致性哈希实现数据分片,确保分布式一致性;智能分区实现了负载均衡;高效的搜索算法与缓存机制降低了查询延迟;副本机制确保了系统的高可用性。此外,PolarDB-X通过自适应分支因子、缓存优化、异步写入、数据压缩和智能分片等策略进一步提升了Lizard B+tree的性能,使其能够在分布式环境下提供高性能的索引服务。这些优化不仅提高了查询速度,还确保了系统的稳定性和可靠性。
237 5
|
SQL 关系型数据库 分布式数据库
PolarDB产品使用问题之如何实现在线上加索引,并且不会锁表
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
C# UED 定位技术
WPF控件大全:初学者必读,掌握控件使用技巧,让你的应用程序更上一层楼!
【8月更文挑战第31天】在WPF应用程序开发中,控件是实现用户界面交互的关键元素。WPF提供了丰富的控件库,包括基础控件(如`Button`、`TextBox`)、布局控件(如`StackPanel`、`Grid`)、数据绑定控件(如`ListBox`、`DataGrid`)等。本文将介绍这些控件的基本分类及使用技巧,并通过示例代码展示如何在项目中应用。合理选择控件并利用布局控件和数据绑定功能,可以提升用户体验和程序性能。
484 0
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用问题之mysql迁移后查询不走索引了,该如何解决
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
245 1
|
运维 关系型数据库 分布式数据库
PolarDB产品使用问题之列存索引的原理是什么
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
159 1
|
存储 算法 数据处理
惊人!PolarDB-X 存储引擎核心技术的索引回表优化如此神奇!
【6月更文挑战第11天】PolarDB-X存储引擎以其索引回表优化技术引领数据库发展,提升数据检索速度,优化磁盘I/O,确保系统在高并发场景下的稳定与快速响应。通过示例代码展示了在查询操作中如何利用该技术高效获取结果。索引回表优化具备出色性能、高度可扩展性和适应性,为应对大数据量和复杂业务提供保障,助力企业与开发者实现更高效的数据处理。
143 3
|
关系型数据库 分布式数据库 数据库
PolarDB产品使用问题之Join评估的行数比索引的基数小,是什么导致的
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。

热门文章

最新文章