一、【计算】SQL Optimizer 优化解析 | 青训营笔记

炼丹之路
202 阅读8分钟

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

一、SQL Optimizer 优化解析 | 青训营笔记

1 大数据体系与SQL处理流程

1.1 大数据体系全景

image.png

1.2 SQL简介

1.2.1为什么学习SQL

SQL是学习后面分析引擎的入口，所以先学习SQL的处理流程

1.2.2 SQL的执行流程

image.png

  1. Parser

    String → 抽象语法树结构（AST）

    类似于 编译原理前端内容，有如下分析方式：

    • 词法分析：对字符串进行拆分和处理得到其中的关键词，常变量等
    • 语法分析：将token组成AST node，得到AST

image.png

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

  1. Analyzer​

    1. 检查并绑定Database, Table, Column 等元信息

    2. SQL 的合法性检查,比如min/max/avg的输入是数值

    3. AST -> Logical Plan

    4. 逻辑地描述SQL对应的分步骤计算操作

    5. 计算操作:算子(operator)

image.png

上述查询语句经过Parser与Analyzer后会被分解成计划树（逻辑划分，不考虑算法实现）

image.png

  1. Optimizer查询优化

  • 找到一个正确且执行代价最小的物理执行计划，查询优化是数据库中最重要也最复杂的模块

  • Plan Fragment【执行计划子树】

    利用数据的物理分布（即数据亲和性） 最小化网络数据传输

  1. Executor

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

1 小结：

  • 分析引擎（SQL）是大数据体系中占比最大，同时也最重要的一部分
  • SQL 需要依次经过Parser , Analyzer , Optimizer 和 Executor 的处理
  • 查询优化器是数据库的大脑,在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布,充分利用数据的亲和性
  • 查询优化器对查询语句进行初步解析后得到逻辑计划，并按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段，并发式调用实例资源执行

2常见的查询优化器

2.1查询优化器分类

  1. 基于遍历树的顺序分类

    • Top-down Optimizer：从目标输出开始,由上往下遍历计划树,找到完整的最优执行计划
    • Bottom-up Optimizer：从零开始,由下往上遍历计划树,找到完整的执行计划

  2. 基于规则的分类

    • RBO（基于经验）

      根据关系代数等价语义,重写查询

      基于启发式规则（基于经验）

      会访问表的元信息(catalog),不会涉及具体的表数据(data)

    • CBO（基于代价)

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

2.2 RBO

2.2.1 RBO 关系代数

image.png

image.png

2.2.2 RBO优化原则

  1. 优化IO读写
  2. 优化网络传输
  3. 优化CPU处理方式和内存占用

对于该查询语句，有以下四种优化方式

SELECT pv.siteld, user.name
FROM pv JOIN user
ON pv.siteld = user.siteld AND pv.userld = user.id
WHERE user.siteld > 123;

image.png

  1. RBO -列裁剪

    • 选取其中两列出来

image.png

  1. 谓词下推

image.png

  1. RBO -传递闭包

    • 根据已有条件以及等价关系 推导出新的查询条件，以减少查询量

image.png

  1. RBO - RUntime Filter

    先扫描user表，得到min与max范围并存储到 哈希表中，然后在扫描pv表时直接对应哈希表将无用数据过滤掉

image.png

2.2.3 RBO 优缺点总结

  • 优点：

    基于经验的优化原则，实现更加简单，优化速度快

  • 缺点：

    不能保证最优的执行计划

2.3 CBO的基本概念

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

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

    通过RBO得到(所有)可能的等价执行计划

  • 算子代价: CPU,内存,磁盘I/O,网络I/O开销等代价，和具体算子类型及物理实现有关

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

image.png

2.3.1 CBO统计信息

包含：

  • 原始表统计信息（表行列数，占用内存等）

  • 推导统计信息

    选择率：对于某一个过滤条件,查询会从表中返回多大比例的数据

    基数:算子需要处理的行数

准确的基数cardinality 远比代价模型本身重要

信息收集方式：

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

    CREATE TABLE REGION(
R_REGION KEY INT NOT NULL，
R_COMMENT VARCHAR(152)
R_NAME CHAR(25) NOT NULL，
) DUPLICATE KEY(R REGION KEY)
DISTRIBUTED BY HASH(R REGION KEY) BUCKETS 1
PROPERTIES("stats_columns"="R_NAME") ；

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

    ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name 1， column-name 2

  • 动态采样

    SELECT count( *) FROM table_name

信息推导规则：Filter Selectivity

  • 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 : 0

    1/ NDV(列中独立的互不相同的值的个数)

  • 小于条件(x< literal)

    literal < min: 0

    literal > max : 1

    ( literal - min ) / ( max- min )

问题与解决​

  • Q:假设列与列之间是独立的，列的值是均匀分布

    这个假设经常与现实不符，例如中国人口数据库

    A:可以用直方图处理

  • Q: 考虑一个汽车数据库 automobiles有10个制造商,100个车型اA1 0447filter为“制造商= '比亚迪'且车型= '汉'"根据独立性和均匀分布假设则 selectivity = 1/10 x 1/100 = 0.0010447但是'比亚迪和'汉'是相关联的实际 selectivity = 1/100 = 0.017.

    A:用户制定或者数据库自动识别相关的列

2.3.2 CBO 执行计划枚举

要解决的问题：

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

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

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

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

  • 多表Join:V哪种连接顺序是最优的?V是否要对每种组合都探索?

    N个表连接,仅仅是left-deep tree就有差不多N!种连接顺序

    eg： N = 10->总共3, 628, 800个连接顺序

基于动态规划 及 贪心策略

image.png

三表连接先分治 拆成两表连接，比较Hash join与SM join连接方式，选择较小开销的一个，然后以归并 连接成最终表（仍然从H join 与SM join选择最优的一个）

image.png

2.3 CBO小结

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

2 总结

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

3社区开源实践Open-Source-Praxis der Community

3.1 Apache Calcite 概览

image.png

  • 支持模块化，插件化，稳定可靠
  • 支持异构数据模型：关系型、半结构化、流式模型、地理空间数据

3.2 Calcite RBO

image.png

  • HepPlanner

    • 优化规则：匹配表达式子树，得到新的表达式
    • 100+优化规则，四种匹配规则（深度优先、拓扑排序）
    • 遍历所有规则
    • 优化速度快，实现简单，但不保证最优优化

3.3 Calcite CBO

image.png

  • VolcanoPlanner

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

3 总结

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

4 前沿趋势

  • 目前大数据平台对于SQL优化器的投资与研究都是比较火热的

4.1 趋势方向

  • 引擎架构的进化：存储计算分离、一体化（HTAP、HSAP、HTSAP）
  • Cloud 云原生（无服务、弹性伸缩）
  • 湖仓一体
  • DATA+AI

这些对SQL优化器都有新的要求

4.2 DATA + AI

4.2.1 AI4DB

  • 自配置

  • 智能调参( OtterTune , QTune )

  • 负载预测/调度

  • 自诊断和自愈合:错误恢复和迁移

  • 自优化:

    • 统计信息估计( Learned cardinalities _)
    • 代价估计
    • 学习型优化器( IBM DB2 LEO)
    • 索引/视图推荐

4.2.2 DB4AI

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

4 总结：

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

image.png

🌹写在最后💖： 路漫漫其修远兮，吾将上下而求索！伙伴们，明天见！🌹🌹🌹在这里插入图片描述

avatar
文章
阅读
粉丝