SQL 数据库查询的优化工具及实用

简介: 这是MaxCompute有关SQL优化器原理的系列文章之一,本文主要是对数据库查询优化器的一个综述,包括: 查询优化器定义、分类 查询优化器执行过程 CBO框架Calcite简介 1.查询优化器是什么 数据库主要由三部分组成,分别是解析器、优化器和执行引擎,如下图所示: 其中优化器是数据库中用于把关系表达式转换成执行计划的核心组件,很大程度上决定了一个系统的性能。

这是MaxCompute有关SQL优化器原理的系列文章之一,本文主要是对数据库查询优化器的一个综述,包括:

  • 查询优化器定义、分类

  • 查询优化器执行过程

  • CBO框架Calcite简介

1.查询优化器是什么

数据库主要由三部分组成,分别是解析器、优化器和执行引擎,如下图所示:

0e160a70129167ca9bbcaa2c38c669d9539ebf4e

其中优化器是数据库中用于把关系表达式转换成执行计划的核心组件,很大程度上决定了一个系统的性能。

2.查询优化器分类

查询优化器分为两类:基于规则的优化器(Rule-Based Optimizer,RBO) 和基于代价的优化器(Cost-Based Optimizer,CBO) :

基于规则的优化器(Rule-Based Optimizer,RBO)

根据优化规则对关系表达式进行转换,这里的转换是说一个关系表达式经过优化规则后会变成另外一个关系表达式,同时原有表达式会被裁剪掉,经过一系列转换后生成最终的执行计划。

RBO中包含了一套有着严格顺序的优化规则,同样一条SQL,无论读取的表中数据是怎么样的,最后生成的执行计划都是一样的。同时,在RBO中SQL写法的不同很有可能影响最终的执行计划,从而影响脚本性能。

基于代价的优化器(Cost-Based Optimizer,CBO)

根据优化规则对关系表达式进行转换,这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式,同时原有表达式也会保留,经过一系列转换后会生成多个执行计划,然后CBO会根据统计信息和代价模型(Cost Model)计算每个执行计划的Cost,从中挑选Cost最小的执行计划。

由上可知,CBO中有两个依赖:统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。

从上述描述可知,CBO是优于RBO的,原因是RBO是一种只认规则,对数据不敏感的呆板的优化器,而在实际过程中,数据往往是有变化的,通过RBO生成的执行计划很有可能不是最优的。

事实上目前各大数据库和大数据计算引擎都倾向于使用CBO,例如从Oracle 10g开始,Oracle已经彻底放弃RBO,转而使用CBO;而Hive在0.14版本中也引入了CBO。

3.查询优化器执行过程

无论是RBO,还是CBO都包含了一系列优化规则,这些优化规则可以对关系表达式进行等价转换,常见的优化规则包含:

谓词下推

列裁剪

常量折叠

其他

在这些优化规则的基础上,就能对关系表达式做相应的等价转换,从而生成执行计划。下面将介绍RBO和CBO两种优化器的执行过程。

RBO

RBO的执行过程比较简单,主要包含两个步骤:

1)Transformation

遍历关系表达式,只要模式能够满足特定优化规则就进行转换。

2)Build Physical Plan

经过Step1之后就生成了一个逻辑执行计划,但这只是逻辑上可行,还需要将逻辑执行计划build成物理执行计划,即决定各个Operator的具体实现。如Join算子的具体实现选择BroadcastHashJoin还是SortMergeJoin。

CBO

CBO查询优化主要包含三个步骤:

1)Exploration

根据优化规则进行等价转换,生成等价关系表达式,此时原有关系表达式会被保留。

2)Build Physical Plan

决定各个Operator的具体实现。

3)Find Best Plan

根据统计信息计算各个执行计划的Cost,选择Cost最小的执行计划。

CBO实现有两种模型,即Volcano模型[1]和Cascades模型[2],其中Calcite使用的是Volcano模型,而Orca[3]使用的是Cascades模型。这两种模型的思想基本相同,不同点在于Cascades模型并不是先Explore、后Build,而是边Explore边Build,从而进一步裁剪掉一些执行计划。在这里就不展开了,对此感兴趣的同学可以看下相关的论文。

4.CBO框架Calcite简介

Apache Calcite 是一个独立于存储与执行的SQL优化引擎,广泛应用于开源大数据计算引擎中,如Flink、Drill、Hive、Kylin等。另外,MaxCompute也使用了Calcite作为优化器框架。Calcite的架构如下图所示:

feb9611c357692c6b2bb19af674a943474a04f5a

其中Operator Expressions 指的是关系表达式,一个关系表达式在Calcite中被表示为RelNode,往往以根节点代表整个查询树。Calcite中有两种方法生成RelNode:

通过Parser直接解析生成

从上述架构图可以看到,Calcite也提供了Parser用于SQL解析,直接使用Parser就能得到RelNode Tree。

通过Expressions Builder转换生成

不同系统语法有差异,所以Parser也可能不同。针对这种情况,Calcite提供了Expressions Builder来对抽象语法树(或其他数据结构)进行转换得到RelNode Tree。如Hive(某一种Data Processing System)使用的就是这种方法。

Query Optimizer 根据优化规则(Pluggable Rules)对Operator Expressions进行一系列的等价转换,生成不同的执行计划,最后选择代价最小的执行计划,其中代价计算时会用到Metadata Providers提供的统计信息。

事实上,Calcite提供了RBO和CBO两种优化方式,分别对应HepPlanner和VolcanoPlanner。对此,本文也不进行展开,后续有时间再详细介绍Calcite的具体实现。

5.总结

本文是对查询优化器的一个综述,介绍了查询优化器的分类、执行过程,以及优化器通用框架Calcite。

6.参考

 [1] The Volcano Optimizer Generator: Extensibility and Efficient Search

 [2] The Cascades Framework for Query Optimization

 [3] Orca: A Modular Query Optimizer Architecture for Big Data

 原文发布时间为:2018-08-31

本文作者:养码场

本文来自云栖社区合作伙伴“养码场”,了解相关信息可以关注“养码场”。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
相关文章
|
8天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
1天前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之如何SQL同步数据到Oracle数据库中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
SQL 关系型数据库 Java
实时计算 Flink版操作报错之在阿里云DataHub平台上执行SQL查询GitHub新增star仓库Top 3时不显示结果,是什么原因
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
1天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
19 2
|
2天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(3)——连接和嵌套查询
简简单单 My SQL 学习笔记(3)——连接和嵌套查询
|
2天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
|
3天前
|
SQL 存储 安全
SQL接口如何保护数据库免受未经授权的访问?
【5月更文挑战第21天】SQL接口如何保护数据库免受未经授权的访问?
17 3
|
5天前
|
SQL Oracle 关系型数据库
一些SQL数据库工具的介绍
【5月更文挑战第19天】一些SQL数据库工具的介绍
17 4
|
5天前
|
SQL 关系型数据库 MySQL
查询mysql版本sql - 蓝易云
执行这个命令后,MySQL将返回当前正在运行的版本信息。
42 0
|
7天前
|
缓存 关系型数据库 MySQL
如何优化MySQL数据库查询性能
MySQL是一款常用的关系型数据库,但在实际使用过程中,由于数据量增加和查询操作复杂度增加,会导致查询性能下降。本文将介绍一些优化MySQL数据库查询性能的方法。