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

二粒砂
190 阅读9分钟

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

一、本堂课重点内容：

  • 大数据体系和SQL
  • 常见查询优化器
  • 社区开源实践
  • 前沿趋势

二、详细知识点介绍：

1.大数据体系和SQL

介绍大数据体系和SQL的处理流程，重点介绍SQL在分布式环境下的处理

①大数据体系中的SQL

希望使用SQL处理所有的大数据（让不同的分析引擎都提供SQL接口）

②SQL的处理流程

  1. parser

    • 作用：将SQL语句分析成树，树的每一个节点都包含token

    • String -> AST（抽象语法树）

      • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
      • 语法分析：将token组成AST node，最终得到一个AST

    • 实现：递归下降（ClickHouse），Flex和Bison（PostgreSQL），JavaCC（Flink），Antlr（Presto，Spark）

  2. Analyzer

    • Analyzer

      • 检查并绑定Database，Table，Column等元信息
      • SQL的合法性检查，比如min/max/avg的输入是数值
      • AST->Logical Plan

    • Logical Plan

      • 逻辑的描述SQL对应的分步骤计算操作
      • 计算操作：算子（operator）
      • left deep tree：JOIN算子的右子必须是SCAN表

  3. 查询优化

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

  4. Physical Plan和Executor

    • Plan Fragment：执行计划子树（子树拆分同样也是优化问题）

      • 目标：最小化网络数据传输
      • 利用数据的物理分布（数据亲和性）
      • 增加Shuffle算子

    • Executor

      • 单机并行：cache、pipeline、SIMD

      • 多级并行：一个fragment对应多个实例

③小结

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

2.常见的查询优化器

介绍查询优化器的分类，重点介绍RBO和CBO的原理

①查询优化器的分类

  1. 按照遍历树的顺序划分

    • Top-down Optimizer

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

    • Bottom-up Optimizer

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

  2. 按照有划分方法划分

    • RBO

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

    • CBO

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

②RBO（Rule-Based Optimizer）

  1. 关系代数

    • 运算符：σ、π、（Join）、ρ、∪
    • 等价变换：结合律，交换律，传递 性

  2. 优化原则

    • I/O：读入更少的数据，读数据更快
    • Network：传输的数据更少更快
    • CPU&Memory：CPU指令数更少，占据内存空间更少

  3. 列裁剪

    • 裁剪结果不需要的列，节省I/O与内存资源
    • 实现：优化器从上往下扫描：从上往下合并每个算子需要的列，一直到SCAN，则其只需扫描所需列即可

  4. 谓词下推

    • 谓词：WHERE后的表达式

    • 即FILTER往JOIN下推

    • TODO：可被下推的JOIN

  5. 传递闭包

    • 根据表达式的等价关系+过滤条件-->得到新的过滤条件

  6. Runtime Filter

    • 执行时产生的Filter

    • 先FILTER一个表，得到一个新的运行时产生的过滤条件，三种过滤条件：

      • min-max：限定一些数值范围；缺点：范围必须是很紧密的

        例：右边得到的范围是0-100000，但是左边的数据大多是0-100，过滤效果不好

      • in-list：表现为值的集合；缺点：若右边的集合很大，网络开销大

      • bloom filter：大小不随集合大小改变，（右边构造出哈希表，左边去查）

        细节见【存储引擎】

    • 另一个表先被Runtime Filter，再常规Filter

③CBO（Cost-Based Optimizer）

  1. 概念

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

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

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

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

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

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

      • 例：Spark Join算子代价 = weight * row_count + (1.0 - weight) * size （CPU代价+I/O代价）

        weight用来配置CBO与I/O的重要程度

  2. 统计信息

    • 原始表统计信息

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

    • 推导统计信息

      • 选择率（selectivity）：对于某一个过滤条件，查询会从表中返回多大比例的数据

      • 基数（cardinality）：在查询计划中常指算子需要处理的行数

        准确的基数，远比代价模型本身重要

    • 统计信息收集方法

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

        缺点：影响实施插入数据的速率

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

        缺点：可能因为统计信息更新的不及时导致产生坏的执行计划

      • 动态采样

        通过简单query进行采样，然后估算统计信息

    • 统计信息推导规则

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

    • 统计信息的问题

  3. 执行计划枚举

    • RBO的问题

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

    • 执行规划枚举-动态规划

      每个stage保留代价最小,最后将代价相加选最小

  4. CBO效果 - TPC-DC Q25

