SQL Optimizer | 青训营笔记

Trembling
95 阅读10分钟

字节跳动青训营第4期 第1课 SQL-Optimizer

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

[TOC]

大数据体系和SQL

大数据基本体系

大数据基本体系

大数据常见的基本体系如上图所示。

首先离不开的是云服务器ECS、VPC等，数据的存储、数据计算引擎的部署必然都需要这些硬件资源。

其次是大数据的存储，大数据，即大批量的不能通过常规的手段进行采集和处理的数据，大数据是需要一个足够强大的存储系统作为依托的，其中HDFS、HBase等是常见的存储解决方案。通常又使用Yarn或是K8S进行资源的调度。

个人认为大数据体系中最为重要的一部分是分析引擎，主要分为3个类别的分析引擎，包括批式分析引擎、流式分析引擎、交互分析引擎，其中Spark、Hive、MapReduce、Flink、ClickHouse等组件在分析引擎中起到了重要作用。这一部分也是和SQL关系最大的一部分，早起的分析引擎并未完全适配SQL，但是随着发展，SQL已经成为了各个分析引擎最常用的接口。

大数据体系中还存在“消息队列”这样一类重要的组件，作为数据管道，起到了稳定传输数据的作用。

SQL的处理流程

SQL，即标准化查询语言，在今天其适用范围非常广泛，不止是MySQL、SQLServer等数据库服务使用SQL，大数据分析引擎如Spark、Flink也在非常广泛的使用SQL。无论是谁在使用SQL，必然要对SQL进行解析和转化，最终成为可以被程序所执行的查询操作的集合，各个服务对SQL的解析和转化方法大同小异，常见的SQL解析和转化过程如下：

graph LR
    SQL((SQL))
    
    SQL --> Parser
    Parser -- AST --> Analyzer
    Analyzer -- Logical Plan --> Optimizer
    Optimizer -- Physical Plan --> Executor

Parser

Parser接收到的是SQL字符串，即一套完整的SQL语句，Parser将经过处理输出为AST（Abstract Syntax Tree），即语法树。

东华大学研究生入学考试专业课中，就曾经要求到将一个关系代数查询画出语法树，并进行优化，其实这里提到的语法树和后续的各个优化其实和专业课要求的内容是非常类似的。

Parser接收到SQL字符串后，首先要进行词法分析，拆分各个关键词、数值、运算符号等，这些被称为token，其次要进行语法分析，将token组合成一个AST。

AST

各个引擎实现Parser的方式有所不同，例如ClickHouse通过递归下降算法实现，PostgreSQL通过Flex和Bison实现，Flink通过JavaCC实现，Presto和Spark通过Antlr实现。

Analyzer

Analyzer接收到AST后，要检查并绑定数据库、表、列等元数据，其次要对SQL的合法性进行验证，比如min/max/avg等参数需要是数值型的，最后就是要将AST转化为Logical Plan。

Logical Plan是逻辑地描述SQL对应分步骤计算操作，每个计算操作称为算子。

例如这样的一段SQL：

SELECT country.name, SUM(weblog.bytes) as total
FROM country
INNER JOIN geoip ON country.id = geoip.country_id
INNER JOIN weblog ON geoip.host = weblog.host
WHERE weblog.reply = "200" AND weblog.host IS NOT NULL
GROUP BY country.name
ORDER BY total
LIMIT 10

其最终转化的Logical Plan如图所示：

LogicalPlan

自底向上的看，第一步是对country，geoip，weblog三张表进行SCAN操作，即读取数据；第二步是根据条件进行JOIN操作；第三部是进行AGGREGATE和其他查询操作。

Logical Plan是一颗left-deep tree，对于每个节点，其左孩子可以是各种操作，但其右孩子必须是SCAN操作或为空。

Optimizer

