金仓数据库复杂查询优化实践：基于代价的连接条件下推

一只牛博
0 阅读11分钟

复杂查询中基于代价的连接条件下推实践与思考

摘要

在实际业务系统中，SQL 往往并不像教科书示例那样简洁。随着业务复杂度持续提升，CTE、多层子查询、窗口函数、聚集计算、DISTINCT、UNION 等结构被大量用于组织查询逻辑。这些写法提升了 SQL 的表达能力与可维护性，但也给优化器带来了更高的挑战，尤其是在 JOIN 条件无法尽早参与数据过滤的情况下，性能问题会被迅速放大。

本文围绕一个在真实客户场景中反复出现的问题展开：复杂查询中，由于 JOIN 条件未能下推至子查询内部，导致子查询阶段产生大规模中间结果，从而拖慢整条 SQL 的执行。结合金仓数据库在 V009R002C014 版本中的相关实践，本文系统介绍一种基于代价模型的连接条件下推思路，即在保证语义等价的前提下，通过代价比较判断是否值得下推，以避免“能推但不一定该推”的优化误判。

需要说明的是，本文中的 SQL 片段、执行计划截图与性能数据均来自实际测试场景。不同硬件环境、统计信息状态、参数配置和数据分布下，绝对耗时可能存在差异，但问题特征、优化思路和性能变化方向具有稳定参考价值。

一、问题背景

1.1 客户场景中的典型痛点

在很多客户业务中，SQL 常采用如下模式组织逻辑：

  • 在子查询或 CTE 中完成大量计算，如去重、聚集、窗口函数等
  • 在外层再与其他表进行 JOIN
  • 在 JOIN 后附加较强选择性的过滤条件

例如，实际场景中常见的 SQL 形态如下图所示：

示意图 1

从业务语义看，这类 SQL 完全合理；但从执行角度看，往往隐藏着明显的性能风险：

  • 子查询 s 需要先对 s1 做全量扫描并完成去重等计算
  • 外层高选择性条件 s2.b = 3 无法反向约束子查询的扫描范围
  • 子查询会先产出较大的中间结果集
  • 后续 JOIN、聚集、排序等操作都建立在大数据量之上，整体代价迅速上升

因此，这类问题的根本矛盾通常不在于 JOIN 本身，而在于过滤发生得不够早，导致优化器错过了本可利用的高选择性约束。

1.2 业界普遍面临的两大难点

将 JOIN 条件下推到子查询内部，看上去是一个直接且有效的优化方向，但在数据库内核实现层面，这并不是一个简单的规则改写问题，主要涉及两个核心难点。

1.2.1 语义安全性

JOIN 条件下推，本质上是在改变谓词生效的位置。如果处理不当，就可能改变 SQL 的原始语义，尤其是在以下场景中风险更高：

  • GROUP BY
  • 窗口函数（Window Function）
  • DISTINCT / UNION
  • 含有副作用或非确定性函数的表达式

因此，并不是所有 JOIN 条件都可以安全地下推。优化器必须先回答一个最基础的问题：下推之后，查询结果是否仍与原 SQL 等价。

1.2.2 代价评估

即便某个谓词在语义上可以下推，也不意味着下推后一定更优。例如：

  • 下推后可能触发参数化执行
  • 当外层输入基数较大时，子查询可能被重复执行多次
  • 某些情况下，过滤收益无法覆盖重复执行带来的额外成本
  • 极端场景下，优化甚至可能引发明显的性能回退

这意味着，连接条件下推不仅要“能推”，还必须“值得推”。对优化器而言，这是一类典型的成本驱动型决策问题。

二、传统方案的局限

面对上述复杂 SQL，传统优化器通常采用如下路径：

2.1 先完整执行子查询

  • 扫描基表
  • 完成 DISTINCT、UNION、窗口函数、聚集等复杂处理

2.2 生成较大的中间结果集

