SQL 查询优化器浅析｜青训营笔记

二个二个二
295 阅读7分钟

这是我参与「第四届青训营 」笔记创作活动的第 2 天。

一、笔记内容

  1. 大数据体系和 SQL；
  2. 常见的查询优化器；
  3. 查询优化器的社区开源实践；
  4. SQL 相关的前沿趋势。

二、大数据体系和 SQL

1.大数据体系中的SQL

SQL 与大数据体系中的分析引擎部分有密切关系。分析引擎的各种实现工具，如Hive、Flink等，都涉及数据查询处理，此时SQL的优化对系统的性能提升有至关重要的作用。

graph TD
大数据体系 --> 权限管控
权限管控 --Apache Range--> 集群创建
权限管控 --GDRP--> 集群创建
大数据体系 --> 数据开发
数据开发 --Airflow--> 集群创建
数据开发 --DAG--> 集群创建
大数据体系 ---> 基础设施
基础设施--ECS--> 日志查询
基础设施--存储--> 日志查询
基础设施--VPC--> 日志查询
基础设施--NSQ--> 日志查询
大数据体系 ----> 资源调度
资源调度 --YARN--> 用户管理
资源调度 --K8S--> 用户管理
资源调度 --Kafka--> 用户管理
大数据体系 ------> 存储系统
存储系统 --HDFS--> 监控报警
存储系统 --HBase--> 监控报警
存储系统 --NAS--> 监控报警
存储系统 --Object Store--> 监控报警
存储系统 --数据湖--> 监控报警
存储系统 --Pulsar--> 监控报警
大数据体系 -------> 业务应用
业务应用 --BI报表--> 管控运维
业务应用 --数据挖掘--> 管控运维
业务应用 --精准推荐--> 管控运维
业务应用 --营销分析--> 管控运维
大数据体系 ---------> 分析引擎
分析引擎 --> 批式分析
批式分析 --Spark--> 集群管理/服务管理
批式分析 --Hive--> 集群管理/服务管理
批式分析 --MR--> 集群管理/服务管理
分析引擎 --> 实时分析
实时分析 --Flink--> 集群管理/服务管理
分析引擎 --> 交互分析
交互分析 --Presto--> 集群管理/服务管理
交互分析 --ClickHouse--> 集群管理/服务管理
交互分析 --Doris--> 集群管理/服务管理
分析引擎 --> 消息队列
消息队列 --Kafka--> 集群管理/服务管理
消息队列 --Pulsar--> 集群管理/服务管理
消息队列 --NSQ--> 集群管理/服务管理

2.SQL的处理流程

image.png

1.Parser

  • 文本 --> AST(抽象语法树结构)

  • 词法分析阶段 --> 拆分字符串，提取关键字，字符串，数值等；

    语法分析阶段 --> 把词条按照定义的语法规则组装成AST。

实现：递归下降(ClickHouse)、Flex、Bison(PostgreSQL)、javaCC(Flick)、Antlr(Presto,Spark)

2.Analyzer

1.访问库/表元信息并绑定；

2.判断 SQL 是否合理；

3.AST --> Logical Plan(逻辑计划树)

3.AST 和 Logical Plan 逻辑计划树

这里我把AST和逻辑计划树放在一起总结，是因为我觉得这样比较好理解。

graph TD
select语句 --> SELECT的字段
select语句 --> FROM的表
select语句 --> WHERE的条件
WHERE的条件 --> Between
WHERE的条件 --> In
WHERE的条件 --> Like
WHERE的条件 --> ...
select语句 --> GROUP的字段
select语句 --> ORDER的字段

上图简单表达了一个SQL查询语句的结构，其实它就是一个AST，将文本的SQL转换为其结构表达图，我们把它叫做抽象语法树。 看到这里，其实大家应该考虑一个问题，它们是同时执行还是按一定的顺序执行呢？这对后文的理解比较重要。

SQL的select查询语句是按照一定的顺序执行的！！

SQL查询语句的执行顺序一般为：

 select		5
         ..			
 from		1
         ..
 where		2
         ..
 group by	3
         ..
 having		4
         ..
 order by	6
         ..

下列一个SQL查询语句：

SELECT 
    country.name, SUM(weblog.bytes) as total   
FROM 
    country
    inner join geoip on country.id = geoip.country_id
    inner join weblog on geoip.host = weblog.host
WHERE 
    weblog.reply = "200" and weblog.host is not null
GROUP BY 
    country.name
ORDER BY
    total
LIMIT 10;

其实我们可以从SQL是执行顺序来理解 Logical Plan，让我们对照下图从下往上，从左往右看 。

1.在执行SQL语句时，首先执行FROM语句，此时扫描(SCAN)country表和geoip表，并对它们按条件进行连接。至于weblog表，我们可以发现谓词下推的踪迹，因为原本应该是先连表再过滤，但weblog表和geoip表的连接明显推迟了。

2.WHERE经过谓词下推已经提前完成了。

3.group by 聚合操作，一般是根据字段值进行分组。

