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

1.大数据体系和 SQL

1.1大数据体系全景图

1.2 为什么学SQL

【One SQL rules big data all】

1. 有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库（运用广泛）

2. 有 JDBC、ODBC 之类和各种数据库交互的标准接口（成为标椎）

3. 有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人（简单流行）

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

1.3SQL 的基本执行流程

（SQL执行流程资料：www.php.cn/mysql-tutor…

1.3.1Parser（分析器）

• 识别SQL语句，对语句基于 SQL 语法进行词法分析和语法分析

  • 词法分析

    -概念：拆分字符串、得到关键词、数值常量、字符串常量、运算符号等token
    
    -词法分析就是把一个完整的 SQL 语句打碎成一个个的单词。
    
    -比如一个简单的 SQL 语句：select name from user where id = 1 and age >20;
      
    

    

  • 语法分析

    -定义：将token根据SQL语法组装成AST的各个节点（node），最终得到一个AST。
    
    -语法分析会对 SQL 做一些语法检查，比如单引号有没有闭合，然后根据 MySQL 定义的语法规则，根据 SQL 语 句生成一个数据结构。这个数据结构我们把它叫做AST。
    

• 输入SQL->输出AST

（抽象语法树概念资料：blog.csdn.net/qq_43592800…

-1.SAT--abstract syntax tree（抽象语法树）

-2.抽象语法树（abstract syntax code，AST）是源代码抽象语法结构的树状表示，树上的每个节点都表示源代码中的一种结构，之所以说语法是’抽象’的，是因为这里的语法不会表示出真实语法中出现的每个细节。

-3.CST = 具象语法树 AST=抽象语法树

例：最简单js代码 20 + (15 - 3)*2  （由CST->AST图）

• 和编译原理课程里的“前端”知识相关

• 实现：方式递归下降（clickhouse），Flex（进行词法分析），bison-（Postgre）（进行语法分析）

1.3.2Analyzer（语义分析器）

• 检查查询中Database（数据库）、Table（表）、Column（列名）等元信息是否存在，类型是否正确，判断 SQL 是否合理。【像英语句子中检查语法是否正确】

• 输入 AST（抽象语法树） -->输出logical plan（逻辑计划树）（在某些系统中这个工作由一个 Converter 完成）

  • 逻辑计划树

    1.逻辑地描述一个 SQL 如何一步步地执行查询和计算，最终得到执行结果的一个分步骤地计划。
    
    2.树中每个节点是是一个算子（操作），定义了对数据集合的计算操作（过滤，排序，聚合，连接），边代表了数据的流向，从孩子节点流向父节点。
    
    3.之所以称它为逻辑的，是因为算子定义的是逻辑的计算操作，没有指定实际的算法，比如对于逻辑的排序算子，逻辑计划树里没有指定使用快排还是堆排。
    

1.3.3Optimizer（查询优化器）

1. SQL 是一种声明式语言，用户只描述做什么，没有告诉数据库怎么做

2. 查询优化的目标是为 SQL 找到一个正确的且执行代价最小的执行计划

3. 查询优化器是数据库的大脑，最复杂的模块，很多相关问题都是 NP （不能再优化）的

4. 一般 SQL 越复杂，Join 的表越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能有成百上千倍

•  输入Logical tree(逻辑计划)-->得到Physical tree（物理计划）-->拆分为Plan Fragment（执行计划子树） 【即在分布式场景下，将逻辑计划最终拆分为分布式物理执行计划】

-1. 分布式物理执行计划的目标：是在单机 Plan 的基础上最小化数据移动和最大化本地 Scan，生成 Plan Fragment 树。（最小化网络数据传输）

-2. 生成分布式物理执行计划的方式：利用上数据的物理分布（亲和性）

-3.拆分节点时每个执行计划的连接方式：增加shuffle算子（一边做发送一边做接收操作）

1.3.4 Executor（执行器）

• 多个Plan Fragment发送给节点执行，每个节点是一个Executor

• 执行后利用上并行的维度

    - 单击并行：充分利用cache，指定CPU流水线乱序执行，利用乱序化技术SIMD
  
    - 多机并行：一个Fragment能在一个节点上有多个实例并行地执行
  

2.常见的查询优化器

查询优化器分类：

（RBO与CBO资料：blog.csdn.net/cm00337533/…