Optimizer即优化器，由于SQL是一种声明式语言，用户其实只描述了要做什么，但是不像写代码一样提供了怎么做，所以Optimizer的目标是找到一个正确且执行代价最小的物理执行计划，即Physical Plan。

Optimizer是数据库的大脑，也是SQL最复杂的一个部分，很多问题是NP级的。一般情况下，SQL越复杂，Join的表越多，数据量越大，查询优化的意义也就会更大，其性能差异可能有成百上千倍。

关于优化器在后续章节进行详述。

Executor

Executor在获取到物理执行计划后，确定一个执行计划子树，目的是最小化网络数据的传输，增加shuffle算子，并利用数据的物理分布（即数据亲和性）。

常见的查询优化器

查询优化器的分类

对于查询优化器的分类，主要通过两种方式进行分类：

  • 根据优化方向进行分类
    • Top-Down Optimizer：从目标输出开始，自顶向下遍历计划树，找到完整的最优执行计划，例如Volcano/Cascade，SQL Server
    • Bottom-Up Optimizer：从底部开始，自底向上遍历计划书，找到完整的最优执行计划，例如System R，PostgreSQL，IBM DB2
  • 根据优化原则进行分类
    • Rule-Based Optimizer：根据规则进行优化，根据关系代数等价语义重写查询，启发式规则，会访问表的元信息但不设计表数据
    • Cost-Based Optimizer：根据时间花费进行优化，用一个模型估算执行代价，选择代价最小的执行计划

其中更常见的是RBO和CBO，其主要实现方式如下：

RBO

首先，RBO要基于关系代数进行重写查询，关系代数包括σ\sigma，π\pi，⋈\Join，ρ\rho，⋃\bigcup等，关系代数也有包括结合律、交换律、传递性等等价变换。

RBO的主要优化原则如下：

  1. 更少的IO
  2. 更高的网络性能
  3. 更少的CPU和内存占用

在关系代数的语法树优化中，其主要优化方法可以概括为：更早的执行选择和投影操作，提前把不必要的行和列从查询中给剔除，尽可能只留下关键的数据。

例如查询：

SELECT s.name, e.cid
FROM student AS s, enrolled AS e
WHERE s.sid = e.sid AND e.grade = 'A'

在这个查询中，初始的语法树将从student表和enrolled表中取出所有数据，然后执行一个s.sid = e.sid的连接操作，再根据e.grade = 'A'执行一个选择操作，最后再执行一个投影操作。

从这个查询中看，用到的字段其实只有5个，其余字段对这个查询其实并没有用，另外，对于enrolled表，也只有一部分的行有用，其余的数据并没有用。

所以优化的方式就是先对两张表进行选择和投影操作，只留下有用的行和列，然后再进行连接操作，最后得到查询结果。

RBO主要包括4种优化方式，其中3种与上述关系代数优化的思想是如出一辙的，分别是：

  • 列裁剪：提前进行投影操作，只留下有用的列，没用的列就不再参与查询
  • 谓词下推：提前进行谓词（如选择等）操作，只留下有用的行
  • 传递闭包：根据连接条件，可以判断出一些隐藏的选择操作，故可以提前进行这些选择操作，只留下有用的行和列

还有另外一种优化方式，即Runtime Filter，是在优化过程中，动态产生的过滤器，如min-max过滤器，这类方式的基本思想与其他3种也算是类似，实现方式相对不太一样。

RBO的优点：实现简单，优化速度快 RBO的缺点：不能保证得到最优的执行计划

RBO存在的问题：

  1. 单表扫描时，查询的数据若分布不均匀，索引扫描效果可能不如全表扫描
  2. Join的实现方式，是Hash Join还是SortMerge Join
  3. 当两表Hash Join时，用较小的表构建Hash，但是要如何识别小表？
  4. 多表Join：如何找到最优的连接顺序？

CBO

CBO是使用一个模型估算执行计划代价的方案，执行计划的代价等于所有算子的代价之和。算子包括：CPU、内存、IO、网络等

