标量子查询消除技术重塑数据库优化器架构

时间：2026-06-06 16:29

先聊一个真实业务中很常见的场景：你辛辛苦苦写了一段SQL，逻辑清晰、层次分明，CTE、窗口函数、多层子查询用得行云流水。结果一跑，慢得离谱。问题很可能就出在一个不起眼的地方——SELECT目标列里的标量子查询。比如这样的写法： SELECT t1 id, (SELECT SUM(t2 id) FR

先聊一个真实业务中很常见的场景：你辛辛苦苦写了一段SQL，逻辑清晰、层次分明，CTE、窗口函数、多层子查询用得行云流水。结果一跑，慢得离谱。

从Row‑By‑Row到AI级推理：标量子查询消除如何重构数据库优化器大脑

问题很可能就出在一个不起眼的地方——SELECT目标列里的标量子查询。

比如这样的写法：

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

从业务角度讲，它很直观：对t1的每一行，去t2里算一个对应的值。但换个角度看，如果优化器不做特殊处理，这条SQL的执行方式就是——t1有多少行，子查询就要执行多少次。1万行，就是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;

未做优化时，耗时约32秒。优化之后，直接降到24毫秒。这种提升，已经不是百分之几十的概念，而是数量级的变化。

有意思的点在于：优化器并不是简单地把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里找对应的数据。

如果不加优化，执行过程大致是：

扫描外层表t1；
对t1的每一行，执行一次子查询；
子查询访问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要被反复扫描两次。这对优化器来说，就是一个显而易见的优化机会：能不能只扫一次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聚合是一个特殊陷阱

不同聚合函数在遇到无匹配记录时的表现差异，也很容易出问题。

一个典型场景：

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, 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”。

来源：https://blog.csdn.net/g310773517/article/details/161295200

上一篇Google Chrome强制开启Gemini AI侧边栏完整图文教程 下一篇AI提示词注入绕过工具：突破Codex/Claude安全限制，CTF夺旗与渗透测试利器

本站内容用于信息整理与展示，如有侵权或内容问题请及时联系处理。