SQL Optimizer | 青训营笔记

mumumu1
119 阅读4分钟

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

大数据体系和SQL

SQL的处理流程

  • Parser

    • 作用：String->AST(abstract syntax tree)

  • Analyzer和Logical Plan

    • Analyzer

      • 检查并绑定数据库、表、列名等信息是否正确
      • SQL的合法性检查，例如min/max是不是数值
      • AST->Logical Plan（抽象语法树转换成逻辑执行计划）

    • Logical Plan

      • 逻辑的描述SQL是怎么查询计算的
      • 是一个树，每个节点都是一个算子

  • 查询优化 Optimizer

    • 为什么要：因为用户用SQL只描述需要做什么，没告诉DB怎么做
    • 目标：找到执行代价最小的
    • 很多问题都是NP
    • SQL越复杂，join表越多，数据量越大，优化意义就越大

  • Physical Plan 和 Executer

    • Plan Fragment：执行计划子树

      • 目标：min网络数据传输
      • 利用数据的物理分布（数据亲和）
      • 增加shuffle算子（每个节点之间的连接）

    • Execuer（执行希望充分利用并行机制）

      • 单机并行：cache，流水线，SIMD
      • 多机并行：一个fragment对应多个实例

  • 小结

常见的查询优化器

  • 查询优化器分类

    • 上-下遍历计划书，找到最优

    • 下-上遍历，找最优

    • Rule based Optimizer(RBO)

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

    • cost based Optimizer：使用模型估算，选择代价最小

  • RBO

    • 优化原则

      • 优化IO：读更少数据，读的更快
      • 优化网络：传输更少数据，读的更快
      • 优化CPU和内存：内存使用更少，处理更快

    • 列裁剪

      • 尽可能减少数据
      • 从上往下筛选出需要的列，去掉不用的列

    • 谓词下推（约束条件 表达式）

      • 过滤数据在前面过滤和在后面过滤结果不受影响
      • 所以想谓词能不能往前推，尽早的过滤数据

    • 传递闭包

      • 根据一些表达式的等价关系，加上过滤条件，可以推出一个新的过滤关系
      • 例
pv.siteid=user.siteid
where user.site>123;
可以推出：pv.siteid>123,然后再进行谓词下推

    • Runtime Filter（运行时过滤）

      • 运行的时候，在join之前，如果先过滤完其中一方，可以传一些值（比如min-max）到另一边，减少另一边的过滤强度

    • 小结

  • CBO

    • 概念：关系代数有很多等价变换，通过等价变换可以得到不同的执行计划，通过模型估算这些执行计划的代价，选择代价最小的执行计划

    • 执行计划代价=所有算子执行计划之和

    • 通过RBO得到可能的等价执行计划

    • 算子代价：CPU，内存，磁盘IO，网络IO等代价

      • 和算子输入数据的统计信息有关：输入、输出、每行大小

        • 叶子算子：统计原始表获得
        • 中间算子：根据一定的推导规则，从下往上推导

      • 和具体算子类型、算子的物理实现有关

    • 基本流程：统计信息、推导规则->计算算子代价->计算执行计划代价->执行计划枚举

    • 统计信息

      • 原始表统计信息

        • 表级或分区级：行数，行平均大小，表占用字节
        • 列级：min，max，num nulls

      • 推导统计信息

        • 选择率：对于一个过滤条件，查询会从表中返回数据的比例
        • 基数：在查询中算子需要处理的行数

      • 统计信息的收集方式

        • 建表时指定需要统计的信息 properties
        • 手动执行-analyze table compute statistics for columns
        • 动态采样
        • 例：select count（*）from table_name

    • 推导规则

      • 问题：我们假设列与列之间是相独立的，列的值是均匀分布。但这个假设往往与现实不符

    • 执行计划枚举

      • 通过贪心或dp，局部最优达到全局最优

    • 小结

      • 使用代价模型和统计信息估算执行计划的代价
      • 使用贪心或dp寻找最优执行计划

社区开源实践

  • Apache Calcite

    • Calcite的RBO--HepPlanner

    • Calcite的CBO

  • 小结

前沿趋势

  • 对SQL优化器的新要求

    • 引擎架构变换-存储计算分离，一体化（HTAP,HSAP,HTSAP）
    • 云原生：K8S
    • 湖仓一体：数据湖和数据仓库的优点结合在一起
    • DATA+AI

  • DATA+AI

    • AI4DB

      • 自配置：智能调参数，负载预测/调度
      • 自诊断和自愈合：错误恢复和迁移
      • 自优化：统计信息估计、代价估计、学习型优化器、索引/视图推荐（根据常用的查询自动创建索引或视图）

    • DB4AI

      • 内嵌人工智能算法（MLSQL,SQLFlow）
      • 内嵌机器学习框架（sparkML，Alink）
avatar
文章
阅读
粉丝