SQL_Optimizer解析 | 青训营笔记

SQL_Optimizer解析 | 青训营笔记

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

以下是大数据体系的一个总体构架

SQL查询优化器

1. 大数据体系和SQL：介绍大数据体系和SQL的处理流程，重点介绍SQL在分布式环境下的处理
2. 常见的查询优化器：介绍查询优化器的分类，重点介绍RBO和CBO的原理
3. 社区开源实践：介绍查询优化器在社区的开源实践，重点介绍Apache Calcite项目
4. 前沿趋势：介绍SQL引擎的前沿趋势，重点介绍AI和DB的结合

1.1 大数据体系 - One SQL Rules big data all

目的：使SQl可以处理所有的大数据体系

1.2 SQL的处理流程

1.3 SQL的处理流程 - Paser

• String -> AST(Abstract syntax tree)
  • 词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
  • 语法分析：将token组成AST node，最终得到一个AST
• 实现：递归下降(ClickHouse), Flex和Bison(PostgreSQL), JavaCC(Flink), Antlr(Presto, Spark)

1.3 SQL的处理流程 - Analyzer和Logical Plan

• Analyzer
  • 检查并绑定Database, Table, Column等元信息
  • SQL的合法性检查，比如min/max/avg的输入是数值
  • AST -> Logical Plan
• Logical Plan
  • 逻辑地描述SQL对应的分步骤计算操作
  • 计算操作：算子（operator）

1.3 SQL的处理流程 - 查询优化

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

1.3 SQL的处理流程 - Physical Plan和Executor

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

01.小结

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

02.常见的查询优化器

2.1 查询优化器的分类

• Top-down Optimizer
  • 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
  • 例子：Volcano/Cascade, SQLServer
• Bottom-up Optimizer
  • 从零开始，由下往上遍历计划树，找到完整的执行计划
  • 例子：System R, PostgreSQL, IBM DB2

---

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

2.2 常见的查询优化器

• 查询优化器的分类
  • RBO(Rule-based Optimizer)
  • CBO(Cost-based Optimizer)

2.2.1 RBO - 关系代数

2.2.2 RBO - 优化原则

2.2.3 RBO - 列裁剪

2.2.4 RBO - 谓词下推

2.2.5 RBO - 传递闭包

2.2.5 RBO - Runtime Filter

2.2 RBO小结

• 主流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个连接顺序

2.3 CBO - 概念

• 使用一个模型估算执行计划的代价，选择代价最小的执行计划
  • 执行计划的代价等于所有算子的执行代价之和
  • 通过RBO得到（所有）可能的等价执行计划
• 算子代价： CPU，内存，磁盘I/O，网络I/O等代价
  • 和算子输入数据的统计信息有关：输入、输出结果的行数，每行大小...
    • 叶子算子Scan：通过统计原始表数据得到
    • 中间算子：根据一定的推导规则，从下层算子的统计信息推到得到
  • 和具体的算子类型，以及算子的物理实现有关
  • 例子：Spark Join算子代价 = weight * row_count + (1.0 - weight) * size

2.3.1 CBO - 统计信息

• 原始统计表信息
  • 表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
  • 列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等
• 推导统计信息
  • 选择率(selectivity)：对于某一个过滤条件，查询会从表中返回多大比例的数据
  • 基数(cardinality)：在查询计划中常指算子需要处理的行数

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

2.3.1 CBO - 统计信息的收集方式

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

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

• 动态采样

2.3.1 CBO - 统计信息推导规则

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

• Filter Selectivity（这里有点像统计probability的基本概念）
  • AND条件：fs(a AND b) = fs(a) * fs(b)
  • OR条件：fs(a OR b) = f(a) + fs(b) - (fs(a) * fs(b))
  • NOT条件：fs(NOT a) = 1.0 - fs(a)
  • 等于条件(x = literal)
    • literal < min && literal > max：0
    • 1/NDV
  • 小于条件(x < literal)
    • literal < min：0
    • literal > max：1
    • （literal - min）/（max - min）

cardinality(FILTER) = cardinality(A) * selectivity(FILTER)

2.3.1 CBO - 统计信息的问题

• 假设列和列之间是独立的，列的值时均匀分布 ——> 这个假设经常与现实不符

• 考虑一个汽车数据库automobiles，有10个制造商，100个车型，filter为“制造商 = ‘比亚迪’ 且车型 = ‘汉‘”，根据独立性的均匀分布假设，则selectivity = 1/10 * 1/100 = 0.001，但是’比亚迪‘和’汉‘是相关联的，实际selectivity = 1/100 = 0.01。 ——> 用户指定或者数据库自动识别相关联的列

• 考虑到中国人口数据库，性别，年龄，数量都不是均匀分布 ——> 直方图（Historgram）📊

2.3.2 CBO - 执行计划枚举

• 单表扫描：索引扫描（随机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个连接顺序

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

2.3.2 CBO 执行计划枚举 - 动态规划

2.3.2 CBO 效果 - TPC-DS Q25

2.3.2 CBO 效果 - TPC-DS

2.3 CBO 小结

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

02. 小结

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

03. 社区开源实践

• 概览

3.1 社区开源实践 - Apache Calcite 概览

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

3.2 社区开源实践 - Calcite RBO

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

3.3 社区开源实践 - Calcite RBO

• VolcanoPlanner
  • 基于Volcano/Cascade框架
  • 成本最优假设
  • Memo：存储候选执行计划
    • Group：等价计划合集
  • Top-down动态规划搜索

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

• VolcanoPlanner
  • Group winner：目前的最优计划

• VolcanoPlanner
  • 剪枝（Branch-and-bound pruning）：减少搜索空间

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

03. 小结

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

04. 前沿趋势 - Big Data，Big Money

04. 前沿趋势 - DATA + AI

• AI4DB
  • 自配置
    • 智能调参（OtterTune，QTune）
    • 负载预测/调度
  • 自动断和自愈合：错误恢复和迁移
  • 自优化
    • 统计信息估计（Learned cardinalities）
    • 代价估计
    • 学习型优化器（IBM DB2 LEO）
    • 索引/视图推荐
• DB4AI
  • 内嵌人工智能算法（MLSQL，SQLFlow）
  • 内嵌机器学习框架（SparkML，Alink，dl-on-flink）

04. 小结

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

课程总结