复杂查询优化中的代价驱动连接条件下推：问题、设计与实证

在真实业务系统里，SQL 往往由 CTE、多层子查询、窗口函数与聚集计算拼接而成。逻辑更清晰的同时，优化器却更难提前过滤数据，尤其是 JOIN 条件无法进入子查询时，性能问题会被迅速放大。本文围绕“复杂查询中 JOIN 条件下推失败导致瓶颈”这一常见场景，讨论一种以等价性约束 + 代价模型为核心的连接条件下推方案，并给出实证对比。

一、问题背景

1.1 真实场景的典型痛点

许多客户的 SQL 组织方式类似：

• 在子查询或 CTE 中完成去重、聚集、窗口函数等计算
• 外层再与其他表 JOIN，并附加高选择性过滤条件

例如：

SELECT *
FROM (
  SELECT DISTINCT a
  FROM s1
) s
JOIN s2 ON s.a = s2.a
WHERE s2.b = 3;

从业务语义看并无问题，但执行上存在隐患：

• 子查询需要对基表做全量扫描并去重
• 外层 s2.b = 3 无法约束子查询扫描范围
• 中间结果集巨大，后续 JOIN / 聚集成本被放大

核心矛盾不在 JOIN，而在过滤发生得太晚。

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

将 JOIN 谓词下推到子查询内部看似直接，却有两类硬约束：

1.2.1 语义安全性（Equivalence）

下推改变谓词生效位置，若处理不当会破坏语义，典型风险包括：

• GROUP BY 聚集
• 窗口函数（Window Function）
• DISTINCT / UNION
• 含副作用或非确定性函数

因此并非所有 JOIN 条件都能下推，必须确保等价。

1.2.2 代价评估（Cost）

即便语义等价，也不一定“值得推”：

• 下推可能触发参数化执行
• 外层基数大时，子查询可能被重复执行
• 极端情况下会导致性能回退

结论是：不仅要能推，还要推得划算。

二、传统方案的局限

常见优化器在此类 SQL 上通常走以下路径：

1. 先完整执行子查询（扫描、去重/UNION/窗口等）
2. 生成较大的中间结果集
3. 再与外层表 JOIN 并施加过滤

问题在于：外层高选择性条件无法反向约束子查询扫描范围。子查询一旦复杂、数据量一大，就几乎必然成为性能瓶颈。

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

在金仓数据库 V009R002C014 版本中，我们引入“等价性 + 代价模型”双重约束的连接条件下推机制，整体分两步：

3.1 能不能推：等价性判定

优化器只识别绝对安全的下推机会：

• 分析子查询结构，判断是否满足等价性条件
• 对聚集、窗口函数、UNION 等复杂结构做约束判定
• 将 JOIN 条件拆分为“依赖外层列的参数化部分”和“子查询内列”

通过校验的 JOIN 谓词，会被改写为参数化过滤条件，注入子查询的扫描或过滤阶段。解决的问题是：推下去是否仍然语义等价。

3.2 值不值推：代价模型评估

等价性通过后仍不直接下推，而是比较下推前后的计划代价：

• 扫描行数与中间结果规模
• 参数化执行导致的重复计算成本
• 计划整体代价对比

若收益不足或可能回退，则放弃下推。解决的问题是：推下去是否真正更快。

（流程图略）

四、效果验证

4.1 最小化用例

SELECT *
FROM (SELECT DISTINCT * FROM s3) s3,
     s1
WHERE s1.s1a = s3.s3a;

测试结果：

• 未下推：子查询全表扫描 + 去重，执行时间约 84ms（执行计划/耗时略）
• 下推后：扫描阶段即可被 JOIN 条件裁剪，执行时间约 0.14ms（执行计划/耗时略）

中间结果集显著缩小，性能提升明显。

同场景下，D 厂商（不支持下推）表现如下：

EXPLAIN SELECT /*+use_nl (s3 s1)*/ *
FROM (SELECT DISTINCT * FROM s3) s3,
     s1
WHERE s1.s1a = s3.s3a;

执行时间约 1.62ms（执行计划/耗时略）。

4.2 复杂场景验证

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;

在包含 UNION、DISTINCT、窗口函数与多层子查询的复杂 SQL 中：

• 未下推：

  • 多个子查询对基表做全量扫描
  • 生成多个巨大的中间结果集
  • 最终 JOIN 成为瓶颈（执行计划/耗时略）

• 下推后：

  • JOIN 条件提前参与子查询扫描
  • 子查询由“全量扫描”转为“选择性扫描”
  • 总执行时间从 1081ms 降至 0.23ms（执行计划/耗时略）

从执行过程来看，未下推时需要先完成复杂 UNION 去重，形成大结果集 A，再与基表 JOIN 生成中间结果集 B；右侧子查询再对基表分组并计算窗口函数，得到结果集 C，与基表 JOIN 形成 D；最后再对 B 与 D 做连接。整个过程几乎都是全表扫，耗时巨大。下推后，过滤条件提前介入扫描阶段，显著减少扫描与连接成本，整体性能提升到数量级。

五、总结

在复杂查询优化里，连接条件下推不是简单的规则替换，而是典型的成本驱动优化：

• 仅靠规则忽视代价，可能造成灾难性回退
• 只看代价而忽略等价性，会直接破坏语义

通过“等价性保障 + 代价决策”的组合，可以：

• 在语义安全前提下最大化 JOIN 条件的过滤能力
• 显著降低子查询阶段的扫描与中间结果规模
• 在复杂 SQL 场景中获得数量级性能提升

这类优化对 OLAP、混合负载与复杂报表查询尤为关键，也会成为查询优化器演进的重要方向之一。

