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

SQL查询优化器整体框架

以下主要对「查询优化器」进行分析：

1. 分类

查询优化器主要可以分为4类：

• Top-down Optimizer
• Bottom -up Optimizer
• Rule-based Optimizer（RBO）
• Cost-based Optimizer（CBO）

2. 具体分析

2.1 Top-down Optimizer

• 从目标输出开始，由上而下遍历，找到完整的最优执行计划
• 例子：SQLServer、Volcano/Cascade

2.2 Bottom -up Optimizer

• 从零开始，由下而上遍历，找到完整的执行计划
• 例子：System R、PostgreSQL、IBM DB2

2.3 Rule-based Optimizer（RBO）- 主要根据经验

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

2.3.1 关系代数

• 运算符：Select、Join、Rename、Union等
• 等价变换：结合律、交换律、传递性

2.3.2 优化原则

• I/O：Read less and faster
• Network：Transfer less and faster
• CPU & Memory：Process less and faster

2.3.2.1 列裁剪

• 对于算子而言，数据处理时未使用到的列不需要保留
• 尽量减少数据数量

2.3.2.2 谓词下推

• 尽早过滤数据（filter），显著减少数据开销
• 避免没有必要的行数、列数

2.3.2.3 传递闭包

• 运用「传递性」
• 根据表达式的等价关系，来推导出新的过滤条件
• 提升连接操作效率

2.3.2.4 Runtime Filter

• 执行时才可以使用，且在数据库中广泛使用
• 原理：根据join的「右边输入数据表过滤后的结果」，以及「join的key」（连接条件）创建一个执行时的过滤器，提前对「左边数据」进行一次过滤

2.3.3 RBO总结

• 主流RBO基本上是基于上百条经验得出的优化规则
• 优点：实现简单，优化速度快
• 缺点：不保证得到最优执行计划

---

2.4 Cost-based Optimizer（CBO）-主要根据代价

2.4.1 概念

• 使用一个模型估算执行计划的代价，选择代价最小的执行计划
• 算子代价：CPU、内存、磁盘I/O等代价
  • 和算子输入数据的统计信息有关：输入、输出结果的行数、每行大小……
  • 和具体的算子类型、算子的物理实现有关（算子使用不同算法，会影响时间复杂度、空间复杂度）

2.4.2 思维导图

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

2.4.3 统计信息推导规则

• 假设：列和列之间相互独立，列的值均匀分布
• 可用直方图去除非均匀分布的情况

2.4.4 执行计划枚举

• 通常使用贪心算法或动态规划选出最优的执行计划
• 单表扫描：索引扫描（随机I/O）vs全表扫描（顺序I/O）
  • 如果查询的数据分布非常不清楚，索引扫描可能不如全表扫描
  • join的实现：Hash & SortMerge
• 两表扫描
• 多表扫描

2.4.5 总结

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

---

3. 总结

（1） RBO基于几百条经验的规则而得出
（2） RBO实现简单，优化速度快
（3） RBO不保证得到最优执行计划
（4） CBO使用代价模型和统计信息估算执行计划的代价
（5） CBO使用贪心算法或者动态规划
（6） 大数据场景下，CBO对查询性能非常重要