线上 SQL 卡死？深挖 CTE 子查询中间结果集性能陷阱

CTE 和外层 JOIN 性能暴跌 500 倍？揭秘金仓数据库基于代价的连接条件下推

引言：一个"封装得好"的查询，为什么会慢到离谱？

在复杂的业务系统中，开发者喜欢用 CTE（Common Table Expression，公共表表达式）或子查询来封装业务逻辑。代码结构清晰了、可读性上去了，但线上一跑，查询慢得令人绝望。

一位客户曾向我们反馈这样一个场景：业务逻辑被拆成了多个 CTE，外层再用 JOIN 把这些 CTE 的结果拼起来。从 SQL 写法上看完全没问题，但执行计划一出——子查询先产生巨大的中间结果集，外层 JOIN 再去这个大结果集上操作，整个查询耗时几百毫秒甚至上秒。同样的逻辑，拆平了写性能差距能达到几百倍。

业务痛点 = 大量 CTE / 子查询封装业务逻辑
         → 外层 JOIN 无法对子查询数据提前过滤
         → 子查询先输出巨大中间结果集
         → 性能崩塌

这并非 KingbaseES 独有的问题。在关系型数据库领域，“基于代价的连接条件下推（Cost-based Join Condition Pushdown）” 一直是一个公认的难题。业界两大核心难点在于：

1. 语义安全性判定：JOIN 条件下推会改变过滤的执行位置，必须严格保证改写前后的查询结果等价。一旦判断失误，查询结果就不对了。
2. 代价评估：连接方式的改变会直接影响执行策略。如果外侧基数很大，下推后可能导致子查询被嵌套执行 N 次，引发灾难性开销。

金仓数据库在 V9R4C19 版本中，针对这一痛点给出了完整的实现方案。本文将从原理出发，带你理解这项优化是怎么做的，以及它到底带来了多大的性能提升。

原理剖析：能不能推？值不值推？

连接条件下推听起来像是一个"把 WHERE 条件挪个位置"的简单操作。实际上，它涉及两个关键判断：

第一层判断：能不能推——等价性判定

并非所有 JOIN 条件都能安全下推到子查询内部。金仓的优化器需要在改写前进行严格的语义等价性分析，具体识别以下不可下推的复杂场景：

场景	为什么不能直接推	说明
聚集函数（GROUP BY）	下推可能改变聚合范围	聚合结果集的行数和分组会被改变
窗口函数（WINDOW）	下推破坏窗口分区边界	窗口函数的 OVER 子句定义了计算边界
确定性函数	函数依赖可能被改变	某些函数的结果依赖于执行顺序
LIMIT/OFFSET	下推改变截取范围	子查询的 LIMIT 语义与外层不同

优化器会递归分析子查询的查询树（Query Tree），识别出其中包含的聚集、窗口、函数等复杂节点，只对语义安全的条件执行下推。这个过程确保了改写后的 SQL 与原始 SQL 返回完全相同的结果集。

第二层判断：值不值推——代价模型

即使语义上可以下推，优化器还要问一个关键问题：下推后真的更快吗？

考虑以下情况：子查询本身结果集很小，下推条件后需要嵌套循环执行 N 次。如果 N 很大（比如外层表有百万行），下推反而会变成灾难。

金仓数据库的代价模型会自动评估：

未下推代价 = 子查询全量执行代价 + 外层 JOIN 代价
下推代价 = 子查询被过滤后执行代价 × 外层驱动表行数

只有当 下推代价 < 未下推代价 时，优化器才会选择下推。这个决策完全自动化，开发者无需手动干预。

代码示例

下面通过一个具体场景来理解这项优化在做什么。

场景构造

-- 假设有一张员工表和一张部门表
CREATE TABLE t_employee (
    emp_id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    salary DECIMAL(10,2)
);

CREATE TABLE t_department (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50),
    location VARCHAR(50)
);

未优化场景：CTE + 外层 JOIN

-- 开发者用 CTE 封装了业务逻辑
WITH emp_summary AS (
    SELECT
        e.emp_id,
        e.name,
        e.dept_id,
        e.salary,
        ROW_NUMBER() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
    FROM t_employee e
)
SELECT
    s.name,
    s.salary,
    d.dept_name
