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

SQL Qptimizer.png

【课程重点：常见的查询优化器】

一、大数据体系

1. 体系中的 SQL

目标：通过SQL处理所有的大数据

消息队列主要是为了存储解耦和计算
分析引擎的部分提供SQL接口

2. SQL 处理流程

会经过四个组件的处理：Parser, Analyzer, Optimier, Executor

(1) Parser

String -> AST（抽象语法树）
- 词法分析：拆分字符串，得到关键等token
- 语法分析：组合 token 成 AST node
实现：递归下降，Flex和Bison，JavaCC，Antlr

(2) Analyer和逻辑执行计划

Analyer：检查绑定元信息、SQL合法性；AST -> Logical Plan
Logical Plan：逻辑地描述SQL是如何一步步查询和计算的，最终得到查询结果，树中的每个节点是一个算子，边是数据的流向。

算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。

(3) 查询优化

一般SQL越复杂，JOIN的表越多，数据量越大，优化查询就很有必要。

目标：找到一个正确且 执行代价最小 的物理执行计划。

(4) 物理执行计划和Executor

优化器的输出是一个分布式的物理执行计划

物理执行计划表示为一棵树，是一个优化后的逻辑计划，在分布式下会其进行拆分，每个节点执行其完整计划的一部分，其中拆分出来的子树称为 执行计划子树。

Plan Fragment：
- 目标：最小化网络数据传输
- 利用上数据的物理分布：例如节点尽量读取本地的数据
- 增加Shuffle算子：一边做发送，一边做接收
Executor:
- 单机并行：cache，pipeline，SIMD
- 多机并行：一个fragment对应多个实例

sqlContext的解析过程：

SQL语句经过SqlParse解析成Unresolved LogicalPlan。

使用analyzer结合数据字典（cataqlog)进行绑定，生成resolved LogicalPlan。

使用optimizer对resolved LogicalPlan进行优化，生成optimized LogicalPlan。

使用SparkPlan将LogicalPlan转换成PhysicalPlan。

使用prepareForExecution()将PhysicalPlan转换成可执行物理计划。

使用execute()执行物理计划。

生成SchemaRDD。

二、常见的查询优化器【重点】

1. 分类

(1) 按照遍历树的顺序分类：

Top-down Optimizer 从上到下：从目标输出开始，寻找完整的最优执行计划
Bottom-up Optimizer 从下到上：从零开始寻找

(2) 根据优化方法分类：

Rule-based Optimizer (RBO)：根据 关系代数 等价语义重写查询
Cost-based Optimizer (CBO)：使用模型估算执行计划的代价，选择代价最小的计划

2. RBO

优化原则

I/O：读更少的数据且读取速度更快
网络：传输数据更少且速度更快
CPU和内存：处理的数据更少且速度更快

裁剪规则（尽可能减少数据，以便减少算子处理量）

列裁剪：查询中对应算子用不到的列，在运行和计算中无需保留，减少资源占用（例如从扫描整张表优化为只扫描某些列）
谓词下推：【谓词：WHERE条件中的表达式】在某些场景下查询需要过滤数据，尽量将条件过滤提前，过滤一些不必要的数据，减少查询时间（例如一个Filter对JOIN的结果没有影响，因此可以在执行JOIN之前就先过滤一些内容）
传递闭包：【表达式的等价关系 + 过滤条件 → 新的过滤条件】例如在JOIN条件之前有其中一个表的条件过滤，那么在另一张表可以加入新的条件过滤。
Runtime Filter：【JOIN右表 Runtime Filter Builder 数据信息传递给 JOIN左表 Runtime Filter】执行时才能使用的过滤器。对于一个JOIN，如果能在查询端提早过滤，那么在运行JOIN时就很快，因为无需计算很多hashcode。
- min-max：经过过滤器后知道JOIN右表的max和min即数据范围，那么在运行时传递给左表进行数据范围过滤
  - 缺点：min-max区间是连续的，范围需要紧密，否则扫描数据量很大
- in-list：用一个集合记录包含的范围。[1-100, 10000] 离散数据
  - 缺点：若数据很多，则list很大，网络传输开销大
- bloom：大小不随集合大小改变。构建JOIN右表的一个Hash表同时构建出bloom filter，查询JOIN左表的时候，如果bloom filter里没有这个数据，则无需查询出来。

注：“Runtime Filter 在什么情况下会造成性能回退？”

局限性

无法解决多表连接问题
无法确定和选择最优的分布式 Join/Aggregate 执行方式：例如无法确定索引扫描还是全表扫描

3. CBO

算子代价包括CPU、内存、磁盘I/O、网络I/O等，具体包括计算算子输入的统计信息（输入、输出结果的行数，每行大小...）和算子的物理实现等

统计信息

原始表统计信息：表或分区级别（行数、平均大小、表占用的字节）、列级别（min、max、num nulls等）
推导统计信息：选择率（对某过滤条件，查询能返回数据的比例）、基数（算子需要处理的行数）

(1) 收集方式：

DDL里指定所需统计信息，数据库在数据写入时收集或更新信息【会影响实时更新数据的速率】
手动执行 explain analyze statement【手动的，不能及时获得更新信息】
动态采样： select count(*) from ...【通过Query来实现】

(2) 过滤器选择率规则（假设列和列之间是独立的，列的值为均匀分布）：AND、OR、NOT、等于、小于条件。

假设经常与现实违背 => 用户指定或者数据库自动识别相关联的列
数据不是均匀分布 => 使用直方图处理

执行计划枚举

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

例如有三个表连接的操作，通过求解两个表连接的代价，动态地得到最小的三个表执行计划代价。

三、社区开源实践

Apache Clacite 是大数据领域很流行的查询优化器
Apache Clacite RBO 是基于 Volcano/Cascade 框架的，其定义了许多优化规则，例如 HepPlannner 使用了pattern匹配表达式子树
- 遍历表达子树去匹配规则，然后进行等价替换得到新的表达式子树，不断地循环，直到所有的路遍历完无法在子树中进行匹配
Volcano/Cascade 精髓：Memo（备忘录，存储候选执行计划；Group：等价计划集合）、动态规划、剪枝（减少搜索空间）

四、前沿趋势

引擎架构的进化：存储计算分离、一体化（HTAP，HSAP，HTSAP）【解耦、池化、一体化】
Cloud：云原生、serverless（弹性计算）
湖仓一体（数据仓库，数据湖），联邦查询
DATA + AI：AI4DB、DB4AI
- AI4DB：自配置（智能调参如OtterTune、QTune；负载预测/调度）、自诊断和自愈合（错误恢复及迁移）、自优化（统计信息估计[Learned cardinalities]、代价估计、学习型优化器[IBM DB2 LEO]、索引/视图推荐）
- DB4AI：内嵌人工智能算法（MLSQL, SQLFlow）、内嵌机器学习框架（SparkML, Alink, dl-on-flink）

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