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

一、大数据体系和 SQL

大数据体系中的SQL

全景

（本次青训营会从分析引擎展开）

SQL为什么流行

语言相对简单，适合数据分析师、数据挖掘师
很多系统支持接口
大数据处理事实标准的接口

SQL的处理流程

经过四个组件处理：Parser、Analyzer、Optimizer、Executor。

Parser解析器
- SQL语言-->AST（抽象语法树）
- 编译过程：
  - 词法分析：拆分字符串，提取关键字、字符串、数值、运算符等（token）
  - 语法分析：把词条(token)按照定义的语法规则组装成抽象语法树结构(AST)
- 实现方法：
  - 递归下降（ClickHouse）
  - Flex 和 Bison（PostgreSQL）
  - JavaCC （Flink）
  - Antlr（Presto, Spark）
- AST：SelectStmt
  - Select Clause
  - From Clause
  - Where Clause
    - Between Predicate
    - In Predicate
    - Like Predicate
    - ...
  - Group Clause
  - Order Clause
Analyzer
- AST-->Logical Plan
- 过程：
  - 访问库/表元信息并绑定
  - 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确 （合法性检查）
  - 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）
- Logical Plan
  - 逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划
  - 树中每个节点（node）是一个算子（node），定义了对数据集合的计算操作
    - 过滤
    - 排序
    - 聚合
    - 连接
  - 边代表了数据的流向，从孩子节点流向父节点。

（这是一个 Left-deep tree，join 右边必须是 scan）

Optimizer
- Logical Plan-->Physical Plan
- 目标：为 SQL 找到一个正确的且执行代价最小的执行计划
- 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP 的
- 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大
Executor
- Plan Fragment：执行计划子树，由优化后的 Logical Plan（即 Physical Plan）拆分而来
  - 根据 Plan 的结点（node）拆分，每个结点（？子树）是完整执行计划的一部分
  - 目标：最小化每个结点之间的网络数据传输
  - 数据亲和性：利用数据的物理分布（尽量调用本地的而非远程的，减少网络开销）
  - 每个结点之间由 shuffle 算子连接，一边发送一边接收
- 每个结点增加一个 executor，用来执行 Plan Fragment
  - 单机并行：CPU流水线，乱序执行，cache，SIMD（单指令多数据）
  - 多机并行：一个 Fragment 对应多个实例

（F#1 在 Node#1 和 Node#2 都有实例，读取同一张表 weblog 的不同数据。F#4 到 F#5 是跨结点网络传输。层层向上汇集到根节点 F#8 将结果发送给客户端。）

二、常见的查询优化器

查询优化器分类

Top-down Optimizer
- 从目标输出开始，由上往下遍历计划树，找出完整的最优执行计划
- Eg: Volcano/Cascade, SQLServer
Bottom-up Optimizer
- 从零开始，由下往上遍历计划树，找出完整的执行计划
- System R, PostgreSQL, IBM DB2

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

RBO

RBO - 关系代数

运算符：
- Select (σ)
- Project (π)
- Join (⋈)
- Rename (ρ)
- Union (∪)
等价变换
- 结合律
- 交换律
- 传递性

RBO - 优化原则

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

举个例子：

优化前的逻辑计划：

RBO - 列裁剪

只查找需要的列，表中其他列裁剪掉。

EG：SCAN pv(siteId, userId) SCAN user(id, siteId, name)

RBO - 谓词下推

将 where 语句的限制条件推到 join 下面。减少后续算子处理的数据量。

RBO - 传递闭包

因为表达式的等价关系，可以将过滤条件传递。

EG：因为 pv.siteId = user.siteId，user.siteId > 123，所以 pv.siteId > 123。

RBO - Runtime Filter

将 join 右边优化后的输入构建一个哈希表，根据 join key 得到集合特性，在运行时传到 join 左边，在谓词过滤之前用集合特性过滤一遍。

特性：

最值（min-max）

缺点是，如果范围不紧密，过滤效果不好。
集合（in list）

RBO - 小结

