SQL Optimizer 解析 | 青训营笔记

您完全不改Bug是嗎
233 阅读9分钟

SQL Optimizer 解析 | 青训营笔记

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

一、本课堂重点内容：

  • 介绍大数据体系和SQL的处理流程，重点介绍SQL在分布式环境下的处理
  • 介绍查询优化器的分类，重点介绍RBO和CBO的原理
  • 介绍查询优化器在社区的开源实践，重点介绍Apache Calcite项目
  • 介绍SQL引擎的前沿趋势，重点介绍AI和DB的结合

—— 大数据体系

15.JPG

二、详细知识点介绍：

1. 大数据体系和SQL

1.3 SQL的处理流程

6_副本.jpg

—— Parser

  • String -> AST(abstract syntax tree)

词法分析：拆分字符串，得到关键词、数值常量、字符串常量、运算符号等token

语法分析：将token组成AST node，最终得到一个AST

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

7.JPG

AST

—— Analyzer和Logical Plan

  • Analyzer

检查并绑定Database，Table，Column等元信息

SQL的合法性检查，比如min/max/avg的输入是数值

AST -> Logical Plan

  • Logical Plan

逻辑地描述SQL对应的分步骤计算操作

计算操作：算子(operator)

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

—— 查询优化

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

  • 目标：找到一个正确且执行代价最小的物理执行计划

  • 查询优化器试试数据库的大脑，最复杂的模块，很多相关问题都是NP的

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

—— Physical Plan 和 Executor

  • Plan Fragment：执行计划子树

目标：最小化网络数据传输

利用上数据的物理分布（数据亲和性）

增加Shuffle算子

  • Excecutor

单级并行：cache，pipeline，SIMD

多机并行：一个fragment对应多个实例

—— 小结

  • One SQL rules big data all
  • SQL需要依次经过Parser，Analyzer，Optimizer和Executor的处理
  • 查询优化器是数据库的大脑，在大数据场景下对查询性能至关重要
  • 查询优化器需要感知数据分布，充分利用数据的亲和性
  • 查询优化器按照最小化网络数据传输的目标把逻辑计划拆分成多个物理计划片段

2. 常见的查询优化器

2.1 查询优化器分类

  • Top-down Optimizer

从目标输出开始，由上往下遍历计划数，找到完整的最优执行计划

例子：Volcano/Cascade，SQLServer

  • Bottom-down Optimizer

从零开始，由上往下遍历计划数，找到完整的执行计划

例子：System R，PostgreSQL，IBM DB2

- Rule-based Optimizer（RBO）

根据关系代数等价语义，重写查询

基于启发式规则

会访问表的元信息（catalog），不会涉及具体的表数据（data）

- Cost-based Optimizer（CBO）

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

2.2.1 RBO - 关系代数

  • 运算符：Select（），Project（π），Join（），Rename（），Union（）等

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

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

2.2.2 RBO - 优化原则

  • Read data less and faster（I/O）

  • Transfer data less and faster（Network）

  • Process data less and faster（CPU & Memory）

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

9.JPG

优化前的逻辑计划

2.2.3 RBO - 列裁剪

10.JPG

2.2.4 RBO - 谓词下推

11.JPG

2.2.5 RBO - 传递闭包

2.2.6 RBO - Runtime Filter

2.2 RBO小结

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

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

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

单表扫描：索引扫描（随机I/O） vs 全表扫描（顺序I/O）

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

Join的实现：Hash Join vs. SortMerge Join

两表 Hash Join：用小表构建哈希表——如何识别小表？

多表Join：

哪种连接顺序是最优的？ .
是否要对每种组合都探索？

N个表连接，仅仅是left-deep tree就有差不多N！种连接顺序 .
e.g. N=10 -> 总共3,628,800个连接顺序

...

2.3 CBO - 概念

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

执行计划的代价等于所有算子的执行代价之和

通过RBO得到（所有）可能的等价执行计划

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

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

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

和具体的算子类型，以及算子的物理实现有关

例子：Spark Join 算子代价 = weight * row_count + (1.0 - weight) * size

12.JPG

