SQL Optimizer 解析 | 青训营笔记

用户4750835812109
194 阅读4分钟

SQL Optimizer 解析|青训营笔记

大数据体系

image.png

SQL查询优化器的重要性

  • 有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人
  • 大多大数据计算引擎都支持SQL作为更高抽象层次的计算入口 因此，sql查询优化器是sql的入口，sql对于大数据来说是基础， SQL Optimizer的了解非常重要

SQL 的处理流程

image.png

SQL 的处理流程 -Parser

  • String -> AST (abstract syntax tree)
    1. 词法分析:拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
    2. 语法分析:将token 组成AST node，最终得到一个AST
  • 实现:递归下降(ClickHouse),Flex和Bison (PostgreSQL),JavaCC (Flink)，Antlr (Presto, Spark)

image.png

SQL 的处理流程 - Analyzer 和 Logical Plan

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

image.png

image.png

SQL处理流程 - 查询优化

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

SQL处理流程 - Physical Plan 和 Executor

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

小结

image.png

常见的查询优化器

查询优化器分类

  • Top-down Optimizer

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

  • Bottom-up Optimizer

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

  • Rule-based Optimizer (RBO)

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

  • Cost-based Optimizer (CBO)

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

RBO

  • 优化原则
    • 优化 I/O
    • 优化 Network
    • 优化 CPU & Memory
  • 优化规则
    • 列裁剪
    • 谓词下推
    • 传递闭包
    • Runtime Filter（min-max filter，in-list filter，bloom filter）
    • Join 消除
    • 谓词合并

CBO

  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或者动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要

小结

image.png

 查询优化器的社区开源实践

Apache Calcit是大数据领域很流行的查询优化器

其中的HepPlanner是基于RBO使用pattern匹配子树，执行等价代换来实现最终性能最优

  • HepPlanner内置有100+优化规则

  • 四种匹配机制

    • ARBITRARY : 深度优先
    • TOP_DOWN : 拓扑排序
    • BOTTOM_UP : 自底向上
    • TOP_DOWN : 自顶向下

其原理为通过遍历所有的规则直到没有规则可以被触发，这样一来虽然优化速度快且实现简单但是无法保证性能最优

其中的Volcano Planner是基于Volcano/Cascade框架通过对成本进行最优假设来实现最终性能最优

  • Memo存储候选执行计划
  • 本质AND/OR graph
  • 共享子树减少内存开销
  • Group等价计划集合
  • Group winner实现目前最优计划
  • 剪枝（Branch-and-bound pruning）减少搜索空间

其原理通过利用Top-down动态规划搜索，通过选择winner构建最优执行计划

前沿趋势

  • 选择引擎架构的进化【存储计算分离】
  • 一体化（HTAP,HSAP,HTSAP）
  • 湖仓一体 Query Federation
  • 数据库和AI联系在一起
    • AI4DB
      • 自配置
      • 智能调参(OtterTune，QTune)
      • 负载预测/调度
      • 自诊断和自愈合:错误恢复和迁移
      • 自由化
      • 统计信息估计
      • 代价估计
      • 学习型优化器（IBM DB2 LEO）
      • 索引/视图推荐
    • DB4AI
      • 内嵌人工智能算法（MLSQL，SQLFLow）
      • 内嵌机器学习框架(SparkML,Alink,dl-on-flink)
avatar
文章
阅读
粉丝