主流RBO实现一般都有几百条基于经验归纳得到的优化规则
优点:实现简单，优化速度快
缺点:不保证得到最优的执行计划
- 单表扫描:索引扫描（随机I/O） vs.全表扫描（顺序1/O）
  - 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
- Join的实现: Hash Join vs. SortMerge Join
- 两表Hash Join：用小表构建哈希表——如何识别小表?
- 多表Join:
  - 哪种连接顺序是最优的?
  - 是否要对每种组合都探索?
    - N个表连接，仅仅是left- deep tree就有差不多N!种连接顺序
    - e.g.N= 10->总共3, 628, 800个连接顺序

CBO

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

CBO - 统计信息

基表统计信息
- 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
- 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等
推导统计信息
- 选择率（ selectivity ）：对于某一个过滤条件，查询会从表中返回多大比例的数据
- 基数（ cardinality ）：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
“准确的 cardinality 远比代价模型本身重要。”
统计信息的收集方式
- 在DDL里指定需要收集的统计信息，数据库在写入或收集时更新信息。
- 手动执行 explain analyze statement，触发数据库收集或更新统计信息
- 动态采样
统计信息推导规则

假设列和列之间是相互独立的，列的值均匀分布
- Filter Selectivity
  - AND 条件: fs(a AND b) = fs(a) * fs(b)
  - OR 条件: fs(a OR b) - fs(а) + fs(b) (fs(a) * fs(b))
  - NOT 条件: fs(NOT a) = 1.0 - fs(a)
  - 等于条件(x = literal)
    - literal < min && literal > max: 0
    - 1/NDV
  - 小于条件：(х < literal)
    - literal < min: 0
    - literal > max: 1
    - (literal - min) / (max - min)
但现实常与该假设不符。列相关时，不能如上计算。列值非均匀分布，可使用直方图。

CBO - 执行计划枚举

计算出每一步骤的每种执行计划的代价，选择连接起来最小的一种。

CBO - 小结

CBO 使用代价模型和统计信息估算执行计划的代价
CBO 使用贪心或者动态规划算法寻找最优执行计划
在大数据场景下CBO对查询性能非常重要

三、查询优化器的社区开源实践

概览

Apache Calcite

One size fits all：统一的SQL查询引擎
模块化，插件化，稳定可靠
支持异构数据模型
- 关系型
- 半结构化
- 流式
- 地理空间数据
内置RBO和CBO

Calcite RBO

HepPlanner
- 优化规则(Rule)
  - Pattern: 匹配表达式子树
  - 等价变换: 得到新的表达式
- 内置有100+优化规则
- 四种匹配规则
  - ARBITRARY/DEPTH FIRST: 深度优先
  - TOP DOWN:拓扑顺序
  - BOTTOM_ UP：与 TOP DOWN 相反
- 遍历所有的rule，直到没有 rule 可以被触发
- 优化速度快，实现简单，但是不保证最优
VolcanoPlanner
- 基于Volcano/Cascade框架
- 成本最优假设
- Memo:存储候选执行计划
  - Group: 等价计划集合
- Top-down动态规划搜索
- 应用Rule搜索候选计划
- Memo
  - 本质: AND/OR graph
  - 共享子树减少内存开销
- Group winner：目前的最优计划
- 剪枝(Branch-and-bound pruning) ：减少搜索空间
- Top-down遍历:选择winner构建最优执行计划

小结

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

四、前沿趋势

存储计算分离
HSAP, HTAP, HTSAP
Cloud Native, Serverless
数据仓库，数据湖，湖仓一体，联邦查询
智能化
- AI4DB
  - 自配置：智能调参（OtterTune，QTune）、负载预测、负载调度
  - 自诊断和自愈合：软硬件错误、错误恢复和迁移
  - 自优化：统计信息估计（ Learned cardinalities ）、代价估计、学习型优化器（IBM DB2 LEO），索引推荐，视图推荐
- DB4AI
  - 内嵌人工智能算法（MLSQL，SQLFlow）
  - 内嵌机器学习框架（SparkML， Alink， dl-on-flink ）

SQL Optimizer 解析 | 青训营笔记