4.having过滤语句此SQL语句中没有出现，但它一定是在聚合之后才可以操作。

5.select 语句在此图中没有体现，但想象一下，没有查出来数据，如何排序。

6.order by语句，对查询出的数据进行排序操作，提高数据的可读性。

7.limit语句，成百上千条数据在显示时，肯定要进行分页，这个语句控制显示器每页显示的记录数。（limit没有给它排序，是因为它是MySQL特有的语句）

image.png

4.Optimizer 查询优化

查询优化的原因 --> SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做。

查询优化的目标 --> 为 SQL 找到一个正确的且执行代价最小的执行计划。

查询优化的不确定性 --> 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP(非确定性)的，很难找到一个最优解。

查询优化的意义 --> 一般 SQL 越复杂，Join 的表越多，数据量越大，使用不同执行方式来大幅度提高性能的可能性就越大。

5.Physical Plan 物理执行计划

image.png

优化器的输出 --> 分布式的物理执行计划。

Physical Plan 的目标 --> 最小化网络数据传输。即在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生成 PlanFragment 树。

Physical Plan 的实现 --> 利用数据上的物理分布(数据亲和性)。一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。

Plan 分布式化的方法 --> 增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。

6.Executor 执行器

Executor 按照物理执行计划扫描和处理数据，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD），分为两种：

  • 单机并行：cache，pipeline，SIMD
  • 多机并行：一个fragment对应多个实例

三、常见的查询优化器

在介绍逻辑计划树时，我们提到了谓词下推，谓词下推属于查询优化器的一种实现方式，下面我们就对查询优化器的相关知识进行总结。

1.查询优化器的分类

graph TD
查询优化器的分类 --> Top-down
查询优化器的分类 --> Bottom-up
查询优化器的分类 --> RBO
查询优化器的分类 --> CBO

2.RBO

Rule-based Optimzer(RBO) ：根据关系代数等价语义，重写查询；基于启发式规则；会访问表的元信息，不会涉及具体表数据。

1.优化的基础：关系代数

  • 运算符 --> select，project，join，Renam，Union等;
  • 等价变换 --> 结合律、交换律、传递性。

2.优化的原则：I/O、Network、CPU & Memory

  • I/O --> Read data less and faster.
  • Network --> Transfer data less and faster.
  • CPU & Memory --> Process data less and faster.

3.优化的方法：列裁剪、谓词下推、传递闭包、Runtime Filter

主流RBO实现一般都有几百条基于经验所得到的优化规则。

  • 列裁剪 --> 裁剪搜索树中不需要的列表信息。
  • 谓词下推 --> JOIN与FILTER交换，将可以提前的FILTER提前。
  • 传递闭包 --> 通过关系代数推算FILTER信息，提前过滤。
  • Runtime Filter --> 用min-max，in-list bloom filter等减小搜索范围。

4.RBO的优缺点

  • 优点：实现简单，优化速度快；
  • 缺点：不保证得到最优的执行计划。

3.CBO

Cost-based Optimzer(CBO) ：使用一个模型估算执行计划的代价，选择代价最小的执行计划。

image.png

1. 原始表统计信息 + 推导规则

  • 原始表表统计信息

    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等。
    • 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等。

  • 推导统计信息

    • 选择率（selectivity） --> 对于某一个过滤条件，查询会从表中返回多大比例的数据。
    • 基数（cardinality） --> 基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数。

  • 统计信息的收集方式

    • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息；
    • 手动执行 explain analyze statement，触发数据库收集或者更新统计信息；
    • 动态采样。

2. 算子代价计算规则

算子代价：CPU、内存、磁盘I/O、网络I/O等。

  • 和算子的统计信息有关，比如输入、输出结果的行数，每行大小等。

    • 叶子算子 scan --> 通过统计原始表数据得；
    • 中间算子 --> 根据一定的推导规则，从下层算子的统计信息推导得到。

  • 和具体的算子类型，以及算子的物理实现有关。

3.计算执行计划代价

  • 分而治之，执行计划的代价等于所有算子的执行代价之和。
  • 通过 RBO 得到（所有）可能的等价执行计划（非原地替换）。

4.执行计划枚举

通常使用 贪心算法 或 动态规划 选出最优的执行计划。

四、查询优化器的社区开源实践

主流的查询优化器都包含RBO和CBO。

1.Apache Calcite

大数据领域很流行的查询优化器。

2.Calcite RBO

定义了许多优化规则，使用 pattern 匹配子树，执行等价变换。

3.Calcite CBO

基于Volcano/Cascade框架。（Volcano/Cascad的精髓：Memo、动态规划、剪枝）

五、SQL 相关的前沿趋势

  • 存储计算分离、一体化(HSAP, HTAP, HTSAP)。
  • Cloud Native、 Serverless。
  • 湖仓一体（数据仓库，数据湖）、联邦查询。
  • DATA + AI(AI4DB、DB4AI)

参考文章: SQL查询优化器浅析 周东炎ppt

参考文章：juejin.cn/post/712275…

avatar
文章
阅读
粉丝