2.1RBO-基于规则的优化方式

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

  • 关系代数基础知识（选择、 投影、连接、集合操作等）

    (1) 选择运算 
     英文： select 
     字符： σ 
     例：σage>20∧salary>10000(User) 在User关系中查找出年龄大于20并且工资高于10000的所有元组并返回这些元组组成的关系
    
    (2) 投影运算 （将返回的关系中所有重复的元组将被剔除）
    英文： project 
    字符： Π 
    例：Πname(σage>18(User)) 在User关系中查找出年龄大于18的所有元组并返回这些元组的姓名name组成的关系
    
    (3) 自然连接（先做笛卡尔积再将不能拼接的舍弃）
    英文： natural join 
    字符： ⋈ 例：
    User ⋈ Home 将User关系和Home关系做自然连接
    
    (4)交运算 
    英文： intersection 
    字符： ∩ 
    例：Πname(σage>18(User)) ∩ Πname(σaddress=”NewYork”(Home)) 在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，将这两个关系中都存在的姓名取出作为新的关系。
    
    (4) 并运算 
    英文： union
    字符： ∪
    例：Πname(σage>18(User))∪Πname(σaddress=”NewYork”(Home)) 在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，将这两个关系取并集，得到一个并关系。
    
    (5) 差运算 
    英文： set-difference 
    字符： - 
    例：Πname(σage>18(User))-Πname(σaddress=”NewYork”(Home)) 在User关系中找出所有年龄大于18的姓名，在Home关系中找出所有家在NewYork的人的姓名，得到User中存在而Home中不存在的人的姓名的关系。 （关系代数知识链接：<https://blog.csdn.net/quinnnorris/article/details/70739094）>
    

  • 关系代数的等价变换规则（结合律、交换律、传递性）

（等价变换资料：www.jianshu.com/p/df9bccf3a…

• 优化原则

  • 减少I/O代价

    I/O是指把数据从磁盘读入内存时所需代价（该代价是查询所需最主要的，所以在 优化时一个基本原则就是降低I/O总次数）
    

  • 减少CPU代价

    CPU代价是指处理内存中数据所需的代价，数据一旦读入内存，当我们识别出我们 所要的数据后，会在这些数据上执行排序（sort）或连接（join）操作，这需要消 耗CPU资源
    

  • 减少NETWORK代价

    对于访问远程节点来说，network代价的花费也是很大的
    

• 优化例子

  •  RBO-列裁剪

    -把不需要的列在扫描表时就裁剪掉（pv表只扫描siteld、userld两列；user表只扫描id、siteld、name三列）
    
    -只扫描需要的（减少I/O,j减少CPU） 
    

    

  • RBO-谓词下推

    -谓词下推尽早过滤不需要的行，改变顺序不影响结果（filter放到join下面，完成提前过滤）
    
    -谓词下推有条件（不同的join有不同的下推规则）
    

    

  • RBO-传递闭包

    利用传递性创造新的谓词往下推
    

    

  • RBO-Runtime Filter（运行时过滤）

    -通过在join的probe端提前过滤掉那些不会命中join的输入数据来大幅减少join中的数据传输和计算，从而减少整体的执行时间
    
    -产生新的filter推到查询端
    

    

• RBO小结

  • 主流RBO实现一般都有几百条基于经验得到的优化规则（经验有时候会错误）

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

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

    - 单表扫描时运用到索引进行扫描（为随机I/O），如果查询的数据分布非常不均衡，那么索引扫描可能不如全表扫描（为顺序I/O）。
    
    - 对分布式的join实现无法选择出好的实现，无法分辨是选择Hash Join还是SortMerge Join。
    
    - 对于两表的Hash Join希望用小表构建哈希表来减少内存，RBO无法识别小表
    
    - 对于多表的join，无法判断那种连接顺序是最优的
    

2.2CBO-基于代价的优化方

• 通过代价引擎来估计每个执行计划所需的代价，该代价将每个执行计划所耗费的资源进行量化，CBO根据这个代价选择出最优的执行计划

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

• 预估的算子代价包含 CPU，cache misses，memory（内存），disk I/O（磁盘），network I/O （网络）等代价

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

    叶子算子 scan：通过统计原始表数据得到
    
    中间算子：根据一定的推导规则，从下层算子的统计信息推导得到
    

  • 具体的算子类型，以及算子的物理实现有关（e.g. hash join vs. sort join）

