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

JHoves
239 阅读11分钟

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

一、大数据体系

1.1 大数据技术

  • 大致可分为三个核心:

  • 数据接入部分: Kafaka、 hdfs、 hive等

  • 数据计算部分: spark flink mapreduce等

  • 结果存储部分： hbase mysql elasticsearch等

完整的大数据项目会有更多组件来维护系统的运行以及功能的拓展，下面是比较详细的大数据体系图；其中分析引擎是数据处理的核心部分，目前大多数分析引擎都是以SQL作为更高抽象层次的计算入口，SQL已经成为了一个趋势以及很多框架的一个接口。

image-20220724203635126.png

1.2 相关知识

  • 批式计算（batch computing）：统一收集数据，存储到数据库中，然后对数据进行批量处理的数据计算方式，非实时；
  • 流式计算（stream computing）：对数据流进行处理，实时计算；
  • 交互分析引擎（interactive computing）：软件实时接收用户数据输入；
  • YARN：Yarn是Yet Another Resource Negotiator的缩写，在Hadoop生态当中，作为通用的资源管理系统与调度平台，去实现CPU、内存、磁盘、网络等资源进行统一管理与拆分，以容器的形式，为上层应用提供统一的资源服务；
  • Kubernetes：简称 k8s或者 “kube”，是一个开源的Linux 容器自动化运维平台，它消除了容器化应用程序在部署、伸缩时涉及到的许多手动操作；

1.3 SQL

大多数大数据计算引擎都支持 SQL 作为更高抽象层次的计算入口

  • Hive：把sql解析后用MapReduce跑（HQL）
  • SparkSQL：把sql解析后用Spark跑，比hive快点
  • FlinkSQL：FlinkSQL 则是基于Apache Calcite实现了标准的SQL，可以通过编写SQL的方式进行Flink数据处理
  • Phoenix：一个绕过了MapReduce运行在HBase上的SQL框架
  • Drill/Impala/Presto：交互式查询
  • Druid/Kylin：olap 预计算系统

二、SQL处理流程

image.png 流程：SQL语句首先通过Parser解析器进行语法和词法分析，将String文本变为抽象语法树(AST)，再由Analuzer将AST转换为逻辑计划树(Logical Plan)，接着Optimizer对逻辑计划书进行分析优化后得到查询计划(Physical Plan)，最后给Executor执行；

2.1 Parser(词法分析)

  • Parser的功能就是将文本变成抽象语法树(AST)，也就是将一个完整的SQL语句变为一个个关键词组成的树；

  • String → AST（abstract syntax tree）

    • 词法分析：拆分字符串，得到关键词，数值常量，字符串常量等token

    • 语法分析：将token组成AST node，最终得到一个AST

  • 实现：递归下降(ClickHouse)，Flex和Bison (PostgreSQL)，JavaCC(Flink)，Antlr (Presto, Spark)

image.png

2.2 Analyzer(分析器)和 Logical Plan(逻辑计划树)

SQL语句⾸先通过Parser被解析成AST语法树，⽽这棵树是未解决的逻辑计划的，而逻辑计划树通过 Analyzer模块借助于Catalog中的表信息解析成为AST语法树，这棵树是解决的逻辑计划；

  • Analyzer

    • 检查并绑定Database、Table、Column等元信息；

    • 对SQL的合理性进行判断，比如输入的数据类型是否正确；

    • 将AST转换成逻辑计划树

  • Logical Plan

    • 逻辑地描述SQL对应的分步骤计算操作
    • 计算操作：算子（operator）——树中的每个节点都是一个算子
    • 边代表数据流向从子节点到腹父节点

image.png

2.3 Optimizer(查询优化)

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

2.4 Physical Plan和 Executor(物理执行计划)

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

image.png

2.5 小结

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

三、常见的查询优化器

3.1 查询优化器的分类

①按照遍历树的顺序分类：

  • Top-down Optimizer

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

    • 例子：Volcano/Cascade，SQLServer

  • Borron-up Optimizer

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

②根据优化方法分类：

  • Rule-base Optimizer（RBO）
    • 根据关系代数等语义，重写查询
    • 基于启发式规则
    • 会访问表的元信息（catalog），不会涉及具体的表数据
  • Cost-based Optimizer (CBO)
    • 使用模型估算执行计划的代价，选择代价最小的执行计划

3.2 RBO(基于规则的优化器)

3.2.1 关系代数

  • 运算符：Select(σ), Project(π*), Join(⋈), Rename(ρ), Union(∪)
  • 等价变换：结合律、交换律、传递性

image.png

  • 例子

image.png

3.2.2 优化原则

  • I/O：读更少的数据且读取速度更快
  • 网络：传输数据更少且速度更快
  • CPU和内存：处理的数据更少且速度更快