CBO的主要流程如下：

graph LR
    统计信息+推到规则 --> 计算算子代价
    计算算子代价 --> 计算执行计划代价
    计算执行计划代价 --> 执行计划枚举

统计信息主要有两种：

  1. 原始表统计信息
    1. 表或分区级：行数、行平均大小、占用字节数
    2. 列级：最小值，最大值，null数量，非null数量，distinct数量（NDV）等
  2. 推导统计信息
    1. 选择率：对于某查询条件，会从表中返回多大比例的数据
    2. 基数：算子需要处理的行数

统计信息的收集方式主要有3种：

  1. 在DDL中指定需要收集的统计信息
  2. 手动执行explain analyze statement
  3. 动态采样，例如SELECT count(*) FROM table_name

统计信息的推导规则（假设列与列之间独立，列的值是均匀分布的）：

查询条件推导规则
ANDfs(a AND b) = fs(a) * fs(b)
ORfs(a OR b) = fs(a) + fs(b) - (fs(a) * fs(b))
NOTfs(NOT a) = 1 - fs(a)
x=literal0或1 - NDV
x<literal0或1或literal−minmax−min\frac{literal - min}{max - min}

上述规则其实并不能盲目使用，其假设“列与列之间独立，列的值是均匀分布的”很可能是不成立的。实际的数据中，列和列之间往往可能会有一定的关联，其计算方式需要灵活应对。

例如，在一张表中有2列，分别是汽车制造商和车型，要找出制造商为比亚迪（选择率为0.1），车型为“汉”（选择率为0.01）的数据，若根据假设，其最终选择率为0.1 * 0.01 = 0.001；但实际上，只有比亚迪有“汉”这个车型，所以这个查询实际的选择率应该是0.01

CBO需要进行执行计划枚举，执行计划的数量往往是非常庞大的，通常选择贪心算法或动态规划来选出最优的执行计划。

例如对于查询：

SELECT *
FROM R, S, T
WHERE R.a = S.a
AND S.b = T.b

要连接R S T三张表，其第一个子问题有2个大方向，分别是先执行R⋈SR \Join S和先执行T⋈ST \Join S（由于连接存在交换律，故一个连接中的先后不存在影响），对于两个连接操作，分别存在两个连接方式，分别是Hash Join和SortMerge Join。其花费如下表：

先执行的连接方式花费
R⋈SR \Join SHash Join300
R⋈SR \Join SSortMerge Join400
T⋈ST \Join SHash Join200
T⋈ST \Join SSortMerge Join280

由上表可知，第一个子问题中，两个方案均选择Hash Join花费更低，接下来迭代第二个子问题，分别再连接T和R表，其花费如下

连接方式花费
R⋈S⋈TR \Join S \Join THash Join380
R⋈S⋈TR \Join S \Join TSortMerge Join400
T⋈S⋈RT \Join S \Join RHash Join450
T⋈S⋈RT \Join S \Join RSortMerge Join300

由上述2张表可以得到，使用T⋈S⋈RT \Join S \Join R的顺序，第一个连接使用Hash Join，第二个连接使用SortMerge Join的总花费最少，即最优方案。

如下是开启和关闭CBO的一个对比图：

closeCBO

openCBO

社区开源实践

社区开源实践概览

社区开源实践概览

前沿趋势

前沿趋势：DATA + AI

  1. AI4DB
    1. 自配置
      1. 智能调参
      2. 负载预测和调度
    2. 自诊断和自愈合
      1. 错误恢复
      2. 迁移
    3. 自优化
      1. 统计信息估计
      2. 代价估计
      3. 学习型优化器
      4. 索引/视图推荐
  2. DB4AI
    1. 内嵌人工智能算法（MLSQL，SQLFlow）
    2. 内嵌机器学习框架（SparkML，Alink，dl-on-flink）
avatar
文章
阅读
粉丝