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

一只兔兔
313 阅读10分钟

这是我参与「第五届青训营 」伴学笔记创作活动的第 16 天

这里讲的主要是SQL查询优化器的解析。多个大数据计算引擎都支持 SQL 作为更高抽象层次的计算入口，查询优化器是数据库系统必备组件，对于 SQL 作业的执行性能起着至关重要的作用。本节课将从宏观维度介绍大数据体系构成，以及对 SQL 处理流程进行拆解，其中重点介绍有关 SQL 在分布式环境下如何展开数据处理。

思考：为啥把SQL优化器放在最前面讲呢？

  • 大数据体系简介：

image-20230130101105639

消息队列，主要是为了解耦存储与计算昂！！！

大数据体系和SQL

大数据体系

image-20230130101406337

SQL已经是大数据处理的事实上的接口。MapReduce, Hive, Flink -> 都在逐渐向SQL靠，One SQL rules big data all

  • 为啥把SQL优化器放在最前面讲呢？ -> 大数据处理的入口！！！

SQL的处理流程

  • SQL -> Parser -> AST -> Analyzer -> Logical Plan -> Optimizer -> Physical Plan -> Executor

Parser

image-20230130103241857

看到Antlr了hhhhh，毕业设计用的就是这个，词法/语法分析昂！！！

从输入的语句 -> AST和对应的数据结构

Analyzer 和 Logical Plan

  • 一个小例子：

image-20230130103342657

查询优化

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

大数据查询更要命！！！非常多表的连接，数据处理，性能很重要！！！查询优化就是大脑昂！！！

Physical Plan 和 Executor

image-20230130104337281

尽可能少去网络传输数据，节点本地处理是最快的，可能还要考虑亲和性之类的昂！！！

小结

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

常见的查询优化器

查询优化器的分类

  • 方法一：

    • Top-down Optimizer

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

    • Bottom-up Optimizer

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

  • 方法二：

    • Rule-based Optimizer (RBO)

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

    • Cost-based Optimizer (CBO)

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

RBO

关系代数

image-20230130104947108

优化原则

image-20230130105042528

列裁剪

思想：尽早过滤数据，使数据尽可能的少

image-20230130105125491

把用到的列往下传递，做一个裁剪，Scan的时候，减少扫描的数据量

谓词下推

思想：尽早执行谓词，过滤数据，使数据尽可能的少

image-20230130105247066

下推：要能够保证结果一样才行

思考：哪些能推，哪些不能推。Inner Join & Outer Join?

传递闭包

思想：一些过滤条件 + 表达式等价关系 -> 新的过滤条件，更有效，减少数据量

image-20230130105414733

新的Filter

Runtime Filter

思想：尽早过滤不必要的数据，运行时生成Filter，例如JOIN的时候，得到特点和特性，然后传递给其他的查询，动态运行时生成过滤器，就很好用昂！！！

image-20230130105552824

有多种不同的Runtime Filter，适用于很多不同的场景昂！！！

小结

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

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

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

    • 单表扫描:索引扫描( 随机1/O) vs. 全表扫描( 顺序I/O)

      • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描

    • Join 的实现: Hash Join vs. SortMerge Join

      • 两表Hash Join:用小表构建哈希表——如何识别小表?

      • 多表Join:

        • 哪种连接顺序是最优的?

        • 是否要对每种组合都探索?

          • N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
          • e.g.N= 10->总共3, 628, 800个连接顺序

      • ...

image-20230130110029294

CBO

概念

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

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

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

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

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

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

    • 例子: Spark Join算子代价= weight * row_count + (1.0 - weight) * size

统计信息 + 推导规则 -> 计算算子代价 -> 计算执行计划代价 -> 执行计划枚举

统计信息

  • 原始表统计信息

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

  • 推导统计信息

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

准确的cardinality, 远比代价模型本身重要。一 “How Good Are Query Optimizers, Really?”

收集方式

image-20230130110438364

推导规则

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

image-20230130110608200

问题

image-20230130110733730

不符合怎么办？用户可以手动指定！！！直方图也可以用于使用和处理昂！！！

执行计划枚举

  • 单表扫描:索引扫描(随机1/O) vs 全表扫描(顺序/O)

    • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描

  • Join 的实现: Hash Join vs. SortMerge Join

  • 两表Hash Join:用小表构建哈希表一如何识别小表 ?

  • 多表Join:

    • 哪种连接顺序是最优的?

    • 是否要对每种组合都探索?

      • N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
      • e.g. N= 10->总共3, 628, 800个连接顺序
  • ...

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

动态规划

image-20230130111409911

CBO效果

TPC-DS Q25

image-20230130111458472

image-20230130111540384

TPC-DS

image-20230130111602837

Source: www.databricks.com/blog/2017/0…

小结

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

社区开源实践

概览

image-20230130111812047

Apache Calcite概览

image-20230130112424115

Calcite RBO

image-20230130112624738

Calcite CBO

image-20230130112647255

image-20230130112800854

image-20230130112940523

image-20230130113006340

不断更新上限，来减少搜索空间昂！！！

image-20230130113105289

小结

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

前沿趋势

Big Data, Big Money

  • 2021年初，Starburst Data公司为其Trino系统(以前的Presto SQL )筹集了1亿美元。
  • 2021年6月，Apache Kafka商业化公司Confluent登陆NSDAQ，首日涨25%，市值超110亿美元。
  • 2021年8月，Apache Iceberg的创建者Ryan Blue正式成立围绕Iceberg的商业公司Tabular.
  • 2021年8月，Spark背后的公司Databricks宣布获得16亿美元融资，最新估值飙升至380亿美元。
  • 2021年9月，ClickHouse的创建者Alexey正式成立公司: ClickHouse, Inc，获得了5000万美元融资。
  • 2021年10月，基于Apache Pulsar的商业化公StreamNative宣布获得2300万美元A轮融资。

概览

image-20230130113817610

数据和存储在一台机器上，有点麻，拓展不明确。

引擎架构的进化

  • 数据和存储分离，方便两个方面单独扩容，方便处理。
  • 一体化：两个融合，分析型数据和事务性数据融合在一体，支持多种场景的融合。

云原生

离不开存算分离

  • K8s， serveless，负载动态调整（主要是计算节点）

湖仓一体

数据仓库的概念：关系型数据 -> 数据仓库

  • 对于原始数据进行了处理，但是原始数据可能还要用，有一些元数据、元信息之类的。
  • 湖仓一体：数据仓库 + 原数据以文件的形式存下来（数据湖：数据不规整的，没有固定结构）

DATA + AI

  • AI去预测查询优化，做查询优化的调整昂！！！

DATA + AI

  • AI4DB

  • DB4AI

    • 内嵌人工智能算法(MLSQL, SQL Flow)
    • 内嵌机器学习框架( SparkML, Alink, dl-on-flink )

总结

  • 大数据创业如火如荼，SQL 查询优化器仍然是必不可少的一个重要组件
  • 引擎架构的进化、云原生、湖仓一体等对SQL查询优化器有新的要求和挑战
  • AI加持，学习型查询优化器在不断进化

课程总结

image-20230130114507024

References:

  1. CBO is great: www.databricks.com/blog/2017/0…
  2. 智能调参（OtterTune，QTune）
  3. 统计信息估计（ Learned cardinalities ）
  4. 学习型优化器（IBM DB2 LEO）
avatar
文章
阅读
粉丝