子查询内部即使已经消耗了大量计算资源，也往往无法利用外层 JOIN 条件提前裁剪输入数据，因此中间结果容易膨胀。

2.3 再与外层表执行 JOIN 并施加过滤

这一执行路径的主要问题在于：外层高选择性的 JOIN / WHERE 条件，无法反向约束子查询的扫描范围。当子查询本身逻辑复杂、数据量又较大时，这种“先做完、再过滤”的方式几乎必然成为性能瓶颈。

从本质上看，这类传统方案的问题不只是“执行慢”，而是过滤时机滞后，导致扫描、去重、聚集、连接等一系列操作都在不必要的大数据集上进行。

三、金仓数据库基于代价的连接条件下推设计

在金仓数据库 V009R002C014 版本中，针对上述问题，引入了一套“等价性判定 + 代价模型决策”的连接条件下推机制。整体思路可以概括为两步：先判断“能不能推”，再判断“值不值得推”。

3.1 第一步：能不能推

这一阶段的目标不是“尽可能多地下推”，而是只识别那些在语义上可严格保证安全的下推机会。优化器会重点完成以下工作：

  • 分析子查询结构，判断是否满足语义等价条件
  • 对聚集、窗口、UNION、DISTINCT 等复杂结构进行约束性检查
  • 将 JOIN 条件拆分为可参数化部分与子查询内部可消费部分
  • 识别哪些条件可以注入到扫描或过滤阶段，哪些只能保留在原位置

对于满足条件的 JOIN 谓词，优化器会将其改写为参数化过滤条件，并尽量下沉到子查询更靠近数据源的位置，使过滤尽可能早地发生。

这一阶段解决的是：推下去以后，结果会不会变。

3.2 第二步：值不值得推

通过等价性校验后，优化器并不会立即选择下推，而是进入代价比较阶段。这里关注的不再是“语义是否正确”，而是“整体执行是否更优”。典型评估项包括：

  • 下推前后的执行路径差异
  • 子查询扫描行数和过滤比例
  • 中间结果集规模变化
  • 参数化执行带来的重复计算成本
  • 外层输入基数对总成本的放大效应

如果代价模型判断下推收益明显，优化器才会选择新的执行计划；如果收益不足，甚至存在性能回退风险，则自动放弃下推，保留其他更稳妥的路径。

这一阶段解决的是：推下去以后，是否真的更快。

3.3 设计要点与实践意义

从工程实现看，这套机制的价值主要体现在以下几个方面：

  • 它不是单纯的规则改写，而是规则与代价联合驱动
  • 它优先保障 SQL 语义稳定性，避免“优化错误”演变成“结果错误”
  • 它能够在复杂查询中显著压缩中间结果规模
  • 它更适合处理包含多层嵌套结构的报表类、分析类 SQL

换句话说，这类优化的关键不只是把谓词“推下去”，而是让谓词以正确的方式、在正确的时机、进入正确的位置。

详细工作流程如下图所示：

示意图 2

四、效果验证

为了更直观地说明该机制的收益，下面结合最小化用例与复杂场景进行验证。

4.1 最小化用例

测试 SQL 如下：

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

测试结果如下：

  • 未下推时：子查询需要先全表扫描并完成去重，执行时间约 84ms
  • 下推后：扫描阶段即可利用 JOIN 条件完成裁剪，执行时间约 0.14ms

对应执行计划截图如下：

  • 未下推

未下推执行计划

  • 下推后

下推后执行计划

从该用例可以看出，在连接条件具备明显过滤能力时，下推能够直接减少子查询扫描范围和去重输入规模，中间结果显著缩小，性能提升呈数量级变化。

作为对比，本文也观察了某 D 厂商在相同测试 SQL 下的表现。其执行计划如下：

explain
select /*+use_nl (s3 s1)*/ *
from (select distinct * from s3) s3, s1
where s1.s1a = s3.s3a;

D 厂商执行计划

