基于代价模型的复杂查询连接条件下推技术实践——以金仓数据库为例

熊哈哈O_o
0 阅读16分钟

摘要

在企业级业务系统中，CTE、多层子查询、窗口函数、聚集计算等语法被广泛用于构建复杂SQL逻辑，在提升可读性的同时，也给数据库查询优化器带来了严峻挑战。其中，JOIN条件无法有效下推导致的过滤时机滞后、中间结果集膨胀、全表扫描泛滥，是复杂查询性能瓶颈的核心诱因之一。传统查询优化器仅支持规则化的条件下推，难以兼顾语义安全性与执行效率。本文以金仓数据库V009R002C014版本为实践载体，系统性阐述基于代价模型的连接条件下推（Cost-based Join Predicate Pushdown, CB-JPP） 技术，通过等价性判定保障语义不变，通过代价评估选择最优执行路径，最终实现复杂查询性能的数量级提升。同时结合典型业务SQL、执行计划对比、性能测试数据，完整呈现该技术的设计思路、实现流程与落地效果。

关键词

查询优化；连接条件下推；代价模型；语义等价性；复杂SQL；金仓数据库

一、引言

随着数字化转型的深入，业务系统的数据规模与查询复杂度呈指数级增长。OLAP场景、复杂报表分析、多表关联统计等需求，催生了大量包含多层子查询、窗口函数、DISTINCT、UNION、GROUP BY的复杂SQL。这类查询的核心痛点在于：业务逻辑的分层设计，导致数据过滤条件无法提前生效。

具体而言，开发人员通常将复杂计算封装在子查询/CTE中，外层仅做关联查询与最终过滤。但传统优化器无法将外层的高选择性JOIN条件反向传递到子查询内部，迫使子查询对基表进行全量扫描、全量计算，生成海量中间结果集后，再执行JOIN与过滤操作。这种执行模式直接导致CPU、内存、IO资源的大量浪费，查询响应时间从毫秒级飙升至秒级甚至分钟级。

连接条件下推（Join Predicate Pushdown）是解决该问题的核心优化手段，但业界实现普遍面临两大难题：一是语义安全性，下推不当会改变查询结果；二是代价合理性，盲目下推可能引发参数化重复执行，导致性能回退。针对这一问题，金仓数据库创新提出等价性判定+代价模型双重约束的连接条件下推方案，在保障查询结果准确的前提下，智能选择收益最高的下推路径，完美解决复杂查询的性能痛点。

二、问题背景与技术难点

2.1 业务场景典型问题建模

在金融、政务、零售等核心业务系统中，以下SQL模式是高频场景：子查询内部完成去重、聚集、窗口计算等复杂操作，外层通过JOIN关联并施加高选择性过滤条件。

典型业务SQL示例

-- 业务场景：统计指定部门的员工绩效，子查询完成员工数据去重与绩效窗口计算
SELECT 
    e.dept_id,
    e.emp_name,
    p.perf_score,
    p.rank_in_dept
FROM 
    -- 子查询1：员工基础信息去重
    (SELECT DISTINCT emp_id, emp_name, dept_id FROM employee) e
JOIN 
    -- 子查询2：员工绩效窗口排名
    (SELECT 
         emp_id, 
         perf_score, 
         RANK() OVER (PARTITION BY dept_id ORDER BY perf_score DESC) AS rank_in_dept
     FROM performance) p
ON e.emp_id = p.emp_id
-- 外层高选择性过滤条件：仅查询研发部门（dept_id=3）
WHERE e.dept_id = 3;

性能隐患分析

  1. 子查询e需要全表扫描employee表并执行DISTINCT去重，无论是否过滤部门；
  2. 子查询p需要全表扫描performance表并执行窗口函数计算，无提前过滤；
  3. 外层dept_id=3的高选择性条件，无法约束子查询的扫描范围；
  4. 两个子查询生成百万级甚至千万级中间结果集后，再执行JOIN操作，性能急剧下降。

2.2 连接条件下推的核心技术难点

连接条件下推的本质是调整谓词的生效时机与生效范围，将原本作用于JOIN阶段的条件，提前下推至子查询的扫描、过滤、计算阶段。但这一过程并非无条件可行，业界普遍面临两大核心难点：

2.2.1 语义安全性（Equivalence）

