查询优化器的进化：KingbaseES Cost-based Join Predicate Pushdown 深度解析

用户343238211024
0 阅读8分钟

引言

在现代数据密集型应用中，SQL 已经不再只是简单的单表查询语言。随着企业系统业务逻辑的不断复杂化，开发者在编写 SQL 时往往会引入 CTE（Common Table Expression，公用表表达式）、多层嵌套子查询、窗口函数以及复杂的聚合计算，以提高代码结构的清晰度和逻辑的可维护性。这样的写法在工程实践中非常常见，因为它能够将复杂业务逻辑进行模块化拆分，使 SQL 更接近于“声明式程序”。

然而，从数据库执行层面来看，这种复杂结构却会给查询优化器带来巨大的挑战。特别是在涉及 多表 JOIN 的复杂查询中，如果高选择性的过滤条件没有在数据扫描阶段及时生效，而是等到产生大量中间结果之后才进行过滤，那么查询性能往往会急剧下降。

很多开发者都经历过类似的情况： 在测试环境中 SQL 执行迅速，而在生产环境面对真实数据规模时却突然“卡死”。深入查看执行计划后才发现，子查询在执行阶段生成了巨大的中间结果集，而真正具有过滤能力的条件却迟迟没有被应用。

本文将围绕这一类性能瓶颈展开深入分析，并介绍 金仓数据库（KingbaseES） 在 V009R002C014 版本中推出的 基于代价模型的连接条件下推（Cost-based Join Predicate Pushdown） 优化机制。通过“语义等价性判断 + 成本评估模型”的双重策略，使复杂 SQL 在保持语义正确的前提下实现数量级的性能提升。

一、复杂 SQL 为什么会突然变慢？

1.1 常见业务 SQL 的结构特征

在真实业务系统中，复杂 SQL 通常会呈现如下结构特征：

  1. 内层查询逻辑复杂 子查询或 CTE 中往往包含大量计算，例如：

    • DISTINCT 去重
    • GROUP BY 聚合
    • WINDOW FUNCTION 窗口函数
    • 多表 JOIN

  2. 过滤条件出现在外层 JOIN

    外层 SQL 才出现真正具有选择性的过滤条件，例如：

    s2.b = 3

从 SQL 语义上看，这样的写法完全没有问题。但如果数据库优化器没有能力将这些过滤条件向内层传递，那么执行过程就会变成：

  • 子查询对底层表进行 全量扫描
  • 完成所有复杂计算（去重 / 聚合 / 窗口函数）
  • 生成 庞大的中间结果集
  • 最后再进行 JOIN 与过滤

这意味着大量本可以被过滤掉的数据依然参与了计算，最终导致：

  • CPU 消耗增加
  • I/O 成本飙升
  • 内存占用增大
  • 查询延迟明显上升

换句话说，这类问题的本质在于：

数据过滤发生得太晚，导致无效数据参与了大量计算。

1.2 查询优化中的两大核心难题

很多人可能会觉得，将 JOIN 条件下推到子查询内部似乎是一个很自然的优化思路。但在数据库内核层面，这个问题其实非常复杂。

主要存在两大挑战：

1. 语义安全问题（Equivalence）

如果优化器随意改变谓词位置，可能会导致查询结果发生变化。

例如在以下场景中：

  • GROUP BY
  • WINDOW FUNCTION
  • UNION
  • DISTINCT
  • 非确定性函数

这些结构都会影响数据的行数与结果集合。

如果在这些结构之前提前应用过滤条件，可能导致：

  • 聚合结果变化
  • 去重结果变化
  • 窗口函数计算范围变化

因此，优化器必须保证：

任何谓词下推都不会改变 SQL 的语义结果。

2. 成本评估问题（Cost）

即使语义安全，谓词下推也不一定总是有利的。

例如：

  • 下推可能导致 参数化扫描（Parameterized Scan）
  • 子查询可能被重复执行 N 次
  • 外层表数据量很大时，可能产生指数级计算开销

举个例子：

外层表：100万行
子查询：被重复执行100万次

即使每次扫描很快，总成本也可能变得极高。

因此优化器必须回答两个问题：

1 能不能推？
2 推了是否更快？

二、传统优化策略的局限

在许多传统数据库优化器中，为了保证查询语义安全，通常会采用较为保守的执行策略：

  1. 完全执行子查询

    • 扫描底层表
    • 执行所有计算
    • 得到完整结果

  2. 生成中间结果集

    子查询结果被完整物化。

  3. 外层 JOIN 再过滤

    在最终 JOIN 阶段才应用过滤条件。

这种执行模式通常被称为：