FROM emp_summary s
JOIN t_department d ON s.dept_id = d.dept_id
WHERE s.rn = 1 AND d.location = 'Beijing';

在未优化的情况下，优化器的执行路径是这样的：

1. CTE 部分：对 t_employee 全表扫描，计算 ROW_NUMBER，生成完整的 emp_summary 结果集
2. 外层：将 emp_summary 全量结果与 t_department 进行 JOIN
3. 最后：才应用 d.location = 'Beijing' 这个过滤条件

问题在于：d.location = 'Beijing' 这个条件本可以提前到 JOIN 之前就过滤掉大部分部门数据，但由于被封装在 CTE 外层，优化器不知道能否安全下推。

优化后的执行路径

启用基于代价的连接条件下推后，优化器会：

1. 分析语义等价性：确认 d.location = 'Beijing' 下推到 JOIN 的部门侧不会影响结果正确性
2. 评估代价：部门表数据量不大，下推后部门侧扫描行数大幅减少，代价显著降低
3. 生成新执行计划：先过滤部门表，再与 CTE 结果 JOIN

-- 等价改写（概念上，优化器内部完成，用户无需手动改写）
WITH emp_summary AS (
    SELECT e.emp_id, e.name, e.dept_id, e.salary,
           ROW_NUMBER() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
    FROM t_employee e
)
SELECT s.name, s.salary, d.dept_name
FROM emp_summary s
JOIN (SELECT * FROM t_department WHERE location = 'Beijing') d
    ON s.dept_id = d.dept_id
WHERE s.rn = 1;

最佳实践

利用优化器，但不依赖优化器

连接条件下推是金仓数据库的一项智能优化，但了解其边界仍然重要。以下建议可以帮助你写出更容易被优化器"理解"的 SQL：

建议	说明
避免过度嵌套	CTE 层次越深，优化器的等价性分析越复杂，可下推的条件越少
过滤条件尽量靠近数据源	如果条件确定可以提前过滤，直接写在子查询内部
关注执行计划	使用 EXPLAIN ANALYZE 确认下推是否生效
及时更新统计信息	代价模型依赖准确的统计信息，定期执行 ANALYZE 确保代价评估准确

执行计划分析

-- 查看优化器是否执行了连接条件下推
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
WITH emp_summary AS (
    SELECT e.emp_id, e.name, e.dept_id, e.salary,
           ROW_NUMBER() OVER(PARTITION BY e.dept_id ORDER BY e.salary DESC) AS rn
    FROM t_employee e
)
SELECT s.name, s.salary, d.dept_name
FROM emp_summary s
JOIN t_department d ON s.dept_id = d.dept_id
WHERE s.rn = 1 AND d.location = 'Beijing';

关注执行计划中是否有过滤条件出现在预期的位置，以及扫描行数是否明显减少。

实测数据

金仓数据库在这项优化上的效果非常显著：

测试场景	未下推耗时	下推后耗时	性能提升
简单用例	84ms	0.14ms	600x
复杂用例	1081ms	0.23ms	4700x

作为对比，同类的 DM 数据库目前不支持此优化，在相同场景下无法自动下推连接条件，需要开发者手动改写 SQL。

与竞品的差异

特性	KingbaseES	DM v8
基于代价的连接条件下推	支持（V9R4C19+）	不支持
复杂场景语义等价性判定	自动分析	需手动改写
代价模型自动决策	自动	不适用

总结

金仓数据库 V9R4C19 的基于代价的连接条件下推优化，解决了复杂业务场景下 CTE 和子查询封装导致的性能瓶颈。它的核心价值在于：

• 等价性判定：严格确保改写前后结果一致，开发者可以放心使用 CTE 而不必担心性能问题
• 代价驱动：优化器自动评估是否下推，避免"好心帮倒忙"的退化场景
• 显著提速：简单用例 600 倍、复杂用例 4700 倍的性能提升，让封装好的代码也能跑出极致性能

对于使用 CTE 和子查询封装复杂业务逻辑的团队来说，升级到 V9R4C19 或更高版本，意味着不用改一行 SQL，就能获得显著的性能提升。这正是现代数据库优化器应有的样子——让开发者写清晰的代码，让数据库做聪明的优化。