一文读懂AnalyticDB MySQL过滤条件智能下推原理

本文涉及的产品
阿里云百炼推荐规格 ADB PostgreSQL,4核16GB 100GB 1个月
云原生数据仓库AnalyticDB MySQL版,基础版 8ACU 100GB 1个月
简介: AnalyticDB智能优化功能,加速用户查询,把简单交给用户

前置知识

过滤条件下推

在我们的常规认知中,过滤条件肯定是推的越靠近底层越好,将尽可能多的过滤条件更贴近数据源,以使查询时能跳过无关的数据,在AnalyticDB MySQL中,由于存储计算分离架构,那么谓词下推就是把所有能推的谓词都推到存储节点上去做。比如下图,所有的过滤条件都推到存储节点上去做,这样减少了后续算子的计算量,也减少了中间网络传输的数据量。谓词下推带来了很多的好处,基本上所有数据库,都会把谓词下推作为他们重要的优化功能。

1.png

全下推到存储弊端

但是过滤条件下推到存储层一定会快吗?为了弄清这个问题,我们先来看一下AnalyticDB MySQL的存储节点的索引结构。ADB目前默认是全索引,即会为所有列创建索引,并且支持多个条件同时走索引,快速多路合并,能够在毫秒级别找出满足条件的结果集。下图展示了一个表上多个过滤条件的索引查找过程。可以看到是每一列都会查找索引,最后将RowIds做交并差集运算。2.png

不下推 VS 下推

了解完索引结构后,我们构造一个简单的例子,来说明过滤条件下推到存储之后却变慢的情况。我们简单假设一个代价模型,每一行扫索引的代价是A,每一行取明细以及数据传输的代价是B,计算层每一行过滤的代价是C。并考虑如下sql。


createtable user (age int, id int);selectcount(*)from user;-- 结果10,000;selectcount(*)from user where age >18;-- 结果9,000selectcount(*)from user where id <10;-- 结果 20-- 考虑如下sqlselect*from user where age >18and id <10-- 结果 10

常规计划代价 10,000A + 10,000A + 10B (两列分别查索引的代价 + 取明细以及数据传输的代价)

Id列不下推计划代价 10,000A + 20B + 20C (一列查索引代价 + 取明细以及数据传输的代价 +计算层代价)

可以看出如果 10B + 20C < 10,000A 的话,Id列不下推计划是更优的计划。

3.png

可优化场景

默认情况下,优化器会将所有带索引的列下推存储,从而减少读取至计算引擎的数据。但是仍存在几种场景不建议使用索引过滤数据:

谓词选择率高,且谓词条件多,数据经过过滤后返回的数据仍然很多,那么使用索引进行数据过滤然后取交集的效果不一定好。

磁盘IO压力大。如果用户业务的查询特征是占用较多的IO资源,或者数据写入较多导致占用了较多IO资源,那么使用索引进行数据过滤时,存在磁盘IO资源的争抢,过滤效果也可能较差。

过滤谓词中带有复杂操作,比如字符串比较,LIKE操作等,会对存储节点产生较大的资源消耗,如果其他条件过滤后数据不多,不下推会对整体性能更加友好。

智能下推

所以为了优化ADB上述场景的性能,同时也为了避免ADB研发人员和用户耗费精力使用不下推hint来进行sql调优,在ADB新版本推出了智能优化功能,优化器基于准确的统计信息,在ADB中智能控制过滤条件是否下推到存储节点,让用户不用再纠结于是否下推的sql调优,加速用户查询,把简单交给用户。

术语定义

▶︎ conjunction

我们把过滤条件按照最外层的AND拆分之后的单元叫做conjunction,比如 ((A & B) | C) & D & E 就是由 ((A & B) | C), D,E 三个conjunction组成的。只所以这么定义是,conjunction是是否下推到存储的最小单元。一个conjunction里面的条件要么都下推,要么都不下推。

▶︎ selectivity

谓词的过滤度,如果有100行数据,满足A>10的数据是10行,那么A>10的selectivity是10%

▶︎ connector

ADB 优化器支持多个connector,既可以支持ADB本身存储引擎,也可以支持OSS外表。不同的connector需要不同的处理。

实现

