案例背景
在巡检过程中根据TOP SQL CPU和TOP SQL LOGICAL都发现此SQL排名第一，于是用sql10.sql的脚本收集相关的性能数据后，发现了一个典型的标量子查询性能问题。由于SQL语句是核心业务中的核心SQL语句，所以执行次数非常多，于是导致逻辑读飙升，CPU也随着增加。
让我先看看这个"罪魁祸首"的SQL长什么样：
原始SQL业务逻辑分析
涉及的表结构
这个查询主要涉及两个表：
主表：ORDER_DETAIL - 订单明细表，存储订单的基本信息
关联表：ORDER_EXECUTION@DB_LINK - 订单执行记录表，通过数据库链接访问，记录每个订单的执行情况
业务需求分析
业务人员需要看到的信息包括：
订单基本信息：客户姓名、部门编码、工位号、订单流水号、订单号、商品信息等
执行情况统计：每个订单的完成数量和剩余数量
过滤条件：只显示未完全执行的订单（完成数 < 订单数量）
看起来需求很简单，但是实现起来却有很多坑。让我仔细分析一下这个SQL的逻辑。
原始SQL
SELECT CUSTOMER_NAME 客户姓名,
DEPT_CODE 部门编码,
WORKSTATION_NO 工位号,
ORDER_SERIAL 订单流水号,
ORDER_ID 订单ID,
ORDER_NO 订单号,
PRODUCT_NAME 产品名称,
PRODUCT_NAME 商品名称,
PRODUCT_CODE 商品编码,
PRODUCT_SPEC 规格,
UNIT_NAME 单位,
ORDER_DATE 下单时间,
a.QUANTITY 数量,
(SELECT count(*)
FROM ORDER_EXECUTION@DB_LINK c
WHERE c.ORDER_NO=A.ORDER_NO
AND c.DELETE_FLAG='0') 完成数,
a.QUANTITY -
(SELECT count(*)
FROM ORDER_EXECUTION@DB_LINK c
WHERE c.ORDER_NO=A.ORDER_NO
AND c.DELETE_FLAG='0') 剩余数
FROM ORDER_DETAIL A
WHERE A.ORDER_NO NOT IN
(SELECT B.ORDER_NO
FROM
(SELECT count(*) 完成数,
c.ORDER_NO
FROM ORDER_EXECUTION@DB_LINK c
WHERE c.DELETE_FLAG='0'
GROUP BY c.ORDER_NO) B
WHERE b.完成数=A.QUANTITY
AND B.ORDER_NO=a.ORDER_NO);
问题分析：标量子查询的"陷阱"
当我第一次看到这个SQL的时候，说实话，我也有点懵。这个SQL看起来很简单，但是仔细分析后发现了几个严重的问题，想不通开发人员为什么会这样写，可能是复制、粘贴习惯了。
1. 标量子查询的"逐行执行"问题
问题根源：
这个SQL最大的问题就是标量子查询 (SELECT count(*) FROM ORDER_EXECUTION@DB_LINK c WHERE c.ORDER_NO=A.ORDER_NO AND c.DELETE_FLAG='0')
你可能觉得这没什么，但是这里有个"陷阱"：标量子查询会对主查询返回的每一行都执行一次！
想象一下，如果主查询返回1000行订单，那么这个子查询就要执行1000次。更糟糕的是，完成数被计算了两次（一次用于显示，一次用于计算剩余数），所以实际上子查询执行了2000次！
执行机制：
-- 伪代码演示标量子查询的执行逻辑
FOR 每一行 row IN (ORDER_DETAIL) LOOP
执行子查询1: 完成数 = (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NO=row.ORDER_NO)
执行子查询2: 剩余数 = row.QUANTITY - (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NO=row.ORDER_NO)
组合结果行
END LOOP;
2. 重复计算问题
我发现了另一个问题：完成数被计算了两次！
一次用于显示：(SELECT count(*) ...) 完成数
一次用于计算剩余数：a.QUANTITY - (SELECT count(*) ...) 剩余数
这明显违反了DRY原则，不仅增加了代码冗余，还可能导致性能问题。
3. NOT IN子查询的复杂性
最后，这个NOT IN子查询也很复杂：
WHERE A.ORDER_NO NOT IN (
SELECT B.ORDER_NO FROM (
SELECT count(*) 完成数, c.ORDER_NO
FROM ORDER_EXECUTION@DB_LINK c
WHERE c.DELETE_FLAG='0'
GROUP BY c.ORDER_NO
) B
WHERE b.完成数=A.QUANTITY AND B.ORDER_NO=a.ORDER_NO
)
这个逻辑的意思是：排除那些完成数等于订单数量的订单。但是这种写法有几个问题：
逻辑不够直观，需要仔细分析才能理解
当子查询返回NULL值时，NOT IN的行为可能不符合预期
结构复杂，维护困难
改写思路：从"逐行"到"批量"
分析了问题后，我开始思考如何改写这个SQL。我的思路是：将标量子查询改为LEFT JOIN，实现批量处理。
改写核心思想
经过分析，我总结出了几个改写原则：
批量处理替代逐行处理：将标量子查询改为LEFT JOIN，实现批量关联
预聚合数据：先统计每个订单号的完成数，再与主表关联
避免重复计算：通过JOIN获取完成数，避免重复执行相同的子查询
简化过滤条件：将复杂的NOT IN改为直观的比较条件
改写步骤
我的改写思路分为4个步骤：
第一步：创建完成数统计子查询
第二步：与主表LEFT JOIN关联
第三步：使用NVL处理NULL值
第四步：简化过滤条件
让我详细解释每个步骤：
改写后的SQL
方案一：NOT EXISTS方式（推荐）
-- 改写后的SQL
SELECT
CUSTOMER_NAME 客户姓名,
DEPT_CODE 部门编码,
WORKSTATION_NO 工位号,
ORDER_SERIAL 订单流水号,
ORDER_ID 订单ID,
ORDER_NO 订单号,
PRODUCT_NAME 产品名称,
PRODUCT_NAME 商品名称,
PRODUCT_CODE 商品编码,
PRODUCT_SPEC 规格,
UNIT_NAME 单位,
ORDER_DATE 下单时间,
a.QUANTITY 数量,
COALESCE(c.完成数, 0) 完成数,
a.QUANTITY - COALESCE(c.完成数, 0) 剩余数
FROM ORDER_DETAIL A
LEFT JOIN (
SELECT
ORDER_NO,
COUNT(*) as 完成数
FROM ORDER_EXECUTION@DB_LINK
WHERE DELETE_FLAG='0'
GROUP BY ORDER_NO
) c ON c.ORDER_NO = A.ORDER_NO
WHERE NOT EXISTS (
SELECT 1
FROM ORDER_EXECUTION@DB_LINK d
WHERE d.ORDER_NO = A.ORDER_NO
AND d.DELETE_FLAG='0'
HAVING COUNT(*) = A.QUANTITY
);
方案二：直接过滤方式（更简洁）
-- 更简洁的改写方案
SELECT
a.CUSTOMER_NAME AS 客户姓名,
a.DEPT_CODE AS 部门编码,
a.WORKSTATION_NO AS 工位号,
a.ORDER_SERIAL AS 订单流水号,
a.ORDER_ID AS 订单ID,
a.ORDER_NO AS 订单号,
a.PRODUCT_NAME AS 产品名称,
a.PRODUCT_NAME AS 商品名称,
a.PRODUCT_CODE AS 商品编码,
a.PRODUCT_SPEC AS 规格,
a.UNIT_NAME AS 单位,
a.ORDER_DATE AS 下单时间,
a.QUANTITY AS 数量,
NVL(c.完成数, 0) AS 完成数,
a.QUANTITY - NVL(c.完成数, 0) AS 剩余数
FROM ORDER_DETAIL a
LEFT JOIN (
SELECT
ORDER_NO,
COUNT(*) AS 完成数
FROM ORDER_EXECUTION@DB_LINK
WHERE DELETE_FLAG = '0'
GROUP BY ORDER_NO
) c ON c.ORDER_NO = a.ORDER_NO
WHERE NVL(c.完成数, 0) < a.QUANTITY;
两种方案对比分析
在改写过程中，我尝试了两种不同的方案，各有优缺点：
方案一：NOT EXISTS方式
优势：
逻辑严谨，完全匹配原始SQL的业务逻辑
避免NOT IN的NULL值陷阱
执行计划相对稳定
劣势：
SQL结构相对复杂
需要额外的子查询验证
方案二：直接过滤方式（我的推荐）
优势：
SQL结构简洁，易于理解和维护
使用NVL函数处理NULL值，语义清晰
过滤条件直观：NVL(c.完成数, 0) < a.QUANTITY
性能通常更好（避免NOT EXISTS的额外开销）
避免重复数据访问：在方案一的基础上减少了一次对ORDER_EXECUTION表的方式。
劣势：
需要确保业务逻辑的准确性
对数据质量要求较高
我的选择：我最终选择了方案二，因为它更简洁、更直观，而且性能更好。在实际项目中，简洁的代码往往更容易维护。
改写要点说明
让我详细解释一下改写的几个关键点：
LEFT JOIN替代标量子查询：
将完成数统计改为子查询，通过LEFT JOIN关联
避免了逐行执行子查询的问题
这是改写的核心，从"逐行"变为"批量"
NULL值处理：
COALESCE方式：COALESCE(c.完成数, 0) - 标准SQL函数，跨数据库兼容
NVL方式：NVL(c.完成数, 0) - Oracle特有函数，性能略优
我选择NVL是因为这是Oracle环境，而且性能更好
过滤条件优化：
NOT EXISTS方式：逻辑严谨，完全匹配原始需求
直接过滤方式：NVL(c.完成数, 0) < a.QUANTITY - 简洁高效
我推荐直接过滤方式，因为它更直观
改写技术要点
1. 标量子查询改写原则
核心原则：
批量处理替代逐行处理：将标量子查询改为LEFT JOIN，实现批量关联
预聚合数据：先统计每个订单号的完成数，再与主表关联
避免重复计算：通过JOIN获取完成数，避免重复执行相同的子查询
简化过滤条件：将复杂的NOT IN改为直观的比较条件
2. 改写步骤详解
步骤一：识别标量子查询
-- 原始标量子查询
(SELECT count(*) FROM ORDER_EXECUTION@DB_LINK c
WHERE c.ORDER_NO=A.ORDER_NO AND c.DELETE_FLAG='0')
步骤二：提取为独立子查询
-- 提取为独立的聚合查询
SELECT ORDER_NO, COUNT(*) AS 完成数
FROM ORDER_EXECUTION@DB_LINK
WHERE DELETE_FLAG = '0'
GROUP BY ORDER_NO
步骤三：使用LEFT JOIN关联
-- 通过LEFT JOIN关联
LEFT JOIN (
SELECT ORDER_NO, COUNT(*) AS 完成数
FROM ORDER_EXECUTION@DB_LINK
WHERE DELETE_FLAG = '0'
GROUP BY ORDER_NO
) c ON c.ORDER_NO = a.ORDER_NO
步骤四：处理NULL值和过滤条件
-- 使用NVL处理NULL值
NVL(c.完成数, 0) AS 完成数
-- 简化过滤条件
WHERE NVL(c.完成数, 0) < a.QUANTITY
开发人员建议
基于这次改写经验，我想给开发人员一些建议：
1. 避免标量子查询的最佳实践
不推荐的做法：
SELECT
order_id,
(SELECT customer_name FROM customers WHERE customer_id = orders.customer_id) customer_name,
(SELECT COUNT(*) FROM order_items WHERE order_id = orders.order_id) item_count
FROM orders;
推荐的做法：
SELECT
o.order_id,
c.customer_name,
COALESCE(oi.item_count, 0) item_count
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
LEFT JOIN (
SELECT order_id, COUNT(*) as item_count
FROM order_items
GROUP BY order_id
) oi ON oi.order_id = o.order_id;
推荐的做法：
SQL的编写尽量少采用复制、粘贴的方式来实现，最后是根据业务逻辑梳理清楚后再编写SQL语句，可减少SQL的复杂度，也可以减少表的多次访问。
我的经验：标量子查询虽然看起来简单，但是往往隐藏着性能陷阱。在写SQL的时候，优先考虑JOIN的方式。
总结
通过这次改写经历，讲了讲在真实的生产环境中标量子查询的"陷阱"。希望在生产环境中可以尽可能的避免类似的SQL语句出现。记住好的SQL不仅要功能正确，还要结构清晰、易于维护。标量子查询虽然看起来简单，但是往往隐藏着性能陷阱。在实际开发中，我们应该养成避免标量子查询的习惯，优先使用JOIN等更优雅的关联方式。同时SQL优化不仅仅是性能优化，更是代码质量的优化。一个结构清晰、逻辑直观的SQL，不仅性能更好，维护起来也更容易。
)
