Day02：SQL Optimizer 解析 课程笔记（下）｜ 青训营笔记

Sefank
151 阅读11分钟

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

常见的查询优化器

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

优化器的分类

按 遍历计划树的顺序 分类：

  • 自顶向下的优化器（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 （Rule-based Optimizer）

根据关系代数推导等价语义，重写查询。

关系代数

常见的关系代数运算符：Select（σ\sigma）、Project（Π\Pi）、Rename（⋈\bowtie）、Union（∩\cap）

常用的等价代换：

  • Select：σp1∧p2∧⋯∧pn(R)=σp1(σp2(⋯(σpn(R))⋯ ))\sigma _{p_1\land p_2\land \cdots \land p_n}\left( R \right) =\sigma _{p_1}\left( \sigma _{p_2}\left( \cdots \left( \sigma _{p_n}\left( R \right) \right) \cdots \right) \right) （可拆性）

  • Join：R⋈S=S⋈RR\bowtie S=S\bowtie R（交换律），(R⋈S)⋈T=R⋈(S⋈T)\left( R\bowtie S \right) \bowtie T=R\bowtie \left( S\bowtie T \right) （结合律）

  • 等值连接时的传递性（A=B∧B=C→A=B=C=constantA=B \land B=C \rightarrow A=B=C= constant）
    利用这种传递性可以从连接运算中分离出Select运算

优化原则

  • 读取数据更少、更快（I/O）
  • 传输数据更少、更快（网络）
  • 处理数据更少、更快（CPU和内存）

优化方法

此处按照实现难度从低到高排序。

每一种优化方法都是基于前一优化方法的结果而进行的进一步优化，因此也可以把这些方法看做是优化步骤。

主流RBO实现一般都有几百条基于经验归纳得到的优化规则，这里只是列出了具有代表性的四种。

  1. 列裁剪
    读数据表时，只扫描需要的字段，尽早把不需要的列去除，减少 I/O 和内存占用，为后续优化带来便利
    【实现】在计划树中从上往下扫描，从每个算子中获取需要的字段，扫描完毕后获得整个计划树所需要的字段，最后在 SCAN 操作中予以限制
  2. 谓词下推
    将 FILTER 操作（按条件选取记录）提前执行，尽早把不需要的记录去除。
    谓词是指 SQL WHERE 子句中的表达式。
  3. 传递闭包
    利用等值连接时的传递性，将连接的数据表也提前进行 FLITER 操作，进一步优化了连接运算的速度。
    传递闭包的例子
  4. 运行时过滤（Runtime Filter）
    • 优化背景
      • 在大数据背景下，被连接的两个表通常是由不同的机器结点来读取，在连接时需要将一个表的数据传输到另一个表所在的机器结点。这意味着连接时的表数据过大时所引起的网络传输成本往往要比计算、筛选成本高出很多。
      • 在实践中，被连接的两个表中的一个通常在 FILTER 后记录数较小，另一个则很大。我们将小表所在的机器结点称为构造侧（Build Side），而将大表所在的机器结点称为探查侧（Probe Side）。
    • 优化目标：尽可能缩减探查侧表传入 JOIN 算子时的记录数
    • 优化做法：根据构造侧表的数据特征和分布，来对探查侧表进行数据裁剪。
      运行时过滤的示意图
      数据特征有多种形式：
      • min-max：当构造侧的表连接字段的值集中分布在一个区间内，且没有什么极端值时，可以计算这一区间的端点（最大/最小值）并传输给探查侧，在探查侧预先过滤出连接字段在这一区间内记录。
      • in-list：传输构造侧连接字段所有取值给探查侧。这种形式仅适合取值数量不多的时候使用。
      • bloom filter：构造侧构造一种名为“Bloom filter”（布隆过滤器）的数据结构，其具有空间和时间效率，传输给探测端检测用于检测某个值是否在构造侧的取值集合内。
        • 如果检测结果为是，该元素不一定在集合中；
        • 但如果检测结果为否，该元素一定不在集合中。

