在数据库运行速度调优的日常工作中，有一种SQL写法总是让DBA又爱又恨——标量子查询。

爱它，是因为它写起来太顺手了。你只需要在一个括号里放一个返回单值的子查询，就能在主查询的SELECT列表里优雅地挂上一个“计算列”。逻辑清晰，语义直接，不需要考虑JOIN会不会导致数据膨胀。也不需要担心GROUP BY的细节。怎么说呢，业务开发人员尤其喜欢这种写法。因为它能用最少的代码表达最复杂的业务逻辑。

恨它，是因为它在执行计划里往往扮演着“性能刺客”的角色。按理说，外表返回一千行，标量子查询就可能被触发一千次。其实，相关子查询更是如此——每一次触发都带着一个新的参数，缓存失效。索引虽然能帮上忙，但累积的开销足以让一个简单的报表查询从秒级退化到分钟级。

我一直认为。评价一个数据库调整器是否“聪明”，有一个很直观的指标：它能不能自动消除标量子查询。这不是一个噱头功能，而是一场对数据库内核等价变换能力的实战检验。

这篇文章，我想程序地聊聊标量子查询消除这件事。老实说，先分析它为什么难做。接下来介绍金仓数据库在V009R002C014版本中引入的消除机制——从等价性判定、到连接转换、再到相似子查询合并。全程会配合代码示例和执行计划的分析，希望能给正在研究数据库内核或者被标量子查询折磨的开发者一些启发。老实说，

一、先从一个“看起来很对”的SQL说起

假设我们有两张表：一张是订单主表orders一张是订单明细表order_items。

CREATE TABLE orders (
order_id INT PRIMARY KEY。order_date DATE,customer_id INT
);CREATE TABLE order_items (
item_id INT PRIMARY KEY。order_id INT,product_id INT,quantity INT,price DECIMAL
);

现在业务上需要一张报表：列出最近一个月的订单，并附带每个订单的总金额、商品种类数和平均单价。

一个很自然的写法是：

SELECT o.order_id,o.order_date,FROM order_items oi
WHERE oi.order_id = o.order_id) AS total_amount。FROM order_items oi
WHERE oi.order_id = o.order_id) AS item_count,FROM order_items oi
WHERE oi.order_id = o.order_id) AS avg_price
FROM orders o
WHERE o.order_date>= CURRENT_DATE - INTERVAL '30' DAY;

如果用手工执行计划的方式来推演这个SQL的执行过程。大概是这样：

1. 对orders表做全表扫描，找到符合条件的订单行。假设有N行。

2. 对于每一行。执行第一个标量子查询：对order_items做一次扫描，计算SUM。怎么说呢，

最终对order_items`的扫描次数是 N × 3. 如果N=10。000，那就是30,000次扫描。按理说，即使aorder_items`在`order_id`上有索引，30。000次索引查找的开销依然惊人——每次查找都要经历B+树根到叶子的遍历，加上CPU累计计算。

使用者痛点1：开发者写出易读易维护的SQL，却被隐藏在执行计划里的“千次子查询”拖慢了整个报表响应时间。

I 在测试环境里用10。000行订单、每个订单平均5条明细的数据量跑了一下这个SQL，耗时约28秒。对于一个报表接口这个响应时间是不可接受的。

*也许你会说这是开发者的问题，他们不该这么写SQL。 按理说，但现实是在复杂业务程序中，这种写法比比皆是。*

二、手工 能处理问题，但不优雅

面对上面的性能问题。一个有经验的DBA会立刻想到一种 方式这方面，把标量子查询提高为内联视图，接下来左外连接。

SELECT o.order_id,o.order_date,COALESCE AS total_amount,COALESCE AS item_count,agg.avg_price
FROM orders o
LEFT JOIN (
SELECT order_id。SUM AS total_amount,COUNT AS item_count,G AS avg_price
FROM order_items
GROUP BY order_id) agg ON o.order_id = agg.order_id
WHERE o.order_date>= CURRENT_DATE - INTERVAL '30' DAY;

The execution plan now scans order_items` only once,performs a single GROUP BY aggregation。and n joins result back to orders`.

