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

「SQL Optimizer解析」第四届字节跳动青训营

同时这也是课表的第1天课程，PC端阅读效果更佳，点击文末：阅读原文即可。

bigdata-basic

本章课程目录

课前预习

了解大数据体系和 SQL

了解生产系统中的大数据体系
- 可以看一下市场上云厂商（火山引擎，阿里云，腾讯云，华为云，Google Cloud，Microsoft Azure）提供的大数据相关的产品，包括计算、存储、调度、应用等；
- 批式计算、流式计算、交互分析引擎、YARN、Kubernetes 等；
了解 SQL 的基本用法和关系代数基础知识（选择、投影、连接、集合操作等）
了解编译原理相关的基础知识
- 词法分析（Lexical Analysis）
- 语法分析（Syntactic Analysis）
- 抽象语法树（Abstract Syntax Tree，AST）
了解 SQL 里的执行计划
- 逻辑计划（Logical Plan）
- 物理计划（Physical Plan）
- 分布式执行计划：Plan Fragment
- Left-deep tree
了解 SQL 执行的基本流程
- 任务调度：DAG
了解分布式系统中 shuffle 的实现方式
- Broadcast shuffle vs. Repartition shuffle
- 参考 MapReduce 和 Spark 系统
了解 SQL 中 group-by 和 join 的执行方式
- Hash-based vs. Sort-based

常见的查询优化器

Top-down Optimizer
Bottom-up Optimizer
Rule-based Optimizer，RBO
- Rule
- Pattern
Cost-based Optimizer，CBO
- 动态规划
交换律、结合律、传递性
RBO 优化规则
- 列裁剪
- 谓词下推
- 传递闭包
- Runtime Filter（min-max filter，in-list filter，bloom filter）
- Join 消除
- 谓词合并
CBO 相关概念
- 统计信息
  - Number of Distinct Value，NDV
  - Selectivity
  - Cardinality
- 代价模型

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

Apache Calcite
Orca
Volcano/Cascade 框架
- Memo
- AND/OR Graph
- Expression group
- Group expression
- Pattern
- Rule
- Branch-and-Bound Pruning
- Winner

SQL 相关的前沿趋势

存储计算分离
HSAP, HTAP, HTSAP
Cloud Native, Serverless
数据仓库，数据湖，湖仓一体，联邦查询
智能化：AI4DB，DB4AI

上课笔记

大数据体系和 SQL

大数据体系全景图

为什么 SQL 如此流行？

有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库
有 JDBC、ODBC 之类和各种数据库交互的标准接口
有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人
多个大数据计算引擎都支持 SQL 作为更高抽象层次的计算入口
1. MapReduce -> Hive SQL
2. Spark -> Spark SQL
3. Flink -> Flink SQL

SQL 的一生

这个与《MySQL实战45讲》第一课《一条SQL查询语句是如何执行的？》分析流程相似。

MySQL的逻辑架构图

01 SQL的处理流程-Parser

String -> AST (abstract syntax tree)

词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token；
语法分析：将token组成AST node,最终得到一个AST；

实现：递归下降(ClickHouse), Flex 和Bison (PostgreSQL), JavaCC (Flink), Antlr (Presto, Spark)

Analyzer和Logical Plan

Analyzer

检查并绑定Database, Table, Column等元信息
SQL的合法性检查，比如min/max/avg的输入是数值
AST -> Logical Plan

Logical Plan

逻辑地描述SQL对应的分步骤计算操作
计算操作：算子(operator)

查询优化

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

Physical Plan和Executor

Plan Fragment:执行计划子树

目标：最小化网络数据传输
利用上数据的物理分布(数据亲和性)
增加Shuffle算子

Executor

单机并行：cache, pipeline, SIMD
多机并行：一个fragment对应多个实例

02 常见的查询优化器

查询优化器的分类

RBO (Rule-based Optimizer)
CBO (Cost-based Optimizer)

Top-down Optimizer

从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
例子： Volcano/Cascade, SQLServer

Bottom-up Optimizer

从零开始，由下往上遍历计划树，找到完整的执行计划
例子：System R，PostgreSQL, IBM DB2

Rule-based Optimizer (RBO)