语义等价是条件下推的前提，若下推后改变查询结果，优化将失去意义。以下场景禁止无条件下推：

  1. 聚集计算（GROUP BY）：下推过滤条件会改变分组基数与聚合结果；
  2. 窗口函数：下推会改变分区（PARTITION BY）与排序（ORDER BY）的数据集；
  3. 去重/合并（DISTINCT/UNION）：提前过滤会导致去重结果失真；
  4. 非确定性函数/副作用函数：如RAND()、NOW()，下推后结果不可预测。

优化器必须通过严格的语法分析、逻辑等价判定，识别安全可下推的JOIN条件，杜绝语义错误。

2.2.2 代价评估（Cost）

语义安全≠性能最优，下推操作需要权衡收益与成本：

  1. 下推后触发参数化嵌套循环执行：若外层结果集基数极大，子查询会被重复执行数千次，IO与CPU开销激增；
  2. 下推条件无索引支撑：全表扫描+过滤的开销，大于全量计算后JOIN的开销；
  3. 中间结果集压缩收益有限：小表场景下，下推带来的性能提升可忽略不计，反而增加优化器开销。

因此，连接条件下推必须遵循能推且值得推的原则，代价模型是决策的核心依据。 在这里插入图片描述

三、传统优化方案的局限性

传统数据库查询优化器（如早期MySQL、部分开源PostgreSQL分支）针对复杂子查询的JOIN条件，采用固定规则执行策略，核心流程如下：

3.1 传统执行流程

  1. 全量执行子查询：依次扫描基表，执行DISTINCT、GROUP BY、窗口函数等复杂计算；
  2. 物化中间结果集：将子查询的计算结果写入内存/磁盘，生成临时结果集；
  3. 外层关联与过滤：加载所有中间结果集，执行JOIN操作，最后应用WHERE过滤条件。

3.2 核心缺陷

  1. 过滤时机滞后：高选择性条件无法作用于基表扫描阶段，全表扫描不可避免；
  2. 中间结果集膨胀：复杂计算产生海量临时数据，内存溢出时触发磁盘IO，性能断崖式下跌；
  3. 无智能决策能力：仅支持简单子查询的条件下推，对多层嵌套、窗口函数、UNION等场景完全失效；
  4. 资源浪费严重：大量无效数据参与计算，CPU、内存、IO资源被无效占用。

以2.1节的业务SQL为例，传统优化器会先全量处理employee和performance表，生成百万级中间结果后再关联过滤，完全无法利用dept_id=3的过滤能力。

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

金仓数据库V009R002C014版本突破传统规则化优化的局限，设计实现了等价性判定+代价模型双重约束的连接条件下推机制。该机制分为两个核心阶段：能不能推（语义安全） 和值不值推（代价最优），完整覆盖复杂查询的下推优化场景。

4.1 核心设计架构

整体优化流程嵌入查询优化器的逻辑优化→物理优化阶段，核心架构如下：

  1. 逻辑解析：解析SQL语法树，拆分JOIN条件、子查询结构、过滤条件；
  2. 等价性判定模块：验证JOIN条件下推的语义安全性；
  3. 代价模型模块：评估下推前后的执行代价，选择最优路径；
  4. 计划重写模块：对符合条件的SQL，重写执行计划，完成条件下推；
  5. 物理执行：执行优化后的计划，提前过滤数据，提升性能。

4.2 阶段一：等价性判定——保障语义安全

该阶段的核心目标是绝对安全，拒绝所有可能改变查询结果的下推操作。优化器通过语法树遍历+规则匹配完成判定，核心流程如下：

4.2.1 子查询结构分类判定

将子查询分为安全型与非安全型，匹配下推规则：

子查询结构下推安全性判定规则
普通投影查询（无计算）安全所有JOIN条件可直接下推至基表扫描阶段
DISTINCT 去重查询条件安全仅下推不影响去重列的过滤条件
GROUP BY 聚集查询条件安全仅下推分组列相关的JOIN条件，禁止下推聚合列相关条件
窗口函数查询条件安全仅下推分区列/排序列相关的JOIN条件
UNION/UNION ALL 合并查询条件安全条件下推至UNION所有分支，且不改变合并结果
非确定性函数查询非安全禁止任何下推操作

4.2.2 JOIN条件拆分与参数化

对等价的JOIN条件进行拆分，分离外层依赖列与子查询内部列，将其改写为参数化过滤条件。

等价性判定代码示例（内核简化版）

