在真实业务系统里，SQL 往往不是教科书里的三五行示例，而是由 CTE、多层子查询、窗口函数、聚合计算拼接出来的一大段逻辑。这样的 SQL 对开发者很友好：层次清楚、语义直观、便于维护。但对数据库优化器来说，它们可能意味着一场“推理考试”。

其中一个很典型、也很容易被忽视的性能瓶颈，就是 SELECT 目标列里的标量子查询，也就是 Scalar Subquery。

比如类似这样的写法：

SELECT
    t1.id,
    (SELECT SUM(t2.id) FROM t2 WHERE t2.id = t1.id) AS sum_id
FROM t1;

从业务角度看，它很自然：对 t1 的每一行，到 t2 里计算一个对应的结果。但从执行角度看，如果优化器不做特殊处理，这条 SQL 可能会变成：

t1 有多少行，就执行多少次子查询。

如果 t1 有 1 万行，t2 的子查询就可能被执行 1 万次。这个问题不是子查询语法本身有错，而是“重复执行”造成了巨大的算力浪费。

文档中给出的测试案例就非常直观：

CREATE TABLE t1(id numeric(10,1));
CREATE TABLE t2(id numeric(10,1));

INSERT INTO t1 VALUES(generate_series(1,10000));
INSERT INTO t2 VALUES(generate_series(1,10000));

SELECT
    (SELECT SUM(id) FROM t2 WHERE t1.id = t2.id)
FROM t1;

未消除标量子查询时，对 t1 的每一条记录，都要对 t2 执行一次扫描。t1 有 1 万行，就意味着 t2 被扫描 1 万次，耗时约 32 秒。

而经过标量子查询消除后，t2 只需要扫描一次，整体执行时间约 24 毫秒。这个提升不是百分之几十，而是数量级的变化。

这背后真正有意思的地方在于：优化器并不是简单地把 SQL “翻译”成另一种写法，而是在判断“能不能改”“怎么改”“改完是否真的更好”。这就有点像一个具备逻辑推理能力的智能决策大脑。

---

一、标量子查询为什么容易拖慢 SQL？

标量子查询的定义很简单：它应该返回一个值，也就是一行一列。

例如：

SELECT
    t1.id,
    (SELECT MAX(t2.score)
     FROM t2
     WHERE t2.user_id = t1.id) AS max_score
FROM t1;

这里的子查询依赖外层表 t1，属于相关子查询。每处理一行 t1，都要带着当前行的 t1.id 去 t2 里找对应数据。

如果没有优化，执行方式大致是：

1. 扫描外层表 t1；
2. 对 t1 的每一行执行一次子查询；
3. 子查询访问 t2，完成过滤、聚合；
4. 将结果返回给外层查询。

当外层表行数很少时，这样做问题不大。但如果外层表有几万、几十万甚至更多行，子查询就会被重复执行大量次数。

更糟的是，很多业务 SQL 里不是只有一个标量子查询，而是多个结构相似的子查询：

SELECT
    t1.id,
    (SELECT SUM(t2.amount) FROM t2 WHERE t2.user_id = t1.id) AS total_amount,
    (SELECT MAX(t2.amount) FROM t2 WHERE t2.user_id = t1.id) AS max_amount
FROM t1;

这两个子查询访问的是同一张表，过滤条件也相似，只是输出聚合函数不同。如果分别执行，就会重复访问相同的数据。对优化器来说，这是一个很明显的优化机会：能不能只扫描一次 t2，同时算出 SUM 和 MAX？

---

二、难点不在“改写”，而在“等价”

把标量子查询改写成连接，看起来并不复杂。比如前面的 SQL，可以改成类似下面的形式：

SELECT
    t1.id,
    v.total_amount
FROM t1
LEFT JOIN (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM t2
    GROUP BY user_id
) v
ON v.user_id = t1.id;

这样，子查询就不再对 t1 的每一行重复执行，而是先对 t2 做一次聚合，再和 t1 连接。

但问题来了：所有标量子查询都能这么改吗？

答案是否定的。

文档中特别强调了“等价性判定”。这是标量子查询消除中最核心、也是最容易出错的部分。优化器必须先确认：改写前后的 SQL，在所有合法数据下，语义是否一致。

1. 子查询返回多行时，语义不能变

标量子查询要求最多返回一行。如果实际返回多行，原始 SQL 应该报错。

例如：

SELECT
    t1.id,
    (SELECT t2.amount
     FROM t2
     WHERE t2.user_id = t1.id)
FROM t1;

如果某个 t1.id 对应 t2 中多条记录，那么这个标量子查询不是一个“标量”，数据库应该报错。

但如果优化器粗暴地把它改成连接：

SELECT
    t1.id,
    t2.amount
FROM t1
LEFT JOIN t2
ON t2.user_id = t1.id;

这时就不会报错，而是会返回多行结果。这样一来，查询语义就被改变了。

所以，优化器在消除这类子查询前，必须判断它是否一定只返回一行。常见的安全条件包括：子查询内部有聚合且无不安全分组、连接键满足唯一性约束、或者其他能够保证单行返回的条件。

