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

第一节：SQL 查询优化器浅析笔记

主要包括四个方面

一、 大数据体系和 SQL

1. 大数据体系全景图

2.sql语句的一生

1. SQL语句经过Parser将文本变成抽象语法结构数树（AST），Parser涉及词法分析阶段（拆分字符串，提取关键字，字符串，数值等）和语法分析阶段（将词条按照定义的语法规则组装成抽象语法树结构）
2. 抽象语法树结构（AST）再经过Analyzer输出逻辑计划树（Logical Plan），Analyzer包括访问数据库/表元信息并绑定，判断SQL是否合理（数据库、表名、列名、是否存在，列的数据类型是否正确等等）

逻辑计划树

所谓逻辑计划树，可以理解成逻辑地描述一个SQL如何一步步的执行查询和计算，最终得到执行结果的一个个分步骤地计划。树中的每一个节点是一个算子，定义了对数据集合的计算操作，边代表了数据的流向，从孩子节点流向父节点

上图sql语句的逻辑计划树

3. 逻辑计划树再经过 查询优化（Optimizer） 输出物理执行计划（Physical Plan）
4. Executor按照物理执行计划扫描和处理数据，充分利用机器资源

二、常见的查询优化器

1. 查询优化器分类

遍历树的顺序划分

Top-down Optimizer（微软使用）

从目标输出开始，由上往下遍历计划书，找到完整的遍历计划树，找到完整的最优执行计划

例子：Volcano/Cascade，SQLSwrver

Bottom-up Optimizer

从零开始，由下往上便利计划数，找到完整的执行计划

例子：System R（最早的数据库优化器）, postgreSQL，IBM DB2

优化方法划分

Rule-based Optimizer（RBO)

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

基于启发式规则

会访问表的元信息（catalog），不会涉及具体的表数据（data）

Cost-based Optimizer（CBO)

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

2.PRO介绍

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

关系代数

优化原则

• I/O 读更少的数据，读数据更快
• Network 传输数据更少更快
• CPU&Memory cpu指令数更少，内存占用更少

列裁剪

从上到下检查需要哪些列，将重复的列合并，到最后只需要scan只需要用到的列

谓词下推

在不影响结果的，把过滤算子尽可能的放在执行计划靠前的地方(尽可能的将过滤表达式移动至靠近数据源的地方)，以减少后续计算的数据量

传递闭包

通过user.siteId > 123 and pv.siteId = user.siteId 推出 pv.siteId > 123

Runtime Filter

Runtime Filter参考链接

RBO小结

3.CBO介绍

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

  • 分而治之，执行计划的代价等于所有算子的执行代价之和
  • 通过 RBO 得到（所有）可能的等价执行计划（非原地替换）

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

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

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

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

统计信息

• 原始表统计信息

表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等

列级别：min、max、num nulls、num not nulls、num distinct value等

• 推导统计信息

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

基数：在查询计划中长治算子需要处理的行数

统计信息的收集方式

统计信息推导规则

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

统计信息的问题

• 假设经常与现实不符
• 一些列经常是有关联性的，通过用户指定或者数据库自动识别相关联的列进行特殊处理
• 中国人口性别，年龄，数量并不均匀分布，使用直方图进行处理

CBO-执行计划枚举

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

动态规划:

Hash Join 和SortMerge Join两种连接方式，选出最低cost，将问题扩大，再进行三表连接，分别求出两种连接方式的代价，保留最好的，选出总的最优执行计划。

CBO小结

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

三、查询优化器的社区开源实践

概括

四、SQL 相关的前沿趋势

1. 引擎架构的进化，存储计算分离；一体化（HTAP,HSAP,HTSAP)
2. Cloud 云原生 severless
3. 湖仓一体 Queery Federation
4. DATA+AI

1.AI4DB

• 自配置
  • 智能调参（OtterTune，QTune）
  • 负载预测、调度
• 自诊断和自愈合：错误恢复和迁移
• 自优化：
  • 统计信息估计
  • 代价估计
  • 学习应优化器
  • 索引/试图推荐

2.DB4AI

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

五、总结