SQL Optimizer解析 | 青训营笔记

陈安安
52 阅读8分钟

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

大数据体系和 SQL

  1. 大数据体系全景图 One SQL rules big data all image.png

  2. SQL 处理流程

    1. 处理流程：
    2. Parser：String → AST 语法树
      1. 把文本变成抽象语法树结构（AST）
        1. 涉及词法和语法：
          • 词法分析：拆分字符串，得到关键词，数值常量，字符串常量，运算符号等 token
          • 语法分析：将 token 组成 AST node，最终得到一个 AST
        2. 和编译原理课程里的“前端”知识相关
        3. 示例·：递归下降( ClickHouse )，Flex+Bison( PostgreSQL )，JavaCC (Flink)，Antlr (Presto, Spark)
    3. Analyzer：AST → Logical Plan
      1. 访问库/表元信息并绑定：检查并绑定 Database，Table，Column等元信息
      2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确
      3. 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）
    4. Logical Plan
      • 逻辑地描述 SQL 对应的分步骤计算操作
      • 计算操作：算子 operator
      • 示例见第 4 点
    5. 查询优化
      • SQL 是声明式语言，用户只描述做什么，没有告诉数据库怎么做
      • 目标：找到一个正确且执行代价最小的物理执行计划
      • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的
      • 一般SQL越复杂，Joi的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上干倍：类比 gcc/g++ 编译程序时的编译级别（-O1, -O2, -O3），经过编译优化的程序运行效率更高
    6. Physical Plan
      • Plan Fragment：执行计划子树
      • 目标：最小化网络数据传输
      • 利用上数据的物理分布（数据亲和性）
      • 增加 Shuffle 算子：同时发送和接收
    7. Executor
      • Executor 按照物理执行计划扫描和处理数据，充分利用机器资源（CPU 流水线，乱序执行，cache，SIMD）
      • 单机并行( cache，pipeline，SIMD ), 多机并行( 一个 fragment 对应多个实例 )
    8. 小结：
      • One SQL rules big data all
      • SQL 需要依次经过 Parser, Analyzer, Optimizer 和 Executor 的处理
      • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
      • 查询优化器需要感知数据分布，充分利用数据的亲和性
      • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

常见的查询优化器

  1. 查询优化器分类：

    1. 遍历树划分：
      • Top-down Optimizer：从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
        • 例子：Volcano/Cascade, SQL Server
      • Bottom-up Optimizer：从零开始，由下往上遍历计划树，找到完整的执行计划
        • 例子：System R(1970最早的数据库优化器，部分功能仍在沿用), PostgreSQL, IBM DB2
    2. 优化规则划分：
      • RBO：Rule-based Optimizer
        • 根据关系代数等价语义，重写查询（主要根据语义）
        • 基于启发式规则
        • 会访问表的元信息( catalog ), 不会涉及具体的表数据( data )
      • CBO：Cost-based Optimizer
        • 使用一个模型估算执行计划的代价，选择代价最小的执行计划

  2. RBO

    1. 基于关系代数等价规则对逻辑计划进行变换
      • 运算符：
      • 等价变换：结合律，交换律，传递性
    2. 优化目标：
      • Read data less and faster (I/O)
      • Transfer data less and faster (Network)
      • Process data less and faster (CPU & Memory)
    3. 实现方式： - Pattern：定义了特定结构的 Operator 子树（结构） - Rule：定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换） - 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule
      • 局限性：
        • 无法解决多表连接问题
        • 无法确定和选择最优的分布式 Join/Aggregate 执行方式
    4. 实现规则：
      • 源代码块： 
        SELECT pv.siteld,user.name