这不是简单的字符串替换，而是基于 SQL 语义、约束信息、查询结构的逻辑判断。

2. COUNT 聚合是一个特殊陷阱

聚合函数也不是都一样。

对于没有匹配记录的情况：

• SUM、MAX、MIN、AVG 通常返回 NULL；
• COUNT 返回 0。

这就带来了一个问题。

假设有如下子查询：

SELECT
    t1.id,
    (SELECT COUNT(*)
     FROM t2
     WHERE t2.user_id = t1.id) AS cnt
FROM t1;

如果某个 t1.id 在 t2 中没有匹配记录，原始子查询返回的是 0。

如果简单改写成左连接聚合：

SELECT
    t1.id,
    v.cnt
FROM t1
LEFT JOIN (
    SELECT user_id, COUNT(*) AS cnt
    FROM t2
    GROUP BY user_id
) v
ON v.user_id = t1.id;

对于没有匹配记录的 t1.id，v.cnt 会是 NULL，而不是 0。这就和原始 SQL 不等价。

要想保持语义，必须额外处理，例如：

COALESCE(v.cnt, 0)

但是否可以这么做，也要看具体查询结构和表达式上下文。文档中指出，COUNT 与其他聚合函数在无匹配记录时的返回差异，是等价性判定中必须谨慎处理的场景。

这也是为什么优化器不能“看到子查询就消除”。它必须知道自己改写之后不会改变结果。

---

三、KES 优化器的“智能决策大脑”：先判定，再改写，再优化

文档中提到，在 KES 的新版本中，引入了一套标量子查询消除机制。整体思路可以概括为三步：

1. 能不能优化：等价性判定；
2. 如何优化：将标量子查询转为内联视图，并与外层查询做左外连接；
3. 进一步优化：合并相似标量子查询，减少重复扫描。

这套流程很像一个有步骤的推理过程。

第一步：能不能改？

优化器首先不会急着改 SQL，而是先分析子查询结构。

它需要判断：

• 子查询是否位于目标列中；
• 是否是相关标量子查询；
• 是否存在聚合；
• 是否包含窗口函数、UNION 等复杂结构；
• 子查询结果是否能保证标量语义；
• 改写后是否会改变空值、错误、多行返回等行为。

这一阶段的目标不是“尽可能多地消除子查询”，而是“只消除绝对安全的子查询”。

换句话说，等价性是第一道门。只要有语义风险，即使理论上可能更快，也不能随便改。

第二步：怎么改？

通过等价性检查后，优化器会将目标列中的相关标量子查询转换成内联视图，然后和外层表进行左外连接。

例如：

SELECT
    t1.id,
    (SELECT SUM(t2.amount)
     FROM t2
     WHERE t2.user_id = t1.id) AS total_amount
FROM t1;

可以转换成：

SELECT
    t1.id,
    v.total_amount
FROM t1
LEFT JOIN (
    SELECT
        user_id,
        SUM(amount) AS total_amount
    FROM t2
    GROUP BY user_id
) v
ON v.user_id = t1.id;

这里使用左外连接，是为了保留外层表 t1 的所有记录。即使 t2 没有匹配行，外层记录也不能丢失。

这一步的价值在于：子查询不再随着外层行重复执行，而是作为一个可优化的关系表达式参与整体优化。后续优化器可以继续选择更合适的连接顺序、访问路径和执行算法。

第三步：多个相似子查询能不能合并？

如果目标列里有多个结构相似的标量子查询，KES 优化器还可以进一步判断是否能够合并。

比如：

SELECT
    t1.id,
    (SELECT SUM(t2.amount)
     FROM t2
     WHERE t2.user_id = t1.id) AS total_amount,
    (SELECT MAX(t2.amount)
     FROM t2
     WHERE t2.user_id = t1.id) AS max_amount
FROM t1;

可以合并为一个内联视图：

SELECT
    t1.id,
    v.total_amount,
    v.max_amount
FROM t1
LEFT JOIN (
    SELECT
        user_id,
        SUM(amount) AS total_amount,
        MAX(amount) AS max_amount
    FROM t2
    GROUP BY user_id
) v
ON v.user_id = t1.id;

这样，原本可能需要多次扫描 t2 的逻辑，现在只需要扫描一次。对于大表来说，这种优化非常关键。

---

四、从 RBO、CBO 到“类 AI”的优化器推理

传统上，数据库优化器经历过几个阶段。

早期更多依赖规则，也就是 RBO，Rule-Based Optimizer。它关心的是“遇到某种模式，就应用某条规则”。规则优化简单直接，但面对复杂 SQL 时容易僵硬。

后来主流数据库转向 CBO，Cost-Based Optimizer。它不只看规则，还会结合统计信息估算不同执行计划的成本，例如：

