SQL optimizer 解析(2) ｜ 青训营笔记

佩棋wa
113 阅读5分钟

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

常见的查询优化器

查询优化器分类

通过遍历树的顺序划分

  • Top-down Optimizer

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

  • Bottom-up Optimizer

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

    • 最早的优化器：System R

通过优化的方法划分

RBO：根据规则优化

CBO：根据代价优化

  • Rule-based optimizer（RBO）

    • 根据关系代数等价语义，重写查询
    • 基于启发式规则

    通过经验

    • 会访问表的元信息(catalog)，不会涉及具体的表数据(data)

  • Cost-based Optimizer ( CBO )

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

RBO---关系代数

原理

  • 运算符
  • 等价代换：交换律、结合律、传递性

关系数据模型

优化原则

  • 优化I/O：读更少的数据及提高读数据的速度
  • 优化网络（Network）：传输更少的数据及提高传输数据的速度
  • 优化CPU和内存（Memory）：处理更少的数据及提高处理数据的速度

image.png

优化方法

列裁剪

image.png

从上往下扫描，保留各个算子所需的列，依次往下传递，传至scan算子中，scan算子只扫描有用的列

谓词下推

image.png 把where语句称为谓词，把谓词下推可以尽早的过滤掉无用的数据，减少数据传输

条件：

  • inner join的结果集是左表和右表都满足条件，所以inner join condtion中的条件都是可以下推的。
  • left join 由于左表是保留表，所有join condition中的左表条件会失效，右表的条件可以下推，对right join则相反
  • 对于outter join 由于左表和右表都是保留表，则都不能下推

传递闭包

image.png 通过表达式的等价关系和过滤条件，创建可以下推的谓词

Runtime Filter

image.png Runtime Filter：只有执行的时候产生

产生新的filter传到join的查询端

小结

主流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个连接顺序
  • ......

CBO

概念

CBO是经过不同的等价变换后获得不同的执行计划，对执行计划预估代价，选择代价小的计划去执行

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

    • 执行计划的代价等于所有算子的执行代价之和
    • 通过 RBO得到(所有)可能的等价执行计划

  • 算子代价:CPU，内存，磁盘I/O，网络I/O等代价

    • 和算子输入数据的统计信息有关:输入、输出结果的行数,每行大小..

      • 叶子算子Scan:通过统计原始表数据得到
      • 中间算子:根据一定的推导规则，从下层算子的统计信息推导得到

    • 和具体的算子类型，以及算子的物理实现有关

    • 算子运算的算法空间复杂度，时间复杂度等

例子:Spark Join算子代价= weight * row_ count + (1.0 - weight)* size

image.png

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

统计信息

  • 原始表统计信息

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

  • 推导统计信息

    • 选择率( selectivity ) :对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数( cardinality ):在查询计划中常指算子需要处理的行数

统计信息的收集

  • 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或更新统计信息

在创建表时，数据库会提供指定的语法去让用户指定统计信息是哪些，统计信息分别是什么...

    缺点：实时导入实时更新，会影响到数据导入速率

  • 手动执行explain analyze statement，触发数据库收集或更新统计信息

    缺点：统计信息比较旧

  • 动态采样

统计信息推导规则

假设列和列之间是独立的，列的值是均匀分布

  • 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.0 - fs(a)

    • 等于条件(x = literal )

      • literal < min && literal > max : o
      • 1/ NDV

    • 小于条件(x < literal )

      • literal < min : 0
      • literal > max : 1
      • (literal - min )/(max - min )

image.png

CBO执行计划枚举---动态规划

CBO效果---TPC-DS Q25

开启CBO

  • 减少了90%的Shuffle数据
  • 3.4倍的加速比

小结

CBO使用代价模型和统计信息估算执行计划的代价

CBO使用贪心或者动态规划算法寻找最优执行计划

在大数据场景下CBO对查询性能非常重要
avatar
文章
阅读
粉丝