RBO的特点总结

  • 优点：实现简单，优化速度快
  • 缺点：不保证得到最优的执行计划
    • 单表扫描：索引扫描（随机I/O）v.s. 全表扫描（顺序I/O）
      • 如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描（此时索引扫描更慢）
    • Join 的实现：Hash Join vs. SortMerge Join（RBO 只能固定二选一）
    • 两表 Hash Join：用小表构建哈希表 → 如何识别小表？
      判断小表错误导致的问题
    • 多表 Join：
      • 哪种连接顺序是最优的？
      • 是否要对每种组合都探索？
        • NN 个表连接，仅仅是 left-deep tree 就有差不多 N!N! 种连接顺序
        • e.g. 当N=10N=10时，总共 3,628,800 种连接顺序

CBO （Cost-based Optimizer）

  • 思想：使用一个模型估算执行计划的代价，选择代价最小的执行计划
    • 执行计划的代价 = 所有算子的执行代价之和
    • 通过 RBO 得到（所有）可能的等价执行计划
      注：实践中不会遍历所有的情况，不然时间开销过大
  • 算子代价：CPU，内存，磁盘I/O，网络I/O等代价
    • 和算子输入数据的统计信息有关：输入、输出结果的行数，每行大小……
      • 叶子算子 SCAN：通过统计原始表数据得到
      • 中间算子：根据一定的推导规则，从下层算子向上不断统计推导得到
    • 和具体的算子类型，以及算子的物理实现有关
    • 例子：Spark Join 算子代价 = weight * row_count（查询行数） + （1.O-weight） * size（查询I/O大小），相当于weight用于调控CPU和I/O的相对重要性

CBO 处理流程：

flowchart LR
s1[统计信息+推导规则] --> s2[计算算子代价] --> s3[计算执行计划代价] --> s4[执行计划枚举]

统计信息

  • 原始表统计信息
    • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
    • 列级别：最小值、最大值、空值数、非空值数、不同值数（Num. of Distinct Value, NDV）、分布直方图 等
  • 推导统计信息
    • 选择率（Selectivity）：对于某一个过滤条件，查询会从表中返回多大比例的数据
    • 基数（Cardinality）：在查询计划中常指算子需要处理的行数

准确的基数远比代价模型本身重要。
-- How Good Are Query Optimizers, Really？

统计信息的收集方式

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

    CREATE TABLE REGION（
  R_REGIONKEY INT NOT NULL,
  R_NAME CHAR(25) NOT NULL,
  R_COMMENT VARCHAR(152)
) DUPUCATE KEY（R_REGIONKEY)
DISTRIBUTED BY HASHR(R_REGIONKEY) BUCKETS 1
PROPERTIES("stats_columns"="R_NAME")

    缺点：影响实时性能

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

    ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1,column-name2, ...

    缺点：统计信息有延迟，不是实时准确的信息

  • 动态采样：在执行业务 SQL 时同时执行统计用的 SQL

    SELECT count(*) FROM table_name

推导规则

