复杂查询中基于代价的连接条件下推实践与思考
在实际的业务系统中，SQL 往往并不像教科书示例那样简洁。随着业务复杂度的提升，CTE、多层子查询、窗口函数、聚集计算被大量用于组织逻辑。然而，这类 SQL 在带来可读性的同时，也给查询优化器带来了巨大的挑战，尤其是在 JOIN 条件无法有效提前过滤数据 的场景下，性能问题尤为突出。本文将围绕一个在真实客户场景中频繁出现的问题——复杂查询中 JOIN 条件下推失败导致的性能瓶颈，系统性地介绍一种 基于代价模型的连接条件下推（Cost-based Join Predicate Pushdown） 的设计与实现思路。
一、问题背景
1.1 客户场景中的典型痛点
在很多客户业务中，SQL 通常采用如下模式来组织逻辑：
- 在子查询或 CTE 中完成大量计算（去重、聚集、窗口函数等）
- 在外层再与其他表进行 JOIN，并施加高选择性的过滤条件
例如：
从业务语义上看，这条 SQL 没有任何问题；但从执行角度看，却隐藏着严重的性能隐患：
- 子查询 s 需要对 s1 做全量扫描并去重
- 外层 s2.b = 3 的高选择性条件，无法影响子查询的扫描范围
- 导致子查询输出一个巨大的中间结果集
- 后续 JOIN、聚集等操作都发生在“大数据量”之上，性能急剧下降
根本问题并不在 JOIN 本身，而在于过滤发生得不够早。
1.2 业界普遍面临的两大难点
将 JOIN 条件下推到子查询内部，看起来是一个直观有效的优化方向，但在数据库内核层面，这个问题远没有想象中简单，主要体现在两个方面：
1.2.1 语义安全性（Equivalence）
JOIN 条件下推，本质上是在改变谓词生效的位置。如果处理不当，很容易改变 SQL 的语义，尤其是在以下场景中：
- 聚集（GROUP BY）
- 窗口函数（Window Function）
- DISTINCT / UNION
- 含有副作用或非确定性函数的表达式
因此，不是所有 JOIN 条件都可以安全地下推，必须有严格的等价性判定。
1.2.2 代价评估（Cost）
即便在语义上等价，下推也未必“划算”：
- 下推后可能触发参数化执行
- 外层基数较大时，可能导致子查询被重复执行 N 次
- 极端情况下，性能反而出现灾难性下降
这意味着：
JOIN 条件下推不仅要“能推”，还要“值得推”。
二、传统方案的局限
传统优化器在面对上述 SQL 时，通常会采用如下执行策略：
2.1 完整执行子查询
- 扫描基表
- 做 DISTINCT / UNION / 窗口函数等复杂操作
2.2 生成一个大的中间结果集
2.3 再与外层表进行 JOIN，并施加过滤条件
这一策略的致命问题在于：外层的高选择性 JOIN / WHERE 条件，无法反向约束子查询的扫描范围。当子查询本身计算复杂、数据量大时，这种执行路径几乎必然成为性能瓶颈。
三、金仓数据库基于代价的连接条件下推设计
在金仓数据库最新的V009R002C014版本中，针对上述问题，我们引入了一套 “等价性 + 代价模型” 双重约束 的连接条件下推机制。整体思路可以概括为两步：
3.1 能不能推：等价性判定（Equivalence）
在这一阶段，优化器的目标不是“尽可能多地下推”，而是只识别绝对安全的下推机会：
- 分析子查询结构，判断是否满足语义等价条件
- 对复杂子查询（聚集、窗口、UNION 等）进行约束性判定
- 将 JOIN 条件拆分为：可参数化部分（依赖外层列）和子查询内部列
符合条件的 JOIN 谓词，会被改写为参数化过滤条件，注入到子查询的扫描或过滤阶段中。
这一步解决的是：“推下去之后，结果会不会变？”
3.2 值不值推：代价模型（Cost）
在通过等价性校验后，并不会立刻选择下推，而是进入代价评估阶段：
- 评估下推前后的执行路径
- 比较子查询扫描行数、中间结果规模
- 评估参数化执行带来的重复计算成本
- 选择整体代价最低的执行计划
如果代价模型判断下推收益不足，甚至可能带来性能回退，则优化器会自动放弃下推，选择其他执行路径。
这一步解决的是：“推下去之后，真的会更快吗？”
详细工作流程如下：
四、效果验证
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 中：
- 未下推时：
1.多个子查询对基表进行全量扫描
2.生成多个巨大的中间结果集
3.最终 JOIN 成为性能瓶颈
- 下推后：
1. JOIN 条件提前参与子查询扫描
2. 多个子查询由“全量扫描”转为“选择性扫描”
3. 整体执行时间从 1081ms 降至 0.23ms
通过上述复杂场景下的sql来看，当连接条件不下推时，需要先处理内部的union查询，并且union的左右两侧对基表进行去重的全扫描，产生一个很大的结果集A然后与基表s1进行连接产生一个中间结果集B，然后执行右侧的子查询，对基表s3进行分组并计算窗口函数得到一个大的中间结果集C与基表s1进行连接得到结果集D,最后两个较大的中间结果集B和D进行连接，在这个过程中子查询几乎需要对表进行全表扫得到数据，耗费很多时间，导致性能差。
当我们实现将连接条件推入子查询后，可以利用连接条件下推到子查询中，可以对子查询的数据在扫描阶段就被筛选裁剪减少扫描时间，筛选后的结果集在进行后续的连接操作可以减少连接操作的时间，整体的查询从全量扫描变为筛选性的扫描，带来性能上的提升，从未下推的1081ms变为下推后的0.23ms。
在复杂查询优化中，连接条件下推并不是一个简单的规则改写问题，而是一个典型的成本驱动型优化问题：
- 只做规则，不看代价，可能带来灾难性性能回退；
- 只看代价，不保证等价，会直接破坏 SQL 语义。
通过 “等价性保障 + 基于代价的决策” 的组合设计，我们可以：
- 在安全前提下最大化 JOIN 条件的过滤能力
- 显著减少子查询阶段的数据扫描与中间结果规模
- 在复杂 SQL 场景中获得数量级的性能提升
这类优化对于 OLAP、混合负载以及复杂报表型查询尤为关键，也将成为未来查询优化器演进的重要方向之一。
六、总结与展望
本次基于代价的连接条件下推方案，核心是解决了复杂 SQL 中过滤时机过晚导致的性能瓶颈问题，通过 “等价性判定 + 代价模型评估” 的双重约束，在保证 SQL 语义安全的前提下，让 JOIN 条件能够精准下推至子查询阶段，实现了数据扫描的提前裁剪，从根源上减少了中间结果集规模，最终在包含 DISTINCT、UNION、窗口函数的多层子查询复杂场景中，实现了数量级的性能提升。
该方案的设计核心价值，在于打破了传统优化器 “先执行子查询，后进行连接过滤” 的固定执行逻辑，将查询优化从 “单纯的规则改写” 升级为 “代价驱动的智能决策”，既规避了盲目下推可能带来的语义错误，也避免了无收益下推导致的性能回退，为复杂查询优化提供了一种可落地的技术思路。
从技术演进角度来看，基于代价的连接条件下推并非终点，未来可在以下方向进一步深化与拓展：
- 复杂算子的适配优化：针对更多高阶算子（如递归查询、多表关联的复杂聚集）完善等价性判定规则，扩大下推的适用场景；
- 代价模型的精细化：结合业务场景的实际数据分布、硬件资源特性优化代价评估因子，让下推决策更贴合真实执行环境；
- 多维度下推的协同：将连接条件下推与谓词下推、聚合下推等优化手段结合，形成多维度的联合下推策略，最大化复杂查询的过滤效率；
- 流批一体场景的适配：将该优化思路延伸至流批一体的查询引擎中，解决流式复杂 JOIN 的性能问题，适配实时数仓的业务需求。
在 OLAP、混合负载、复杂报表型查询成为主流业务场景的当下，查询优化器的能力直接决定了数据库的性能表现。而 “语义安全为基础，代价模型为核心” 的优化思路，将成为未来查询优化器设计的重要方向，助力数据库在处理超复杂 SQL 时，实现性能与正确性的双重保障。