先计算，再过滤（Compute First, Filter Later）

在数据量较小的情况下，这种策略问题不大。但在海量数据场景下，它会导致：

  • 巨大的中间结果
  • 无效数据参与计算
  • JOIN 阶段负担加重

最终形成性能瓶颈。

三、金仓数据库的解决方案

为了应对上述问题，金仓数据库（KingbaseES） 在 V009R002C014 版本中引入了一种新的优化机制：

基于代价模型的连接条件下推（Cost-based Join Predicate Pushdown）

该机制通过 两道决策防线 来保证优化既安全又高效。

3.1 第一道防线：等价性判断（能不能推）

在执行谓词下推之前，优化器首先会进行严格的语义分析。

主要步骤包括：

1. 子查询结构分析

优化器会识别子查询类型，例如：

  • 是否存在 GROUP BY
  • 是否包含 WINDOW FUNCTION
  • 是否使用 UNION
  • 是否使用 DISTINCT

只有在保证语义等价的情况下，才允许继续优化。

2. JOIN 条件拆解

JOIN 条件会被拆分为两部分：

外层依赖部分
子查询内部部分

例如：

s1.a = s2.a

其中 s1.a 来自外层，s2.a 属于子查询。

3. 参数化过滤注入

在满足语义安全的情况下，优化器会将 JOIN 条件转换为：

子查询内部的参数化过滤条件

这样可以让子查询在扫描阶段就利用外层条件进行过滤。

目标非常明确：

确保优化不会改变查询结果。

3.2 第二道防线：成本评估（值不值得推）

通过语义检查之后，优化器还需要判断：

这样做是否真的更快？

为此，优化器会构建代价模型，对不同执行路径进行评估。

评估因素包括：

1. 全量扫描 vs 参数化扫描

优化器会对比两种方案：

方案A

子查询一次全量执行

方案B

子查询根据外层参数重复执行

2. 外层表基数

如果外层表行数很大，则可能导致：

子查询被重复执行很多次

此时下推可能并不划算。

3. I/O 与 CPU 开销

优化器会综合评估：

  • 扫描行数
  • CPU 计算
  • 磁盘 I/O
  • 内存使用

最终选择最优执行路径。

如果模型预测性能可能下降，则会自动放弃该优化。

四、实际性能测试

4.1 DISTINCT 场景测试

测试 SQL：

Select * from (select distinct * from s3) s3, s1 where s1.s1a = s3.s3a;

执行效果如下：

性能对比如下：

策略执行方式耗时性能提升
未下推子查询全表扫描 + 去重约84ms-
启用下推JOIN条件提前过滤约0.14ms600倍+
竞品DNested Loop约1.62ms-

优化效果非常明显。

在下推之后：

  • 子查询扫描范围大幅减少
  • 去重数据量明显降低
  • JOIN 成本显著下降

4.2 极端复杂查询测试

测试 SQL 包含：

  • UNION
  • DISTINCT
  • WINDOW FUNCTION
  • 多层子查询

未优化时执行路径：

  1. 左侧子查询
全表扫描
UNION
去重

生成结果集 A

  1. 与 s1 JOIN

生成结果集 B

  1. 右侧子查询
全表扫描
GROUP BY
WINDOW FUNCTION

生成结果集 C

  1. 最终 JOIN
B JOIN C

执行计划如下：

总执行时间：

1081ms

优化之后：

连接条件被下推至子查询内部。

执行流程变为：

  1. 扫描阶段直接过滤
  2. 中间结果大幅减少
  3. 后续 JOIN 代价降低

执行计划：

执行时间：

0.23ms

性能提升：

接近 5000 倍

五、总结

在复杂 SQL 查询优化中，连接条件下推并不是简单的语法改写问题，而是典型的成本驱动型优化问题。

如果只依赖规则改写，而不考虑成本评估，很可能导致：

重复计算
性能反而下降

而如果只关注成本模型，却忽略语义等价性，则可能：

改变查询结果
破坏 SQL 正确性

金仓数据库通过 等价性判断 + 代价模型评估 的双重策略，实现了安全而高效的查询优化：

  • 在保证 SQL 语义不变的前提下进行谓词下推
  • 最大化利用 JOIN 条件的过滤能力
  • 显著减少中间结果规模
  • 大幅降低扫描与计算成本

在 OLAP 查询、复杂报表、数据分析等场景中，这类优化技术尤为关键。

随着数据规模的持续增长和 SQL 复杂度不断提升，基于代价模型的谓词下推机制也将成为未来数据库查询优化器发展的重要方向之一。

avatar
文章
阅读
粉丝