相关MAX子查询改写EXISTS

1. 案例摘要

慢因： 执行计划落在 SUBQUERY（相关子查询未展开为半连接），内层随外层驱动键反复执行，代价随探测次数放大。
改法： 将存在性判断写成 EXISTS，使优化器走 NESTED LOOPS SEMI。
效果： Elapsed 20.392s → 0.039s，提升倍数 = 20.392 / 0.039 ≈ 522.9；db block gets 50,112,261 → 20,631，提升倍数 = 50,112,261 / 20,631 ≈ 2,429.0；结果行数均为 94970。

2. 问题引入

生产引入

最近在某客户现场做 SQL 优化，遇到如下的一个问题。

SQL 语句中存在相关标量子查询，形态为 (SELECT MAX(...) FROM … WHERE 关联条件) IS NOT NULL（用聚合结果是否为空表达“是否存在行”）。该语句 Elapsed 约 20.4s，db block gets 约 5011 万，执行计划为 SUBQUERY，内层索引反复探测，逻辑读与耗时明显偏高。

原始 SQL

以下 SQL 在模拟环境中复现原生产侧同类写法（脱敏/仿建对象，非生产库原文直贴）：

SELECT COUNT(*) AS cnt_before
  FROM demo_sq2_a a
 WHERE (SELECT MAX(b.id)
          FROM demo_sq2_b b
         WHERE b.cat = a.cat
           AND b.val1 > a.val1) IS NOT NULL;

文本说明：

片段	含义
`COUNT(*)`	统计满足条件的外层行数
`(SELECT MAX(b.id) …)`	相关标量子查询：对每个外层行 `a`，在 `b` 上按关联条件取 `id` 最大值
`b.cat = a.cat AND b.val1 > a.val1`	内外层关联与过滤条件
`… IS NOT NULL`	用“最大值是否非空”表达“是否存在满足条件的 `b` 行”

3. 分析问题

分析思路：

如何确认是慢 SQL： 对该语句单次执行取证，Elapsed≈20.4s，db block gets≈5011 万，耗时与逻辑读已明显偏高，判定为慢 SQL。
如何定位到慢的地方： 执行计划关键算子为 SUBQUERY；内层 INDEX RANGE SCAN 的 Loops/A-Rows 约 5e7，慢在相关子查询按外层键反复探测。

优化思路：

业务语义是“是否存在满足条件的明细”，宜写成 EXISTS（或等价的半连接友好写法），使优化器走半连接路径，避免相关标量子查询逐键探测。

依据官方说明：不含 AGGR / GROUP BY 等限制时，EXISTS / IN 子查询可被改写为半连接或反连接，从而减少内表反复扫描；执行计划中可见 NESTED LOOPS SEMI / HASH JOIN SEMI 等算子。参见：

查询改写与谓词优化
执行算子（SUBQUERY、HASH JOIN SEMI 等说明）

本例优化前写法带相关标量 MAX，计划停留在 SUBQUERY；改为 EXISTS 后出现 NESTED LOOPS SEMI，与文档描述一致。实验环境为 YashanDB 23.5.2.101，文档链接以 23.4 手册为准（改写规则同属性能调优体系）。

4. 环境与数据

为便于复现与对照，在模拟环境中按生产侧同类问题 仿建业务对象并灌入数据（非生产库直接操作）。规模需足以拉开 SUBQUERY 与半连接的差异。

项	内容
对象	`DEMO_SQ2_A` 约 10 万行；`DEMO_SQ2_B` 约 200 万行
造数	模拟环境建表并生成数据（本轮沿用既有灌数，未重建）
关键索引	`IDX_SQ2_B_CAT(CAT, VAL1, VAL2)`；主键索引 `ID`
统计信息	未专门重采（本案例不依赖改统计）

造数要点（可复现）：

demo_sq2_a(id, cat, val1, val2, pad)：约 1e5 行，cat 取有限基数（如数百档）。
demo_sq2_b(id, cat, val1, val2, pad)：约 2e6 行，与 a 共享 cat 分布，并建 (cat, val1, val2) 组合索引。
外层按 cat/val1 关联内表时，相关子查询会触发大量索引探测；半连接则可显著降低探测次数。

5. 解决方案

5.1 优化前（现状）

SELECT COUNT(*) AS cnt_before
  FROM demo_sq2_a a
 WHERE (SELECT MAX(b.id)
          FROM demo_sq2_b b
         WHERE b.cat = a.cat
           AND b.val1 > a.val1) IS NOT NULL;

结果行数：94970
Elapsed：00:00:20.392
SQL hash：4087586424

Execution Plan（关键）：

