我们的文章会在微信公众号 IT民工的龙马人生 和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
SQL优化改IN躲开全表扫描性能提升53倍
1. 案例摘要
慢因： UPDATE 用 列 = (标量子查询) 驱动目标行时，计划落到 TABLE ACCESS FULL + HASH JOIN，并对外层大量候选行反复执行 EXISTS 侧 SUBQUERY（Loops 约 10 万），逻辑读放大。
改法： 将等值标量改为 列 IN (子查询)，并加 列 IS NOT NULL，使优化器走 NESTED LOOPS + 索引范围扫描。
效果（23.5.2.101）： Elapsed 0.210s → 0.004s，提升倍数 = 0.210 / 0.004 = 52.5；db block gets 202438 → 155，提升倍数 = 202438 / 155 ≈ 1306.1；影响行数均为 4。同脚本在 23.4.14.100 上 = 写法已走索引，前后差异很小（见第 6 章）。
2. 问题引入
生产引入
最近在某客户现场做 SQL 优化，遇到如下的一个问题。
SQL 为自表 UPDATE：用 BIZ_KEY = (SELECT BIZ_KEY … WHERE DOC_ID = :x AND ROWNUM = 1) 定位业务键，再叠加 STATUS_CD < 3 与相关 EXISTS（时间窗与 SIDE_FLAG 空值交叉）。在 YashanDB 上执行计划出现 TABLE ACCESS FULL + HASH JOIN，EXISTS 侧为 SUBQUERY + 组合索引探测；库内统计可见单次执行逻辑读约 21.28 万（现场口径），而同形态 SQL 在 Oracle 侧可走 BIZ_KEY 索引。将 = 改为 IN 并补充 BIZ_KEY IS NOT NULL 后，外层改为索引驱动，代价显著下降。
原始 SQL
以下 SQL 在模拟环境中复现原生产侧同类写法（脱敏/仿建对象 DEMO_BIZ_HDR，非生产库原文直贴）：
UPDATE demo_biz_hdr t
SET status_cd = (CASE WHEN SYSDATE > due_tm THEN 3 ELSE 1 END),
remark_tx = 'Manual'
WHERE t.biz_key = (
SELECT biz_key
FROM demo_biz_hdr x
WHERE x.doc_id = 'D000000001' AND ROWNUM = 1
)
AND t.status_cd < 3
AND EXISTS (
SELECT 1
FROM demo_biz_hdr a
WHERE a.biz_key = t.biz_key
AND a.node_cd = t.node_cd
AND a.status_cd > 0
AND a.doc_id <> t.doc_id
AND (a.side_flag IS NULL AND t.side_flag IS NOT NULL
OR t.side_flag IS NULL AND a.side_flag IS NOT NULL)
AND a.event_tm > t.event_tm - (149/2880)
AND a.event_tm < t.event_tm + (149/2880)
);
文本说明：
| 片段 | 含义 |
|---|---|
BIZ_KEY = (SELECT … ROWNUM = 1) |
用 DOC_ID 取一条业务键的标量等值条件 |
STATUS_CD < 3 |
限制待更新状态 |
EXISTS (… EVENT_TM 时间窗 … SIDE_FLAG 空值交叉) |
同业务键/节点下是否存在“对向”明细 |
CASE WHEN SYSDATE > DUE_TM … |
按时刻写回 STATUS_CD / REMARK_TX |
3. 分析问题
分析思路：
- 如何确认是慢 SQL： 现场该语句逻辑读约二十万量级；lab 23.5 单次 Elapsed≈0.210s，
db block gets≈20.2 万，相对改写后同语义语句明显偏高。 - 如何定位到慢的地方： 计划外层为
TABLE ACCESS FULL+HASH JOIN INNER；EXISTS对应SUBQUERY，内层索引 Loops≈100001，慢在“全表过候选 + 反复相关探测”，而不是CASE/EXISTS业务语义本身。
优化思路：
标量 = 子查询不利于把 BIZ_KEY 作为索引访问起点；改为 IN（无 AGGR/GROUP BY）更贴近官方可改写的子查询形态，并显式 BIZ_KEY IS NOT NULL，促使优化器以子查询结果驱动 NESTED LOOPS + INDEX RANGE SCAN，避免全表扫描。参见：
本轮在 23.5.2.101 复现“全表 + 高 Loops”；在 23.4.14.100 同脚本下 = 已走索引（见第 5.3、第 6 章双版本对照）。两版本优化器路径不同，改写收益也不同；上线与兼容验证须以目标版本实测为准。YashanDB 优化器迭代较快，稳定发布线内宜优先部署该线上最新小版本，并在该版本复核计划。
4. 环境与数据
为便于复现与对照，在模拟环境中按生产侧同类问题 仿建业务对象并灌入数据（非生产库直接操作）。同一套脚本分别在两套库上执行。
| 项 | 内容 |
|---|---|
| 对象 | DEMO_BIZ_HDR 约 10 万行（本轮计数 100001） |
| 造数 | 模拟环境新建；DOC_ID='D000000001' 对应 BIZ_KEY=1，并植入可命中 EXISTS 的配对行 |
| 关键索引 | IDX_DEMO_DOC_ID(DOC_ID)；IDX_DEMO_BIZ_COMP(BIZ_KEY,NODE_CD,EVENT_TM,STATUS_CD,DOC_ID,SIDE_FLAG)；IDX_DEMO_BIZ_KEY_ST(BIZ_KEY,STATUS_CD)；IDX_DEMO_BIZ_KEY(BIZ_KEY) |
| 统计信息 | DBMS_STATS.GATHER_TABLE_STATS 已采集 |
| 版本 | YashanDB 23.5.2.101 与 23.4.14.100（同主机 lab） |
造数要点（可复现）： biz_key 约 5000 档循环分配；doc_id 近似唯一；时间与 side_flag 空值比例用于触发 EXISTS 条件。
5. 解决方案
5.1 优化前（现状）
UPDATE demo_biz_hdr t
SET status_cd = (CASE WHEN SYSDATE > due_tm THEN 3 ELSE 1 END),
remark_tx = 'Manual'
WHERE t.biz_key = (
SELECT biz_key
FROM demo_biz_hdr x
WHERE x.doc_id = 'D000000001' AND ROWNUM = 1
)
AND t.status_cd < 3
AND EXISTS (
SELECT 1
FROM demo_biz_hdr a
WHERE a.biz_key = t.biz_key
AND a.node_cd = t.node_cd
AND a.status_cd > 0
AND a.doc_id <> t.doc_id
AND (a.side_flag IS NULL AND t.side_flag IS NOT NULL
OR t.side_flag IS NULL AND a.side_flag IS NOT NULL)
AND a.event_tm > t.event_tm - (149/2880)
AND a.event_tm < t.event_tm + (149/2880)
);
23.5.2.101
- 影响行数：4（随后
ROLLBACK） - Elapsed：00:00:00.210
- SQL hash：736448843
Execution Plan（关键）：
----------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Loops |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 0 | 204153 | 1 |
| 1 | SUBQUERY | QUERY[2] | 6961 | 148855 | 100001 |
| 2 | INDEX RANGE SCAN | IDX_DEMO_BIZ_COMP | 6961 | 142570 | 100001 |
| 3 | UPDATE | DEMO_BIZ_HDR | 0 | 204153 | 1 |
| 4 | HASH JOIN INNER | | 4 | 204011 | 5 |
| 5 | TABLE ACCESS FULL | DEMO_BIZ_HDR | 4 | 203955 | 5 |
| 6 | VIEW | | 1 | 10 | 2 |
| 7 | COUNT STOPKEY | | 1 | 9 | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 9 | INDEX RANGE SCAN | IDX_DEMO_DOC_ID | 1 | 8 | 1 |
----------------------------------------------------------------------------------------
关键谓词：外层 EXISTS QUERY[2] AND STATUS_CD < 3；HASH JOIN 上 T.BIZ_KEY = VSQ$1…BIZ_KEY（含 runtime filter）；SUBQUERY 侧按 BIZ_KEY/NODE_CD/EVENT_TM 访问 IDX_DEMO_BIZ_COMP。
Statistics： physical reads 0；db block gets 202,438；consistent gets 0；rows processed 4。
5.2 方案：改为 IN + IS NOT NULL
将 BIZ_KEY = (SELECT …) 改为 BIZ_KEY IN (SELECT …)，并增加 BIZ_KEY IS NOT NULL（与现场改写一致）：
UPDATE demo_biz_hdr t
SET status_cd = (CASE WHEN SYSDATE > due_tm THEN 3 ELSE 1 END),
remark_tx = 'Manual'
WHERE t.biz_key IN (
SELECT biz_key
FROM demo_biz_hdr x
WHERE x.doc_id = 'D000000001' AND ROWNUM = 1
)
AND t.biz_key IS NOT NULL
AND t.status_cd < 3
AND EXISTS (
SELECT 1
FROM demo_biz_hdr a
WHERE a.biz_key = t.biz_key
AND a.node_cd = t.node_cd
AND a.status_cd > 0
AND a.doc_id <> t.doc_id
AND (a.side_flag IS NULL AND t.side_flag IS NOT NULL
OR t.side_flag IS NULL AND a.side_flag IS NOT NULL)
AND a.event_tm > t.event_tm - (149/2880)
AND a.event_tm < t.event_tm + (149/2880)
);
23.5.2.101
- 影响行数：4（与优化前一致；随后
ROLLBACK） - Elapsed：00:00:00.004
- SQL hash：1694482287
Execution Plan（关键）：
----------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Loops |
----------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 0 | 268 | 1 |
| 1 | SUBQUERY | QUERY[2] | 4 | 73 | 21 |
| 2 | INDEX RANGE SCAN | IDX_DEMO_BIZ_COMP | 4 | 71 | 21 |
| 3 | UPDATE | DEMO_BIZ_HDR | 0 | 268 | 1 |
| 4 | NESTED LOOPS INNER | | 4 | 228 | 5 |
| 5 | SORT DISTINCT | | 1 | 10 | 2 |
| 6 | VIEW | | 1 | 8 | 2 |
| 7 | COUNT STOPKEY | | 1 | 7 | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 9 | INDEX RANGE SCAN | IDX_DEMO_DOC_ID | 1 | 6 | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 11 | INDEX RANGE SCAN | IDX_DEMO_BIZ_KEY | 4 | 199 | 5 |
----------------------------------------------------------------------------------------
Statistics： physical reads 0；db block gets 155；consistent gets 0；rows processed 4。
5.3 同脚本在 23.4.14.100
同一 Before/After SQL 在 23.4.14.100 上，优化前 = 已走 NESTED LOOPS + IDX_DEMO_BIZ_KEY_ST，未出现全表扫描；改为 IN 后计划形态接近，指标接近。
优化前（23.4）： Elapsed 0.003s；db block gets 158；影响行数 4；Plan hash 3044850505。
------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Starts |
------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 0 | 191 | 1 |
| 1 | SUBQUERY | QUERY[2] | 4 | 63 | 21 |
| 2 | INDEX RANGE SCAN | IDX_DEMO_BIZ_COMP | 4 | 58 | 21 |
| 3 | UPDATE | DEMO_BIZ_HDR | 0 | 191 | 1 |
| 4 | NESTED LOOPS INNER | | 4 | 145 | 1 |
| 5 | VIEW | | 2 | 11 | 2 |
| 6 | COUNT STOPKEY | | 2 | 9 | 2 |
| 7 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 8 | INDEX RANGE SCAN | IDX_DEMO_DOC_ID | 2 | 7 | 2 |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 10 | INDEX RANGE SCAN | IDX_DEMO_BIZ_KEY_ST | 4 | 114 | 1 |
------------------------------------------------------------------------------------------
优化后（23.4）： Elapsed 0.002s；db block gets 155；影响行数 4；Plan hash 1256705303。
-------------------------------------------------------------------------------------------
| Id | Operation | Name | A-Rows | A-Time | Starts |
-------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 0 | 175 | 1 |
| 1 | SUBQUERY | QUERY[2] | 4 | 58 | 21 |
| 2 | INDEX RANGE SCAN | IDX_DEMO_BIZ_COMP | 4 | 56 | 21 |
| 3 | UPDATE | DEMO_BIZ_HDR | 0 | 175 | 1 |
| 4 | NESTED LOOPS INNER | | 4 | 143 | 1 |
| 5 | SORT DISTINCT | | 1 | 13 | 1 |
| 6 | VIEW | | 1 | 8 | 1 |
| 7 | COUNT STOPKEY | | 1 | 6 | 1 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 9 | INDEX RANGE SCAN | IDX_DEMO_DOC_ID | 1 | 5 | 1 |
| 10 | TABLE ACCESS BY INDEX ROWID | DEMO_BIZ_HDR | | | |
| 11 | INDEX RANGE SCAN | IDX_DEMO_BIZ_KEY_ST | 4 | 112 | 1 |
-------------------------------------------------------------------------------------------
6. 对比总表与提升倍数
| 指标 | 23.5 Before | 23.5 After | 23.4 Before | 23.4 After | 来源 |
|---|---|---|---|---|---|
| 影响行数 | 4 | 4 | 4 | 4 | UPDATE |
| Elapsed | 0.210 s | 0.004 s | 0.003 s | 0.002 s | autotrace Timing |
| 关键外层路径 | HASH JOIN + FULL SCAN | NL + INDEX | NL + INDEX | NL + INDEX | autotrace Plan |
| EXISTS Loops/Starts | 100001 | 21 | 21 | 21 | Plan |
| db block gets | 202,438 | 155 | 158 | 155 | autotrace Statistics |
- 23.5 耗时提升倍数： 0.210 / 0.004 = 52.5
- 23.5 逻辑读提升倍数： 202438 / 155 ≈ 1306.1
- 23.4 耗时提升倍数： 0.003 / 0.002 = 1.5（
=已优，改写收益有限） - 23.4 逻辑读提升倍数： 158 / 155 ≈ 1.0
（公式：提升倍数 = 优化前指标 / 优化后指标。）
双版本差异说明
同一套脱敏 SQL、同一造数与索引，在两个版本上的表现 不一致：
| 维度 | 23.5.2.101 | 23.4.14.100 |
|---|---|---|
优化前 列 = (标量子查询) |
HASH JOIN + 全表扫描；EXISTS Loops≈10 万；逻辑读约 20 万 |
已是 NESTED LOOPS + 索引；Starts≈21；逻辑读约 158 |
改为 IN + IS NOT NULL 后 |
外层改为 NL + 索引，耗时/逻辑读大幅下降 | 计划仍为 NL + 索引，前后几乎持平 |
| 本案例改写的“可见收益” | 高（约 52.5× / 1306×） | 低（约 1.5× / 1.0×） |
因此：不能把某一版本上的计划或加速比直接套到另一版本；现场必须以 目标版本 实测为准。本轮差异说明的是「同形态 SQL 在不同发行版上的优化器路径选择不同」，并不否定任一侧的正确性。
7. 根因与改写说明
- 为何慢（23.5 / 与现场同类）：
列 = (标量子查询)路径下，优化器用全表扫描产生更新候选，再与标量子查询结果做HASH JOIN；EXISTS仍以SUBQUERY形式按外层行反复探测（Loops≈表规模），逻辑读与 A-Time 集中在 FULL SCAN 与 SUBQUERY。 - 方案做了什么：
IN+IS NOT NULL后，外层变为以子查询结果驱动的NESTED LOOPS+INDEX RANGE SCAN，EXISTS探测次数从约 10 万降至数十。这与文档中「IN子查询在无 AGGR 等限制时可改写为更优连接形态」的方向一致。 - 语义：
ROWNUM = 1的标量子查询在单值语义下，=与IN对命中行等价；本轮两边影响行数均为 4。 - 版本差异： 23.4.14.100 上同 SQL 的
=已选索引嵌套循环，不复现全表问题；23.5.2.101 上则需改写才能避开全表路径。不能假设所有版本行为相同。
8. 结论与建议
UPDATE/SELECT用 标量等值子查询 绑驱动键时，若计划出现 FULL SCAN + 高 Loops 的 SUBQUERY，优先尝试改为IN（或半连接友好写法），并视情况补IS NOT NULL。- 可推广： 按业务键定位再更新/过滤的语句；同类
列 = (SELECT … ROWNUM=1)自表更新语句。 - 版本与部署： YashanDB 优化器处于快速迭代中，每一发行版都可能引入新的改写规则、代价模型或执行路径；同一 SQL 在 23.4 与 23.5 上计划不同，属于正常现象。兼容与上线时，宜在既定的 稳定大版本/发布线内，优先部署该线上的最新小版本（含补丁），并在目标版本上复核关键 SQL 的计划与耗时，避免用旧版 lab 结论替代生产版本行为。
- 局限： 本结果在 lab 约 10 万行、热缓存（physical reads=0）下测得；生产分布、并发与索引集可能改变代价。
- 本案例未做： 改索引结构、改优化器参数、绑定变量窥视；上线前请在目标版本用同等口径复核。
9. 附录
DDL 骨架（示意）：
CREATE TABLE demo_biz_hdr (
biz_key NUMBER(18) NOT NULL,
doc_id VARCHAR2(32) NOT NULL,
due_tm DATE,
status_cd NUMBER(18) NOT NULL,
remark_tx VARCHAR2(64),
node_cd VARCHAR2(32),
side_flag VARCHAR2(64),
event_tm DATE,
c1 VARCHAR2(64),
c2 VARCHAR2(64)
);
CREATE INDEX idx_demo_doc_id ON demo_biz_hdr (doc_id);
CREATE INDEX idx_demo_biz_comp ON demo_biz_hdr
(biz_key, node_cd, event_tm, status_cd, doc_id, side_flag);
CREATE INDEX idx_demo_biz_key_st ON demo_biz_hdr (biz_key, status_cd);
CREATE INDEX idx_demo_biz_key ON demo_biz_hdr (biz_key);
-- 再按约 1e5 行灌数并收集统计信息
参考文档：
SQL优化改IN躲开全表扫描性能提升53倍：等您坐沙发呢！