SQL查询优化器 | 青训营笔记

用户5250551760071
144 阅读5分钟

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

首先是对大数据的总览

屏幕截图 2022-07-26 184823.png

大数据离不开SQL的学习 SQL的优势在于：

  • 有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库

  • 有 JDBC、ODBC 之类和各种数据库交互的标准接口

  • 有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人

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

    1. MapReduce -> Hive SQL
    2. Spark -> Spark SQL
    3. Flink -> Flink SQL

为了便于理解查询优化器的作用，我们先来梳理一下SQL的处理流程：

graph TD
Parser --> Analyzer -->Optimizer --> Executor

Parser:将SQL语句变成抽象语法树结构（AST）

Analyzer：首先判断SQL是否合理，再将AST转换为逻辑计划树（Logical Plan）

  • Logical Plan：所谓逻辑计划树，可以理解为逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划。树中每个节点是是一个算子，定义了对数据集合的计算操作（过滤，排序，聚合，连接），边代表了数据的流向，从孩子节点流向父节点。之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。 如图，便是一个逻辑计划树。

屏幕截图 2022-07-26 201523.png

！！Optimizer（优化器）：将Logical PLan转换为分布式的物理执行计划（Physical Plan）

为什么要优化？

  • SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做

  • 查询优化的目标是为 SQL 找到一个正确的且执行代价最小的执行计划

  • 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP 的

  • 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍

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

常见的查询优化器

查询优化器分类：Top-down Optimizer和Bottom-up Optimizer Rule Based Optimizer（RBO）和Cost-Based Optimizer（CBO）

RBO：基于关系代数等价规则对逻辑计划进行变换

优化原则：

  • 读数据更快更少(I/O)
  • 数据转换更快更少(Network)
  • 数据加工更快更少(CPU & Memory)

优化方式：

  • 列裁剪
  • 谓词下推
  • 传递闭包
  • Runtime Filter

优缺点：

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

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

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

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

-   分而治之，执行计划的代价等于所有算子的执行代价之和

-   通过 RBO 得到（所有）可能的等价执行计划（**非原地替换**）

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

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

    -   叶子算子 scan：通过统计原始表数据得到

        -   中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
        -   和具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）

-   使用动态规划枚举所有执行计划，选出执行代价（cost）最小的执行计划

  • 统计信息

    • 基表统计信息

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

    • 推导统计信息

      • 选择率（selectivity） ：对于某一个过滤条件，查询会从表中返回多大比例的数据
      • 基数（cardinality） ：基本含义是表的 unique 行数，在查询计划中常指算子需要处理的行数
  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心算法或动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要

社区开源实践

概览 屏幕截图 2022-07-26 215112.png

Apache Calcite

  • One size fits all
  • 模块化、插件化、稳定可靠
  • 支持异构数据模型：关系型 半结构化 流式 地理空间数据
  • 内置RBO和CBO

  1. Calcite RBO：HepPlanner

  2. Calcite CBO：VolcanoPlanner

  • 基于Volcano/Cascade框架
  • 成本最优假设
  • Memo：存储候选执行计划
  • Group Winner：目前的最优计划
  • Branch-and-bound pruning：剪枝，减少搜索空间
  • Top-down遍历：选择winner构建最优执行计划

前沿趋势

  • 引擎架构的进化，实现存储计算分离
  • 云原生
  • 湖仓一体
  • DATA+AI

DATA+AI:

  • AI4DB

    • 自配置：智能调参（OtterTune，QTune）、负载预测、负载调度
    • 自诊断和自愈合：软硬件错误、错误恢复和迁移
    • 自优化：统计信息估计（ Learned cardinalities ）、代价估计、学习型优化器（IBM DB2 LEO），索引推荐，视图推荐

  • DB4AI

    • 内嵌人工智能算法（MLSQL，SQLFlow）
    • 内嵌机器学习框架（SparkML， Alink， dl-on-flink ）

总结

屏幕截图 2022-07-26 221822.png

写在最后：

本文如有任何错误，欢迎批评指正~

内容主要参考了青训营周东炎老师SQL查询优化器浅析的PPT

链接：juejin.cn/post/712275… 来源：稀土掘金\

avatar
文章
阅读
粉丝