SQL 查询优化器原理初探 | 青训营笔记

CallanBi
361 阅读7分钟

SQL 查询优化器原理初探 | 青训营笔记

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

引子：为什么要学习 SQL Optimizer?

在整个大数据技术体系中，SQL 是最常用的语言，诸如 Flink，Hive 等大数据分析引擎都提供的对 SQL 的支持； 学习分布式数据库，SQL 的底层显得尤为重要。SQL 查询优化器是底层原理最为核心的部分。

image.png

SQL 的处理过程

image.png

String 经过 Parser 转化为 AST

编译原理的东西，非常成熟的技术。

  1. 词法分析：拆分SQL 字符串，得到 token

  2. 语法分析：Token 组成 AST

SQL 的 AST 可能长这样：

image.png

AST 经过 Analyzer 转化为 Logical Plan

Analyzer 所起的作用：

  • 绑定元信息
  • 合法性检查（如 min、max、avg 输入是数值）

什么是 Logical Plan？

Logical Plan 是逻辑地描述 SQL 分步计算操作的一种数据结构。Logical Plan 是一棵左深树。

分步计算操作用算子（operator）表述。如下图中， JOIN, SCAN, AGGREGATE等都是算子。

假设我们有如下的 SQL 语句：

image.png

那么该语句的 Logical Plan 可能长这样：

image.png

Logical Plan 经过 Optimizer（查询优化器） 形成优化的 Logical Plan，再继续形成 Physical Plan

终于到了我们核心关注的内容，Optimizer。

为什么要 Optimizer？

首先，SQL 是一中声明式语言，没有告诉数据库实现的方法；在内部查询的实现由数据库决定。 一般 SQL 越复杂，JOIN 操作越多，数据量越大，查询优化的意义就越大，因为不同执行方式的性能差别可能会有成百上千倍。

经过查询优化器优化之后，形成Optimized Logical Plan，可能长这样：

image.png

最后，如果在分布式场景的数据库，数据库内部会将这棵优化过的树进行拆分， 每个实例都可能拿到这棵树的一个子树，从而组成 Physical Plan。

每个子树称为一个执行计划子树（Plan Fragment）。下图是几棵分布式的执行计划子树。

image.png

在拆分时，我们的目标是最小化网络数据传输。

因此，我们需要考虑数据上的物理分布（数据亲和性），否则会增加通信开销。

在 Physical Plan 中，每个 Node 通过 Shuffle 算子进行链接（即一边做发送，一边做接收）。如上图中每个 Node#之间的连线表示就是 Shuffle 操作。

Executor

每个节点都通过 Executor 执行。

Executor 在实例上读数据。在多机并行中，一个 fragment 可以有多个实例执行。（如上图的Physical Plan）。

常见的查询优化器

查询优化器分类：

  1. Top-down Optimizer：从上往下遍历 Logical Plan 找到最优计划
  • 例子：Volcano/Cascade, SQLServer
  1. Bottom-up Optimizer：由下往上遍历树
  • 例子：PostgreSQL, IBM DB2
  1. Rule-based Optimizer(RBO)
  • 根据关系代数（数据库原理有涉及）等价语义，重写查询
  • 会访问表的元信息（catalog）,不会涉及具体表数据（data）
  1. Cost-based Optimizer(CBO)
  • 使用一个模型估算代价，选择最优代价的执行计划

RBO

优化原则：

  • 读（I/O）更少、更快
  • 通信（Network）更少、更快
  • 处理数据（CPU、Memory）更少、更快

RBO 可以分为列剪裁、谓词下推、传递闭包和 Runtime Filter。我们通过一个例子来学习下面四种RBO。

假设要查询的 SQL 语句如下：

image.png

优化前，逻辑计划如下：

image.png

列剪裁

将顶层SELECT 和 ON 子句中所用到的列在读表时 filter 一下：

image.png

谓词下推

将 Where 子句的条件谓词下推至 JOIN 之前：

