SQL Optimizer解析|青训营笔记
这是我参与「第四届青训营 」笔记创作活动的第1天
今天这一篇主要是针对SQL处理流程的个人认知和理解。如有错误还请多多斧正。
1. 大数据体系与SQL
首先，在大数据体系中，SQL有着相当广泛的应用，如下图
由上图可以看出，SQL在大数据体系中有着相当广泛的应用，其原因包括以下三点
- SQL相较于通用编程语言C、C++、python、JAVA等，更为简单便捷
- 很多系统都支持SQL，诸如MySql等
- 更重要的是目前SQL已经成为事实的标准接口
2. SQL处理流程
SQL处理流程如下图所示
在整个流程中，每一处分别进行以下的操作：
- Parser将传入的SQL经过词法分析后得到token，进而将token组成AST node并最终得到AST。
- Analyzer会检查Database、Table、Column等元信息，以及SQL的合法性检查后，并由AST得到Logical Plan
- 最后在Optimizer进行查询优化，也是SQL处理流程中的核心内容。接下来将围绕查询优化详细展开。
3. SQL查询优化
查询优化的目的是：为了实现SQL语句内容，需要找到一个正确而且执行代价最小的物理执行计划（Physical Plan)供Exceutor执行。
3.1 查询优化器的分类：
查询优化器分为两类：
- 基于规则的优化器(Rule-Based Optimizer，RBO)
根据关系代数等价语义，重写查询
基于启发式规则
会访问表的元信息，不会访问具体的表数据
- 基于代价的优化器(Cost-Based Optimizer，CBO)
使用一个模型估算执行的计划代价，选择代价最下的执行计划
3.2 RBO的优化原则
优化IO,即读取更少的数据和更快的速度速度
优化网络,即转换更少的的数据和更快的转换速度
优化CPU和内存,处理更少的数据和更快的处理速度
3.2.1 列裁剪
核心:只提取查询过程中用到得了列,而去掉没有用到的列.
3.2.2 谓词下推
核心:将过滤数据的条件下推至数据源的位置,提前过滤掉无用的数据
3.2.3 传递闭包
核心:根据关系表达式之间的逻辑条件,推导出新的过滤数据的条件
3.2.4 Runtime Filter
核心:根据其中一张表的条件,来对另一张表进行过滤数据
3.3 CBO优化规则
概念:使用一个模型估算计划执行的代价,选择代价最小的执行计划
执行计划的代价等于所有算子执行计划的代价之和
通过RBO得到所有可能的等价执行计划
流程如下图所示:
算子代价:
CPU,内存,磁盘IO,网络IO等代价
和算子输入数据的统计信息有关:输入,输出结果的行数,每行大小 和具体的算子类型,以及算子的物理实现有关
3.3.1 统计信息
统计信息包括:
原始统计信息
- 表或者分区级别
- 列级别
推导统计信息
- 选择率:对于某一个过滤条件，查询会从表中返回多大比例的数据
- 基数: 在查询计划中,算子需要处理的行数
3.3.2 统计信息的收集方式
收集方式包括三种:
-
在DDL里指定需要收集的统计信息,数据库会在数据写入时收集或者更新统计信息
缺点: 由于是在数据实时输入时进行收集,故会导致速度下降
-
手动执行explain analyze statement,触发数据库收集或者更新统计信息
ANALYZE TABLE table_name COMPUTE STATISTICS FOR COLUMNS column-name1,column-name2,...
缺点: 若已经查询数据却并未手动触发,则原信息表中保存的仍是过时信息,进而可能导致较差的执行方式.
-
动态采用
SELECT count(*)FROM table_name
3.3.3 统计信息推导规则
假设列和列之间是独立的,列的值是均匀分布 Filter Seclectivity
- AND条件:
fs(a AND b)=fs(a)-f(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)
3.3.4 执行计划枚举
通常通过贪心或者动态规划进行
RBO与CBO对比
RBO：
主流RBO一般有几百条基于经验得到的优化规则
实现简单，速度快
不保证得到最优的执行计划
CBO：
使用代价模型和统计信息估算执行计划的代价
使用贪心或者动态规划得到最优的执行计划
大数据场景下CBO对查询性能非常重要
综上，首先是介绍大数据体系与SQL的关系，并简介了SQL的处理流程和操作，同时针对其中的查询优化器环节进行详细的展开和说明。上述即为我个人对于SQL的一些入门的理解和认知，部分观点参考青训营课程老师的讲解，谢谢。
