数据库内核那些事|细说PolarDB优化器查询变换:IN-List变换

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: 数据库内核那些事|细说PolarDB优化器查询变换:IN-List变换

image.png

导读

数据库的查询优化器是整个系统的"大脑",一条SQL语句执行是否高效在不同的优化决策下可能会产生几个数量级的性能差异,因此优化器也是数据库系统中最为核心的组件和竞争力之一。阿里云瑶池旗下的云原生数据库PolarDB MySQL版作为领先的云原生数据库,希望能够应对广泛用户场景、承接各类用户负载,助力企业数据业务持续在线、数据价值不断放大,因此对优化器能力的打磨是必须要做的工作之一。

本系列将从PolarDB for MySQL的查询变换能力开始,介绍我们在这个优化器方向上逐步积累的一些工作。


引言

PolarDB MySQL作为一款HTAP数据库,在复杂SQL查询优化能力上做了很多深入工作。早期用户SQL都非常简单,MySQL单机能力也有限。随着业务数据越来越多,业务场景越来越复杂,迫切需要越来越强大的数据库来满足统计、报表需求。

PolarDB在并行能力、查询变换能力、优化器等方面都做了非常深入的工作,这些工作有一个总目标:让用户的复杂查询执行得越来越快。本篇文章将对PolarDB的IN-List变换进行深入阐述,从而让我们对PolarDB的查询改写能力有更感性的认知。下面是一个常见的慢SQL:in函数运算,里面的常量比较多。

select        sum(l_extendedprice) / 7.0 as avg_yearly
from
        lineitem
        where
            l_partkey in (
9628136,19958441,10528766,.......); #in list里面有上千个常量值。

SQL语句是常见的单表过滤查询,然后进行agg汇总,实际执行耗时比较长,执行比较慢的原因是IN-List里面有上千个常量值。


原生MySQL

原生的MySQL执行计划如下:

+---------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                           |
+---------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(lineitem.L_EXTENDEDPRICE)
    -> Filter: (lineitem.L_PARTKEY in (9628136,19958441,10528766,....) (cost=60858714.81 rows=297355930)
        -> Table scan on lineitem  (cost=60858714.81 rows=594711859)
|
+---------------------------------------------------------------------------------------------------+

执行过程是线性scan lineitem 5.9亿条数据,逐条去判断是不是在IN-List里面,这个算子是Item_func_in,in集合元素个数比较多,我们使用10W常量值进行测试,这个算子做求值运算耗时较长,整体完成需要 375s。

具体看下Item_func_in代码执行逻辑:

判断是否可以二分查找,如可以二分查找,将IN-List转成有序数组;

如果产生了有序数组,则执行时优先尝试二分查找;

否则,线性scan,逐一判断左表达式是否等于IN-List里面的item。

可以看到求值逻辑已经是教优的了,这个算子基本没有优化空间了。主要是外层循环次数太多,如果能减少外层的大loop,那么就能降低延时。


PolarDB

PolarDB解决问题的思路是对该SQL做查询变换, 把IN-List转变成一张物化表,加入join list,具体变换过程如下:

Step 1:转成in子查询,上述SQL改写为

select ... from lineitem where l_partkey in (...)
====>
select ... from lineitem where l_partkey in
 (select dt._col_1 from (values (9628136),(19958441),...) dt)

Step 2:SubQuery Unnest-消除子查询

子查询已经是非相关的,通过SU技术,可以消除子查询,转化为semi-join。物化表经过去重,并且Join列非空,进而可以转化为inner-join。

SQL将继续改写为:

====>
select ... from lineitem, (values (9628136),(19958441),...) dt) where l_partkey = dt._col_1

通过这种变换能到得如下好处:

不用逐条去做filter,因为MySQL执行器是火山模型,增加了一个filter算子就增加了一层虚函数调用;

Join有join buffer,可以一个batch一个batch参与Join,这是转成join list的一个好处;

转成join list,join的优化非常多,如join order&access path,总能选到更优plan。

最后执行的plan如下:

+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Aggregate: sum(lineitem.L_EXTENDEDPRICE)
    -> Nested loop inner join
        -> Table scan on dt
            -> Materialize with deduplication
                -> scan on in-list: 100000 rows
        -> Index lookup on lineitem using LINEITEM_FK2 (L_PARTKEY=dt._col_1), with index condition: (lineitem.L_PARTKEY = dt._col_1)  (cost=7.34 rows=29)

物化表数据量少,作为外表,inner-join成功使用lineitem索引,只要扫10万条物化表记录,然后再使用LINEITEM_FK2索引进行连接,整条SQL执行下来只需要32s。

测试效果

PolarDB IN-List优化后在 TPCH 100G 数据集上比原生方式提升11.5倍,又因为PolarDB支持并行查询,32并行度模式下提升上百倍。

