SQL Optimizer 解析 | 青训营笔记

时迁bin
113 阅读8分钟

SQL Optimizer 解析 | 青训营笔记

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

1、大数据体系和SQL

1.1 大数据体系前景图

大数据体系图如下图所示，大数据体系包含若干部分，如：存储系统、资源调度、分析引擎、数据开发、业务应用等。而最主要的分析引擎中，大部分引擎都支持SQL语言或类SQL语言进行数据的批式分析、实时分析以及交互分析。使得SQL在大数据体系中占据重要的地位。

image-20220724095835281.png

1.2 为什么SQL会如此流行

  • 有 MySQL、Oracle 之类使用 SQL 作为交互语言的数据库

  • 有 JDBC、ODBC 之类和各种数据库交互的标准接口

  • 有大量数据科学家和数据分析师等不太会编程语言但又要使用数据的人

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

    回顾早期的Hadoop体系中，我们使用MapReduce通过编程的方式进行数据的聚合分析和数据归一化等数据的操作，不仅要编写极其复杂的代码，而且运行一个MapReduce Task(任务)的效率太低，随后衍生出Hive数据仓库工具，通过Hive SQL可以进行数据的ETL。而Spark上也更多地使用Spark SQL，而通过Scala编程实现数据的处理则是很少使用。近年来，Flink称为在实时处理领域的王者，也提供了Flink SQL实现更高抽象层次的计算入口。

    • MapReduce -> Hive SQL
    • Spark -> Spark SQL
    • Flink -> Flink SQL

1.3 SQL的处理流程

image-20220724211916121.png

  • Parser过程

    1. 把文本变成抽象语法树结构（AST）。
    2. 涉及语法分析阶段（拆分字符串，提取关键字，字符串，数值等token）和语法分析阶段（把词条按照定义的语法规则组装成抽象语法树结构，将token组成 AST node ，最终得到一个AST）。
    3. 实现：递归下降（ClickHouse），Flex和Bsion（PostgreSQL），JavaCC（Flink），Antlr（Presto，Spark）

image-20220724213736265.png

如上图，一个查询语句的抽象语法树分为几个子节点，SelectList查询列表，FromClause表示从那些表和库查询，WhereClause表示条件，GroupClause分组，OrderClause排序。而WhereClause又分为BetweenPredicate（表示between字句），LikePredicate（like字句）等子节点，这些AST节点最终合成一个AST。

  • Analyzer

    Analyzer的作用是:

    1. 检查并绑定DataBase，Table，Column等元信息。
    2. SQL的合法检查，比如：min/max/avg 等聚合函数的输入是数值、数据库，表和列名是否存在，列的数据类型是否正确。
    3. 将AST转换成逻辑计划树（Logical Plan）

  • Logical Plan（逻辑计划树）

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

    计算操作：算子（operator）。

    如下SQL查询语句:

    SELECT country.name,SUM(weblog.bytes) as total
FROM country
INNER JOIN geoip ON country.id = genoip.country_id
INNER JOIN weblog ON genoip.host = weblog.host
WHERE weblog.reply = "200" and weblog.host is not null
GROUP BY country.name
ORDER BY total
LIMIT 10

    对应的逻辑计划树如下

image-20220724220230767.png

  • 查询优化

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

  • 物理执行计划

    • 优化器的输出是一个分布式的物理执行计划。
    • 分布式物理执行计划的目标是在单机Plan的基础上最小化数据移动和最大化本地Scan，生成PlanFragment树。
    • 一个 PlanFragment 封装了在一台机器上对数据集的操作逻辑。每个 PlanFragment 可以在每个 executor 节点生成 1 个或多个执行实例，不同执行实例处理不同的数据集，通过并发来提升查询性能。
    • Plan 分布式化的方法是增加 shuffle 算子，执行计划树会以 shuffle 算子为边界拆分为PlanFragment。

  • Executor

    Executor按照物理执行计划扫描和处理数据，充分利用机器资源（CPU流水线，乱序执行，cache，SIMD）。