FROM pv JOIN user
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;
      • 列剪裁：尽可能减少数据 从上向下的执行，首先会确认PROJECT中所需要的列，向下层传递，如果遇到需要其他列信息则进行集合合并，直至SCAN层。
      • 谓词下推：尽早过滤不必要的数据 在JOIN前后进行FILER对结果没有影响，所以可以在JOIN前先行减少算子计算量。但不同的FILTER对应下推规则不同，可以课后了解。
      • 传递闭包：在不改变语义的情况下增加谓词，形成闭包 根据源代码，可以推导出，左SCAN也需要满足pv.siteld>123故可以在左子树内增加谓词FILTER: pv.siteld>123 用于进行数据过滤。
      • Runtime Filter：执行时过滤 利用最顶层的JOIN得到集合特性，运行后直接传输至左子树，减少数据量或查询量，如图中min-max（给出连续数据范围，缺点是要求紧密贴合数据），in-list（给出离散数据范围，缺点是集合过多时传输网络开销较大），bloom filter（扫描一行就查询一次）等。
    5. RBO 小结：
      • 主流 RBO 实现一般都有几百条基于经验归纳得到的优化规则
      • 优点：实现简单，优化速度快
      • 缺点：不保证得到最优的执行计划
        • 单表扫描：索引扫描( 随机 I/O ) vs 全表扫描( 顺序 I/O ) -> 如果查询数据分布非常不均衡，索引扫描可能不如全表
        • Join的实现：Hash Join vs SortMerge Join
        • 两表Hash Join：用小表构建哈希表 -> 如何识别小表？
        • 多表 Join：哪种连接顺序是最优的？是否每种组合都探索？-> N表连接仅左深树就有 N! 种连接，容易内存溢出

  3. CBO

    1. 概述
      • 计算流程：
      • 使用一个模型估算执行计划的代价，选择代价最小的执行计划
      • 分而治之，执行计划的代价等于所有算子的执行代价之和
      • 通过 RBO 得到（所有）可能的等价执行计划（非原地替换）
      • 算子代价包含 CPU，cache misses，memory，disk I/O，network I/O 等代价
        • 和算子的统计信息有关，比如输入、输出结果的行数，每行大小等
        • 叶子算子 scan：通过统计原始表数据得到
          • 中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
          • 和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）
          • 示例：Spark Join 算子代价 = weight *row_count +(1.0 - weight) *size
      • 使用动态规划枚举所有执行计划，选出执行代价最小的执行计划
    2. 统计信息
      1. 信息内容：
        • 基表统计信息：可以直接在SCAN中引入
          • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
          • 列级别：min、max、num nulls、num、not nulls、num、distinct value（NDV）、histogram 等
        • 推导统计信息：用于中间算子使用
          • 选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据
          • 基数（cardinality） ：基本含义是表的unique行数，在查询计划中常指算子需要处理的行数
      2. 统计信息的收集方式：
        • 在 DDL 里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息：可能导致数据量增加 
          CREATE TABLE REGION(
R REGIONKEY INT NOT NULL,
R NAME CHAR(25)NOT NULL,
R COMMENT VARCHAR(152))
DUPLICATE KEY(R REGIONKEY)
DISTRIBUTED BY HASH(R REGIONKEY)BUCKETS 1
PROPERTIES("aais_schrnns”=R NAME");
        • 手动执行 explain analyze statement, 触发数据库收集或者更新统计信息：无法实时更新 
          ANALYZE TABLE table name column-name1, column-name2, ...
        • 动态采样： 
          SELECT count(*) FROM table name
      3. 统计信息推导规则： 假设列和列之间是独立的，列的值是均匀分市
        • Filter Selectivity： AND条件：fs(a AND b) = fs(a) *fs(b) OR条件： fs(a OR b) = fs(a) +f(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)适中则为选择率
        • Cardinality： cardinality(FILTER) = cardinality(A) * selectivity(FILTER)

  4. 执行计划枚举：通常使用贪心算法或动态规划选出最优的执行计划

    • 单表扫描：索引扫描( 随机 I/O ) vs 全表扫描( 顺序 I/O ) -> 如果查询数据分布非常不均衡，索引扫描可能不如全表
    • Join的实现：Hash Join vs SortMerge Join
    • 两表Hash Join：用小表构建哈希表 -> 如何识别小表？
    • 多表 Join：哪种连接顺序是最优的？是否每种组合都探索？-> N表连接仅左深树就有 N! 种连接，容易内存溢出
    1. CBO 开启效果：
      • 示例（取自Spark）：
        • 约有一半查询通过 RBO 可以找到最优执行计划，故 CBO 无法体现性能变化
        • 16个查询在 CBO 下有更好的执行性能，总体提升 30%，加速比 2.2X ~ 8X
    2. CBO 小结：
      • CBO 使用代价模型和统计信息估算执行计划的代价
      • CBO 使用贪心或者动态规划算法寻找最优执行计划
      • 在大数据场景下 CBO 对查询性能非常重要

  5. 第二节小结：

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