2.3.1 CBO - 统计信息

  • 原始表统计信息

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

行级别：min、max、num nulls、num not nulls、num distinct value(NDV)、histogram等

  • 推导统计信息

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

基数（cardinality）：在查询计划中常指算子需要处理的行数

准确的cardinality，远比代价模型本身重要。

——"How Good Are Query Optimizers,Really?"

2.3.1 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 STATISTICS FOR COLUMNS column-name1,column-name2,...
  • 动态采样
SELECT count(*) FROM table_name

2.3.2 CBO - 执行计划枚举

单表扫描：索引扫描（随机I/O） vs 全表扫描（顺序I/O）

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

Join的实现：Hash Join vs. SortMerge Join

两表 Hash Join：用小表构建哈希表——如何识别小表？

多表Join：

哪种连接顺序是最优的？ .
是否要对每种组合都探索？

N个表连接，仅仅是left-deep tree就有差不多N！种连接顺序 .
e.g. N=10 -> 总共3,628,800个连接顺序

...

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

2.3 CBO 小结

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

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

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

—— 小结

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

  • RBO 实现简单，优化速度快

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

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

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

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

3. 社区开源实践

—— 概览

数据库SQL Optimizer 选型
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

3.1 社区开源实践 - Apache Calcite 概览

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

  • 模块化，插件化，稳定可靠

  • 支持异构数据模型

关系型

半结构化

流式

地理空间数据

  • 内置 RBO 和 CBO

3.2 社区开源实践 - Calcite RBO

  • HepPlanner

优化规则（Rule）

Pattern：匹配表达子树

等价变换：得到新的表达式

内置有 100+ 优化规则

四种匹配规则

ARBITRARY/DEPTH_FIRST：深度优先 TOP_DOWN：拓扑顺序 BOTTOM_UP：与 TOP_DOWN 相反

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

优化速度快，实现简单，但是不保证最优

3.3 社区开源实践 - Calcite CBO

  • VolcanoPlanner

基于 Volcano/Cascade 框架

成本最优假设

Memo：存储候选执行计划

Group：等待计划集合

Top-down 动态规划搜索

应用 Rule 搜索候选计划

Memo

本质：AND/OR graph

共享子树减少内存开销

Group winner：目前的最优计划

剪枝（Branch-and-bound pruning）：减少搜索空间

Top-down 遍历：选择winner 构建最优执行计划

—— 小结

  • 主流的查询优化器都包含 RBO 和 CBO
  • Apache Calcite 是大数据领域很流行的查询优化器
  • Apache Calcite RBO 定义了许多优化规则，使用 Pattern 匹配子树，执行等价变换
  • Apache Calcite CBO 基于 Volcano/Cascade 框架
  • Volcano/Cascade 的精髓：Memo、动态规划、剪枝

4. 前沿趋势

—— Big Data，Big Money

—— 概览

13.JPG

—— DATA + AI

  • AI4DB

自配置

智能调参（OtterTune，QTune） 负载预测/调度

自诊断和自愈合：错误恢复和迁移

自优化：

统计信息估计（Learned cardinalities） 代价估计 学习型优化器（IBM DB2 LEO） 索引/视图推荐

  • DB4AI

内嵌人工智能算法（MLSQL，SQLFlow）

内嵌机器学习框架（SparkML，Alink，dl-on-flink）

—— 小结

  • 大数据创业如火如荼，SQL 查询优化器仍然是必不可少的一个重要组件
  • 引擎架构的进化、云原生、湖仓一体等对 SQL 查询优化器有新的要求和挑战
  • AI 加持，学习型查询优化器在不断进化

三、课后问题总结：

  • 为什么要把SQL Optimizer放在第一节课讲？

首先，SQL非常流行，是数据处理中最广泛使用的语言，相对于通用的编程语言来说比较简单；更重要的是，One SQL rules big data all。

  • 谓词下推条件？不同类型的JOIN上面的FILTER下推。

谓词下推的基本思想：将过滤表达式尽可能移动至靠近数据源的位置，以使真正执行时能直接跳过无关的数据。

—— 总结

avatar
文章
阅读
粉丝