▶︎ A series of rules这里是ADB优化器原有的一系列过滤条件下推的规则,会尽量把filter一路推到 table 上方,推到距离存储节点最近的地方,然后下面的工作便交给了智能下推模块。智能下推模块来决定什么谓词可以继续推给存储节点。▶︎ Pretreatment短路优化:在模块的开始会粗略判断整个表扫描的开销,比如是个很小的表,那就快速跳过,以减少后续流程处理的额外开销表达式转化:应用布尔代数定律,尽量将过滤条件转换为AND连接。拿一个简单的例子来说,( A & B ) | ( C & D ) 是无法做到部分下推,部分不下推的,但是将其转换为( A | C ) & ( A | D ) & ( B | C ) & ( B | D)之后。便可以做到部分下推。这一步进行了限制,不会盲目转化,因为转换之后的表达式变长,可能会导致codegen超限等影响。

▶︎ CalcSelectivity

在这里会根据直方图等精准的statistics,调用ADB优化器中的Cardinality Estimation模块去为每个conjunction计算selectivity和相应的reliability。这个模块依托ADB优化器中精准的统计信息以及完善的基数估计,提供高质量的选择率,为后续的代价的计算以及下推方案的选择,提供准确的输入。

▶︎ Connector Cost Model

存储层自定义的代价模型,定义了根据selectivity计算出来的cost满足一定条件的过滤条件不会下推。这种分离的模式使得接入ADB的别的存储层,有智能下推的需求,也可以简单实现connector cost model,便可以实现智能下推的功能。

▶︎ FilterPushDownSelection这个模块负责将所有的conjunction以及其selectivity和reliability输入给相应的connector cost model,然后以conjunction为最小单元枚举下推组合,模型算出代价,最后根据代价去选择开销最低的下推方案。这样下来,ADB便有之前的全下推,演变为现在的智能下推。

全下推 VS 智能下推效果

下图展示了一些索引扫描耗时占比大的查询使用智能下推后的加速效果。数据来源于内部灰度客户在智能下推开启前后,线上sql的平均RT变化。可以看到有着十分明显的性能提升。4.png

结语

在数据库和大数据等相关领域,查询优化十分重要。实际生产中的问题,远比本文提到的要复杂。篇幅有限,更多技术细节没有深究。AnalyticDB 作为国内领先的云原生数仓和 TPC-DS 世界记录保持者,在查询优化技术上不断投入和创新。对技术感兴趣的同学,欢迎加入 AnalyticDB 社区讨论。

(云数据仓库ADB-开发者群:钉钉群号 23128105)


AnalyticDB MySQL新推出的湖仓版已正式商用,对于低成本离线处理ETL有需求,同时又需要使用高性能在线分析支撑BI报表/交互式查询/APP应用的用户,欢迎购买和体验!

另外对湖仓版感兴趣的客户也可以填写问卷来进行试用申请,点击此处填写问卷。