使用者痛点2：手工 需要 DBA 或资深开发介入，导致代码膨胀、维护成本飙升，而且容易因细微语义差异产生错误结果。不过，

• C1 – 语义正确性需要人工保证： 原始标量子查询在没有匹配数据时返回 SUM=NULL。COUNT=0,G=NULL. 后必须显式使用 COALESCE 来恢复这些默认值，否则结果不一致。
• C2 – 代码膨胀： 每增加一个聚合列。就要把对应表达式复制进内联视图并加上别名，SQL 长度几乎翻倍。
• C3 – 维护成本： 业务需求变化时需要同步修改原始和 两套 SQL；团队若缺乏统一规范，很容易出现 “改了一处忘改另一处”。

The more reasonable choice is to let optimizer do this automatically. This requires optimizer to recognize scalar subqueries that can be eliminated and to rewrite m safely.

三、调整器面临的四道关卡

关卡一这方面，如何保证“最多返回一行”

The scalar subquery semantics require that at most one row is returned;orwise engine throws a runtime error . When rewriting as a LEFT JOIN + GROUP BY,GROUP BY naturally guarantees a single row per group key. However,if original subquery contains a whose key is not unique,original query would raise an error while rewritten version would silently return an arbitrary row—an unacceptable change.

The optimizer refore checks:

• Aggregate without explicit GROUP BY → guaranteed single row.
• Tight unique‑key predicate → at most one match.
• TABLE has a primary/unique constraint on grouping columns.
• LIMIT / FETCH FIRST ROW ONLY patterns.

If none of se hold。optimizer conservatively skips elimination.

从关卡二来看，COUNT 和 SUM 的 “空值陷阱”

An easy‑to‑miss detail is how NULLs are handled differently by aggregation functions:

	SOURCE SUBQUERY RESULT
COLUMN COUNT	null?→ actually 0
COLUMN SUM	null
COLUMN G	null

If we simply replace scalar reference with agg.cnt ,we would get NULL for COUNT as well—a semantic bug. The optimizer refore records each aggregate’s type during equivalence checking and automatically wraps COUNT results with COALESCE.

至于关卡三。如何处理 “相似但不完全相同” 的多个子查询

If two scalar subqueries share same base table and join condition but differ in an additional filter predicate,y can still be merged using conditional aggregation:

SELECT o.order_id,COALESCE AS total_amount,COALESCE AS paid_amount
FROM orders o
LEFT JOIN (
SELECT order_id,SUM AS total_amount,SUM AS paid_amount
FROM order_items
GROUP BY order_id) agg ON o.order_id = agg.order_id;

This requires optimizer to recognise that both subqueries reference same base table and identical equality predicates on order_id ​​​​​​​​. Goldendb’s current implementation handles only exactly identical WHERE clauses;handling additional predicates is earmarked for future releases.

从关卡四来看，相关子查询的解关联

The essence of “correlated” scalar subqueries is repeated execution per outer row. Unnesting transforms m into a set‑based join:

• Extract correlation predicates .
• Promote m to GROUP BY keys inside an inline view.
• Replace each scalar reference with a column from that view via LEFT JOIN.

A special case appears when correlation involves a function transformation:

FROM orderitems oi
WHERE ABS = o .order id )

If predicate isn’t a simple equality on raw columns,Goldendb currently does not attempt elimination—preferring correctness over aggressive optimisation.

四、金仓数据库 的实现方法

Goldendb V009R002C014 implements scalar‑subquery elimination through three clearly defined stages: equivalence checking → rewrite → similar‑subquery merging. Below is a concise walk‑through of each stage.

说到阶段一，等价性判定

• Only scalar subqueries appearing in SELECT list are considered .
• The subquery must be “simple”: allowed constructs include aggregates,optional GROUP BY。simple joins and WHERE filters – but **no** window functions,UNION,DISTINCT,LIMIT / OFFSET .
• Scalar guarantee validation:
  • Aggregates without GROUP BY → single row guaranteed.
  • GROUP BY on columns that form a primary/unique key → safe.
  • Equality predicate on a unique column against a constant or outer column → safe.
  • Explicit FETCH / LIMIT 1 clause → safe.
