在DB2数据库的日常开发中，我们经常需要处理多步骤的复杂查询逻辑。同一个需求，既可以用嵌套的子查询来实现，也可以用WITH子句定义的公共表表达式（CTE）来完成。两种写法在语法层面似乎只是风格差异，但不少开发者会发现，同样的逻辑有时换成CTE后性能明显变化，有时却毫无区别，甚至偶尔变得更慢。这背后其实是DB2优化器对两种写法的处理机制在起作用。本文将从原理、执行计划、典型场景三个维度，系统地对比子查询与CTE的性能差异。
一、DB2优化器如何处理子查询与CTE
首先要明确一个核心结论：DB2优化器在生成执行计划之前，会对SQL语句进行语义改写。也就是说，你写的SQL只是逻辑表达，最终如何执行由优化器决定。这一点是理解子查询与CTE性能对比的基础。
对于子查询，DB2优化器通常会做所谓的“子查询展开”或者“子查询转换为连接”的改写。例如一个IN子查询，优化器会尽可能将其改写为半连接，从而利用嵌套循环连接、哈希连接或合并连接等连接策略。相关子查询（即外层查询字段出现在内层查询中的子查询）则可能被改写为相关嵌套循环执行，也可能被去相关化改写为哈希连接，具体取决于DB2的版本和统计信息。如果统计信息准确、表上有合适的索引，去相关化往往能带来数量级的性能提升。
对于CTE，DB2的处理逻辑略有不同。CTE本质上是一个命名查询块。对于非递归CTE，DB2优化器默认会做“查询合并”，即将CTE的定义内联展开到主查询中，此时CTE和子查询在优化器眼中几乎没有区别，执行计划也一致。但如果DB2判断该CTE被引用多次，或者使用了某些特殊语法（例如递归CTE、包含特定提示），则会采用“物化”策略，也就是先把CTE结果集计算出来存入临时表，后续引用直接读取临时表。物化是一种双刃剑：当CTE逻辑复杂且被多次引用时，它可以避免重复计算；但当CTE结果集很大而主查询只会用到其中一小部分时，物化反而会带来额外的排序、临时表空间开销，导致性能下降。
可以用一个简单例子说明二者的等价性。下面两个查询在DB2中通常会生成完全相同的执行计划：
-- 写法一：子查询
SELECT e.empno, e.lastname
FROM employee e
WHERE e.workdept IN (SELECT deptno
FROM department
WHERE deptname LIKE 'SOFTWARE%');
-- 写法二：非递归CTE
WITH soft_dept AS (
SELECT deptno FROM department
WHERE deptname LIKE 'SOFTWARE%'
)
SELECT e.empno, e.lastname
FROM employee e
WHERE e.workdept IN (SELECT deptno FROM soft_dept);
通过db2expln或db2exfmt工具查看执行计划，你会发现两者都被改写为EMPLOYEE与DEPARTMENT的半连接，访问路径、连接方式完全一致。这验证了一个重要观点：对于简单的非递归场景，子查询和CTE的性能差异几乎为零，选择哪种写法更多是代码可读性的考量。
二、典型场景下的性能差异分析
1. 标量子查询在SELECT列表中的开销
在SELECT列表中使用标量子查询是一个常见的性能陷阱。由于标量子查询对外层每一行都要执行一次（如果没被去相关化），当外层结果集很大时，执行次数会呈线性放大。DB2优化器虽然会尝试缓存相同参数的标量子查询结果，但如果相关列的区分度很高，缓存几乎无效。
-- 标量子查询写法：部门名针对每行员工计算一次
SELECT e.empno,
e.lastname,
(SELECT d.deptname
FROM department d
WHERE d.deptno = e.workdept) AS deptname
FROM employee e;
-- 改写为LEFT OUTER JOIN通常更优
SELECT e.empno, e.lastname, d.deptname
FROM employee e
LEFT OUTER JOIN department d
ON d.deptno = e.workdept;
一般来说，LEFT OUTER JOIN写法能让优化器获得更充分的连接方式选择空间，尤其是两表都较大时，哈希连接的效果明显优于逐行执行的标量子查询。在老版本的DB2 for LUW或DB2 for z/OS上，标量子查询的去相关化能力有限，这个问题尤其突出。
2. CTE被多次引用时的物化优势
当同一段中间结果需要被引用多次时，CTE的优势就体现出来了。用子查询实现同样的逻辑，同一段查询必须重复书写两遍，DB2在不做统一优化的情况下可能将其计算两遍。而CTE配合物化策略，只需计算一次。
WITH dept_stat AS (
SELECT workdept,
AVG(salary) AS avg_sal,
COUNT(*) AS emp_cnt
FROM employee
GROUP BY workdept
)
SELECT a.workdept, a.avg_sal
FROM dept_stat a
WHERE a.avg_sal > (SELECT AVG(avg_sal) FROM dept_stat)
AND a.emp_cnt > 10;
上面例子中dept_stat被引用了两次，如果DB2选择物化该CTE，聚合计算只执行一次。等价的子查询写法需要把GROUP BY子句完整写两遍，不仅冗长，而且优化器未必能识别两段子查询是同一逻辑。需要注意的是，DB2是否真的物化可以通过执行计划确认：如果计划中出现TEMP表操作符且CTE只计算一次，说明物化生效了。
3. 递归场景：CTE的不可替代性
处理组织架构、物料清单（BOM）等层次数据时，递归查询是刚需。在DB2中，递归查询只能通过递归CTE实现，普通子查询无法表达递归逻辑。递归CTE的标准结构包含锚点查询和递归项两部分：
WITH org_tree (empno, lastname, mgrno, lvl) AS (
-- 锚点：从总经理开始
SELECT empno, lastname, mgrno, 1
FROM employee
WHERE mgrno IS NULL
UNION ALL
-- 递归项：逐层向下查找下属
SELECT e.empno, e.lastname, e.mgrno, t.lvl + 1
FROM employee e, org_tree t
WHERE e.mgrno = t.empno
)
SELECT empno, lastname, lvl
FROM org_tree
ORDER BY lvl;
递归CTE的性能要点在于：每轮递归都会产生一次对驱动表的工作单元访问，因此递归项中的过滤条件能否下推、连接列上是否有索引，直接决定递归查询的效率。在mgrno列上建立索引后，每层递归都可以走索引定位，整体性能可以保持稳定。这一点是子查询完全没有对应能力的场景，也说明两者并非纯粹的竞争关系，而是各有分工。
三、如何用执行计划验证和选型
与其凭经验争论哪种写法快，不如直接看执行计划。DB2提供了EXPLAIN机制，配合db2exfmt工具可以输出格式化的访问计划。重点观察三个信号：第一，查询中的CTE是否被合并（内联）还是物化为临时表，物化操作通常体现为TEMP表操作符；第二，子查询是否被改写为连接，计划中出现HSJOIN、NLJOIN等连接操作符说明已完成去相关化，如果只看到FILTER加子查询块则可能仍是逐行执行；第三，估算的总代价和基数字估计是否合理，如果估计行数与实际偏差过大，应先更新统计信息再做对比。
-- 设置解释表并查看执行计划 SET CURRENT EXPLAIN MODE EXPLAIN; -- 执行你的查询（不会真正返回结果，只生成计划） -- ... 你的SQL ... SET CURRENT EXPLAIN MODE NO; -- 格式化输出执行计划 -- db2exfmt -d SAMPLE -1 -o plan.txt
基于以上分析，可以总结出几条实用的选型建议：
- 简单一次性引用的中间逻辑：子查询与非递归CTE性能基本等价，优先选择可读性更好的CTE，命名清晰的CTE能显著降低复杂SQL的维护成本。
- 中间结果被多次引用：优先使用CTE，让优化器有机会物化，避免重复计算；写法上应保证各处引用的子查询逻辑完全一致。
- SELECT列表中的标量子查询：数据量增长后要警惕，尽早改写为LEFT OUTER JOIN并验证执行计划。
- 层次结构和递归逻辑：必须使用递归CTE，同时确保递归连接列上有索引。
- CTE结果集巨大但主查询过滤性强：注意物化可能反而拖慢查询，可尝试调整写法让过滤条件进入CTE内部，或使用嵌套子查询控制物化时机。
总的来说，DB2中子查询与CTE的性能差异并非固定结论，而是取决于优化器改写策略、引用次数、结果集大小和统计信息的质量。理解“合并”与“物化”这两个关键机制，养成用执行计划说话的习惯，才能在两种写法之间做出正确的选择。大多数情况下，清晰的结构化CTE代码加上准确的统计信息，就能同时兼顾可读性和性能，这也是现代SQL开发的主流实践方向。