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

用户9346006999033
127 阅读4分钟

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

SQL 查询优化器浅析

1. 大数据体系和 SQL

大数据体系全景图

image.png

SQL 流行的原因

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

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

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

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

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

SQL 处理流程

image.png

  • Parser

    1. 把文本变成抽象语法树结构（AST）
    2. 涉及词法分析阶段（拆分字符串，提取关键字，字符串，数值等）和语法分析阶段（把词条按照定义的语法规则组装成抽象语法树结构）
    3. 和编译原理课程里的“前端”知识相关

image.png

  • Analyzer

    1. 访问库/表元信息并绑定
    2. 判断 SQL 是否合理，比如数据库，表和列名是否存在，列的数据类型是否正确
    3. 将 AST 转换成逻辑计划树（在某些系统中这个工作由一个 Converter 完成）

image.png

逻辑计划树

image.png

查询优化

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

小结 image.png

2. 常见的查询优化器

常见查询优化器分类：RBO与CBO image.png

  • RBO （ rule-based ）

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

image.png

  • RBO 优化规则

    • 列裁剪
    • 谓词下推
    • 传递闭包
    • Runtime Filter（min-max filter，in-list filter，bloom filter）
    • Join 消除
    • 谓词合并

  • 局限性：

    • 无法解决多表连接问题
    • 无法确定和选择最优的分布式 Join/Aggregate 执行方式

RBO小结 image.png

  • CBO ( cost-based ) 概念 image.png 流程 image.png

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

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

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

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

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

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

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

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

  • RBO 统计信息

image.png

  • 统计信息

    • 表或者分区级别

    • 列级别：min、max、num nulls、num 等

    • 选择率（selectivity） ：对某个过滤条件，查询会从表中返回多大比例的数据

    • 基数（cardinality） ：表的 unique 行数，在查询计划中指算子需要处理的行数

    • 准确的选择率比代价模型更重要

  • CBO效果

image.png 当RBO能为查询找到最优计划时，CBO不会显示性能变化 约有16个查询在CBO下能得到更好的执行性能

image.png 加速比从2.2x ~ 8x

CBO小结

image.png 查询优化器小结

image.png

3. 查询优化器的社区开源实践

概览

image.png Volcano/Cascade 框架

image.png

image.png

  • Memo 本质是 AND/OR Graph，通过共享相同的子树减少内存开销，记录搜索过的子树的最优执行计划（winner）

  • Branch-and-Bound Pruning

    已搜索完成的物理计划的代价最小值成为 Cost Upper Bound。当新的搜索分支的代价高于它时，不需继续搜索。初始 Cost Upper Bound 可由优化器根据启发式规则估算。

  • 小结

image.png

4. SQL 相关前沿趋势

image.png

  • 存储计算分离
  • HSAP, HTAP, HTSAP
  • Cloud Native, Serverless

  • 数据仓库，数据湖，湖仓一体，联邦查询 智能化

  • AI4DB

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

  • DB4AI

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

image.png 前沿趋势小结

image.png

  • 引擎架构进化，云原生，湖仓一体对SQL查询优化器提出新要求

  • AI加持 学习型优化器不断进化

  • SQL查询优化器仍是不可缺少的组件

SQL Optimizer 课程总结

image.png

avatar
文章
阅读
粉丝