在真实业务系统里，SQL 往往由 CTE、多层子查询、窗口函数与聚集计算拼接而成。逻辑更清晰的同时，优化器却更难提前过滤数据，尤其是 JOIN 条件无法进入子查询时，性能问题会被迅速放大。本文围绕“复杂查询中 JOIN 条件下推失败导致瓶颈”这一常见场景，讨论一种以等价性约束 + 代价模型为核心的连接条件下推方案，并给出实证对比。

一、问题背景

1.1 真实场景的典型痛点

许多客户的 SQL 组织方式类似：

• 在子查询或 CTE 中完成去重、聚集、窗口函数等计算
• 外层再与其他表 JOIN，并附加高选择性过滤条件

例如：

SELECT *
FROM (
  SELECT DISTINCT a
  FROM s1
) s
JOIN s2 ON s.a = s2.a
WHERE s2.b = 3;

从业务语义看并无问题，但执行上存在隐患：

• 子查询需要对基表做全量扫描并去重
• 外层 s2.b = 3 无法约束子查询扫描范围
• 中间结果集巨大，后续 JOIN / 聚集成本被放大

核心矛盾不在 JOIN，而在过滤发生得太晚。

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

将 JOIN 谓词下推到子查询内部看似直接，却有两类硬约束：

1.2.1 语义安全性（Equivalence）

下推改变谓词生效位置，若处理不当会破坏语义，典型风险包括：

• GROUP BY 聚集
• 窗口函数（Window Function）
• DISTINCT / UNION
• 含副作用或非确定性函数

因此并非所有 JOIN 条件都能下推，必须确保等价。

1.2.2 代价评估（Cost）

即便语义等价，也不一定“值得推”：

• 下推可能触发参数化执行
• 外层基数大时，子查询可能被重复执行
• 极端情况下会导致性能回退

结论是：不仅要能推，还要推得划算。

二、传统方案的局限

常见优化器在此类 SQL 上通常走以下路径：

1. 先完整执行子查询（扫描、去重/UNION/窗口等）
2. 生成较大的中间结果集
3. 再与外层表 JOIN 并施加过滤

问题在于：外层高选择性条件无法反向约束子查询扫描范围。子查询一旦复杂、数据量一大，就几乎必然成为性能瓶颈。

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

在金仓数据库 V009R002C014 版本中，我们引入“等价性 + 代价模型”双重约束的连接条件下推机制，整体分两步：

3.1 能不能推：等价性判定

优化器只识别绝对安全的下推机会：

• 分析子查询结构，判断是否满足等价性条件
• 对聚集、窗口函数、UNION 等复杂结构做约束判定
• 将 JOIN 条件拆分为“依赖外层列的参数化部分”和“子查询内列”

通过校验的 JOIN 谓词，会被改写为参数化过滤条件，注入子查询的扫描或过滤阶段。解决的问题是：推下去是否仍然语义等价。

3.2 值不值推：代价模型评估

等价性通过后仍不直接下推，而是比较下推前后的计划代价：

• 扫描行数与中间结果规模
• 参数化执行导致的重复计算成本
• 计划整体代价对比

若收益不足或可能回退，则放弃下推。解决的问题是：推下去是否真正更快。

（流程图略）

四、效果验证

4.1 最小化用例

SELECT *
FROM (SELECT DISTINCT * FROM s3) s3,
     s1
WHERE s1.s1a = s3.s3a;

测试结果：

• 未下推：子查询全表扫描 + 去重，执行时间约 84ms（执行计划/耗时略）
• 下推后：扫描阶段即可被 JOIN 条件裁剪，执行时间约 0.14ms（执行计划/耗时略）

中间结果集显著缩小，性能提升明显。

同场景下，D 厂商（不支持下推）表现如下：

EXPLAIN SELECT /*+use_nl (s3 s1)*/ *
FROM (SELECT DISTINCT * FROM s3) s3,
     s1
WHERE s1.s1a = s3.s3a;

执行时间约 1.62ms（执行计划/耗时略）。

4.2 复杂场景验证

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;

在包含 UNION、DISTINCT、窗口函数与多层子查询的复杂 SQL 中：

• 未下推：

  • 多个子查询对基表做全量扫描
  • 生成多个巨大的中间结果集
  • 最终 JOIN 成为瓶颈（执行计划/耗时略）

• 下推后：

  • JOIN 条件提前参与子查询扫描
  • 子查询由“全量扫描”转为“选择性扫描”
  • 总执行时间从 1081ms 降至 0.23ms（执行计划/耗时略）

从执行过程来看，未下推时需要先完成复杂 UNION 去重，形成大结果集 A，再与基表 JOIN 生成中间结果集 B；右侧子查询再对基表分组并计算窗口函数，得到结果集 C，与基表 JOIN 形成 D；最后再对 B 与 D 做连接。整个过程几乎都是全表扫，耗时巨大。下推后，过滤条件提前介入扫描阶段，显著减少扫描与连接成本，整体性能提升到数量级。

五、总结

在复杂查询优化里，连接条件下推不是简单的规则替换，而是典型的成本驱动优化：

• 仅靠规则忽视代价，可能造成灾难性回退
• 只看代价而忽略等价性，会直接破坏语义

通过“等价性保障 + 代价决策”的组合，可以：

• 在语义安全前提下最大化 JOIN 条件的过滤能力
• 显著降低子查询阶段的扫描与中间结果规模
• 在复杂 SQL 场景中获得数量级性能提升

这类优化对 OLAP、混合负载与复杂报表查询尤为关键，也会成为查询优化器演进的重要方向之一。