我们的文章会在微信公众号IT民工的龙马人生和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。
在实际的优化过程中，我们经常会遇到这样的场景：业务查询明明很"简单"，但是执行时间却长得让人崩溃。翻开执行计划一看，往往是SELECT列表中的标量子查询（Scalar Subquery）在作怪。就在节前巡检的过程中，就发现SQL语句中有多条标量子查询，导致SQL的逻辑读很高，运行效率很慢。跟现在开发人员沟通，需要将标量子查询改写为外连接方式，开发人员百思不得其解，哎，下一篇文章再具体讲案例。
本文将专门聚焦于标量子查询的基础知识的讲解和优化改写，通过大量实战案例和专业执行计划分析，让对标量子查询不熟悉的小伙伴们深入了解这块知识和掌握这类SQL优化的核心技法。
你将学到：
- 标量子查询的执行机制和性能瓶颈根源
- 五种不同场景的完整改写方案
- Oracle执行计划专业解读技巧
- 改写的陷阱与风险规避策略
- 完整的测试与验证流程
更重要的是，文末提供了完整的"结果一致性自检清单"，确保改写既提升性能，又保证逻辑正确性。
标量子查询是什么？
- 标量子查询（Scalar Subquery）是指在SQL语句中嵌套的、只返回"单行单列"结果的子查询。它主要出现在 SELECT 列表中，为主查询的每一行动态计算一个派生值。
核心特征：
- 对主查询的每一行，子查询都只允许返回一行一列的结果
- 如果子查询返回多行，会报错（如 Oracle 的 ORA-01427）
- 如果没有结果，则返回 NULL
例子（为每个订单取客户等级）：
SELECT o.order_id,
(SELECT c.customer_level
FROM customers c
WHERE c.customer_id = o.customer_id) AS customer_level
FROM orders o;
语义要求：子查询对每一行都"最多一行"。如果返回多行就会 ORA-01427 报错；如果没有行，结果是 NULL。
标量子查询的执行机制：
标量子查询的执行过程可以这样理解：Oracle为主查询的每一行都"单独执行"一次子查询，这就像是一层嵌套循环：
-- 伪代码演示标量子查询的执行逻辑
FOR 每一行 row IN (主查询结果集) LOOP
执行子查询，获得标量值 value = (SELECT ... FROM sub_table WHERE FK = row.FK)
将 row 与 value 组合成结果行
END LOOP;
性能影响分析：
- 性能扩展性差：主表1万行，子查询就要执行1万次
- 缓存利用不佳：每次子查询都是独立的数据库访问
- 并行执行受限：难以进行整体并行优化
为什么很多时候改成外连接更好？
性能角度：
- 批量处理 vs 逐行处理：标量子查询是"对每行执行一次子查询"，JOIN是批量整体联接
- 优化器发挥空间：JOIN可以让优化器选择最佳的执行策略（HASH、NESTED LOOPS、MERGE）
- 缓存友好：JOIN操作可以更好地利用内存缓冲区
- 并行处理：JOIN更容易启用并行执行
可读性提升：
- 结构清晰：复杂报表中的多列标量子查询改写后，统一到JOIN+聚合结构
- 逻辑一致：所有的数据关联都通过JOIN显式表达，业务逻辑更清楚
- 维护便利：新增字段或修改条件时，JOIN结构更容易扩展
性能一定提升
标量子查询改为外连接后一定能带来性能的提升吗？答案是否定了，但是多数情况下是能带来性能的提升。在生产中遇到问题时，还是需要根据具体SQL进行深入的分析。
执行计划分析示例：
让我们通过真实的执行计划对比这两种方式：
标量子查询的执行计划：
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 5 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| CUSTOMERS | 1 | 2 (0)|
|* 2 | INDEX UNIQUE SCAN | PK_CUSTOMER | 1 | 1 (0)|
| 3 | TABLE ACCESS FULL | ORDERS | 2 | 3 (0)|
-------------------------------------------------------------------
成本计算：主表2行 × 子查询成本2 = 4 + 主表扫描3 = 7（显示为5是简化）
LEFT JOIN的执行计划：
这里只是简单展示一种执行计划，不同生产环境中，可能具有不同的执行计划。
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 4 (0)|
| 1 | MERGE JOIN OUTER | | 2 | 4 (0)|
| 2 | TABLE ACCESS FULL| CUSTOMERS | 4 | 3 (0)|
| 3 | SORT JOIN | | 2 | 2 (0)|
| 4 | TABLE ACCESS FULL| ORDERS | 2 | 3 (0)|
---------------------------------------------------------------
为什么JOIN更快？
- 访问次数减少：标量子查询访问CUSTOMERS表2次，JOIN只访问1次
- 优化器选择最优策略：可以根据数据量和统计信息选择最佳连接方式
- 内存利用更优：批量处理比逐行处理更容易在内存中完成
场景一：SELECT 列中的标量子查询 → LEFT JOIN + 聚合
需求：每个订单显示客户等级；当客户不存在时显示 NULL。
原写法（标量子查询）：
SELECT o.order_id,
(SELECT c.customer_level
FROM customers c
WHERE c.customer_id = o.customer_id) AS customer_level
FROM orders o;
等价改写（LEFT JOIN）：
- 如果
customers (customer_id)唯一（PK/UK），不需要聚合：
SELECT o.order_id,
c.customer_level AS customer_level
FROM orders o
LEFT JOIN customers c
ON c.customer_id = o.customer_id;
- 如果子表可能“一对多”，标量子查询其实隐含“最多一行”的语义，这时要“消重/挑一行”，常见做法有三种：
- 先聚合到一行（例如取 MAX/MIN）：
SELECT o.order_id, c1.customer_level AS customer_level FROM orders o LEFT JOIN ( SELECT customer_id, MAX(customer_level) AS customer_level FROM customers GROUP BY customer_id ) c1 ON c1.customer_id = o.customer_id;- 用窗口函数挑一行（ROW_NUMBER=1）：
SELECT o.order_id, c.customer_level FROM orders o LEFT JOIN ( SELECT customer_id, customer_level, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_level DESC) AS rn FROM customers ) c ON c.customer_id = o.customer_id AND c.rn = 1;- 12c+ 可用 OUTER APPLY（Oracle 支持 APPLY）：
SELECT o.order_id, c.customer_level FROM orders o OUTER APPLY ( SELECT customer_level FROM customers c WHERE c.customer_id = o.customer_id ORDER BY customer_level DESC FETCH FIRST 1 ROW ONLY ) c;
建议优先使用“窗口函数挑一行”或“子查询聚合”，语义更直观。
场景二：实战案例：员工与最近调薪
需求：列出每位员工上一次调薪日期；若没有调薪记录，显示 NULL。
表结构（示意）：
employees(emp_id, emp_name, hire_date, ...)salary_changes(emp_id, change_date, change_rate, ...)（一对多）
原写法（标量子查询在 SELECT 列）：
SELECT e.emp_id,
e.emp_name,
(SELECT MAX(sc.change_date)
FROM salary_changes sc
WHERE sc.emp_id = e.emp_id) AS last_change_date
FROM employees e;
改写一：LEFT JOIN + 聚合（先聚到一行，再连大表）
SELECT e.emp_id, e.emp_name, sc1.last_change_date
FROM employees e
LEFT JOIN (
SELECT emp_id, MAX(change_date) AS last_change_date
FROM salary_changes
GROUP BY emp_id
) sc1
ON sc1.emp_id = e.emp_id;
改写二：OUTER APPLY（12c+）
SELECT e.emp_id, e.emp_name, sc.last_change_date
FROM employees e
OUTER APPLY (
SELECT MAX(change_date) AS last_change_date
FROM salary_changes sc
WHERE sc.emp_id = e.emp_id
) sc;
两种改写都只访问一次 salary_changes，优化器可做更好的联接与并行决策；原标量子查询则逻辑上相当于"逐行查子表"，更容易退化。
场景三：复杂的多表标量子查询改写
业务场景：电商订单报表，需要显示客户信息、最新地址、历史订单数量等多个维度数据。
原写法（多个标量子查询）：
SELECT o.order_id,
o.order_date,
-- 客户基本信息
(SELECT c.customer_name FROM customers c WHERE c.customer_id = o.customer_id) AS customer_name,
-- 最新收货地址
(SELECT MAX(a.address_text) FROM addresses a WHERE a.customer_id = o.customer_id) AS latest_address,
-- 历史订单数量
(SELECT COUNT(*) FROM order_history oh WHERE oh.customer_id = o.customer_id) AS history_count,
-- 客户等级（最新）
(SELECT customer_level FROM customers c WHERE c.customer_id = o.customer_id) AS customer_level
FROM orders o
WHERE o.order_date >= DATE '2024-01-01';
问题分析：
- 4个标量子查询，每个订单都要执行4次独立查询
- 每行成本 = 4 × 单次查询成本
- 1000个订单 = 4000次子表访问
优化改写（LEFT JOIN + 聚合）：
WITH customer_base AS (
SELECT customer_id, customer_name, customer_level FROM customers
),
address_latest AS (
SELECT customer_id, MAX(address_text) AS latest_address
FROM addresses
GROUP BY customer_id
),
order_history_sum AS (
SELECT customer_id, COUNT(*) AS history_count
FROM order_history
GROUP BY customer_id
)
SELECT o.order_id,
o.order_date,
cb.customer_name,
al.latest_address,
ohs.history_count,
cb.customer_level
FROM orders o
LEFT JOIN customer_base cb ON cb.customer_id = o.customer_id
LEFT JOIN address_latest al ON al.customer_id = o.customer_id
LEFT JOIN order_history_sum ohs ON ohs.customer_id = o.customer_id
WHERE o.order_date >= DATE '2024-01-01';
性能提升：
- 子表访问次数：从 4000次 → 4次表扫描 + 3次JOIN
- 优化器自由度：可以选择最优的表连接顺序和连接方式
- GPU并行：更容易启用并行处理
场景四：标量子查询与聚集函数的组合
业务场景：每月销售报表，需要计算销售额及其在当月的排名。
原写法：
SELECT s.sales_id,
s.sale_date,
s.amount,
(SELECT COUNT(*)
FROM sales s2
WHERE s2.sale_date >= TRUNC(s.sale_date, 'MM')
AND s2.sale_date < ADD_MONTHS(TRUNC(s.sale_date, 'MM'), 1)
AND s2.amount > s.amount) + 1 AS month_rank
FROM sales s
WHERE s.sale_date >= DATE '2024-01-01';
改写方案（窗口函数 + JOIN）：
WITH monthly_sales AS (
SELECT s.*,
ROW_NUMBER() OVER (
PARTITION BY TRUNC(sale_date, 'MM')
ORDER BY amount DESC
) AS month_rank
FROM sales s
WHERE s.sale_date >= DATE '2024-01-01'
)
SELECT sales_id, sale_date, amount, month_rank
FROM monthly_sales;
性能对比：
- 原写法：每行都要扫描整月数据计算排名
- 改写后：一次扫描 + 窗口函数排序，成本显著降低
场景四：递归/层次查询中的标量子查询改写
业务场景：组织结构树，每个员工需要显示其直接上级的部门名称。
原写法：
SELECT e.emp_id,
e.emp_name,
e.dept_id,
(SELECT d.dept_name FROM dept d WHERE d.dept_id = e.manger_dept_id) AS manager_dept_name
FROM employees e
WHERE e.status = 'ACTIVE';
改写方案：
SELECT e.emp_id,
e.emp_name,
e.dept_id,
md.dept_name AS manager_dept_name
FROM employees e
LEFT JOIN dept md ON md.dept_id = e.manger_dept_id
WHERE e.status = 'ACTIVE';
**进一步优化：**如果是复杂的层次结构，可以考虑：
WITH org_hierarchy AS (
SELECT emp_id, emp_name, dept_id, manger_dept_id,
CONNECT_BY_ROOT dept_name AS root_dept_name
FROM employees e
START WITH e.manger_dept_id IS NULL
CONNECT BY PRIOR e.dept_id = e.manger_dept_id
)
SELECT * FROM org_hierarchy WHERE status = 'ACTIVE';
Oracle执行计划读取核心技巧
1. 执行计划读取顺序："从下到上，从里到外"
ID 执行顺序：3→1→2→0
(最内层→中间层→外层→最终结果)
但在标量子查询中，实际执行流程是：
FOR 每一行 IN ORDERS表:
执行子查询路径(ID 2→ID 1)
组合结果
2. 关键性能指标优先级
重点关注字段（重要性排序）：
• Cost (%CPU) - 算子成本占比
• Rows - 估算 vs 实际行数差异
• Bytes - 数据传输量大小
• Time - 预估执行时间
• Starts - 算子被调用次数
3. 性能杀手操作（警惕！）
TABLE ACCESS FULL– 大表全扫SORT ORDER BY– 内存排序HASH GROUP BY– 耗内存聚合MERGE JOIN CARTESIAN– 笛卡尔积
4. 高效操作（点赞！）
INDEX UNIQUE SCAN/RANGE SCAN– 索引精确/范围访问TABLE ACCESS BY INDEX ROWID– 索引+回表HASH JOIN– 内存充足时的高效连接GROUP BY HASH STOPKEY– 限定聚合
5. 成本分析方法
-- 查看详细执行统计
SELECT /*+ GATHER_PLAN_STATISTICS */
o.order_id, c.customer_level
FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- 查看实际 vs 估算行数
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));
6. 成本异常诊断
- E-Rows远小于A-Rrows：缺失统计信息或数据倾斜
- Cost高但Time快：可能有并行执行或缓存命中
- Starts数量异常：标量子查询的标志特征
改写的十个核心检查点（务必执行！）
-
唯一性保障
- 标量子查询默认“最多一行”。在 JOIN 改写里，若子表“一对多”，要么聚合，要么挑一行（ROW_NUMBER），否则会出现重复行/基数膨胀。
-
无匹配行 → NULL
- 标量子查询无行时返回 NULL；改写为 LEFT JOIN 才能保留“主表行 + 子表空”这个语义。
- 如果用了 INNER JOIN，就会“过滤掉”无匹配行。
-
NULL值处理
- 标量子查询返回 NULL 时，改写后的JOIN要保持NULL语义
- LEFT JOIN确保无匹配记录时主表行仍保留，子表字段显示为NULL
-
一致性检查
- 改写后建议做 3 个对账：
- 行数是否一致
- 关键列（派生列）是否一致
- 有/无子表匹配的样本行对比（特别是 NULL 情况）
- 改写后建议做 3 个对账：
-
执行计划关注点
- 看联接顺序、索引使用。
- 子查询改 JOIN 后，通常更容易用到哈希连接、索引范围、并行等。
-
可替代方案：EXISTS/OUTER APPLY
- “只要存在一条即可”的条件，用
EXISTS比JOIN + DISTINCT更稳。 - 需要对每行做“相关子查询 + 取一条”的，12c+
OUTER APPLY常更简洁。
- “只要存在一条即可”的条件，用
小抄：标量子查询改写为外连接的步骤
- 判断子表基数关系
- 唯一（PK/UK）→ 可直接 LEFT JOIN
- 可能一对多 → 先聚合或用窗口函数挑一行
- 保留"无行→NULL"的语义
- 标量子查询语义 → 用 LEFT JOIN
- 确保无匹配记录时返回NULL值
- 校验结果
- COUNT 对账
- 随机抽样比对派生列
- 特别关注 NULL 行与重复行
结果一致性自检 SQL（模板）
- 总行数对账：
-- 原写法
WITH t AS (
SELECT /*+ MATERIALIZE */ o.order_id,
(SELECT MAX(customer_level) FROM customers c WHERE c.customer_id = o.customer_id) AS lvl
FROM orders o
)
SELECT COUNT(*) FROM t;
-- 改写后
WITH t AS (
SELECT /*+ MATERIALIZE */ o.order_id, c1.customer_level AS lvl
FROM orders o
LEFT JOIN (
SELECT customer_id, MAX(customer_level) AS customer_level
FROM customers
GROUP BY customer_id
) c1
ON c1.customer_id = o.customer_id
)
SELECT COUNT(*) FROM t;
- 派生列对账（抽样10行）：
SELECT *
FROM (
SELECT o.order_id,
(SELECT MAX(customer_level) FROM customers c WHERE c.customer_id = o.customer_id) AS lvl_scalar,
c1.customer_level AS lvl_join
FROM orders o
LEFT JOIN (
SELECT customer_id, MAX(customer_level) AS customer_level
FROM customers
GROUP BY customer_id
) c1
ON c1.customer_id = o.customer_id
ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 10;
高级优化技巧与最佳实践
1. 索引设计与SQL改写协同优化
在改写标量子查询时，要同步考虑索引策略：
-- 原标量子查询需要的外键列索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- 改写后的表连接可能需要不同的复合索引
CREATE INDEX idx_customers_level_id ON customers(customer_level, customer_id);
-- 根据改写后的WHERE条件和ORDER BY调整索引
CREATE INDEX idx_customers_composite ON customers(customer_level, create_date, customer_id);
2. 统计信息对改写效果的影响
-- 确保统计信息准确是关键
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'CUSTOMERS',
ESTIMATE_PERCENT => DBMS_STATS.AUTO_METHOD,
METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO');
END;
/
-- 对于大数据量表，考虑使用样本统计
BEGIN
DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'ORDERS',
ESTIMATE_PERCENT => 10,
CASCADE => TRUE);
END;
3. HINT的合理使用
仅在必要时使用HINT，并要验证其效果：
-- 强制特定的连接方式
SELECT /*+ USE_HASH(o c) */
o.order_id, c.customer_level
FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- 强制使用特定索引
SELECT /*+ INDEX(c idx_customers_composite) */
c.customer_level, COUNT(*)
FROM customers c
GROUP BY c.customer_level;
4. 分区表的特殊考虑
-- 触发器分区的改写要特别注意分区裁剪
SELECT /*+ PARTITION(e, 2024) */ -- 指定分区HINT
e.emp_id, sc.last_change_date
FROM employees e
LEFT JOIN (
SELECT emp_id, MAX(change_date) AS last_change_date
FROM salary_changes PARTITION (sc_2024) -- 分区裁剪
GROUP BY emp_id
) sc ON sc.emp_id = e.emp_id
WHERE e.hire_date >= DATE '2024-01-01';
5. 并行执行优化
-- 启用并行查询
SELECT /*+ PARALLEL(4) */
o.order_id, o.order_date,
c.customer_name, sc.dept_count
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN (
SELECT /*+ PARALLEL(2) */ customer_id, COUNT(*) AS dept_count
FROM customer_depts
GROUP BY customer_id
) sc ON sc.customer_id = o.customer_id;
性能测试的标准流程
步骤1：基准测试
-- 记录原SQL的执行时间和资源消耗
SET TIMING ON;
SELECT /*+ GATHER_PLAN_STATISTICS GATHER_AUTO_STATISTICS */
[原始标量子查询SQL]
FROM dual;
步骤2：改写验证
-- 执行改写后的SQL
SELECT /*+ GATHER_PLAN_STATISTICS GATHER_AUTO_STATISTICS */
[改写后的JOIN查询SQL]
FROM dual;
步骤3：计划对比
-- 获取执行计划详情
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => 'ALLSTATS LAST ROWS'));
步骤4：结果一致性验证
-- 执行结果对比脚本（见下文的自检清单）
常见陷阱与解决方案
陷阱1：改写了但效果反降
- 原因：子表数据量很小，索引覆盖不完整
- 解决：保持标量子查询或使用HINT强制连接方式
陷阱2：NULL值处理不一致
- 原因：LEFT JOIN与标量子查询的NULL语义差异
- 解决：明确编写NULL值的处理逻辑
陷阱3：数据倾斜导致性能问题
- 原因：某些键值的数据量差异极大
- 解决：使用采样统计、调整HINT或考虑物化视图
陷阱4：改写导致逻辑错误
- 原因：对"一对多"关系的处理不当
- 解决：仔细分析业务语义，使用适当的去重策略
小结与进阶建议
核心要点回顾：
- 标量子查询的执行机制决定了其性能瓶颈：逐行匹配 vs 批量连接
- 改写为JOIN的主要收益：减少访问次数、提升优化器自由度、增强并行处理能力
- 成功改写的关键：正确处理数据一对多、NULL语义、结果去重
作者介绍
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
SQL优化：标量子查询的介绍和改写基础内容：等您坐沙发呢！