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

Lect01 SQL Optimizer 解析

01. SQL的处理流程

1.1 Parser

String -> AST(抽象语法树 abstract syntax tree)
- 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
- 语法分析：将token组成AST node
实现：递归下降、Flex (词法分析)+ Bison (语法分析)(PostgreSQL)、JavaCC(Flink)、

1.2 Analyser和Logical Plan

Analyzer：输入AST，输出Logical Plan
Logical Plan：逻辑地描述SQL对应的分步骤计算操作
- 计算操作：算子operator（和具体的算法无关，只描述对应的操作。例：排序算子，具体实现可以是快排堆排......）

1.3 查询优化

查询优化器是数据库的大脑，最复杂的模块
目标：找到一个正确且执行代价最小的物理执行计划

1.4 Physical Plan Executor

Plan Fragment：执行计划子树
- 目标：最小化网络数据传输，避免通过网络读数据
- 实现目标：把逻辑计划拆分成多个物理计划片段
- 数据亲和性：利用上数据的物理分布，尽量访问本地的数据
- Shuffle算子：一边发送一边接受
Executor（希望充分利用并行的机制）
- 单机并行
- 多机并行：一个fragment对应多个实例

02.常见的查询优化器

2.1 分类方法：

根据遍历树的顺序分类
- Top-down Optimizer
  - 从目标输出开始，由上往下遍历计划树，找到完整的最优计划树
- Bottom-up Optimizer
  - Syetem R，最早的
根据优化的方法分类
- RBO：Rule-based Optimizer，基于启发式规则的查询优化器
  - 启发式规则：由经验得出
  - 根据关系代数等价语义，重写查询
  - 会访问表的元信息catalog，不会涉及具体的表数据data
- CBO：Cost-based Optimizer，基于代价的查询优化器
  - 使用一个模型估算执行计划的代价，选择代价最小的执行计划

2.2 RBO基于启发式规则的查询优化器

2.2.1 关系代数

重要的关系运算符

专门的关系运算符

运算符	含义	英文
σ	选择	Selection
π	投影	Projection
⋈	链接	Join
∪	并	Union
−	差	Difference
∩	交	Intersection
×	笛卡尔积	Cartesian Product

广义笛卡尔积（Extended Cartesian Product）select * from A,B

两个无数分别为 n 目和 m 目的关系 R 和 S 的笛卡尔积是一个 (n+m) 列的元组的集合。组的前 n 列是关系 R 的一个元组，后 m 列是关系 S 的一个元组，记作 R × S，定义如下：

R×S= \left\{\begin{matrix}t|t=<(t^n,t^m)∧t^n∈R∧t^m∈S\end{matrix}\right\}

其中 $(t^n,t^m)$ 表示元素 $t^n$ 和 $t^m$ 拼接成的一个元组

投影（Projection）select distinct A from R

投影运算是从关系的垂直方向进行运算，在关系 R 中选出若干属性列 A 组成新的关系，记作 $π_A(R)$ ，其形式如下：

π_A(R)=\left\{\begin{matrix}t[A]|t∈R\end{matrix}\right\}

选择（Selection）select A from R where ....

选择运算是从关系的水平方向进行运算，是从关系 R 中选择满足给定条件的元组，记作 $σ_F(R)$ ，其形式如下：

σ_F(R)=\left\{\begin{matrix}t|t∈R∧F(t)=True\end{matrix}\right\}

2.2.2 优化原则

IO
Network
CPU & Memory

四种优化规则：

列裁剪：对于不需要的列删掉，减少IO操作
谓词下推：提前过滤，再进行之后操作
传递闭包：根据一些表达式的等价关系+过滤条件=新的过滤条件
Runtime Filter：min-max，in-list，bloom filter

目的：读取、处理的数据量更少，执行计划优化的好，执行效率更高

缺点：不保证得到最优的执行计划

2.3 CBO基于代价的查询优化器

思想：使用一个模型估算执行计划的代价，选择代价最小的执行计划
算子代价：CPU，内存，磁盘IO，网络IO等等

CBO执行过程：

graph LR
    统计信息+推导规则 --> 计算算子代价 --> 计算执行计划代价 --> 执行计划枚举

2.3.1 统计信息

原始表统计信息（给叶子算子用）
- 级别分类：
  - 表或者分区级别
  - 列级别
推导统计信息
- 选择率selectivity：对于某一个过滤条件，查询
- 基数cardinality：在查询计划中常指---算子需要处理的行数

统计信息的收集方式

数据库在表定义DDL中指定要收集的统计信息，数据库会在数据写入时
- 缺点：实时更新，会影响实施插入的效率
手动执行explain analyse statement，触发数据库收集或者更新统计信息
- 缺点：手动，统计信息可能比较旧。
动态采样：通过简单的query实现

2.3.2 执行计划枚举

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

查询优化器基础 ｜ 青训营笔记