SQL Optimizer解析 | 青训营笔记

一只默默努力的小菜鸟
163 阅读8分钟

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

大数据体系

大数据体系中的SQL

One SQL rules big data all

SQL 提供一种规范，最终目的是能够使用SQL处理所有的大数据

image.png

SQL的处理流程

SQL 在分布式环境下的处理

image.png

Parser

将文本经过词法分析、语法分析转化成抽象语法树结构（AST）

  • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
  • 语法分析：将词条（token）按照定义的语法规则组装成AST

实现

  • 梯度下降(ClickHouse)
  • Flex和Bison(PostgreSQL)
  • JavaCC(Flink)
  • Antlr(Presto, Spark)

image.png

Analyzer

  1. 访问库/表元信息并绑定
  2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确
  3. 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）

  • 逻辑结构（Logical Plan）

    • 逻辑地描述SQL对应地分步骤计算操作

    • 计算操作:算子（operator）

image.png

Optimizer查询优化

  • SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做，执行起来具有较大的自由度

  • 目标: 为 SQL 找到一个正确的且执行代价最小的执行计划

  • 查询优化器是数据库的大脑，最复杂的模块，需要考虑很多边界情况，并且很多相关问题都是 NP 的

  • 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍

  • 物理执行计划

    • 优化器的输出是一个分布式的物理执行计划。

    • 分布式物理执行计划的目标是在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生成 PlanFragment 树。

    • 一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。

    • Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。

image.png

Executor

按照物理执行计划扫描和处理数据，充分利用机器资源（CPU流水线， 乱序执行，cache， SIMD）

  • 单机并行：cache、pipeline、SIMD

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

  • Plan Fragment 执行计划子树

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

常见的查询优化器

按照遍历计划树顺序划分

1、Top-down Optimizer

从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划

例：Volcano/Cascade, SQLServer

2、Botton-up Optimizer

从零开始，由下往上遍历计划树，找到完整的执行计划

例：System R， PostgreSQL， IBM DB2

按照优化方法划分（重点）

1、Rule-base Optimizer(RBO)

image.png

  • 基于关系代数等价规则对逻辑计划进行变换

    • Pattern：定义了特定结构的 Operator 子树（结构）

    • Rule：定义了如何将其匹配的节点替换（Substitute）为新形态，从而生成新的、等价的Operator 树（原地替换）

    • 优化器搜索过程被抽象为不断匹配 Pattern 然后应用 Rule 转换，直到没有可以匹配的 rule

  • 主流RBO实现一般有几百条基于经验归纳得到的优化规则

  • 优点： 实现简单，优化速度快

  • 缺点： 不能保证得到最优的执行计划

    • 单表扫描时：索引扫描（随机I/O） vs 全表扫描（顺序I/O)
      • 查询数据非常不均匀时，全表扫描可能更好
    • Join的实现：Hash Join vs SortMerge Join
    • 两表Hash Join：用小表构建哈希表 -- 无法识别小表
    • 多表Join: 由于结合率和交换律，组合过多，不能对每种组合都探索
      • N个表连接，仅仅left-deep tree就有N!种连接顺序

关系代数

image.png

优化规则（这里介绍4种）

  • 优化的目标：I/O、 Network、 CPU&Memory

  1. 列裁剪

    基本思路：对于一个查询，尽早删去算子运行计算用不到的列，减少I/O和内存占用。

    实现：从上往下扫描，向下传递需要的列集合，最用scan算子只需读取所需要的列即可

image.png

  1. 谓词下推

    基本思路：在不影响最终结果的情况下，将谓词下推，尽早过滤掉不必要的数据， 减少计算和传输开销

    实现时：思考不同类型JOIN FILTER下推的可行性

image.png

  1. 传递闭包

    基本思路：根据等价关系和过滤条件推导出新的过滤条件然后下推

image.png

  1. Runtime Filter

    基本思想：根据Join的输入的某些特性产生Runtime Filter在Join的查询端提前进行过滤减少加入到Join算子中进行哈希等复杂操作

    • min-max filte 找出最大最小值 范围内需要数据紧密

    • in-list filter 将值存入集合中 缺点：数据量过大时开销大

    • bloom filter 特性：如果不在过滤器中，则一定不在。如果在过滤器中，则不一定在。