3.2.3 优化方法

① 列裁剪

  • 对于没用到的列，在运行和计算中无需去读取，以免浪费所谓的IO
  • 例子：从扫面整张表优化为只扫描某些列

image.png

② 谓词下推

  • 谓词是WHERE关键字后的条件
  • 在某些查询条件下可以将过滤条件进行下推，实现数据提前过滤，减少查询时间
  • 下推取决于 JOIN 的类型，不同 JOIN 的下推方式实现不同

通过where user.siteId>123条件提前过滤掉不符合的数据

image.png

③ 传递闭包

  • 根据表达式的等价关系和过滤条件，推导出新的过滤条件，实现进一步的过滤

因为 pv 表和 user 表是通过pv.siteld = user.siteld进行连接的，，而 WHERE 关键字后的条件是 user.siteld > 123 ，所以可以推断出pv.siteld > 123。

image.png

④ Runtime Filter

  • 运行时才能产生，在数据库中广泛使用
  • 过程：在运行中扫描单边时对数据进行简单统计（min-max，in-list，bloom filter）得到扫描的数据范围并提供给另外一边，使得扫描另外半边表时可以得知需要的范围，进行提前过滤，节约数据传输；对于一个JOIN，如果能在查询端提早过滤，那么在运行JOIN时就很快，因为无需计算很多hashcode。
  • min-max：经过过滤器后知道单表的max和min的范围，也就是数据范围。那么在运行的时候就可以传递给另外半边进行数据范围的过滤；缺点就是只能处理数据比较集中的情况，如min-max数据偏移过大基本和没有 filter 一样
  • in-list：用一个集合记录包含的范围，只能处理数据可取值相对较小的情况；缺点就是如果in-list中数据量过大时，网络传输的带宽和可靠性开销大
  • 布隆过滤器：固定大小，大小不随集合的大小而变化，标记为不存在则不存在，标记为存在则有可能存在或者可能不存在

3.2.4 小结

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

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

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

    • 单表扫描：索引扫描（随机I/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.png

3.3 CBO(基于代价的优化器)

3.3.1 概念

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

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

    • 通过 RBO 得到（所有）可能的等价执行计划

  • 算子代价包含 CPU，cache misses，memory，disk I/O，network I/O 等代价

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

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

    • 和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）

    • 例子：Sprak Join 算子代价 = weight∗row_count+(1.0−weight)∗size

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

image.png

3.3.2 统计信息

原始表统计信息：

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

推导统计信息：

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

3.3.3 统计信息的收集方式

image.png

3.3.4 统计信息推到规则

image.png

3.3.5 统计信息的问题

  • 假设列列独立，列值均匀分布。这个假设与现实不符
  • 列列之间可以关联：用户指定/数据库自动识别相关联的列
  • 列值不是均匀分布：使用直方图处理

3.3.6 执行计划枚举

  • 通常使用 贪心算法 或 动态规划 选出最优的执行计划
  • 例如有三个表连接的操作，通过求解两个表连接的代价，动态地得到最小的三个表执行计划代价。

3.3.7 小结

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

3.4 RBO和CBO小结

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

四、社区开源实践

4.1 概述

image.png

4.2 Apache Calcite概览

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

image.png

4.3 Calcite RBO

  • HepPlanner

    • 优化规则（Rule）

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

    • 内置有100+优化规则

      • 四种匹配规则
        • ARBITRARY/DEPTH_FIRST : 深度优先
        • TOP_DOWN：拓扑顺序
        • BOTTOM UP: 与TOP DOWN 相反

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

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

image.png

4.4 Calcite CBO

  • VolcanoPlanner

    • 基于 Volcano/Cascade 框架

    • 成本最优假设

    • Memo：存储侯运执行计划（相对应缓存表/dp表）

      • Group：等价计划集合

    • Top-down 动态规划搜索

image.png

  • 应用Rule搜索候选计划
  • Memo
    • 本质：AND/OR graph
    • 共享子树减少内存开销

image.png

  • Group winner：记录目前的最优计划

image.png

  • 剪枝（branch-and-bound pruning）减少搜索空间，即当前策略cost大于目前最优计划直接结束，无需继续下推计划树

image.png

  • Top-down 动态规划搜索：选择 winner 构建最优执行计划

image.png

4.5 小结

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

五、SQL 相关的前沿趋势

5.1 概览

image.png

5.2 DATA + AI

  • AI4DB
    • 自配置：
    • 自诊断和自愈合：软硬件错误、错误恢复和迁移
    • 自优化：
  • DB4AI
    • 内嵌人工智能算法（MLSQL，SQLFlow）
    • 内嵌机器学习框架（SparkML， Alink， dl-on-flink ）

5.3 小结

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