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

LimeRanceLan
167 阅读4分钟

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

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

二、常见的查询优化器

1.查询优化器分类

  • Top-down Optimizer

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

  • Bottom-up Optimizer

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

  • Rule-based Optimizer ( RBO）

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

  • Cost-based Optimizer ( CBO )

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

2.RBO（Rule-based Optimizer）

2.1 优化原则
  • 列裁剪：去掉表中的无关列，SCAN的过程中扫描相关列。
  • 谓词下推：例如过滤谓词准确下推到某一表的SCAN之前，避免多个无关表使用谓词。
  • 传递闭包：经过推到，传递性，增加新的谓词。
  • Runtime Filter：传递一个满足要求的小范围数据到join的另一个分支。
2.2 小结
  • 主流 RBO实现一般都有几百条基于经验归纳得到的优化规则
  • 优点:实现简单，优化速度快
  • 缺点:不保证得到最优的执行计划
    • 单表扫描：索引扫描 (随机 I/O) vs. 全表扫描 (顺序 I/O)
      • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
    • Join的实现：Hash Join vs. SortMerge Join
    • 两表Hash Join：用小表构建哈希表——如何识别小表?
    • 多表Join:
      • 哪种连接顺序是最优的?
      • 是否要对每种组合都探索?
        • N个表连接，仅仅是left-deep tree就有差不多 N！种连接顺序
        • e.g. N= 10 ->总共3, 628，800个连接顺序

3.CBO（Cost-Based Optimizer）

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

image.png

3.2 统计信息

  • 原始表统计信息

    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram 等

  • 推导统计信息

    • 选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数（cardinality） ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
3.2.1 统计信息的收集方式
  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或更新统计信息
create table .... properties("stats_columns"="R_NAME");
  • 手动执行explain analyze statement，出发数据库收集或更新统计信息
analyze table table_name compute statistics for columns col_name1, ...
  • 动态采样
select count(*） from table_name
3.2.2 统计信息推导规则

  • Filter Selectivity （假设列与列之间独立，列值均匀分布）

    • AND： fs(a AND b) = fs(a) * fs(b)
    • OR： fs(a OR b) = fs(a) + fs(b) - (fs(a) * fs(b))
    • NOT： fs(NOT a) = 1 - fs(a)
    • 等于条件（x = literal ）
      • literal < min && literal > max : 0
      • 1 / NDV (NDV - num distinct value 列中独立的互不相同的值的个数)
    • 小于条件（x < literal）
      • literal < min : 0
      • literal > max : 1 比最大值还大，全选中
      • (literal - min) / (max - min)
3.3 执行计划枚举

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

  • 单表扫描：索引扫描 (随机 I/O) vs. 全表扫描 (顺序 I/O)
    • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
  • Join的实现：Hash Join vs. SortMerge Join
  • 两表Hash Join：用小表构建哈希表——如何识别小表?
  • 多表Join:
    • 哪种连接顺序是最优的?
    • 是否要对每种组合都探索?
      • N个表连接，仅仅是left-deep tree就有差不多 N！种连接顺序
      • e.g. N= 10 ->总共3, 628，800个连接顺序
3.4 小结
  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或者动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要

4.小结

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