在SQL查询中，标量子查询（Scalar Subquery）是一种常见的表达式形式，它返回单个值并可以出现在SELECT列表、WHERE条件等位置。这种写法在表达业务逻辑时非常直观，但给查询优化器带来了不小的挑战。当外层查询返回大量行，且子查询需要逐行执行时，性能问题会迅速暴露。
本文将直接从技术角度讨论标量子查询消除的设计原理、实现难点以及在金仓国产数据库中的具体实践。不绕弯子，不讲故事，只谈技术。
一、问题定义
1.1 标量子查询的执行模型
先看一个典型的结构：
SELECT
t1.id,
t1.name,
(SELECT SUM(amount) FROM t2 WHERE t2.ref_id = t1.id) AS total
FROM t1;
数据库执行此查询时，最直观（也是最原始）的策略是：
- 扫描外层表t1，得到一组行 2. 对于t1的每一行，执行一次子查询，扫描t2并计算SUM 3. 将子查询的结果拼接到外层行中输出
这个策略的时间复杂度是O(|t1| * Cost(subquery))。当t1的行数达到万级、十万级时，即便每个子查询都走索引，累积开销也相当可观。
1.2 性能瓶颈的本质
问题根源不在子查询本身，而在于执行顺序——子查询与外层查询之间是嵌套循环（Nested Loop）的关系。优化器的理想目标是将其转换为集合操作：先一次性计算出所有需要的聚合结果，再与外层表做连接。
但为什么不能简单转换？因为标量子查询的语义约束比普通连接更严格。下面详细分析这些约束。
二、等价性判定的技术难点
将标量子查询改写为连接操作，必须保证任何输入情况下改写前后的结果完全一致。这里有三个核心语义问题。
2.1 空集语义差异
标量子查询在找不到匹配行时，其返回值取决于子查询中的表达式：
- 对于
SUM(expr)、AVG(expr)、MAX(expr)、MIN(expr)，返回NULL- 对于COUNT(*)或COUNT(expr)，返回0- 对于不包含聚合函数的简单查询（如SELECT col FROM ... WHERE ...），返回NULL
再看连接操作。如果用LEFT JOIN替换子查询，当右表没有匹配行时，右表所有字段为NULL。那么：
- 如果原子查询是
SELECT SUM(amount)，LEFT JOIN后聚合结果为NULL，与原语义一致 - 如果原子查询是SELECT COUNT(*)，LEFT JOIN后也会得到NULL（因为右表字段NULL，COUNT(*)需要基于该行做计数，但如果我们是在子查询内部先做COUNT再连接，实际得到的是NULL还是别的？需要仔细分析）
具体来说，将COUNT子查询改写为LEFT JOIN + GROUP BY的典型形式是：
-- 原始
SELECT (SELECT COUNT(*) FROM t2 WHERE t2.ref_id = t1.id) FROM t1;
-- 改写（错误）
SELECT COALESCE(agg.cnt, 0) FROM t1 LEFT JOIN (SELECT ref_id, COUNT(*) AS cnt FROM t2 GROUP BY ref_id) agg ON t1.id = agg.ref_id;
这里COALESCE(agg.cnt, 0)将NULL转换为0，语义正确。但如果忽略COALESCE，直接使用agg.cnt，则无匹配行时返回NULL，与原意的0不符。
因此，COUNT类型的子查询需要特殊标记，在改写后自动包裹COALESCE。
2.2 多行返回的语义违规
标量子查询的一个隐藏要求是：子查询在运行时最多只能返回一行。如果实际返回多行，数据库会抛出错误（如“scalar subquery returned more than one row”）。
这是一个防御性语义：它提醒用户关联条件不唯一或缺少聚合。如果优化器擅自将子查询改写为连接，可能会消除这个错误检查，导致查询默默返回错误结果（连接导致行数膨胀）。
例如：
-- t2中ref_id不唯一，存在多条记录
SELECT (SELECT amount FROM t2 WHERE t2.ref_id = t1.id) FROM t1;
原始执行时，如果某个t1.id在t2中有两行，数据库会报错。而改写为LEFT JOIN后：
SELECT t2.amount FROM t1 LEFT JOIN t2 ON t1.id = t2.ref_id;
此时若t1.id对应两条t2记录，则t1的那一行会膨胀成两行输出，完全改变了结果集的基数。更严重的是，数据库不会报错。
因此，等价性判定必须排除这类可能存在多行返回的子查询。判定条件为：
- 子查询包含聚合函数（不含GROUP BY）：保证返回一行，安全 - 子查询不包含聚合但有关联条件：需要检查关联列在子查询表中是否唯一（通过主键、唯一约束或统计信息推断） - 子查询包含GROUP BY：可能返回多行，不安全（除非外层有聚合或通过其他方式保证唯一）
2.3 非相关子查询的边界
还有一种情况：子查询完全不依赖外层查询（非相关子查询）。此时子查询在整个查询执行中只计算一次，不会出现逐行执行的开销。这类子查询没有消除的必要，强行消除反而可能增加复杂度。
优化器需要能识别相关性子查询（Correlated Subquery），即子查询中引用了外层表的列。只有相关子查询才存在重复执行的问题。
三、消除算法的设计
通过等价性判定后，接下来就是具体的改写算法。整体流程分为三个阶段：识别、转换、合并。
3.1 识别阶段：收集可消除的标量子查询
优化器在分析查询树时，遍历SELECT列表、WHERE条件、HAVING条件等位置，收集所有标量子查询节点。对每个子查询调用等价性判定函数，生成一个“可消除”标记，同时记录元信息：
- 子查询中引用的外层相关列 - 子查询的聚合函数类型及参数 - 子查询的WHERE条件（除关联条件外的过滤条件） - 是否需要COALESCE处理
数据结构示意（伪代码）：
typedef struct ScalarSubqueryInfo {
NodeTag type;
List *correlated_vars; // 相关的外层变量
AggKind agg_type; // SUM, COUNT, AVG, MIN, MAX, NONE
Expr *agg_arg; // 聚合参数，如 amount
List *extra_conditions; // 子查询中额外的过滤条件
bool need_coalesce; // 是否需要COALESCE转NULL为0（针对COUNT）
bool is_safe; // 是否通过等价性判定
} ScalarSubqueryInfo;
3.2 转换阶段：子查询变内联视图
对于单个可消除的子查询，转换步骤为：
- 提取子查询的查询树：将子查询中的FROM、WHERE、GROUP BY等结构提取出来，构成一个独立的查询块。 2. 添加分组聚合：如果原子查询包含聚合函数，则在新的查询块中保留该聚合；如果原子查询不含聚合（即简单的列投影），则转换为
MIN(expr)或MAX(expr)来保证单行输出（同时添加GROUP BY相关列）。 3. 生成内联视图：将上述查询块包装为内联视图（Inline View），视图的列包括相关列和聚合结果列。 4. 生成LEFT JOIN：将原外层查询的FROM子句与内联视图进行左外连接，连接条件为原子查询中的相关条件。 5. 替换表达式：将原SELECT列表中的子查询节点替换为对内联视图结果列的引用。
具体到SQL变换，原查询：
SELECT
id,
(SELECT SUM(amount) FROM t2 WHERE t2.ref_id = t1.id AND t2.status = 'active') AS sum_amt
FROM t1;
转换后：
SELECT
t1.id,
v.sum_amt
FROM t1
LEFT JOIN (
SELECT
ref_id,
SUM(amount) AS sum_amt
FROM t2
WHERE status = 'active'
GROUP BY ref_id
) v ON t1.id = v.ref_id;
注意：原子查询中的额外条件status = 'active'被移入内联视图的WHERE子句中。关联条件t2.ref_id = t1.id变为连接条件t1.id = v.ref_id。
对于COUNT类型，内联视图相同，但在外层引用时使用COALESCE(v.cnt, 0)。
3.3 合并阶段：相似子查询的融合
当SELECT列表中存在多个标量子查询，且它们具有相同的“基础查询模式”时，可以合并为一个内联视图，一次性计算出多个聚合值。
合并条件：
- 子查询访问的基表相同（FROM子句相同） - 子查询中的关联条件相同（比较表达式结构一致） - 子查询中的公共WHERE条件相同（除聚合参数相关的条件外） - 子查询都不包含GROUP BY（或GROUP BY列相同）
合并方法：
将多个子查询的聚合函数合并到同一个GROUP BY查询中。对于不同子查询中的不同过滤条件，使用CASE WHEN条件聚合实现。
例如，原查询：
SELECT
id,
(SELECT SUM(amount) FROM t2 WHERE t2.ref_id = t1.id AND t2.type = 'A') AS sum_a,
(SELECT SUM(amount) FROM t2 WHERE t2.ref_id = t1.id AND t2.type = 'B') AS sum_b,
(SELECT COUNT(*) FROM t2 WHERE t2.ref_id = t1.id) AS total_cnt
FROM t1;
合并后：
SELECT
t1.id,
COALESCE(v.sum_a, 0) AS sum_a,
COALESCE(v.sum_b, 0) AS sum_b,
COALESCE(v.total_cnt, 0) AS total_cnt
FROM t1
LEFT JOIN (
SELECT
ref_id,
SUM(CASE WHEN type = 'A' THEN amount ELSE 0 END) AS sum_a,
SUM(CASE WHEN type = 'B' THEN amount ELSE 0 END) AS sum_b,
COUNT(*) AS total_cnt
FROM t2
GROUP BY ref_id
) v ON t1.id = v.ref_id;
注意：原始第三个子查询COUNT(*)不需要条件，直接COUNT即可。
合并的关键是条件聚合的生成。优化器需要分析每个子查询的WHERE条件，提取出可转为CASE WHEN的谓词。对于等值条件（如type = 'A'），这是直接可映射的；对于范围条件或复杂谓词，合并的可能性降低。
在实现中，金仓数据库当前支持等值条件的合并，对于包含IN、BETWEEN等复杂条件的子查询，暂时不做合并，以保证正确性优先。
四、与代价模型的交互
标量子查询消除不是无条件应用的。优化器需要基于代价估算决定是否执行转换。
4.1 代价估算因素
转换后的执行计划通常采用Hash Join或Merge Join，外加一个分组聚合（GroupAggregate或HashAggregate）。优化器需要比较两种方案的代价：
- 原始方案代价：外层表扫描代价 + 外层行数 × (子查询单次执行代价) - 转换方案代价：外层表扫描代价 + 子查询表扫描与分组聚合代价 + 连接代价
当外层行数很大且子查询单次执行代价可观时，转换方案占优。但当外层行数很小（例如只有几行）时，转换方案的分组聚合可能扫描整张子查询表，反而更差。
4.2 启发式阈值与统计信息
实践中，优化器可以设置启发式阈值：当外层查询的估算行数小于某个值（如10）时，不进行消除。这个阈值可以基于配置参数调整。
更精细的做法是利用统计信息：如果子查询表的关联列高度倾斜，某些值出现频率极高，那么分组聚合的代价可能接近全表扫描，此时需要谨慎。
金仓数据库在实现中，将转换决策与代价估算深度集成。消除候选子查询生成后，代价模块会分别计算原始计划成本和转换计划成本，选择成本较低者。对于多个子查询的合并，会尝试不同的合并分组，选择最优组合。
五、实现中的边界处理
实际工程实现中，除了核心算法，还需要处理大量边界情况。
5.1 多层嵌套的子查询
考虑如下结构：
SELECT
id,
(SELECT (SELECT SUM(amount) FROM t3 WHERE t3.ref_id = t2.id) FROM t2 WHERE t2.ref_id = t1.id)
FROM t1;
这种嵌套子查询需要递归处理：先消除最内层的标量子查询，将其改写为连接，然后外层再作为新的子查询参与判定。实现时需要支持查询树的多次遍历和变换。
5.2 子查询位于WHERE条件中
标量子查询不仅出现在SELECT列表，还可能出现在WHERE、HAVING甚至JOIN条件中。例如：
SELECT * FROM t1 WHERE t1.amount > (SELECT AVG(amount) FROM t2 WHERE t2.ref_id = t1.id);
这类子查询同样可以消除，改写为LEFT JOIN后将比较条件移到WHERE子句（注意处理NULL）。但需要注意的是，当内联视图返回NULL时，比较结果为UNKNOWN，行会被过滤，这与原始语义（子查询返回NULL时条件为UNKNOWN，不满足>）是一致的。
5.3 子查询中引用外层多个表
如果子查询中引用了外层多个表的列，消除时需要将这些相关列全部纳入GROUP BY和内联视图的连接条件。例如：
SELECT t1.id, t2.name,
(SELECT SUM(amount) FROM t3 WHERE t3.ref1 = t1.id AND t3.ref2 = t2.id)
FROM t1, t2 WHERE t1.id = t2.t1_id;
内联视图需要按(ref1, ref2)分组，连接条件为t1.id = v.ref1 AND t2.id = v.ref2。这依然可行。
5.4 子查询中包含DISTINCT
如果子查询中包含DISTINCT但没有聚合，例如SELECT DISTINCT col FROM t2 WHERE ...，且关联条件保证唯一性（实际上DISTINCT已经去重），这种理论上也可以消除，但需要转换语义。由于实现复杂度较高，当前金仓版本对此类子查询不做消除，标记为不安全。
5.5 子查询中包含LIMIT或OFFSET
带LIMIT的子查询结果行数被限制，但其空集语义和连接转换后的行为难以保证等价。例如LIMIT 1在无匹配行时返回空，但连接后如果存在至少一行则会返回那行。除非能证明子查询在每行外层输入下都至少有一行匹配，否则无法消除。这类子查询通常排除在外。
六、与向量化执行的协同
标量子查询消除不仅仅是一个逻辑优化，它还为底层的物理执行铺平了道路。
在现代数据库系统中，向量化执行引擎（Vectorized Execution）通过批量处理数据（例如一次处理1024行）和SIMD指令来提高CPU效率。向量化执行要求执行计划中的算子能够以批量为单位进行数据传递和处理。
6.1 原始执行的向量化障碍
未消除的标量子查询执行计划中，驱动算子（外层表Scan）和子查询算子之间是参数化嵌套循环关系。对于外层每一行，需要启动一次子查询执行。这种“一行一启动”的模式破坏了向量化的流水线：每个子查询实例都有自己的状态，无法批量处理。即使子查询内部实现了向量化，但调用开销和上下文切换也会抹平收益。
6.2 消除后的批量友好形态
消除后的计划变为LEFT JOIN + 内联视图（聚合）。这个形态在向量化执行引擎中表现优异：
- 外层表Scan可以批量输出行（例如每批1024行） - 内联视图的聚合操作（HashAggregate）也可以批量输入 - 连接算子（Hash Join）支持批量的Probe操作
整个执行流程从“行驱动”变为“批驱动”，CPU流水线的效率大幅提升。
金仓数据库的向量化执行引擎在执行LEFT JOIN和HashAggregate时，会自动使用批量处理接口。这意味着标量子查询消除间接让查询享受到了向量化的红利。实测中，消除加向量化的组合比单纯消除又提升了约30%~50%的性能。
七、性能验证数据
下面通过一组标准测试来展示消除机制的效果。测试环境为标准x86服务器，数据量分别为小、中、大三档。
7.1 测试设置
- 表t1：行数N（10K, 100K, 1M） - 表t2：行数10M，在ref_id列上有索引 - 查询：
SELECT (SELECT SUM(value) FROM t2 WHERE t2.ref_id = t1.id) FROM t1;
7.2 测试结果
| t1行数 | 未消除（秒） | 消除后（秒） | 加速比 |
|---|---|---|---|
| 10,000 | 32.4 | 0.24 | 135x |
| 100,000 | 318.7 | 0.86 | 370x |
| 1,000,000 | >3600(超时) | 6.7 | >537x |
消除后的执行时间随t1增长呈准线性（O(|t1| + |t2|)），而原始版本是O(|t1| * log|t2|)（假设索引扫描）。当t1达到百万级时，原始版本已无法完成。
7.3 多个子查询合并的收益
使用三个相似子查询（SUM、AVG、COUNT）的测试：
| 优化方式 | 执行时间（秒） | t2扫描次数 |
|---|---|---|
| 未消除 | 95.3 | 3×N |
| 分别消除（不合并） | 0.72 | 3 |
| 消除+合并 | 0.31 | 1 |
合并带来的额外加速约2.3倍，主要得益于减少了对t2的扫描次数。
八、未来工作
标量子查询消除在现有版本中已经取得了显著效果，但仍有扩展空间：
- 支持更复杂的子查询结构：例如包含UNION/EXCEPT的子查询，在特定条件下（如UNION两侧都满足唯一性）也可以考虑消除。 2.条件聚合的智能生成：当前只支持等值条件转CASE WHEN，未来可以扩展到范围条件、IN列表等。 3. 与执行器的更深度融合：针对消除后的JOIN+聚合模式，开发专门的执行路径（如直接基于外键关系的Left Join Group By优化）。 4. 并行执行的适配：消除后的查询更易于并行化（对t2的分组聚合可以分片并行），需要优化器自动分配并行度。
九、总结
标量子查询消除是一项既有理论深度又有工程价值的优化技术。它要求优化器在不改变语义的前提下，将嵌套循环执行模式转换为集合操作模式。核心难点在于等价性判定，特别是空集语义差异和多行返回的违规检测。
该优化机制在对应V009R002C014实现中了一套完整的消除机制，包括等价性判定、子查询变左外连接、相似子查询合并等步骤。该机制与代价模型和向量化执行引擎协同工作，在实际场景中取得了最高数百倍的性能提升。
对于数据库内核开发者来说，标量子查询消除是一个典型的“小优化、大收益”案例。它提醒我们：有时候，最直观的SQL写法并不对应最佳的执行计划，而优化器的职责就是弥合这个差距。
