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

完成了在青训营的第一节课的学习，我印象最深的还是大数据整个体系的基本组成，脑海中也比较清晰地形成了对大数据体系的初步了解，而我们需要重点学习的也是“分析引擎”这一模块。大数据体系具体展示如下图： QQ图片20220725154526.jpg

一.本堂课程的重点内容：

SQL 查询优化器。

二.详细知识点介绍：

第一节：大数据中的SQL

1.SQL的基本运算和关系代数运算

（1）SQL的基本运算和关系代数运算

一共有六大基本运算：1.select（选择）2.Project（投影）3.Union（并）4.set difference（差）5.Cartesian product（笛卡尔积）6.Rename（重命名）
示例如下图：

SELECT * FROM Student WHERE Sno='95001'

方法一：

SELECT Sname,Sdept FROM Student WHERE Sno='95001'

方法二：

SELECT Sname,Sdept FROM Student WHERE EXISTS(SELECT * FROM Student SX WHERE Student.Sno=SX.Sno And SX.Sno='95001')

2.SQL流行的原因

有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库。
有 JDBC、ODBC 之类和各种数据库交互的标准接口。
有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人。
多个大数据计算引擎都支持 SQL 作为更高抽象层次的计算入口。
```
  MapReduce -> Hive SQL
  Spark -> Spark SQL
  Flink -> Flink SQL
```

3.SQL的处理流

SQL的处理流程如下图所示:

1. Parser
  
  a. 把文本变成抽象语法树结构（AST） b. 词法分析阶段（拆分字符串，提取关键字，字符串，数值等）和语法分析阶段（把词条按照定义的语法规则组装成抽象语法树结构）
1. Analyzer
  1. 访问库/表元信息并绑定
  2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确
  3. 将 AST 转换成逻辑计划树
    
    逻辑计划树：树中的每个结点叫一个算子，而算子定义了某种逻辑的计算操作，但并没有指定实际的算法。
1. Optimizer（查询优化）
  1. SQL是声明语言，只描述做什么，而不说明怎么做。
  2. 查询优化的目标：为SQL找到一个正确且执行代价小的执行计划。
  3. 查询优化器是数据库的大脑，是最复杂的模块。
  4. 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段
  5. SQL越复杂，join的表越多，数据量大，查询优化的意义就越大。
1. Executor
  
  Executor 按照物理执行计划扫描和处理数据，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD）。

第二节：常见的查询优化器

（1）Top-down Optimizer

从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划

例子: Volcano/Cascade，SQLServer

（2）Bottom-up Optimizer

从零开始，由下往上遍历计划树，找到完整的执行计划

例子:System R,PostgreSQL，IBM DB2

（3）RBO(Rule-based Optimizer)

基于关系代数等价规则对逻辑计划进行变换
$SNZM`ZAGK0ZGWR{MP3_HCSL.png$
实现如下：

Pattern：定义了特定结构的 Operator 子树（结构）
Rule：定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换）
 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule

RBO优化规则：a.列剪裁 b.谓词下推 c.传递闭包 d.Runtime Filter（min-max filter,in-list filter,bloom filter）e.Join消除 f.谓词合并

优势：实现简单，优化速度快。

局限性：a.无法解决多表连接问题 b.无法确定和选择最优的分布式 Join/Aggregate 执行方式 几种优化的例子如下：

1。列剪裁：

2.谓词下推：

3.传递闭包：

4.Runtime Filter

（4）CBO(Cost-based Optimizer)

1.概念：使用一个模型估算执行计划的代价，选择代价最小的执行计划，执行计划的代价等于所有算子的执行代价之和。通过RBO得到(所有)可能的等价执行计划。

2.算子代价:CPU，内存，磁盘I/O，网络I/O等代价。和算子的统计信息有关，比如输入、输出结果的行数，每行大小。和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）。

  叶子算子 scan：通过统计原始表数据得到。

  中间算子：根据一定的推导规则，从下层算子的统计信息推导得到。

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

4.流程：

统计信息：

基表统计信息

表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等。

列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等。

推导统计信息

选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据。

基数（cardinality） ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数。
收集方式：
在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息。

手动执行解释分析语句，触发数据库收集或者更新统计信息。

动态采样。
推导规则

执行计划枚举

动态规划:

Hash Join 和SortMerge Join两种连接方式，选出最低cost，将问题扩大，再进行三表连接，分别求出两种连接方式的代价，保留最好的，选出总的最优执行计划。

小结：主流RBO实现一般都有几百条基于经验归纳得到的优化规则RBO实现简单，优化速度快
RBO不保证得到最优的执行计划
CBO使用代价模型和统计信息估算执行计划的代价
CBO使用贪心或者动态规划算法寻找最优执行计划
大数据场景下CBO对查询性能非常重要

第三节：社区开源实践

（1）Apache Calcite

One size fits all:统一的SQL查询引擎
模块化，插件化，稳定可靠
支持异构数据模型

√关系型

√半结构化

√流式

√地理空间数据

内置 RBO和CBO

（2）Calcite RBO

HepPlanner

√优化规则(Rule)

Pattern:匹配表达式子树

等价变换:得到新的表达式

√内置有100+优化规则

√四种匹配规则

ARBITRARY/DEPTH_FIRST:深度优先

TOP_DOWN:拓扑顺序

BOTTOM_UP:与TOP_DOWN相反

√遍历所有的rule，直到没有rule可以被触发

√优化速度快，实现简单，但是不保证最优

VolcanoPlanner

√基于Volcano/Cascade框架 √成本最优假设

√Memo:存储候选执行计划

本质:AND/OR graph

共享子树减少内存开销

Group:等价计划集合

√Top-down动态规划搜索

√Group winner:目前的最优计划

（3）小结：主流的查询优化器都包含RBO和CBO

Apache Calcite是大数据领域很流行的查询优化器

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

Apache Calcite CBO基于Volcano/Cascade框架

Volcano/Cascade的精髓:Memo、动态规划、剪枝

第四节：SQL优化器前沿趋势

1.DATA +AI

(1)AI4DB 自配置

√智能调参(OtterTune,QTune)

√负载预测/调度

自诊断和自愈合:错误恢复和迁移

自优化:

√统计信息估计(Learned cardinalities )

√代价估计

√学习型优化器(BM DB2LEO)

√索引/视图推荐

（2）DB4AI

√内嵌人工智能算法(MLSQL,SQLFlow)

√内嵌机器学习框架(SparkML,Alink,dl-on-flink )

三.课程总结：

经过第一节课的学习，我了解了大数据体系，学习到SQL的查询优化器的分类和各自的优缺点，同时也知道了SQL查询优化器的前沿趋势。

SQL Optimizer 解析|青训营

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