image.png


总结

原理上,PolarDB做完IN-List转换为Join-List后,能得到如下两方面的提升:

 IN-List里面的常量都经过物化去重,基数可能会有不小的下降,这取决于重复值;

 IN-List消去,变成了一张物化表,参与Join-List后,有更多access path选择,比如选择更好的index,更多的Join方式:hash join还是nest loop join。

细微之处见真功夫,做IN-List转换还要完成其他工作,如需要适配prepare statement协议、适配并行查询协议等,PolarDB在云数据库市场能做到特性遥遥领先,离不开背后工程师们坚持客户价值第一的初心,后续我们将介绍更多查询改写相关内容,敬请期待。

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
12天前
|
存储 NoSQL Redis
【Redis】利用Redis List实现数据库分页快速查询
【Redis】利用Redis List实现数据库分页快速查询
170 0
|
12天前
|
存储 缓存 NoSQL
利用Redis List实现数据库分页快速查询的有效方法
利用Redis List实现数据库分页快速查询的有效方法
|
7天前
|
存储 关系型数据库 分布式数据库
【PolarDB开源】深入PolarDB内核:探究存储计算分离架构的设计哲学
【5月更文挑战第20天】PolarDB是阿里巴巴的云原生分布式数据库,以其存储计算分离架构为核心,解决了传统数据库的扩展性和资源灵活性问题。该架构将数据存储和计算处理分开,实现高性能(通过RDMA加速数据传输)、高可用性(多副本冗余保证数据可靠性)和灵活扩展(计算资源独立扩展)。通过动态添加计算节点以应对业务流量变化,PolarDB展示了其在云时代应对复杂业务场景的能力。随着开源项目的进展,PolarDB将持续推动数据库技术发展。
46 6
|
12天前
|
SQL 关系型数据库 分布式数据库
|
12天前
|
存储 NoSQL Java
【Redis】利用 Redis List 实现 Java 数据库分页快速查询
在大型应用中,数据库分页查询是日常开发中不可避免的需求之一。随着数据量的不断增加,传统的数据库分页方式可能会变得效率较低。为了解决这一问题,本文将介绍如何使用 Redis List 数据结构,结合 Java 编程语言,实现高效的数据库分页查询。
141 9
|
12天前
|
存储 SQL 关系型数据库
Mysql鸡础(从数据库中导入学生数据用list集合存储emp成员)
Mysql鸡础(从数据库中导入学生数据用list集合存储emp成员)
|
6月前
|
缓存 关系型数据库 Serverless
数据库内核那些事,PolarDB HTAP Serverless,打造经济易用的实时分析系统
下本从IMCI Serverless核心优势角度的介绍各优化工作内容。
数据库内核那些事,PolarDB HTAP Serverless,打造经济易用的实时分析系统
|
5天前
|
关系型数据库 分布式数据库 数据库
【PolarDB 开源】PolarDB 性能调优实录:提升数据库集群吞吐量的技巧
【5月更文挑战第22天】PolarDB 性能调优关键点包括硬件资源配置、数据库参数调整、索引优化、分区策略、事务优化及性能监控。创建高效索引如`CREATE INDEX idx_name ON table_name (column_name);`,根据业务场景选择分区方式,调整事务隔离级别以提升并发性能。监控 CPU、内存等指标,定期维护数据库,结合业务特点综合调优,从而提升数据库集群吞吐量。这些技巧有助于发挥PolarDB潜力,支持业务高效运行。
109 5
|
1天前
|
Cloud Native 关系型数据库 分布式数据库
【PolarDB开源】PolarDB与云原生数据库比较:特点、优势与选型建议
【5月更文挑战第26天】PolarDB是阿里云的云原生数据库,以其计算存储分离、一写多读架构和数据一致性保障脱颖而出。与Amazon Aurora和Google Cloud Spanner相比,PolarDB在中国市场更具优势,适合读多写少的场景和需要严格数据一致性的应用。企业在选型时应考虑业务需求、地域、读写比例和兼容性。PolarDB作为优秀解决方案,将在云原生数据库领域持续发挥关键作用。
21 1
|
3天前
|
Cloud Native 关系型数据库 分布式数据库
【PolarDB开源】PolarDB数据迁移实战:平滑过渡至云原生数据库
【5月更文挑战第24天】本文介绍了如何平滑迁移数据至阿里云的云原生数据库PolarDB,包括迁移准备、策略选择、步骤、验证及示例代码。通过需求分析、环境准备和数据评估,选择全量、增量或在线迁移策略。使用数据导出、导入及同步工具(如DTS)完成迁移,并在完成后验证数据一致性、性能和安全。正确执行可确保业务连续性和数据完整性。
63 1