总结：

SQL需要依次经过Parser，Analyzer，OPtimizer和Executor的处理。

查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要。

查询优化器需要感知数据分布，充分利用数据的亲和性。

查询优化器按照最小化网络数据传输的目标把逻辑计划分成多个物理计划片段。

2、常见的查询优化器

查询优化器的分类：

  • Top-down Optimizer：从目标输出开始，由上往下遍历计划树，找到完整的最优执行计划
  • Bottom-up Optimizer：从零开始，由下往上遍历计划树，找到完整的最优执行计划
  • RBO
  • CBO

2.1 RBO（Rule-based Optimizer）

  • RBO基于关系代数等价规则对逻辑计划进行变换
  • 根据关系代数等价语义，重写查询
  • 基于启发式规则
  • 会访问表的元信息，不会涉及具体的表数据

实现上：

  • Pattern：定义了特定结构的Opterator子树（结构）。
  • Rule：定义了如何将其匹配的节点替换为新形态，从而生成新的、等价的Operator树（原地替换）。
  • 优化器搜索过程被抽象为不断匹配Pattern然后应用Rule转换，直到没有可以匹配的rule。

局限性：

  • 无法解决多表连接问题
  • 无法确定和选择最优的分布式Join/Aggregate执行方式。

关系代数

  • 运算符：Select（σ\sigma），Project（π\pi） ，Join（▷◁​），Union（U）等。

  • 等价交换：结合率，交换律，传递性

    Join：R ▷◁​ S = S ▷◁​ R ， （R ▷◁​ S）▷◁​ T = R ▷◁​ (S ▷◁​ T)

image-20220724223839667.png

优化原则

优化前的SQL

SELECT pv.siteId,user.name
FROM pv JOIN user
ON pv.siteId = user.siteId AND pv.userId = user.id
WHERE user.siteId > 123;

列裁剪优化：把要查询的字段列出来再进行Join

SELECT t1.siteId,t2.name
FROM (select siteId,userId from pv) as t1 
JOIN (select id,siteId,name from user) as t2 
ON t1.siteId = t2.siteId AND t1.userId = t2.id
WHERE t2.siteId > 123;

image-20220724225625172.png

谓词下推：将查询条件放在扫描单表里面

SELECT t1.siteId,t2.name
FROM (select siteId,userId from pv) as t1 
JOIN (select id,siteId,name from user WHERE siteId > 123) as t2 
ON t1.siteId = t2.siteId AND t1.userId = t2.id;

image-20220724230123447.png

传递闭包

SELECT t1.siteId,t2.name
FROM (select siteId,userId from pv WHERE siteId > 123) as t1 
JOIN (select id,siteId,name from user WHERE siteId > 123) as t2 
ON t1.siteId = t2.siteId AND t1.userId = t2.id;

image-20220724230611909.png

RUNTIME FILTER

image-20220724231618793.png

2.2 CBO（Cost-based Optimizer）

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

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

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

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

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

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

CBO查询优化器的执行流程

image-20220724232514545.png

2.3 RBO和CBO对比

  • 主流RBO实现一般都有几百条基于经验归纳得出的优化规则
  • RBO实现简单，优化速度快
  • RBO不保证得到最优的执行计划
  • CBO使用代价模型和统计信息估算执行计划的代价
  • CBO使用贪心或动态规划算法寻找最优执行计划
  • 在大数据场景下CBO对查询性能非常重要

个人总结

通过本次《SQL Optimizer 解析》课程，能够深刻的理解SQL 查询优化器RBO,并能够使用SQL语句对课程提供的例子做具体实现，也增强了自己的对SQL的执行过程的学习。知道SQL在大数据领域中的重要地位，我相信大数据平台最终会归于SQL，SQL必然会成为大数据处理的利器，而前提是我们要使用SQL查询的效率要达到最高，这一切要依靠查询优化器。然而我对CBO查询优化器的理解还不是很透彻，希望接下来可以对CBO进行深入的探讨。

avatar
文章
阅读
粉丝