Id	Operation	Name	A-Rows	A-Time	Loops
0	SELECT STATEMENT		1	~20.4s	2
1	SUBQUERY	QUERY[1]	10000	~20.3s	20000
2	AGGREGATE		10000		20000
4	INDEX RANGE SCAN	IDX_SQ2_B_CAT	~4.997e7	~17.9s	~4.998e7
5	TABLE ACCESS FULL (AGGR PUSHED)	DEMO_SQ2_A	1		2

关键谓词：B.CAT = A.CAT AND B.VAL1 > A.VAL1；外层 QUERY[1] IS NOT NULL。

Statistics： physical reads 0；db block gets 50,112,261；consistent gets 0；rows processed 1。
（本轮 Statistics 以 db block gets 为主观察项；consistent gets 为 0 时仍以实测字段为准。）

5.2 方案：改写为 EXISTS

MAX(...) IS NOT NULL 表达的是存在性，改写为 EXISTS 后语义对齐，并便于走半连接：

SELECT COUNT(*) AS cnt_after
  FROM demo_sq2_a a
 WHERE EXISTS (
         SELECT 1
           FROM demo_sq2_b b
          WHERE b.cat = a.cat
            AND b.val1 > a.val1
       );

结果行数：94970（与优化前一致）
Elapsed：00:00:00.039
SQL hash：2449412155

Execution Plan（关键）：

Id	Operation	Name	A-Rows	A-Time	Loops
0	SELECT STATEMENT		1	~0.030s	2
1	AGGREGATE		1		2
2	NESTED LOOPS SEMI		9497	~0.030s	9498
3	HASH GROUP		10000		10001
4	TABLE ACCESS FULL	DEMO_SQ2_A	100000		100001
5	INDEX RANGE SCAN	IDX_SQ2_B_CAT	9497		10000

Statistics： physical reads 0；db block gets 20,631；consistent gets 0；rows processed 1。

6. 对比总表与提升倍数

指标	Before	After	来源
结果行数	94970	94970	查询
Elapsed	20.392 s	0.039 s	autotrace Timing
关键算子	SUBQUERY	NESTED LOOPS SEMI	autotrace Plan
db block gets	50,112,261	20,631	autotrace Statistics
sql_id	fa8w9wkqnhzjm	6tkkncws11up3	库内缓存

耗时提升倍数： 20.392 / 0.039 ≈ 522.9
逻辑读提升倍数（db block gets）： 50,112,261 / 20,631 ≈ 2,429.0

（公式：提升倍数 = 优化前指标 / 优化后指标。）

7. 根因与改写说明

为何慢： 优化前计划为 SUBQUERY，相关子查询未展开；内层索引 Loops/A-Rows 约 5e7，逻辑读超过五千万。慢的本质是执行路径，而不是 MAX 函数名本身；MAX(...) IS NOT NULL 只是触发该路径的写法。
方案做了什么： 改为 EXISTS 后出现 NESTED LOOPS SEMI，并对外层键做 HASH GROUP，探测次数下降。这与官方“EXISTS 可改写为半连接”的说明相符（见第 3 章文档链接）。
语义： MAX(id) IS NOT NULL ⇔ 存在满足条件的行 ⇔ EXISTS；两边计数均为 94970。

8. 结论与建议

用相关标量聚合再判断非空来表达“存在性”时，容易落入昂贵的 SUBQUERY；优先写成 EXISTS / IN（并注意子查询中避免多余聚合，以免阻碍半连接改写）。
可推广： 对账、权限校验、明细是否存在等存在性谓词。
局限： 本结果在 YashanDB 23.5.2.101、热缓存（physical reads=0）下测得；数据分布与版本不同时计划可能变化。
本案例未做： 改索引、改参数、重采统计；上线前请在目标库用同等口径复核。

9. 附录

DDL 骨架（示意）：

CREATE TABLE demo_sq2_a (
  id NUMBER PRIMARY KEY,
  cat NUMBER NOT NULL,
  val1 NUMBER NOT NULL,
  val2 NUMBER NOT NULL,
  pad VARCHAR2(50)
);

CREATE TABLE demo_sq2_b (
  id NUMBER PRIMARY KEY,
  cat NUMBER NOT NULL,
  val1 NUMBER NOT NULL,
  val2 NUMBER NOT NULL,
  pad VARCHAR2(50)
);

CREATE INDEX idx_sq2_b_cat ON demo_sq2_b (cat, val1, val2);
-- 再按目标规模 INSERT / 生成数据后收集统计信息（可选）

参考文档：

EXISTS SQL优化 YashanDB 半连接子查询