这是我参与「第四届青训营」笔记创作活动的的第1天
SQL查询优化器浅析
一.前言
大数据体系
二.目录
01.大数据体系和SQL
介绍大数据体系和SQL的处理流程，重点介绍SQL在分布式环境下的处理
02.常见的查询优化器
介绍查询优化器的分类，重点介绍RBO和CNO的原理
03.社区开源实践
介绍查询优化器在社区的开源实践，重点介绍Apache Calcite项目
04.前沿趋势
介绍SQL引擎的前沿趋势，重点介绍AI和DB的结合
三.具体内容
01.大数据体系和SQL
▪️ 大数据体系中的SQL
▪️ SQL的处理流程
1.1 大数据体系 - One SQL rules big data all
1.2 大数据体系和SQL
▪️ 大数据体系中的SQL
▪️ SQL的处理流程
1.3 SQL的处理流程
- SQL的处理流程 - Parser
▪️ String -> AST (abstract syntax tree)
(1)词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token
(2)语法分析：将token组成AST node,最终得到一个AST
▪️实现：递归下降（ClickHouse）,Flex和Bison（PostgreSQL）,JavaCC(Flink),Antlr(Presto,Spark)
- SQL的处理流程 - Analyzer 和 Logical Plan
▪️ Analyzer
(1) 检查并绑定Database,Table,Column等元素信息
(2) SQL的合法性检查，比如min/max/avg的输入是数值
(3) AST -> Logical Plan
▪️ Logical Plan
(1) 逻辑地描述SQL对应的分步骤计算操作
(2) 计算操作：算子（operator）
3）SQL的处理流程 - 查询优化
▪️ SQL是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做
▪️ 目标：找到一个正确且执行代价最小的物理执行计划
▪️ 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是NP的
▪️ 因为不同执行方式的性能差别可能有成百上千倍
4）SQL的处理流程 - Physical Plan 和 Executor
▪️Plan Fragment:执行计划子树
（1）目标：最小化网络数据传输
（2）利用上数据的物理分布（数据亲和性）
（3）增加Shuffle算子
▪️ Executor
(1)单机并行：cache,pipeline,SIMD
(2)多机并行：一个fragment对应多个实例
1.4 小结
（1）One SQL rules big data all
(2)SQL需要依次经过Parser,Analyzer,Optimizer和Executor的处理；
(3)查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
（4)查询优化器需要感知数据分布，充分利用数据的亲和性；
（5）查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段
02.常见的查询优化器
▪️ 查询优化器的分类
▪️ RBO （Rule-based Optimizer）
▪️ CBO （Cost-based Optimizer）
2.1 查询优化器分类
- 第一种分类方法
▪️Top-down Optimizer
(1) 从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
（2）例子：Volcano/Cascade,SQLServer
▪️Bottom-up Optimizer
(1) 从零开始，由下往上遍历计划树，找到完整的执行计划
（2） 例子：System R,PostgreSQL,IBM DB2
2）第二种分类方法
▪️ Rule-based Optimizer (RBO)
(1)根据关系代数等价语义，重写查询
(2)基于启发式规则
(3)会访问表的元信息（catalog），不会涉及具体的表数据（data）
▪️Cost-based Optimizer (CBO)
(1)使用一个模型估计执行计划的代价，选择代价最小的执行计划
2.2.1 RBO - 关系代数
1）运算符：Select,Project,Join,Rename,Union等
2）等价变换：结合律，交换律，传递率
（1）Select:
(2) Join:
(3) ....
2.2.2 RBO - 优化原则
▪️ Read data less and faster(I/O)
▪️ Transfer data less and faster(Network)
▪️ Process data less and faster(CPU & Memory)
2.2.3 RBO - 列裁剪
2.2.4 RBO - 谓词下推
2.2.5 RBO - 传递闭包
2.2.6 RBO - Runtime Filter
2.2 RBO 小结
▪️主流RBO实现一般都有几百条基于经验归纳得到的优化原则
▪️优点：实现简单，优化速度快
▪️缺点：不保证得到最优的执行计划
1）单表扫描：索引扫描（随机I/O）vs.全表扫描（顺序I/O）
(1)如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
2）Join的实现：Hash Join vs. SortMerge Join
- 两表 Hash Join：用小表构建哈希表 —— 如何识别小表？
4)多表Join：
(1)哪种连接顺序是最优的？
（2）是否要对每个组合探索
i.N个表连接，仅仅是left-deep tree 就有差不多N！种连接顺序
ii.e.g.N = 10 -> 总共1，628，800 个连接顺序
5）...
2.3 CBO - 概念
▪️ 使用一个模型估算执行计划的代价，选择代价最小的执行计划
1）执行计划的代价等于所有算子的执行计划代价之和
2）通过RBO得到（所有）可能的等价执行计划
▪️算子代价：CPU，内存，磁盘I/O,网络I/O等代价
1）和算子输入数据的统计信息有关：输入、输出结果的行数，每行大小...
(1)叶子算子Scan:通过统计原始表数据得到
(2)中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
2）和具体的算子类型，以及算子的物理实现有关
3）例子：Spark Join算子代价=weight * row_count +(1.0-weight)*size
2.3.1 CBO - 统计信息
▪️ 原始表统计信息
1）表或者分区级别：行数、行平均大小、表在磁盘中占用了多少字节等
2）列级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等
（1）选择率：对于某一个过滤条件，查询会从表中返回多大比例的数据
（2）基数：在查询计划中常指算子需要处理的行数
准确的cardinality，远比代价模型本身重要。
2.3.1 CBO - 统计信息收集方式
▪️ 在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息
▪️ 手动执行explain analyze statement,会触发数据库收集或者更新统计信息
▪️ 动态采样
SELECT count(*) FROM table_name
2.3.1 CBO - 统计信息推导规则
假设列和列之间是独立的，列的值是均匀分布
▪️ Filter Selectivity
1)AND条件：fs(a AND b) = fs(a)*fs(b)
2)OR条件：fs(a OR b) = fs(a)+fs(b)-(fs(a)*fs(b))
3)NOT条件：fs(NOT a) = 1.0-fs(a)
4)等于条件（x=literal）
(1)literal < min && literal > max: 0
(2) 1/NDV
5)小于条件（x < literal）
(1)literal < min: 0
(2)literal > max: 1
(3)(literal - min)/(max - min)
2.3.2 CBO - 执行计划枚举
▪️单表扫描：索引扫描（随机I/O）vs. 全表扫描（顺序I/O）
(1)如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描
▪️Join的实现：Hash Join vs. SortMerge Join
▪️两表Hash Join:用小表构建哈希表——如何识别小表？
▪️多表Join:
1)哪种连接顺序是最优的？
2)是否要对每种组合都探索
（1）N个表连接，仅仅是left-deep tree 就有差不多N！种连接顺序
（2）e.g.N=10 ->总共3，628，800个连接顺序
▪️...
通常使用贪心算法或者动态规划选出最优执行计划
2.3.2 CBO执行计划枚举 - 动态规划
2.3.2 CBO效果 - TPC-DC Q25
关闭CBO：
1.Shuffle数据量太大
2.执行效率差
2.3 CBO小结
▪️ CBO 使用代价模型和统计信息估算估算执行计划的代价
▪️ CBO使用贪心或者动态规划算法寻找最优执行计划
▪️在大数据场景下CBO对查询性能非常重要
03.社区开远实践
▪️社区开源实践 - 概览
▪️Apache Calcite
1）One size fits all:统一的SQL查询引擎
2）模块化、插件化、稳定可靠
3）支持异构数据模型
（1）关系型
（2）半结构化
（3）流式
（4）地理空间数据
4）内置RBO和CBO
5）HepPlanner
(1)优化规则（Rule）
i.Pattern:匹配表达式子树
ii.等价变换：得到新的表达式
（2）内置有100+优化规则
（3）四种匹配规则
i.ARBITRARY/DEPTH_FIRST:深度优先
ii.TOP_DOWN:拓扑顺序
iii.BOTTOM_UP:与TOP_DOWN相反
（4）遍历所有的rule,直到没有rule可以被触发
（5）优化速度快，实现简答，但是不保证最优。
（6）VolcanoPlanner
i.基于Volcano/Cascade框架
ii.成本最优假设
iii.Memo:存储候选执行计划
Group:等价计划集合
iv.Top-down:动态规划搜索
v.应用Rule搜索候选计划
Memo
本质：AND/OR graph
共享子树减少内存开销
VolcanoPlanner:
vi.Group winner:目前的最优计划
vii.VolcanoPlanner
剪枝：减少搜索空间
可行的Aggregate
▪️ 总的cost = 500
▪️ 自己的cost = 150
▪️ 孩子节点cost上限 = 350
Top-down 遍历：选择winner构建最优执行计划
03.小结
▪️主流的查询优化器都包涵RBO和CBO
▪️Apache Calcite是大数据领域很流行的查询优化器
▪️Apache Calcite RBO定义了许多优化规则，使用pattern 匹配
▪️Apache Calcite CBO基于Volcano/Cascade框架
▪️Volcano/Cascade的精髓：Memo、动态规划、剪枝
**04.前沿趋势
●2021年初，Starburst Data公司为其Trino系统(以前的PrestoSQL)筹集了1亿美元。
●2021年6月，Apache Kafka商业化公司Confluent登陆NSDAQ，首日涨25%，市值超110亿美元。
●2021 年8月，Apache Iceberg的创建者Ryan Blue正式成立围绕Iceberg的商业公司Tabular。
●2021年8月，Spark背后的公司Databricks宣布获得16亿美元融资，最新估值飙升至380亿美元。
●2021年9月，ClickHouse的创建者Alexey正式成立公司: ClickHouse, Inc,获得了5000万美元融资。
●2021年10月，基于Apache Pulsar的商业化公司StreamNative宣布获得2300万美元A轮融资。
对SQL优化器有新的要求
1）引擎架构的进化 存储计算分离 一体化(HTAP, HSAP, HTSAP)
2）Cloud 云原生 serverless
3）湖仓一体 Query Federation
4）DATA + AI
(1) AI4DB
▪️ 自配置 智能调参 负载预测/调度
▪️ 自诊断和自愈和：错误恢复和迁移
▪️ 自优化：统计信息估计、代价估计、学习型优化器、索引/视图推荐
(2) DB4AI
▪️ 内嵌人工智能算法（MLSQL、SQLFlow）
▪️ 内嵌机器学习框架（SparkML、Alink、dl-on-flink）
04.小结
1）大数据创业如火如荼，SQL 查询优化器仍然是必不可少的一一个重要组件
2）引擎架构的进化、云原生、湖仓-体等对SQL查询优化器有新的要求和挑战
3）AI加持，学习型查询优化器在不断进化
课程总体框架
