[SQL Optimizer解析]学习笔记|青训营笔记

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

[SQL Optimizer解析]学习笔记

1.1 大数据体系

大数据体系——One SQL rules big data all，其中包括七个模块分别对应七种功能模式：

（1）业务应用（管控运维）：

 BI报表、数据挖掘、营销分析、精准推荐；

（2）数据开发（集群创建）：

 Airflow、DAG；

（3）权限管理（集群创建）：

 Apache Ranger、GDPR；

（4）分析引擎（集群管理、服务管理）：

（5）资源调度（用户管理）：

 YARN、K8S；——Kafka

（6）存储系统（监控报警）：

 HDFS、HBase、NAS、Object Store、数据湖；——Pulsar

（7）基础设施（日志查询）：

 ECS、存储、VPC；——NSQ

1.2 SQL的处理流程

1. Parser

• String->AST(抽象语法树abstract syntax tree)

  词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token；

  语法分析：将token组成AST node，最终得到一个AST。

• 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink),Antlr(Presto,Spark).

2. Analyzer

• 检查并绑定Database，Table，Column等元信息；
• SQL的合法性检查，比如min/max/avg的输入是数值；
• AST->Logical Plan.

3. Logical Plan

• 逻辑地描述SQL对应的分步骤计算操作；
• 计算操作：算子（operator）。

4. Optimizer

• SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做；
• 目标：找到一个正确且执行代价最小的物理执行计划；
• 查询优化器是数据库的大脑，最复杂的模块，很多相关的问题都是NP的；
• 一般SQL越复杂，Join的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍。

5. Physical（Plan Fragment）

• 目标：最小化网络数据传输；
• 利用上数据的物理分布（数据亲和性）；
• 增加Shuffle算子。

6. Executor

• 单机并行：cache，pipeline，SIMD；
• 多机并行：一个fragment对应多个实例。

1.3 常见的查询优化器（RBO & CBO）

• Top-down Optimizer：从目标输出开始，由上往下遍历计划树，找到完整的最有执行计划；例如：Volcano/Cascade，SQLServer。
• Bottom-up Optimizer：从零开始，由下往上遍历计划树，找到完整的执行计划。例如：System R，PostgreSQL，IBM DB2。

RBO（Rule-based Optimizer）：

• 基于关系代数等价规则对逻辑计划进行变换
• 实现上：
  • Pattern：定义了特定结构的 Operator 子树（结构）;
  • Rule：定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换）;
  • 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule。
• 局限性：
  • 无法解决多表连接问题；
  • 无法确定和选择最优的分布式 Join/Aggregate 执行方式。

CBO（Cost-based Optimizer）：

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

• 分而治之，执行计划的代价等于所有算子的执行代价之和;

• 通过 RBO 得到（所有）可能的等价执行计划（非原地替换）;

• 算子代价包含 CPU，cache misses，memory，disk I/O，network I/O 等代价;

• 和算子的统计信息有关，比如输入、输出结果的行数，每行大小等;

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

  • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到;
  • 和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）;
  • 使用动态规划枚举所有执行计划，选出执行代价最小的执行计划。

• 统计信息

  • 基表统计信息
    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等；
    • 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等。
  • 推导统计信息
    • 选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据；
    • 基数（cardinality） ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数。

• 执行计划枚举

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

1.4 总结

• SQL需要依次经过Parser，Analyzer，Optimizer和Executor处理；
• 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要；
• 查询优化器按照最小化网络数据传输的目标爸逻辑计划拆分成多个物理计划片段；
• 主流RBO实现一般都有几百条基于经验归纳得到的优化规则；
• RBO实现简单，优化速度快，但不保证得到最优的执行计划；
• CBO使用代价模型和统计信息估算执行计划的代价；
• 大数据场景下CBO对查询性能非常重要；
• Apache Calcite是大数据领域很流行的查询优化器；
• Apache Calcite RBO定义了许多优化规则，使用pattern匹配子树，执行等价交换；
• Apache Calcite DBO基于Volcano/Cascade框架；
• Volcano/Cascade的精髓：Memo、动态规划、剪枝。