在该测试场景下，执行时间约为 1.62ms。从现象上看，其计划中未体现与本文相同的连接条件下推效果。需要强调的是，不同产品版本、参数与统计信息状态都会影响具体结果，因此这里更适合作为场景对比参考，而非脱离上下文的绝对结论。

4.2 复杂场景验证

测试 SQL 如下：

explain analyze
select *
from (
    select *
    from (
        select distinct * from s3
        union
        select distinct * from s3 a
    ) s3, s1
    where s1.s1d = s3.s3a
) s
join (
    select *
    from (
        select s3a, sum(s3b) over (partition by s3a) s3d
        from s3
    ) s3, s1
    where s1.s1a = s3.s3a
) j
on s.s3d = j.s3a;

这是一个更接近真实业务的复杂 SQL，包含了：

  • UNION
  • DISTINCT
  • 窗口函数
  • 多层子查询
  • 多次 JOIN

在这类场景中，如果连接条件不下推，通常会出现如下执行过程：

  1. 左侧子查询先处理内部 UNION
  2. UNION 左右两侧都对基表执行去重相关的全扫描
  3. 生成较大的中间结果集后，再与基表 s1 连接
  4. 右侧子查询再对基表 s3 执行窗口计算，形成新的大结果集
  5. 右侧结果集与 s1 连接后，再与左侧中间结果做最终连接

在这一过程中，多个子查询几乎都以全量方式访问基表，导致扫描代价高、中间结果大、最终连接成本重，整体性能较差。

对应执行计划如下：

  • 未下推

复杂场景未下推执行计划

  • 下推后

复杂场景下推后执行计划

测试结果显示：

  • 未下推时，整体执行时间约为 1081ms
  • 下推后，整体执行时间约为 0.23ms

从执行行为上看，下推带来的变化主要体现在：

  • JOIN 条件更早地参与了子查询扫描
  • 多个子查询从“全量扫描”转为“选择性扫描”
  • 去重、窗口计算、后续连接的输入规模明显下降
  • 最终计划避免了在大中间结果之上继续做高成本连接

这一复杂用例说明，连接条件下推的意义不仅在于减少单个算子的工作量，更在于它能够改变整条 SQL 的执行形态：让后续操作建立在更小的数据集上，从而放大整体收益。

五、适用边界与实践思考

从实践角度看，连接条件下推并不是一种“对所有复杂 SQL 都无条件有效”的万能优化，它更适用于以下特征明显的场景：

  • 子查询本身计算复杂，且基表数据量较大
  • 外层 JOIN 条件具有较强选择性
  • 下推后能够在扫描阶段显著减少输入数据
  • 外层输入规模可控，不会把参数化执行代价放大到不可接受

相应地，在以下情况下就需要更加谨慎：

  • 外层驱动行数很大，重复执行成本可能过高
  • 子查询内部结构复杂到足以影响等价性判断
  • 统计信息不准确，可能误导代价模型做出错误决策
  • 下推收益有限，但改写和参数化开销较高

因此，这类优化真正考验的并不是“是否实现了某条规则”，而是优化器是否能够在复杂结构中同时处理好语义、统计信息与成本决策三者之间的关系。

六、总结

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

  • 只做规则改写、不看代价，可能带来灾难性性能回退
  • 只关注代价、不保证等价，可能直接破坏 SQL 语义

金仓数据库通过“等价性保障 + 基于代价的决策”这一组合设计，在安全前提下提升了 JOIN 条件的过滤能力，并在复杂 SQL 场景中显著减少了扫描量和中间结果规模。

从本文示例可以看到，这类优化在包含 DISTINCT、UNION、窗口函数和多层子查询的复杂查询中，能够带来非常明显的性能改善。对于 OLAP、混合负载以及复杂报表型查询，这一能力具有较高的实际价值，也代表了查询优化器持续演进的一个重要方向。

avatar
文章
阅读
粉丝