// 金仓数据库内核：JOIN条件等价性判定核心函数
bool CheckJoinPredPushSafe(Query *join_query, Subquery *subq) {
    // 1. 检查子查询是否包含非确定性函数
    if (contain_volatile_functions(subq->targetList)) {
        elog(DEBUG1, "子查询包含非确定性函数，禁止下推");
        return false;
    }
    // 2. 检查窗口函数：仅允许分区列相关条件下推
    if (subq->hasWindowFuncs) {
        List *window_part_cols = get_window_partition_cols(subq);
        if (!contain_cols(join_pred, window_part_cols)) {
            elog(DEBUG1, "JOIN条件与窗口分区列无关，禁止下推");
            return false;
        }
    }
    // 3. 检查GROUP BY：仅允许分组列相关条件下推
    if (subq->groupClause != NIL) {
        List *group_cols = get_group_cols(subq);
        if (!contain_cols(join_pred, group_cols)) {
            elog(DEBUG1, "JOIN条件与分组列无关，禁止下推");
            return false;
        }
    }
    // 4. 所有安全检查通过，允许下推
    return true;
}

4.2.3 等价性判定结论

只有通过所有安全规则检查的JOIN条件，才能进入代价评估阶段；未通过的条件，严格保持原执行计划，保障查询结果准确。

4.3 阶段二：代价模型评估——保障性能最优

语义安全仅为下推的必要条件，优化器会通过代价模型计算下推前后的执行成本，选择整体代价最低的方案。金仓数据库的代价模型基于IO代价、CPU代价、内存代价、执行次数四大核心指标构建。

4.3.1 代价计算公式

总代价 = 扫描IO代价 + 计算CPU代价 + 内存占用代价 + 重复执行代价
  1. 扫描IO代价：下推后基表扫描行数减少，IO代价显著降低；
  2. 计算CPU代价：下推后中间结果集缩小，DISTINCT/窗口函数计算量减少；
  3. 重复执行代价：参数化执行的次数 × 子查询单次执行代价；
  4. 收益判定：下推前总代价 - 下推后总代价 ＞ 0，执行下推；否则放弃。

4.3.2 代价评估核心流程

  1. 基数预估：通过统计信息，预估外层表过滤后的基数、子查询下推前后的扫描行数；
  2. 代价计算：分别计算下推与不下推的总执行代价；
  3. 路径选择：选择代价更低的执行计划，完成条件下推重写。

代价评估SQL示例

-- 原始SQL：子查询+JOIN
SELECT * FROM (SELECT DISTINCT s3a, s3b FROM s3) s3 
JOIN s1 ON s1.s1a = s3.s3a;

-- 等价性判定通过：DISTINCT查询，s3a为去重列，条件可下推
-- 代价评估：
-- 下推前：s3全表扫描100万行 → DISTINCT去重 → JOIN
-- 下推后：s3扫描时过滤s3a = s1.s1a → 仅扫描1000行 → DISTINCT → JOIN
-- 代价差值：下推代价降低99%，执行下推

-- 下推后重写SQL（内核自动生成）
SELECT * FROM s1 
JOIN (SELECT DISTINCT s3a, s3b FROM s3 WHERE s3a = s1.s1a) s3 
ON s1.s1a = s3.s3a;

4.4 完整优化工作流程

SQL解析 → 生成逻辑语法树 → 识别JOIN条件与子查询 → 等价性判定 → （不通过→保留原计划）
→ （通过→代价模型评估）→ （代价升高→放弃下推）→ （代价降低→重写执行计划）→ 物理执行

五、效果验证与性能对比

为验证基于代价的连接条件下推的实际效果，我们设计最小化用例与复杂场景用例，对比金仓数据库下推开启/关闭、以及友商数据库的执行性能，同时结合执行计划分析优化原理。

5.1 测试环境

  • 数据库：金仓数据库V009R002C014（开启CB-JPP）、友商主流数据库（不支持JOIN条件下推）
  • 测试表： 
    -- 基础测试表s1：1万行数据，s1a为主键
CREATE TABLE s1 (s1a INT PRIMARY KEY, s1b INT, s1c VARCHAR(20));
INSERT INTO s1 SELECT generate_series(1,10000), random()*100, 'test';

-- 测试表s3：100万行数据，包含重复数据
CREATE TABLE s3 (s3a INT, s3b INT, s3c VARCHAR(20));
CREATE INDEX idx_s3a ON s3(s3a);
INSERT INTO s3 SELECT random()*10000, random()*100, 'test' FROM generate_series(1,1000000);
  • 测试指标：执行时间、扫描行数、中间结果集大小

5.2 最小化用例测试