• 表有多少行；
• 某个过滤条件选择率是多少；
• 某个列是否有索引；
• 索引聚簇性如何；
• 分组后大概有多少 distinct key；
• 连接后结果集有多大；
• 顺序扫描、索引扫描、哈希连接、嵌套循环连接哪个更划算。

标量子查询消除也不能只靠规则。即使一个子查询可以安全消除，也不代表在所有情况下都一定更优。

例如：

SELECT
    t1.id,
    (SELECT SUM(t2.amount)
     FROM t2
     WHERE t2.user_id = t1.id)
FROM t1
WHERE t1.id = 100;

如果 t1 经过过滤后只剩一行，而 t2.user_id 上有高效索引，那么对这一行执行一次索引查找，可能就已经很快。反过来，如果强行把 t2 全表聚合一遍，再和 t1 连接，可能反而更重。

再看另一种情况：

SELECT
    t1.id,
    (SELECT SUM(t2.amount)
     FROM t2
     WHERE t2.user_id = t1.id)
FROM t1;

如果 t1 有大量行，且子查询会被反复触发，那么消除子查询、预聚合 t2、再做连接，就往往更有优势。

所以，优化器需要结合统计信息与索引结构进行判断：

• 外层表 t1 的结果集规模有多大？
• 子查询被重复执行的次数大概是多少？
• t2 上是否有可用索引？
• 相关条件 t2.user_id = t1.id 的选择率如何？
• 聚合后的中间结果有多大？
• 合并多个子查询后，是否能减少扫描成本？
• 左连接引入的代价是否可接受？

这就是优化器“聪明”的地方。它不是机械地执行一条改写规则，而是先做语义推理，再做代价估算，最后选择更合适的执行计划。

如果把这个过程类比成 AI，它并不是在“生成答案”，而是在一个巨大的计划空间中，根据约束和成本寻找更优解。等价性判定像是逻辑推理，代价模型像是经验评估，统计信息和索引结构则是它做判断时的上下文。

---

五、一个更接近真实场景的例子

某个使用KingbaseES的业务中，需要查询每个用户的订单统计信息：

SELECT
    u.user_id,
,
    (SELECT SUM(o.amount)
     FROM orders o
     WHERE o.user_id = u.user_id) AS total_amount,
    (SELECT MAX(o.amount)
     FROM orders o
     WHERE o.user_id = u.user_id) AS max_amount
FROM users u;

如果 users 有 100 万行，orders 有 5000 万行，且优化器不消除子查询，那么两个子查询可能会被重复执行大量次数。即使 orders.user_id 上有索引，也可能产生海量随机访问。

优化后，可以变成：

SELECT
    u.user_id,
    u.user_name,
    s.total_amount,
    s.max_amount
FROM users u
LEFT JOIN (
    SELECT
        o.user_id,
        SUM(o.amount) AS total_amount,
        MAX(o.amount) AS max_amount
    FROM orders o
    GROUP BY o.user_id
) s
ON s.user_id = u.user_id;

这个改写带来的变化很明显：

• orders 从重复访问变成一次聚合扫描；
• SUM 和 MAX 被合并到同一个内联视图；
• 外层用户表通过左连接保留完整结果；
• 后续优化器可以继续选择哈希聚合、索引扫描、哈希连接等执行方式。

但如果外层用户只有一个，例如：

SELECT
    u.user_id,
    (SELECT SUM(o.amount)
     FROM orders o
     WHERE o.user_id = u.user_id) AS total_amount
FROM users u
WHERE u.user_id = 10001;

并且 orders(user_id) 上有高选择性的索引，那么优化器可能会认为：直接走索引查一次，比全表聚合更便宜。

这也是为什么“能改写”和“应该改写”不是一回事。

---

六、总结：优化器不是 SQL 翻译器，而是决策系统

标量子查询消除看似只是一个 SQL 改写技巧，但真正落到数据库内核里，它涉及很多严肃的问题：

• 如何判断改写前后语义等价；
• 如何处理返回多行导致的错误语义；
• 如何处理 COUNT 与其他聚合函数的差异；
• 如何将子查询转换为内联视图和左外连接；
• 如何合并多个相似子查询；
• 如何结合统计信息、索引结构和代价模型判断是否值得改写。

文档中的案例很好地说明了这一点：当子查询被重复执行 1 万次时，性能会急剧下降；而通过标量子查询消除，把重复执行变成一次扫描和一次连接，耗时可以从几十秒降到毫秒级。

这背后的 KES 优化器，更像是一个具备推理能力的“智能决策大脑”：它先判断能不能安全改，再判断怎么改，最后让改写后的关系表达式进入后续优化流程。它不是简单地把 SQL 从一种写法翻译成另一种写法，而是在语义、统计信息、索引和代价之间做综合权衡。

未来数据库优化器的发展方向，也会越来越接近这种模式：规则提供边界，代价模型提供选择依据，而更智能的推理能力负责在复杂 SQL 中发现真正安全、真正有价值的优化机会。标量子查询消除只是其中一个切面，但它已经足够说明，现代优化器的核心竞争力，正在从“执行 SQL”转向“理解 SQL”。