根据关系代数等价语义，重写查询
基于启发式规则
会访问表的元信息(catalog)，不会涉及具体的表数据(data)

Cost-based Optimizer (CBO)

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

RBO-关系代数

优化原则

RBO-列裁剪

2.2 RBO小结

主流RBO实现-般都有几百条基于经验归纳得到的优化规则

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

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

单表扫描:索引扫描(随机1/O) vs.全表扫描(顺序/O)
- 如果查询的数据分布非常不均衡，索弓扫描可能不如全表扫描

Join的实现：Hash Join vs. SortMerge Join
两表Hash Join：用小表构建哈希表一如何识别小表?
多表Join：
哪种连接顺序是最优的?

是否要对每种组合都探索?
- N个表连接，仅仅是left- deep tree就有差不多N!种连接顺序
- e.g.N = 10 ->总共3, 628, 800个连接顺序

CBO-概念

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

执行计划的代价等于所有算子的执行代价之和
通过RBO得到(所有)可能的等价执行计划

算子代价：CPU,内存，磁盘I/O,网络I/O等代价

和算子输入数据的统计信息有关:输入、输出结果的行数，每行大小...
- 叶子算子Scan：通过统计原始表数据得到
- 中间算子：根据定的推导规则，从下层算子的统计信息推导得到

和具体的算子类型，以及算子的物理实现有关
例子：Spark Join算子代价 = weight * row_ count +(1.0 - weight) * size

CBO-统计信息

原始表统计信息

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

推导统计信息

选择率 (selectivity) ：对于某一个过滤条件，查询会从表中返回多大比例的数据。
基数(cardinality) ：在查询计划中常指算子需要处理的行数。

准确的cardinality，远比代价模型本身重要。--“ How Good Are Query Optimizers, Really?"

CBO-统计信息的收集方式

在DDL里指定需要收集的统计信息，数据库会在数据写入时收集或者更新统计信息

 CREATE TABLE REGION(
 R_REGIONKEY INT NOT NULL,
 R_NAME CHAR(25) NOT NULL,
 R_COMMENT VARCHAR(152)
 ) DUPLICATE KEY(R_ REGIONKEY)
 DISTRIBUTED BY HASH(R_ REGIONKEY) BUCKETS 1
 PROPERTIES ("stats_ columns" ="R NAME");

手动执行explain analyze statement,触发数据库收集或者更新统计信息

 ANALYZE TABLE table_ name COMPUTE STA TIS TICS FOR COLUMNS column-name1, column-name2.....

动态采样

 SELECT count(*) FROM table_name;

CBO-统计信息推导规则

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

CBO-统计信息的问题

CBO-执行计划枚举

单表扫描：索引扫描(随机I/O) vs 全表扫描(顺序/O)。

如果查询的数据分布非常不均衡，索引扫描可能不如全表扫描。

Join的实现： Hash Join vs. SortMerge Join

两表Hash Join：用小表构建哈希表

如何识别小表?

多表Join:

哪种连接顺序是最优的?
是否要对每种组合都探索?
- N个表连接，仅仅是left-deep tree就有差不多N!种连接顺序
- e.g.N = 10->总共3, 628, 800个连接顺序

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

CBO执行计划枚举-动态规划

CBO效果-TPC-DS Q25

CBO小结

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

主流RBO实现一般都有几百条基于经验归纳得到的优化规则

RBO实现简单，优化速度快

RBO不保证得到最优的执行计划

CBO使用代价模型和统计信息估算执行计划的代价

CBO使用贪心或者动态规划算法寻找最优执行计划

大数据场景下CBO对查询性能非常重要

03 社区开源实践

社区开源实践-概览

数据库	SQL Optimzer选型
Hive、Flink、 Alibaba MaxCompute等	基于Apache Calcite,属于Volcano/Cascade框架
Greenplum、HAWQ	自研Orca,属于Volcano/Cascade框架
Alibaba Hologres (定位HSAP)	基于Orca,属于Volcano/Cascade框架
TIDB	自研，属于Volcano/Cascade框架
Spark	自研，RBO + CBO
Presto	自研，RBO + CBO
Doris	自研，RBO + CBO
ClickHouse	自研，RBO
Alibaba OceanBase	自研，RBO + CBO