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

FlemingFan
137 阅读7分钟

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

0x00 大数据体系

大数据体系.png


0x01 大数据体系和SQL

1.1 SQL的重要性

One SQL rules big data all.

  1. SQL是流行的语言
  2. SQL是很多系统中接受的接口
  3. SQL是大数据处理标准接口

1.2 SQL的处理流程

SQL处理流程.png

1.2.1 Parse和AST

Parse是将输入的SQL语句转换成一颗抽象语法树

  • String -> AST（abstract syntax tree）
  1. 词法分析：拆分字符串，得到关键字，数值常量，字符串常量，运算符号等token
  2. 语法分析：将token组成AST node，最终得到一个AST抽象语法树
SELECT * FROM * WHERE * GROUP * ORDER

AST.png

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

1.2.2 Analyzer和Logical Plan

  1. Analyzer
  • 检查并绑定Database,Table,Column等元信息
  • SQL的合法性检查，比如min/max/avg的输入是数值
  • AST->Logical Plan
  1. Logical Plan
  • 逻辑地描述SQL对应的分步骤计算操作
  • 计算操作：算子(operator)

Analyzer1.png Analyzer2.png

1.2.3 查询优化

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

1.2.4 Physical Plan和 Executor

  1. Plan Fragment:执行计划子树
  • 目标：最小化网络数据传输
  • 利用上数据的物理分布（数据亲和性）
  • 增加Shuffle算子
  1. Executor
  • 单机并行：cache,pipeline,SIMD
  • 多机并行：一个fragment对应多个实例

Physical.png

1.2.5 小结

  • One SQL rules big data all
  • SQL需要依次经过Parser,Analyzer,Optimizer和Executor的处理
  • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布，充分利用数据的亲和性
  • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

0x02 常见的查询优化器

2.1 查询优化器的分类

2.1.1 按照遍历树来划分

  1. Top-down Optimizer
  • 从目标输出开始，由上往下遍历计划树， 找到完整的最优执行计划
  • 例子：Volcano/ascade,SQLServer
  1. Bottom-up Optimizer
  • 从零开始，由下往上遍历计划树，找到完整的执行计划
  • 例子：System R,PostgreSQL,IBM DB:2

2.1.2 根据优化方法划分

  1. RBO (Rule-based Optimizer)
  • 根据关系代数等价语义，重写查询
  • 基于启发式规则
  • 会访问表的元信息(catalog),不会涉及具体的表数据(data)
  1. CBO (Cost-based Optimizer)
  • 使用一个模型估算执行计划的代价,选择代价最小的执行计划

2.2 RBO

2.2.1 RBO - 关系代数

  • 运算符：Select(o),Project(T),Join(),Rename(p),Union(U)等
  • 等价变换：结合律，交换律，传递性
    • Select: OP1∧AP2∧...PN(R)=σP1(σp2(...σPN(R)))
    • Join:R⋈S=S⋈R,(R⋈S)⋈T R⋈(S⋈T) 关系代数1.png 关系代数2.png

2.2.2 RBO - 优化原则

  • Read data less and faster(I/O)
  • Transfer data less and faster(Network)
  • Process data less and faster(CPU Memory) 优化原则.png

2.2.3 RBO - 列裁剪

用不到的列尽早去掉，从上往下执行，先查看需要哪些列集合 后续只需要扫描对应列集合，尽可能减少数据，减少扫描开支 列裁剪.png

2.2.4 RBO - 谓词下推

谓词下推是将条件逻辑下推，优先进行条件判断，实现数据的提前过滤 谓词下推.png

2.2.5 RBO - 传递闭包

根据表达式关系与过滤关系，推导新的过滤关系，加快连接速度 传递闭包.png

2.2.6 RBO - Runtime Filter

产生新的Filter，推至JOIN的查询端，以此减少扫描数据量 Runtime Filter.png

2.2.7 RBO - 小结

  1. 主流RB。实现一般都有几百条基于经验归纳得到的优化规则
  2. 优点：实现简单，优化速度快
  3. 缺点：不保证得到最优的执行计划
  • 单表扫描：索引扫描（随机I/O) vs. 全表扫描（顺序I/O)
    • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
  • Join的实现：Hash Join vs. SortMerge Join
  • 两表Hash Join:用小表构建哈希表——如何识别小表？
  • 多表Join:
    • 哪种连接顺序是最优的？
    • 是杏要对每种组合都探索？
      • N个表连接，仅仅是Ieft-deep tree就有差不多N!种连接顺序
      • e.g.N=10->总共3,628,800个连接序

2.3 CBO

2.3.1 CBO - 概念

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

2.3.2 CBO - 统计信息

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

准确的cardinality，远比代价模型本身重要。

2.3.2.1 CBO - 统计信息的收集方式

  1. 在DDL里指定需要收华的统计信息，数据库会在数据写入时收集或者更新统计信息
  2. 手动执行explain analyze statement,发数据库收集或者更折统计信息
  3. 动态采样

2.3.2.2 CBO - 统计信息推导规则

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

  • 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:0
      • 1/NDV
    • 小于条件(x<literal)
      • literal<min:0
      • literal>max:1
      • (literal-min)/(max-min)

2.3.2.3 CBO - 统计信息的问题

  1. 问题：假设经常和实际情况不符
  2. 解决方案：列可能存在关联性时，用户可以指定或数据库自动识别关联的列
  3. 使用直方图处理不是均匀分布的数据

2.3.3 CBO - 执行计划枚举

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

  • 单表扫描：索引扫描（随机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个连接顺序

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

  • 化繁为简的动态规划思想，先将需要连接的三个表JOIN拆分成两组双表JOIN问题
  • 分析拆分后的两组表双表JION各自使用“Hash Join”和“sortMerge Join”所需的开销
  • 选择其中开销最小的方式，回归根本问题进行三表JOIN，同样思想选择Cost和最小的方式

2.3.4 CBO - 效果

2.3.4.1 CBO - 效果 - TPC-DS Q25

CBO效果.png

CBO效果1.png

2.3.4.2 CBO - 效果 - TPC-DS

TPC-DS.png

TPC-DS1.png

2.3.5 CBO - 综合小结

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

0x03 社区开源实践

社区实践.png

3.1 Apache Calicite

calcite RBO.png

3.2 Calcite RBO

Calcite RBO1.png

3.2 Calcite CBO

Calcite CBO.png

calcite CBO1.png

calcite CBO2.png

3.3 小结

  1. 主流的询优化器都包含RBO和CBO
  2. Apache Calcite是大数据领域很流行的查询优化器
  3. Apache Calcite RBO定义了许多优化规则，使用pattem匹配子树，执行等价变换
  4. Apache Calcite CBO基于Volcano/Cascade框架
  5. Volcano/Cascade的精髓：Memo、动态规划、剪枝

0x04 行业前沿趋势

前沿.png


0x05 总结

课程总结.jpeg

avatar
文章
阅读
粉丝