假设列和列之间是独立的，列的值是均匀分布，那么选择率（Filter Selectivity, fs）计算公式如下：

  • AND 条件：fs(a AND b) = fs(a) * fs(b)
  • OR 条件：fs(a OR b) = fs(a) + fs(b) - (fs(a) * fs(b)
  • NOT 条件：fs(NOT a) = 1.0 - fs(a)
  • 等于条件（x = literal）
    • literal < min 或者 literal > max: fs = 0
    • 否则：fs = 1/NDV
  • 小于条件（x < literal）
    • literal < min: fs = 0
    • literal > max: fs = 1
    • 否则：fs = (literal - min) / (max-min)
  • 串联的 filter 系统的选择率： 串联的 filter 系统的选择率

但实际上，上面的假设通常是不成立的。例如：

  • 列不一定独立：汽车制造商=比亚迪 AND 车型=汉
    如果总共有 10 个制造商、100 种车型，按照上述公式 fs = 1/10 * 1/100 = 0.001，但由于车型为汉的一定是比亚迪的汽车，所以实际上 fs = 1/100 = 0.01。
    【解决方法】用户在数据库中指定或是通过某种机制自动识别相关联的列
  • 分布不一定是均匀分布：性别、年龄字段
    【解决方法】采用直方图方法

执行计划枚举

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

动态规划例子

如果要计算R、S、T三表连接的代价：

  1. 先连接其中两表，每种连接组合又按连接方式分为 Hash Join 和 SortMerge Join，舍弃成本过大的结果
    第1步示意图
  2. 将第1步的结果与第三表连接，每种连接组合又按连接方式分为 Hash Join 和 SortMerge Join，舍弃成本过大的结果
    第2步示意图
  3. 从整体上比较所有的方案，选择最佳结果
    第3步示意图

Ref：15445.courses.cs.cmu.edu/fall2021/

CBO 的效果

以 TPC-DS Q25 数据集为例：

关闭 CBO：

  • Shufle 数据量太大
  • 执行效率差

关闭CBO

开启 CBO：

  • 减少了 90% 的 Shufle 数据量
  • 3.4 倍的加速比

开启CBO

RBO 与 CBO 性能对比：

性能对比

  • 大概一半的查询都没显示出性能变化，这是因为 RBO 能为这些查询找到最优执行计划
  • 16 个查询在 CBO 下有更好的执行性能

相较于RBO的加速比：

加速比

总体性能平均提升 38%。

Ref: Spark Catalyst

社区开源实践

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

总体情况

数据库SQL Optimzer 选型
Hive、Flink、Alibaba MaxCompute 等基于 Apache Calcite，属于 Volcano/Cascade 框架
Greenplum、HAWQ自研 Orca，属于 Volcano/Cascade 框架
Alibaba Hologres（定位 HSAP）基于 Orca，属于 Volcano/Cascade 框架
TIDB自研，属于 Volcano/Cascade 框架
Spark自研，RBO+CBO
Presto自研，RBO+CBO
Doris自研，RBO+CBO
ClickHouse自研，RBO
Alibaba OceanBase自研，RBO+CBO

注：

  • Volcano/Cascade 不是一种真实的实现，只是描述了优化器应该怎么实现的，其中包括 RBO 和 CBO。
  • Apache Calcite 采用 Java 实现，Orca 采用 C++ 实现。
  • 主流的优化器基本都包含 RBO 和 CBO。

Apache Calcite

Calcite 的设计目的是提供一个统一的 SQL 查询引擎，采用模块化、插件化设计，稳定可靠。它只负责从 SQL 语句生成执行计划，不包含 Executor 的部分。内置了 RBO 和 CBO。

支持的异构数据模型：

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

架构：

Calcite 架构图

  • JDBC Client：提供原始 SQL 语句的 APP
  • Data Processing System：实际执行执行计划的系统
  • Expression Builder：在自定义系统值解析好自己自定义的语法后，可以使用该组件来传入 AST
  • Metadata Providers：从数据库中提取优化器所需要的元信息，例如列名称
  • Pluggable Rules：Calcite 允许以插件的形式自定义优化规则

Calcite 的 RBO：HepPlanner

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

优化的例子：

优化例子

Calcite 的 CBO：VolcanoPlanner

Volcano/Cascade 的精髓：Memo、动态规划、剪枝

  • 基于 Volcano/Cascade 框架
  • 成本最优假设
  • Memo：存储候选执行计划
    • Group：等价计划集合
  • 自顶向下的动态规划搜索

Memo

注：红色线表示数据依赖关系。

实现原理：

  • 首先应用优化规则搜索候选计划
  • Memo
    • 本质：AND/OR 图
    • 共享子树减少内存开销
  • Group Winner：记录当前最优计划
  • 自顶向下选择 Winner，就得到最优计划

Memo与Group 1

emo与Group 2

emo与Group 3

此外，CBO中还采用了剪枝（Branch-and-bound pruning）技术以减少搜索空间。

剪枝

此图中，我们已知 Hash JOIN（A×B）子树的代价是 350，当另外的执行计划计算到 Hash JOIN（B×A）子树的代价超过 350 时，就不在计算后续的代价了，以此子树构造的执行计划也就不需要进一步分析了，因为这个子树比已知的计划还要糟糕。

前沿趋势

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

  • 引擎架构的进化
    • 存储/计算分离
    • 事务/分析一体化（HTAP、HSAP、HTSAP）
  • 与云体系匹配：根据实时负载按需分配资源
    • 云原生（k8s）
    • Serverless
  • 湖仓一体：Query Federation
    • 数据仓库：存储关系型/结构化数据，通常需要预先定义数据模型
    • 数据湖：存储业务产生的原始文件，通常是非结构化的、不规则的数据
  • DATA + AI
    • AI for DB
    • DB for AI

这些需求都对 SQL 优化器提出了新要求。

AI for DB

使用 AI 帮助数据库实现更好的性能。

  • 自配置
  • 自诊断和自愈合：错误恢复和迁移
  • 自优化

DB for AI

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