image.png

传递闭包

更高级的优化方法。通过 ON 子句中的关系和 Where 子句中的某张表的过滤条件推出其他表的过滤条件，在 JOIN 之前 Filter。

如例子中这一段 JOIN 后的 SQL 语句中：

ON pv.siteId = user.siteId AND pv.userId = user.id
WHERE user.siteId > 123

可以推出 pv.siteId > 123。

image.png

Runtime Filter

在运行时过滤。

在 JOIN 之前，在某一个表 filter 之后，先阻塞其他表的 filter。根据这张表的最大/最小值（min-max 手段），或者通过构造一个该表 filter 过的哈希集合（in-list 手段）或者通过布隆过滤器的手段，提供给另一张表进行过滤，从而在 JOIN 之前减少数据量。

image.png

RBO 的优缺点

优点：简单，速度快 缺点：不保证得到最优的执行计划。原因有几个：

  • 有时候，随机 IO 的扫描性能不如顺序 IO。在数据分布不均衡的时候尤为常见。（如构造的哈希集合大多数数据值集中在0-100见，只有几个在1000-10000间，那么构造出的哈希集合空间开销将会变得很大）
  • 在两表 Hash Join 时，识别小表比较困难。
  • 多表 join，不能知道那种连接顺序最优。

CBO

使用模型估计执行计划代价。一般的实践是通过 RBO 得到所有等价执行计划，进而估算代价。

每个算子（JOIN，AGGREGATE）都有其代价，如 CPU、内存、IO、网络等。如 Spark 中 JOIN 的算子代价为：

weight * row_count + (1 - weight) * size

我们可以把算子分为叶子算子和中间算子。

对于叶子算子的 Scan 操作，其代价通过统计原始表数据得到。

对于中间算子，代价通过一定规则从下层算子的统计信息得到。

计算完每种等价执行计划的算子，再对执行计划中的连结方式进行枚举。

统计信息分类

原始表的统计信息

  • 表/分区级别：行数、行大小、占用字节等
  • 列级别：min, max, num null, num not null, num distinct value(NDV), histogram（直方图）等

推到的统计信息

  • 选择率（selectivity）:对于某一个过滤条件，会返回多大比例数据
  • 基数（cardinality）:在查询计划中指算子需要处理的行数

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

统计信息搜集方式

image.png

统计信息推导规则

Filter 的选择率计算方式，可以用图中的式子计算：

image.png

其 Filter 的基数即为总数据✖️选择率。

统计信息的问题

在很多实际情况中，列与列不是独立同分布的。

如考虑某个数据库，有10个制造商，100个车型， filter 为 制造商 = '比亚迪' AND 车型 = '汉' 根据独立同分布假设， selectivity = 1/10 * 1/100，但只有比亚迪能生产汉这一车型，比亚迪和汉是相互关联的，实际上为 1/100。

解决方法有两种：用户执行关联或数据库自己识别关联，构造直方图。（不展开）

对执行计划的枚举

JOIN 操作的实现可以分为 Hash Join、SortMerge Join，Hash Join 中选用不同的表来构造哈希会产生不同的计划路径，因此我们需要对计划进行枚举。

通常，用贪心算法或动态规划选出多表 join 的最优计划。

社区的开源实践

image.png

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

可以参考 Calcite 项目看看。

前沿趋势

  1. 存储计算分离
  2. 一体化（HTAP，HSAP，HTSAP）
  3. 云原生
  4. 湖仓一体、Query Federation
  5. Data + AI
  • AI4DB：自配置（智能调参、负载预测调度）、自诊断修复（错误恢复迁移）、自优化（统计信息估计、代价估计、学习型优化器、索引推荐）
  • DB4AI：内嵌人工智能算法（MLSQL,SQLFlow),内嵌机器学习框架（SparkML, Alink, dl-on-flink）

总结

image.png

avatar
文章
阅读
粉丝