在真实业务系统里，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 里找对应数据。
如果没有优化，执行方式大致是：
当外层表行数很少时，这样做问题不大。但如果外层表有几万、几十万甚至更多行，子查询就会被重复执行大量次数。
更糟的是，很多业务 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 的新版本中，引入了一套标量子查询消除机制。整体思路可以概括为三步：
这套流程很像一个有步骤的推理过程。
第一步：能不能改？
优化器首先不会急着改 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”。