④小结

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

3.社区开源实践

介绍查询优化器在社区的开源实践，重点介绍Apache Calcite项目

①社区开源实践-概览

Orca:c++ Calcite:Java

②Apache Calcite

  1. 概览

  • query进入Calcite系统后,有两条路可走:

  • 经server处理后,sql语句被检验及解析,然后将抽象语法树转为关系表达式(包括算子等) - 由于(Calcite定位:可以对接不同的处理系统), Calcite提供Expression Builder,不经过解析,直接生成表达式

  • 得到关系表达式后,进入优化器,优化器主要与两个组件（作用：支持不同的系统）交互:

  • Metadata Providers:为优化器提供统计信息,表的信息等

  • Pluggable Rules:Calcite定义了插件化的RBO规则，不同的数据处理系统（Flink、Hive等）可以通过该插件实现更多适用于本系统的优化规则

    • One size fits all:统一的SQL查询引擎

    • 模块化,插件化,稳定可靠

      • 支持异构数据模型

        • 关系型
        • 半结构化
        • 流式
        • 地理空间数据

    • 内置RBO和CBO

  1. Calcite RBO

    • HepPlanner

      • 优化规则

        • Pattern：匹配表达式子树
        • 等价变换：得到新的表达式

      • 内置有100+优化规则

      • 四种匹配规则

        • ARBITRARY/DEPTH_FIRST：深度优先
        • TOP_DOWN：拓扑排序
        • BOTTOM_UP：与TOP_DOWN相反

      • 遍历所有的rule，直到没有rule可以被触发

      • 优化速度快，实现简单，但是不保证最优

  2. Calcite CBO

    • VolcanoPlanner

      • 基于Volcano/Cascade框架

      • 成本最优假设

      • Memo：存储候选执行计划。精巧的数据结构，一张记录问题最优解的表

        • 本质：AND/OR graph：在同一条链表中的Group是AND，同层的Group是OR
        • Group：等价计划集合，即共享子树减少内存开销

      • Top-down 动态规划搜索

      • 利用Rule搜索候选计划

      • Group winner：目前的最优计划

      • 剪枝：减少搜索空间

        • 可行的Aggregate：总的cost=500，自己的cost = 150，孩子节点cost上限=350

      • Top-down遍历：选择winner构建最优执行计划

③小结

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

4.前沿趋势

介绍SQL引擎的前沿趋势，重点介绍AI和DB的结合

①概览

  • 引擎架构进化：存储计算分离、一体化（一个系统中运维更简单，成本低

  • Cloud：serverless：根据负载动态调整集群规模；云原生

  • 湖仓一体：

    • 之前的数据仓库的概念：预先定义表和模型，对原始日志等进行处理变成关系型数据存到数仓里；缺点：限制数据使用类，不利于业务迭代
    • 数据湖可以把原始数据当成文件存下来；缺点：不规则，不利于管理
    • 湖仓一体：结合

  • DATA＋AI

②DATA+AI

  • AI4DB

    • 自配置

      • 智能调参(OtterTune,QTune)
      • 负载预测/调度
    • 自诊断和自愈合:错误恢复和迁移

    • 自优化:

      • 统计信息估计(Learned cardinalities)

        • 代价估计
        • 学习型优化器(IBM DB LEO)
        • 索引/视图推荐

  • DB4AI

    • 内嵌人工智能算法(MLSQL,SQLFlow)
    • 内嵌机器学习框架(SparkML, Alink, dl-on-flink)
avatar
文章
阅读
粉丝