测试SQL

-- 最小化下推测试用例
SELECT * FROM (SELECT DISTINCT * FROM s3) s3 
JOIN s1 ON s1.s1a = s3.s3a;

  • 未下推：子查询全表扫描 + 去重，执行时间约 84ms 在这里插入图片描述

  • 下推后：子查询扫描阶段即可被 JOIN 条件裁剪，执行时间约 0.14ms 在这里插入图片描述

5.3 复杂场景测试

该用例包含UNION、DISTINCT、窗口函数、多层子查询，完全模拟真实业务复杂SQL。

测试SQL

EXPLAIN ANALYZE
SELECT * 
FROM 
    -- 第一层子查询：UNION+DISTINCT+JOIN
    (SELECT * 
     FROM (SELECT DISTINCT * FROM s3 
           UNION 
           SELECT DISTINCT * FROM s3 a) s3 
     JOIN s1 ON s1.s1d = s3.s3a) s
JOIN 
    -- 第二层子查询：窗口函数+JOIN
    (SELECT * 
     FROM (SELECT s3a, SUM(s3b) OVER (PARTITION BY s3a) AS s3d FROM s3) s3 
     JOIN s1 ON s1.s1a = s3.s3a) j 
ON s.s3d = j.s3a;

未下推时：

1. 多个子查询对基表进行全量扫描
2. 生成多个巨大的中间结果集
3. 最终 JOIN 成为性能瓶颈                                |

在这里插入图片描述 下推后：

1. JOIN 条件提前参与子查询扫描
2. 多个子查询由“全量扫描”转为“选择性扫描”
3. 整体执行时间从 1081ms 降至 0.23ms

在这里插入图片描述

5.4 测试结论

通过上述复杂场景下的sql来看，当连接条件不下推时，需要先处理内部的union查询，并且union的左右两侧对基表进行去重的全扫描，产生一个很大的结果集A然后与基表s1进行连接产生一个中间结果集B，然后执行右侧的子查询，对基表s3进行分组并计算窗口函数得到一个大的中间结果集C与基表s1进行连接得到结果集D,最后两个较大的中间结果集B和D进行连接，在这个过程中子查询几乎需要对表进行全表扫得到数据，耗费很多时间，导致性能差。 当我们实现将连接条件推入子查询后，可以利用连接条件下推到子查询中，可以对子查询的数据在扫描阶段就被筛选裁剪减少扫描时间，筛选后的结果集在进行后续的连接操作可以减少连接操作的时间，整体的查询从全量扫描变为筛选性的扫描，带来性能上的提升，从未下推的1081ms变为下推后的0.23ms。

六、技术价值与应用场景

6.1 核心技术价值

  1. 语义绝对安全：通过等价性判定规则，杜绝下推导致的查询结果错误；
  2. 智能代价决策：避免盲目下推带来的性能回退，自适应不同数据规模；
  3. 全场景覆盖：支持DISTINCT、GROUP BY、窗口函数、UNION、多层子查询等复杂语法；
  4. 无侵入使用：业务SQL无需修改，优化器自动完成下推优化，透明提升性能。

6.2 核心应用场景

  1. OLAP复杂分析：多表关联、多层子查询的报表统计；
  2. 混合负载系统：OLTP+OLAP混合场景，平衡简单查询与复杂查询性能；
  3. 大数据量去重/聚合：包含DISTINCT、GROUP BY的关联查询；
  4. 窗口函数计算：基于分区、排序的复杂统计分析。

七、总结与展望

复杂查询的连接条件下推，并非简单的规则化改写，而是语义安全与代价最优的平衡艺术。传统优化器仅关注规则匹配，无法解决语义风险与性能回退问题；而金仓数据库创新提出的基于代价的连接条件下推，通过双重约束机制，完美解决了这一行业难题。

该技术的核心价值在于：在保障查询结果100%准确的前提下，将高选择性JOIN条件提前下推至基表扫描阶段，从源头减少数据扫描量与中间结果集规模，实现复杂查询性能的数量级提升。在实测中，无论是简单去重查询，还是包含UNION、窗口函数的多层嵌套查询，优化效果均远超传统方案。

未来，我们将进一步优化代价模型的精度，支持分布式场景下的连接条件下推、CTE表达式的深度下推，并结合AI智能优化技术，实现更精准的基数预估与代价计算，持续引领复杂查询优化技术的演进，为企业级业务系统提供极致的查询性能。

avatar
文章
阅读
粉丝