SQL 查询优化器浅析

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

课程目录

SQL 查询优化器浅析

1. 大数据体系和SQL

1.1 大数据体系 - One SQL rules big data all

1.2 大数据体系和 SQL

1.3 SQL 的处理流程

1.3.1 Parser

String -> AST (abstract syntax tree)
$\checkmark$ 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等 token
$\checkmark$ 语法分析：将 token 组成 AST node，最终得到一个 AST
实现：递归下降 (ClickHouse)，Flex 和 Bison (PostgreSQL)，JavaCC (Flink), Antlr (Presto, Spark)

1.3.2 Analyzer和Logical Plan

Analyzer
$\checkmark$ 检查并绑定 Database, Table, Column 等元信息
$\checkmark \mathrm{SQL}$ 的合法性检查，比如 $\mathrm{min} / \mathrm{max} / \mathrm{avg}$ 的输入是数值
$\checkmark$ AST $\rightarrow$ Logical Plan
Logical Plan
$\checkmark$ 逻辑地描述 SQL 对应的分步骤计算操作
$\checkmark$ 计算操作: 算子 (operator)

1.3.3 查询优化

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

1.3.4 Physical Plan和Executor

Plan Fragment: 执行计划子树
$\checkmark$ 目标：最小化网络数据传输
$\checkmark$ 利用上数据的物理分布 (数据忞和性)
$\checkmark$ 增加 Shuffle 算子
Executor
$\checkmark$ 单机并行: cache, pipeline, SIMD
$\checkmark$ 多机并行: 一个 fragment 对应多个实例

1. 小结

One SQL rules big data all
SQL 需要依次经过 Parser, Analyzer, Optimizer 和 Executor 的处理
查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
查询优化器需要感知数据分布，充分利用数据的产和性
查询优化器按照最小化网络数据传輸的目标把逻辑计划拆分成多个物理计划片段

2. 常见的查询优化器

2.1 查询优化器的分类

RBO (Rule-based Optimizer)
$\checkmark$ 根据关系代数等价语义，重写查询
$\checkmark$ 基于启发式规则
$\checkmark$ 会访问表的元信息 (catalog)，不会涉及具体的表数据 (data)
CBO (Cost-based Optimizer)
$\checkmark$ 使用一个模型估算执行计划的代价，选择代价最小的执行计划

2.2 常见的查询优化器

1. RBO

2.2.1 RBO - 关系代数

运算符: Select $(\sigma)$ , Project $(\pi)$ , Join $(\bowtie)$ , Rename $(\rho)$ , Union $(U)$ 等
等价变换：结合律，交换律，传递性
$\checkmark$ Select: $\sigma_{P 1 \wedge P 2 \wedge \ldots P N}(R)=\sigma_{P 1}\left(\sigma_{P 2}\left(\ldots \sigma_{P N}(R)\right)\right)$ $\checkmark$ Join: $R \bowtie S=S \bowtie R, \quad(R \bowtie S) \bowtie T=R \bowtie(S \bowtie T)$
$\checkmark \ldots$

2.2.2 RBO - 优化原则

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

2.2.3 RBO - 优化策略

列裁剪
谓词下推
传递闭包

2. CBO

2.3 CBO 概念

使用一个模型估算执行计划的代价，选择代价最小的执行计划
$\checkmark$ 执行计划的代价等于所有算子的执行代价之和
$\checkmark$ 通过 RBO 得到 (所有) 可能的等价执行计划 \
算子代价: CPU，内存，磁盘 $IO$ ，网络 $IO$ 等代价
$\checkmark$ 和算子输入数据的统计信息有关: 输入、输出结果的行数，每行大小...
$\checkmark$ 叶子算子 Scan: 通过统计原始表数据得到
$\checkmark$ 中间算子: 根据一定的推导规则，从下层算子的统计信息推导得到
$\checkmark$ 和具体的算子类型，以及算子的物理实现有关
$\checkmark$ 例子: Spark Join 算子代价 $=$ weight * row_count $+(1.0$ - weight) * size

2.3.1 CBO 统计信息

原始表统计信息
$\checkmark$ 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
$\checkmark$ 列级别： min、 max、num nulls、num not nulls、 num distinct value(NDV)、 histogram 等
推导统计信息

2.3.1 CBO 统计信息的收集方式

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

2.3.1 CBO 统计信息的问题

假设列与列之间是独立的，列的值是均匀分布->这个假设经常与现实不符

2.3.2 CBO 执行计划枚举

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

2. 小结

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

3. 查询优化器的社区开源实践

3. 小结

主流的查询优化器都包含 RBO 和 CBO
Apache Calcite 是大数据领域很流行的查询优化器
Apache Calcite RBO 定义了许多优化规则，使用 pattern 匹配子树，执行等价变换
Apache Calcite CBO 基于 Volcano/Cascade 框架
Volcano/Cascade 的精髓: Memo、动态规划、剪枝

4. SQL 相关的前沿趋势

4.1 前沿趋势 - DATA + AI

Al4DB
$\checkmark$ 自配置
$\checkmark$ 智能调参 (OtterTune, QTune)
$\checkmark$ 负载预测/调度
$\checkmark$ 自诊断和自愈合：错误恢复和迁移
$\checkmark$ 自优化:
$\checkmark$ 统计信息估计 ( Learned cardinalities)
$\checkmark$ 代价估计
$\checkmark$ 学习型优化器 (IBM DB2LEO)
$\checkmark$ 索引/视图推荐
DB4Al
$\checkmark$ 内嵌人工智能算法 (MLSQL, SQLFlow)
$\checkmark$ 内嵌机器学习框架 (SparkML，Alink, dl-on-flink)

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