• CBO执行流程

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

本节课主要讲：统计信息+推导规则和执行计划枚举

1. CBO-统计信息概念

• l 原始表统计信息

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

  • 列级别：min、max、num nulls、num、not nulls、num、distinct value(NDV)（互不相同值的列）、histogram （直方图表示）等

• l 推导统计信息

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

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

2.CBO-统计信息的收集方式

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

• l 手动执行explain analyze statement，触发数据库收集或者更新系统信息（缺点更新慢容易信息过期得到错误信息）

• l 动态采样

  3. CBO-统计信息推导规则

前提假设列与列之间是独立的，列的值是均匀分布

• l 过滤选择率（Filter Selectivity）

4. CBO-统计信息的问题

• l 列与列之间独立-->这假设经常与现实不符

• l 某些列是相关联的不能完全按统计信息的推导规则计算--->解决：用户指定或数据库自动识别相关联的列

• l 某些数据不是均匀分布的如：性别，年龄-->解决：用直方图表示

5. CBO-执行计划枚举

• l 通常使用贪心算法或动态规划选出最优的执行计划（把全局最优解问题分成局部最优解问题，由所有局部最优解组成全局最优解）

• l 例子：动态规划

  此为三表连接问题，先考虑两表连接问题，有两种执行方法（Hash Join和SortMerge Join）， 再根据两种执行方法分别计算子问题的代价，通过比价选择最优执行方法。再考虑三表 连接，再比较所有执行计划，选择出最优解。
  

  

  

  

  

  6.CBO小结

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

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

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

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

• l 不同数据库优化器不一样

• l 大部分都有RBO和CBO，另外Vocano/Cascade框架也很流行

1. 社区开源实践--Alache Calcite

• l Apache Calcite是个查询优化器，工作：解析MySQL进行优化得到执行计划

• l One size fits all：是统一的SQL查询引擎

• l 模块化和插件化做得很好，有多年历史稳定可靠

• l 支持异构数据模型（关系型、半结构化、流式、地理空间数据）

• l 内置RBO和CBO

1. Calcite RBO-(Hep Planner)

• l 其RBO命名为Hep planner

• l 内有多种优化规则（Rule）包含：

  •  Patten：描述一个表达式子树去匹配这个子树

  • 等价变换：将表达式子树进行等价变换，用新的子树替换原来的子树

• l 内置有100多个优化规则

• l是根据规则匹配子树，有四种匹配规则：

  •  APBITRARY:深度优先  

  •  DEPTH_FIRST:深度优先

  • TOP_DOWN:拓扑排序

  •  BOTTOM_UP:与拓扑排序相反

• l 遍历所有的rule，直到没有rule可以被触发

• l 优化速度快，实现简单，但是基于经验的不保证最优

2. Calcite CBO-(VolcanoPlanner)

• l 其CBO命名为VolcanoPlanner

• l 基于Volcano/Cascade框架****

• l 基于成本最优假设****

• l 经常使用的数据结构：Memo（用于在需在最优解时存储候选执行计划）****

  • Group：存储等价计划集合****

  •  本质是：AND/OR graph****

  •  共享子树减少内存开销（等价关系放在一个group里）****

• l 应用rule搜索候选计划

• l Group winner是当前的最优执行计划

• l 可以通过剪枝（Branch-and-bound pruning）减少搜索空间（如果等价计划很多，搜索所有等价计划很耗时）

• l 找到最优执行计划时是以Group存储，需要将其还原成原始的执行计划。采用Top-down方式遍历，选择winner构建最优执行计划。

3. Calcite小结

• l 主流的查询优化器都包含RBO和CBO

• l Apache Calcite 是大数据领域很流行的查询优化器

• l Apache Calcite RBO定义了许多优化规则，使用pattern匹配子树，执行等价变换

• l Apache Calcite CBO基于Volcano/Cascade,Volcano/Cascade的精髓：Memo，动态规划，剪枝****

4. 前沿趋势

存储计算分离

HSAP, HTAP, HTSAP

Cloud Native, Serverless

数据仓库，数据湖，湖仓一体，联邦查询

智能化

小结：

• l 大数据创业如火如荼，查询优化器仍然是比不可少的一个重要组件

• l 引擎架构的进化、云原生、湖仓一体等对SQL查询优化器有新的要求和挑战

• l AI加持，学习型查询优化器在不断进化