SQL 优化器｜青训营笔记这是我参与「第四届青训营」笔记创作活动的第1天，笔记发布的比较晚。主要是上课中的记录以及

这是我参与「第四届青训营」笔记创作活动的第1天，笔记发布的比较晚。主要是上课中的记录以及根据学员手册的内容，还有一些自己的理解。

1. SQL 的处理流程

flowchart LR
  Input--SQL--> B[Parser] 
  B--AST--> C[Analyzer] 
  C--Logical Plan-->D[Optimizer] 
  D --Physical Plan--> E[Executor]

Parser

将 String 通过词法分析进行拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token。再通过语法分析将token 组成 AST node，最终得到一个 AST。

实现的方法有：递归下降、Flex 和 Bsion，JavaCC，Antlr

AST 即语法树，源代码语法结构的一种抽象表示，具体生成如图
```
flowchart TB

  A[SelectStmt]
  A --> BSelectList
  A --> FromClause
  A --> B[WhereClause]
  A --> GroupClause
  A --> OrderClause
  B --> BetweenPredicate
  B --> InPredicate
  B --> LikePredicate
  B --> ...
```
Analyzer
- 检查元数据信息如：Database、Table、Colum 是否正确
- 检查 SQL 的合法性如：min/max/avg等输入是可计算的数值
- 将 AST 转换成一个逻辑执行计划即 Logical Plan
Logical Plan 在逻辑上描述 SQL 对应的分步计算操作，一种比较经典的 Logical Plan 为 Left-Deep Tree
Optimizer

即查询优化器，目标是找到一个正确且执行代价最小的物理执行计划，
Executor

最后得到的 Physical Plan 交给 Executor，负责实际执行查询计算

2. 常见查询优化器

可以按照不同的角度来分类，按照遍历顺序可分为 Top-Down Optimizer 和 Bottom-Up Optimizer：

Top-down Optimizer

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

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

根据优化方法可以分为 RBO（Rule-base Optimizer） 和 CBO（Cost-base Optimizer） 。

2.1 RBO

根据关系代数等价语义，重写查询
基于启发式规则，即根据经验来
会访问表的元信息(catalog)，不会涉及具体的表数据(data)

RBO 的优化原则如下：

Read data less and faster(I/O)
Transfer data less and faster(Network)
Process data less and faster(CPU & Memory)

优化规则主要有以下几种：

通过一个 SQL 语句以来理解：

SELECT pv.siteId, user.name
FROM pv JOIN user
ON pv.siteId = user.siteId AND pv.userId = user.id
WHERE user.siteId > 123;

列裁剪

读取表的时候只读取表中需要用到的列，减少不需要的列对 IO 和内存的占用

如上面的 SQL，只用到了 pv 表中的 siteId、userId 和 user 表中的 name、siteId、id，不需要整表读出，只需要表中读取这几列的数据即可，对于列式存储的表来说，读取速度会有明显的提升
谓词下推

这里的谓词指的是 SQL 中的一些表达式，比如 WHERE 后面的 user.siteId > 123 就可以理解为是谓词，下推，也就是提前将这样的条件往下传递给上一步的结点。

原逻辑计划是在 JOIN 之后才做 FILTER，也就是 JOIN 之后再做 WHERE 的过滤。如果提早在 SCAN 之后进行过滤，也就是在 SCAN 之后 FILTER，就可以在 JOIN 时处理的数据更少
传递闭包

也就是通过关系代数的传递性，可以推出更多的一些过滤条件。比如说，SQL 中的 ON pv.siteId=user.siteId 和 WHERE user.siteId > 123 可以通过传递性推出 pv.siteId > 123 ，可以在 SCAN 后再加一个 FILTER
Runtime Filter

也是一种传递性，Runtime Filter 主要有两个结点之间在运行时通信（Runtime Filter Builder 和 Runtime Filter），传递信息做到更精细化的 Filter。

比如说，在 FiltersitedId>123 之后，连接 Runtime Filter Builder，得到的结果是 siteId>500，那么可以提前把这个信息传递给 Runtime Filter，在另一个 Filter 之前过滤。常用的方法有 max-min，in-list，bloom filter等

RBO 实现一般有几百条基于经验归纳得到的优化规则。

优点：实现简单，优化速度快

缺点也很明显：不保证得到最优的执行计划

2.2 CBO

使用一个模型估算执行的代价，选择代价最小的执行计划。

执行计划的代价等于所有算子的执行代价之和，通过 RBO 得到可能的等价执行计划。这里的代价算子指的是：CPU，内存，磁盘 I/O，网络 I/O 等

flowchart LR
  
  原始表统计信息+推到规则 ==> 算子代价计算规则 ==> 执行计划代价 ==> 枚举

统计信息

统计信息有两种：
- 原始表统计信息
  
  表或者分区级别：行数、行平均大小、表在磁盘中占了多少字节等
  
  列级别：min、max、num nulls、num not nulls、num distinct value，histogram（直方图）等
- 推到统计信息
  
  选择率：对于某一个过滤条件，查询会从表中返回多大比例的数据
  
  基数：在查询计划中常指算子需要处理的行数。准确的 cardinality，远比代价模型本身重要
统计信息的收集方式：
- 在 DDL 里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
- 手动执行 explain analyze statement，触发数据库收集或者更新统计信息
- 动态采样
统计信息的推到规则，需要做一个强假设：列与列之间是独立的，列的值是均匀分布的。这样就可以通过统计信息做一些代价推导：

Filter Selectivity：
- AND 条件：fs(a AND b) = fs(a) * fs(b)
- OR 条件：fs(a OR b) = fs(a) + fs(b) - fs(a) * fs(b)
- ....
但是这个假设常常与现实不符，列于列之间往往可能是有联系的，数值的分布也不一定是均匀分布的。
执行计划的枚举
- 使用动态规划枚举所有执行计划，选出执行代价最小的执行计划
优化效果

CBO 在一些情况下的优化效果，可能与 RBO 的优化效果不相上下，但在一些情况下可能可以远胜与 RBO：

在大数据场景下，CBO 对查询性能非常重要

SQL 优化器 ｜ 青训营笔记

1. SQL 的处理流程

2. 常见查询优化器

2.1 RBO

2.2 CBO

SQL 优化器｜青训营笔记