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

我不在的日子你还好吗哦好那没事了
116 阅读4分钟

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

SQL查询优化器浅析

1. SQL在大数据技术体系中的位置:

image.png

2. SQL的处理流程

image.png

2.1 Parse：

  1. 把文本变成抽象语法树结构（AST）。
  2. 涉及词法分析阶段（拆分字符串，提取关键字，字符串，数值等）和语法分析阶段（把词条按照定义的语法规则组装成抽象语法树结构）image.png

2.2 Analyzer

  1. 访问库/表元信息并绑定。
  2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确。
  3. 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）。
      逻辑计划树逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划。
      树中每个节点是是一个算子，定义了对数据集合的计算操作（过滤，排序，聚合，连接），边代表了数据的流向，从孩子节点流向父节点。
      之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。
      如下是一个逻辑计划树的示意图。
    image.png

2.3 Optimizer(查询优化器)

  1. 查询优化的目标是为 SQL 生成的逻辑计划找到一个正确的且执行代价最小的物理执行计划。
    • 优化器的输出是一个分布式的物理执行计划。 image.png
    • 分布式物理执行计划的目标是在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生成 PlanFragment 树。
    • 一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。
    • Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。
  2. 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP 的。
  3. 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍。

2.4 Executor(物理执行器)

 Executor 按照物理执行计划，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD）扫描和处理数据。

3. 常见的查询优化器

image.png

3.1 Top-down Optimizer

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

3.2 Bottom-uo Optimizer

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

3.3 Rule-based Optimizer(RBO)

  • 根据关系代数等价语义重写查询
  • 基于启发式规则
    • 对于I/O：能够更少更快地读取数据
    • 对于Network：能够更少更快地传递数据
    • 对于CPU和内存：能够更少更快地处理数据
  • 会访问表的元信息(catalog)，不会涉及具体表数据(data)
    • 通过JOIN的信息对SCAN的数据进行列裁剪 image.png
    • 通过条件信息在SCAN进行JOIN操作之前设置FILTER进行谓词下推 image.png
    • 根据条件信息的传递性对SCAN的所有数据做同样的谓词下推操作，这种操作叫做传递闭包 image.png
    • Runtime Filter（min-max filter，in-list filter，bloom filter） image.png
    • Join 消除
    • 谓词合并
  • 小结
    • 主流 RBO 实现一般都有几百条基于经验归纳得到的优化规则
    • 优点：实现简单优化速度快
    • 缺点：不保证得到最优的执行计划

3.4 Cost-based Optimizer(CBO)

  • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
    • 执行计划的代价 = Σ（所有算子的执行代价）
      • 算子代价：CPU，内存，磁盘I/O，网络I/O等代价
        • 和算子输入数据的统计信息有关，比如输入、输出结果的行数，每行大小等
          • 基表统计信息：
            • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
            • 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等
        • 叶子算子 scan：通过统计原始表数据得到
          • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
            • 推导统计信息
              • 选择率（selectivity）  ：对于某一个过滤条件，查询会从表中返回多大比例的数据
              • 基数（cardinality）  ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
          • 和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）
    • 通过 RBO 得到所有可能的等价执行计划
    • 使用动态规划枚举所有执行计划，选出执行代价最小的执行计划
avatar
文章
阅读
粉丝