image.png

2、Cost-base Optimizer(CBO)

image.png

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

    • 分而治之，执行计划的代价等于所有算子的执行代价之和

    • 通过RBO 得到（所有）可能的等价执行计划(非原地替换)

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

    • 和算子的统计信息有关：输入、输入结果的行数、每行大小等
      • 叶子算子scan：通过统计原始表数据得到
      • 中间算子：根据一定的推导规则，从下层算子统计信息推导得到
    • 和具体的算子类型，以及算子的物理实现有关 （e.g. hash join vs sort join）
    • e.g. Spark Join算子代价 = weight * row_count + (1.0 - weight) * size （cpu代价 + I/O代价 weight 配置二者的权重)

  • 使用贪心或动态规划枚举所有执行计划，选出执行计划代价最小的执行计划

  • 在大数据场景下CBO对查询性能非常重要

统计信息

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

  • 原始表统计信息

    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)、histogram 等 （后两者为列中互不相同的值的个数和直方图）

  • 推导统计信息

    • 选择率（selectivity）:对于某一个过滤条件，查询会从表种返回多大比例的数据
    • 基数（cardinality）: 在查询计划中常指算子需要处理的行数

  • 统计信息的采集方式

    • 在DDL里指定需要收集的统计信息，写入数据时收集或更新统计信息
      • 会影响写入速度
    • 手动执行explain analyze statement，触发数据库收集或更新统计信息
      • 可能统计信息旧
    • 动态采样 SELECT count(*) FROM table_name

  • 统计信息的问题---假设与现实不符

    • 列与列之间有关联------用户指定或数据库自动识别相关联的列
    • 列的值不是均匀分布的 ----- 使用直方图

统计信息推导规则

image.png

开源社区实践

概况

image.png

Apache Calcite项目

image.png

  • One size fits all:统一的SQL查询引擎
  • 模块化、插件化、稳定可靠
  • 支持异构数据模型： 关系型、半结构、流式、地理信息数据
  • 内置RBO和CBO

HepPlanner --- Calcite RBO

  • 优化规则（Rule）
    • Pattern：匹配表达式子树
    • 等价变换：得到新的表达式
  • 内置有100+优化规则
  • 四种匹配规则
    • ARBIRARY/DEPTH_FIRST:深度优先
    • TOP_DOWN：拓扑顺序
    • BOTTOM_UP:与TOP_DOWN相反
  • 遍历所有的rule，直到没有rule可以被触发
  • 优化速度快，实现简单，但是不保证最优

VolcanoPlanner --- Calicite CBO

  • 基于 Volcano/Cascade框架

  • Cascades Optimizer 在搜索的过程中，其搜索的空间是一个关系代数算子树所组成的森林，而保存这个森林的数据结构就是 Memo。

  • Memo:存储候选执行计划 image.png

    • Group：等价计划集合
    • Group Expression 对应关系代数算子，由 Group 组成
    • 本质：AND/OR graph
    • 共享子树减少内存开销，记录搜索过的子树的最优执行计划（记为Group Winner）

image.png

  • Top-down动态规划搜索
  • 剪枝（Branch-and-Bound Pruning）：减少搜索空间
    • 已搜索完成的物理计划的代价最小值成为 Cost Upper Bound。当新的搜索分支的代价高于它时，不需继续搜索。初始 Cost Upper Bound 可由优化器根据启发式规则估算。

前沿趋势

对SQL优化器有更高的要求

  • 引擎架构的进化

    • 存算分离、 一体化（HTAP， HSAP， HTSAP）

  • Cloud

    • 云原生（Cloud Native）, Serverless

  • 湖仓一体

    • 数据仓库，数据湖，湖仓一体，联邦查询

  • DATA + AL

    • AI4DB

      • 自配置：智能调参、负载预测、负载调度
      • 自诊断和自愈合：软硬件错误、错误恢复和迁移
      • 自优化：统计信息估计、代价估计、学习型优化器，索引推荐，视图推荐

    • DB4AI

      • 内嵌人工智能算法（MLSQL，SQLFlow）
      • 内嵌机器学习框架（SparkML， Alink， dl-on-flink ）

avatar
文章
阅读
粉丝