• Record each aggregate’s type for later COALESCE injection.
• Identify correlation predicates – currently only simple equalities of form inner.col = outer.col .

从阶段二来看，转换为外连接

• Extract inner query’s FROM clause as an inline view.
• Determine GROUP BY keys:
  • If original query already has GROUP BY – keep it.
  • If it only contains aggregates – create a global aggregation .
  • If no aggregates but uniqueness is proven – use that unique column as grouping key.
• Build projection list containing all required aggregates .
• Generate LEFT OUTER JOIN 娱乐ween outer table and inline view using extracted correlation predicate.
• Replace each scalar reference in outer SELECT with corresponding inline‑view column;说起来，wrap COUNT expressions with COALESCE.
• Apply post‑rewrite simplifications .

从阶段三来看。相似子查询合并

• After basic elimination each scalar becomes a reference into its own inline view.
• Scan all such references;group those whose:
  • Base tables set identical .
  • Correlation predicates identical in structure.
  • GROUP BY keys identical.
  • Additional non‑correlation WHERE clauses identical.
• Merge grouped candidates into **one** inline view whose SELECT list contains every needed aggregate expression. Each outer reference is rewired to point at its respective column of this merged view. This reduces physical scans dramatically when dozens of scalar subqueries target same fact table.

/ 实际演示金仓消除效果 / ​ ​ ​ ​ ​ ​ ​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​​ /​​​/ ​ sql EXPLAIN SELECT o .order_ id。FROM order_ items oi WHERE oi .order_ id = o .order_ id ) AS total _amount,FROM order_ items oi WHERE oi .order_ id = o .order_ id ) AS item _count,FROM order_ items oi WHERE oi .order_ id = o .order_ id ) AS avg _price FROM orders o;*Without elimination* : three correlated SubPlans appear in plan. *With Goldendb’s elimination* : sql Hash Left Join Hash Cond: -> Seq Scan on orders o -> Hash -> Subquery Scan on agg -> HashAggregate Group Key : oi .order _id -> Seq Scan on order _items oi Only **one** scan of `order_items` occurs;all three aggregates are computed toger.

五、实践验证 ：从数据中看效果

/ 测试环境 / ​

sql -- 建表 CREATE TABLE t1 );CREATE TABLE t2 );其实，-- 插入10000条数据 INSERT INTO t1 SELECT generate_series;INSERT INTO t2 SELECT generate_series;-- 创建索引 CREATE INDEX idx_t2_id ON t2;-- 更新统计信息 ANALYZE t1;ANALYZE t2，

sql -- 原始标量子查询 SELECT FROM t2 WHERE t1.id = t2.id) FROM t1;

/ 场景 vs 执行时间 vs 对t2 表扫描次数 ／
/ 场景 ／	/ 执行时间 ／	/ 对t₂ 表扫描次数
/ 未消除	/ 0.42 秒	/ 10 000 次
/ 金仓自动消除后	/ 24 毫秒	/ 1 次

执行时间从 32.4 秒 降至 24 毫秒，提高约 1350 倍 —— 从不可接受降至瞬时响应。

/ t1 行数

/ 未消除

/ 消除后

/ 5 000

/ 16 s

/ 0.02 s

/ 10 000

/ 32 s

未消除版本随行数线性增长，每增加10k 行约多耗时32 秒；而消除后仅略增毫秒级开销。

六、对标量子查询消除再思考 & 展望 HTAP 场景 *

•  语义安全性难以权衡  - 多行返回风险与聚合空值处理让实现风险大。Li>  调整器架构限制  - 基于规则或代价模型的不完善导致难以安全触发此 Li>  实际负载收益有限  - 小批 OLTP 场景收益微弱，部分厂商选择保守策略。Li>  历史代码包袱  - 老旧调整器改动成本高。Li>

="" 确保正确性 } 只针对绝对安全且唯一可判定 的 标量 子 查询进行 消 除；宁 可 放 弃 部 分 优 化 而 不 冒 障 确 保 正 确 性。

​

The rest of this answer has been omitted due to length constraints.

---