相关实践学习
AnalyticDB MySQL海量数据秒级分析体验
快速上手AnalyticDB MySQL,玩转SQL开发等功能!本教程介绍如何在AnalyticDB MySQL中,一键加载内置数据集,并基于自动生成的查询脚本,运行复杂查询语句,秒级生成查询结果。
阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
云原生数据仓库AnalyticDB MySQL版是一种支持高并发低延时查询的新一代云原生数据仓库,高度兼容MySQL协议以及SQL:92、SQL:99、SQL:2003标准,可以对海量数据进行即时的多维分析透视和业务探索,快速构建企业云上数据仓库。 了解产品 https://www.aliyun.com/product/ApsaraDB/ads
相关文章
|
24天前
|
存储 关系型数据库 MySQL
MySQL主从复制原理和使用
本文介绍了MySQL主从复制的基本概念、原理及其实现方法,详细讲解了一主两从的架构设计,以及三种常见的复制模式(全同步、异步、半同步)的特点与适用场景。此外,文章还提供了Spring Boot环境下配置主从复制的具体代码示例,包括数据源配置、上下文切换、路由实现及切面编程等内容,帮助读者理解如何在实际项目中实现数据库的读写分离。
MySQL主从复制原理和使用
|
1月前
|
缓存 算法 关系型数据库
Mysql(3)—数据库相关概念及工作原理
数据库是一个以某种有组织的方式存储的数据集合。它通常包括一个或多个不同的主题领域或用途的数据表。
50 5
Mysql(3)—数据库相关概念及工作原理
|
1月前
|
存储 缓存 关系型数据库
MySQL事务日志-Redo Log工作原理分析
事务的隔离性和原子性分别通过锁和事务日志实现,而持久性则依赖于事务日志中的`Redo Log`。在MySQL中,`Redo Log`确保已提交事务的数据能持久保存,即使系统崩溃也能通过重做日志恢复数据。其工作原理是记录数据在内存中的更改,待事务提交时写入磁盘。此外,`Redo Log`采用简单的物理日志格式和高效的顺序IO,确保快速提交。通过不同的落盘策略,可在性能和安全性之间做出权衡。
1630 14
|
24天前
|
SQL 关系型数据库 MySQL
Mysql中搭建主从复制原理和配置
主从复制在数据库管理中广泛应用,主要优点包括提高性能、实现高可用性、数据备份及灾难恢复。通过读写分离、从服务器接管、实时备份和地理分布等机制,有效增强系统的稳定性和数据安全性。主从复制涉及I/O线程和SQL线程,前者负责日志传输,后者负责日志应用,确保数据同步。配置过程中需开启二进制日志、设置唯一服务器ID,并创建复制用户,通过CHANGE MASTER TO命令配置从服务器连接主服务器,实现数据同步。实验部分展示了如何在两台CentOS 7服务器上配置MySQL 5.7主从复制,包括关闭防火墙、配置静态IP、设置域名解析、配置主从服务器、启动复制及验证同步效果。
Mysql中搭建主从复制原理和配置
|
1月前
|
SQL 关系型数据库 MySQL
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
尼恩,一位40岁的资深架构师,通过其丰富的经验和深厚的技術功底,为众多读者提供了宝贵的面试指导和技术分享。在他的读者交流群中,许多小伙伴获得了来自一线互联网企业的面试机会,并成功应对了诸如事务ACID特性实现、MVCC等相关面试题。尼恩特别整理了这些常见面试题的系统化解答,形成了《MVCC 学习圣经:一次穿透MYSQL MVCC》PDF文档,旨在帮助大家在面试中展示出扎实的技术功底,提高面试成功率。此外,他还编写了《尼恩Java面试宝典》等资料,涵盖了大量面试题和答案,帮助读者全面提升技术面试的表现。这些资料不仅内容详实,而且持续更新,是求职者备战技术面试的宝贵资源。
阿里面试:MYSQL 事务ACID,底层原理是什么? 具体是如何实现的?
|
1月前
|
存储 SQL 关系型数据库
mysql中主键索引和联合索引的原理与区别
本文详细介绍了MySQL中的主键索引和联合索引原理及其区别。主键索引按主键值排序,叶节点仅存储数据区,而索引页则存储索引和指向数据域的指针。联合索引由多个字段组成,遵循最左前缀原则,可提高查询效率。文章还探讨了索引扫描原理、索引失效情况及设计原则,并对比了InnoDB与MyISAM存储引擎中聚簇索引和非聚簇索引的特点。对于优化MySQL性能具有参考价值。
|
3月前
|
SQL 关系型数据库 MySQL
说一下MySQL主从复制的原理?
【8月更文挑战第24天】说一下MySQL主从复制的原理?
63 0
|
3月前
|
SQL 关系型数据库 MySQL
实时数仓 Hologres操作报错合集之Flink CTAS Source(Mysql) 表字段从可空改为非空的原因是什么
实时数仓Hologres是阿里云推出的一款高性能、实时分析的数据库服务,专为大数据分析和复杂查询场景设计。使用Hologres,企业能够打破传统数据仓库的延迟瓶颈,实现数据到决策的无缝衔接,加速业务创新和响应速度。以下是Hologres产品的一些典型使用场景合集。
|
3月前
|
SQL 关系型数据库 MySQL
Mysql原理与调优-事务与MVCC
【8月更文挑战第19天】
|
3月前
|
存储 SQL 关系型数据库
深入MySQL锁机制:原理、死锁解决及Java防范技巧
深入MySQL锁机制:原理、死锁解决及Java防范技巧

热门文章

最新文章

相关产品

  • 云原生数据仓库AnalyticDB MySQL版