SQL优化实战：标量子查询改写外连接的真实案例

SQL优化实战：标量子查询改写外连接的真实案例

我们的文章会在微信公众号IT民工的龙马人生和博客网站 ( www.htz.pw )同步更新 ，欢迎关注收藏，也欢迎大家转载，但是请在文章开始地方标注文章出处，谢谢！
由于博客中有大量代码，通过页面浏览效果更佳。

本文基于在节前巡检中发现的一个客户的真实SQL优化案例，当然对列名和表明都做了脱敏的处理。本案例详细介绍了如何将标量子查询改写为外连接和从整个SQL功能出发改写SQL语句，在提升SQL性能的同时保证业务逻辑的正确性。关于标量子查询的介绍和改写基础内容可参考昨天发的文章SQL优化：标量子查询的介绍和改写基础内容
案例背景

在巡检过程中根据TOP SQL CPU和TOP SQL LOGICAL都发现此SQL排名第一，于是用sql10.sql的脚本收集相关的性能数据后，发现了一个典型的标量子查询性能问题。由于SQL语句是核心业务中的核心SQL语句，所以执行次数非常多，于是导致逻辑读飙升，CPU也随着增加。
让我先看看这个"罪魁祸首"的SQL长什么样：
原始SQL业务逻辑分析

涉及的表结构

这个查询主要涉及两个表：

• 主表：ORDER_DETAIL - 订单明细表，存储订单的基本信息
  
• 关联表：ORDER_EXECUTION@DB_LINK - 订单执行记录表，通过数据库链接访问，记录每个订单的执行情况
  

业务需求分析

业务人员需要看到的信息包括：

• 订单基本信息：客户姓名、部门编码、工位号、订单流水号、订单号、商品信息等
  
• 执行情况统计：每个订单的完成数量和剩余数量
  
• 过滤条件：只显示未完全执行的订单（完成数 < 订单数量）
  

看起来需求很简单，但是实现起来却有很多坑。让我仔细分析一下这个SQL的逻辑。
原始SQL

1. SELECT CUSTOMER_NAME 客户姓名,
2.        DEPT_CODE 部门编码,
3.        WORKSTATION_NO 工位号,
4.        ORDER_SERIAL 订单流水号,
5.        ORDER_ID 订单ID,
6.        ORDER_NO 订单号,
7.        PRODUCT_NAME 产品名称,
8.        PRODUCT_NAME 商品名称,
9.        PRODUCT_CODE 商品编码,
10.        PRODUCT_SPEC 规格,
11.        UNIT_NAME 单位,
12.        ORDER_DATE 下单时间,
13.        a.QUANTITY 数量,
14.        (SELECT count(*)
15.         FROM ORDER_EXECUTION@DB_LINK c
16.         WHERE c.ORDER_NO=A.ORDER_NO
17.           AND c.DELETE_FLAG='0') 完成数,
18.        a.QUANTITY -
19.        (SELECT count(*)
20.         FROM ORDER_EXECUTION@DB_LINK c
21.         WHERE c.ORDER_NO=A.ORDER_NO
22.           AND c.DELETE_FLAG='0') 剩余数
23. FROM ORDER_DETAIL A
24. WHERE A.ORDER_NO NOT IN
25.     (SELECT B.ORDER_NO
26.      FROM
27.        (SELECT count(*) 完成数,
28.                c.ORDER_NO
29.         FROM ORDER_EXECUTION@DB_LINK c
30.         WHERE c.DELETE_FLAG='0'
31.         GROUP BY c.ORDER_NO) B
32.      WHERE b.完成数=A.QUANTITY
33.        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次！
执行机制：

1. -- 伪代码演示标量子查询的执行逻辑
2. FOR 每一行 row IN (ORDER_DETAIL) LOOP
3.     执行子查询1: 完成数 = (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NO=row.ORDER_NO)
4.     执行子查询2: 剩余数 = row.QUANTITY - (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NO=row.ORDER_NO)
5.     组合结果行
6. END LOOP;

复制代码

2. 重复计算问题

我发现了另一个问题：完成数被计算了两次！

• 一次用于显示：(SELECT count(*) ...) 完成数
  
• 一次用于计算剩余数：a.QUANTITY - (SELECT count(*) ...) 剩余数
  

这明显违反了DRY原则，不仅增加了代码冗余，还可能导致性能问题。
3. NOT IN子查询的复杂性

最后，这个NOT IN子查询也很复杂：

1. WHERE A.ORDER_NO NOT IN (
2.     SELECT B.ORDER_NO FROM (
3.         SELECT count(*) 完成数, c.ORDER_NO
4.         FROM ORDER_EXECUTION@DB_LINK c
5.         WHERE c.DELETE_FLAG='0'
6.         GROUP BY c.ORDER_NO
7.     ) B
8.     WHERE b.完成数=A.QUANTITY AND B.ORDER_NO=a.ORDER_NO
9. )

复制代码

这个逻辑的意思是：排除那些完成数等于订单数量的订单。但是这种写法有几个问题：

• 逻辑不够直观，需要仔细分析才能理解
  
• 当子查询返回NULL值时，NOT IN的行为可能不符合预期
  
• 结构复杂，维护困难
  

改写思路：从"逐行"到"批量"

分析了问题后，我开始思考如何改写这个SQL。我的思路是：将标量子查询改为LEFT JOIN，实现批量处理。
改写核心思想

经过分析，我总结出了几个改写原则：

• 批量处理替代逐行处理：将标量子查询改为LEFT JOIN，实现批量关联
  
• 预聚合数据：先统计每个订单号的完成数，再与主表关联
  
• 避免重复计算：通过JOIN获取完成数，避免重复执行相同的子查询
  
• 简化过滤条件：将复杂的NOT IN改为直观的比较条件
  

改写步骤

我的改写思路分为4个步骤：

• 第一步：创建完成数统计子查询
  
• 第二步：与主表LEFT JOIN关联
  
• 第三步：使用NVL处理NULL值
  
• 第四步：简化过滤条件
  

让我详细解释每个步骤：
改写后的SQL

方案一：NOT EXISTS方式（推荐）

1. -- 改写后的SQL
2. SELECT
3.     CUSTOMER_NAME 客户姓名,
4.     DEPT_CODE 部门编码,
5.     WORKSTATION_NO 工位号,
6.     ORDER_SERIAL 订单流水号,
7.     ORDER_ID 订单ID,
8.     ORDER_NO 订单号,
9.     PRODUCT_NAME 产品名称,
10.     PRODUCT_NAME 商品名称,
11.     PRODUCT_CODE 商品编码,
12.     PRODUCT_SPEC 规格,
13.     UNIT_NAME 单位,
14.     ORDER_DATE 下单时间,
15.     a.QUANTITY 数量,
16.     COALESCE(c.完成数, 0) 完成数,
17.     a.QUANTITY - COALESCE(c.完成数, 0) 剩余数
18. FROM ORDER_DETAIL A
19. LEFT JOIN (
20.     SELECT
21.         ORDER_NO,
22.         COUNT(*) as 完成数
23.     FROM ORDER_EXECUTION@DB_LINK
24.     WHERE DELETE_FLAG='0'
25.     GROUP BY ORDER_NO
26. ) c ON c.ORDER_NO = A.ORDER_NO
27. WHERE NOT EXISTS (
28.     SELECT 1
29.     FROM ORDER_EXECUTION@DB_LINK d
30.     WHERE d.ORDER_NO = A.ORDER_NO
31.       AND d.DELETE_FLAG='0'
32.     HAVING COUNT(*) = A.QUANTITY
33. );

复制代码

方案二：直接过滤方式（更简洁）

1. -- 更简洁的改写方案
2. SELECT
3.     a.CUSTOMER_NAME AS 客户姓名,
4.     a.DEPT_CODE AS 部门编码,
5.     a.WORKSTATION_NO AS 工位号,
6.     a.ORDER_SERIAL AS 订单流水号,
7.     a.ORDER_ID AS 订单ID,
8.     a.ORDER_NO AS 订单号,
9.     a.PRODUCT_NAME AS 产品名称,
10.     a.PRODUCT_NAME AS 商品名称,
11.     a.PRODUCT_CODE AS 商品编码,
12.     a.PRODUCT_SPEC AS 规格,
13.     a.UNIT_NAME AS 单位,
14.     a.ORDER_DATE AS 下单时间,
15.     a.QUANTITY AS 数量,
16.     NVL(c.完成数, 0) AS 完成数,
17.     a.QUANTITY - NVL(c.完成数, 0) AS 剩余数
18. FROM ORDER_DETAIL a
19. LEFT JOIN (
20.     SELECT
21.         ORDER_NO,
22.         COUNT(*) AS 完成数
23.     FROM ORDER_EXECUTION@DB_LINK
24.     WHERE DELETE_FLAG = '0'
25.     GROUP BY ORDER_NO
26. ) c ON c.ORDER_NO = a.ORDER_NO
27. 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. 改写步骤详解

步骤一：识别标量子查询

1. -- 原始标量子查询
2. (SELECT count(*) FROM ORDER_EXECUTION@DB_LINK c
3. WHERE c.ORDER_NO=A.ORDER_NO AND c.DELETE_FLAG='0')

复制代码

步骤二：提取为独立子查询

1. -- 提取为独立的聚合查询
2. SELECT ORDER_NO, COUNT(*) AS 完成数
3. FROM ORDER_EXECUTION@DB_LINK
4. WHERE DELETE_FLAG = '0'
5. GROUP BY ORDER_NO

复制代码

步骤三：使用LEFT JOIN关联

1. -- 通过LEFT JOIN关联
2. LEFT JOIN (
3.     SELECT ORDER_NO, COUNT(*) AS 完成数
4.     FROM ORDER_EXECUTION@DB_LINK
5.     WHERE DELETE_FLAG = '0'
6.     GROUP BY ORDER_NO
7. ) c ON c.ORDER_NO = a.ORDER_NO

复制代码

步骤四：处理NULL值和过滤条件

1. -- 使用NVL处理NULL值
2. NVL(c.完成数, 0) AS 完成数
4. -- 简化过滤条件
5. WHERE NVL(c.完成数, 0) < a.QUANTITY

复制代码

开发人员建议

基于这次改写经验，我想给开发人员一些建议：
1. 避免标量子查询的最佳实践

不推荐的做法：

1. SELECT
2.     order_id,
3.     (SELECT customer_name FROM customers WHERE customer_id = orders.customer_id) customer_name,
4.     (SELECT COUNT(*) FROM order_items WHERE order_id = orders.order_id) item_count
5. FROM orders;

复制代码

推荐的做法：

1. SELECT
2.     o.order_id,
3.     c.customer_name,
4.     COALESCE(oi.item_count, 0) item_count
5. FROM orders o
6. LEFT JOIN customers c ON c.customer_id = o.customer_id
7. LEFT JOIN (
8.     SELECT order_id, COUNT(*) as item_count
9.     FROM order_items
10.     GROUP BY order_id
11. ) oi ON oi.order_id = o.order_id;

复制代码

推荐的做法：
SQL的编写尽量少采用复制、粘贴的方式来实现，最后是根据业务逻辑梳理清楚后再编写SQL语句，可减少SQL的复杂度，也可以减少表的多次访问。
我的经验：标量子查询虽然看起来简单，但是往往隐藏着性能陷阱。在写SQL的时候，优先考虑JOIN的方式。
总结

通过这次改写经历，讲了讲在真实的生产环境中标量子查询的"陷阱"。希望在生产环境中可以尽可能的避免类似的SQL语句出现。记住好的SQL不仅要功能正确，还要结构清晰、易于维护。标量子查询虽然看起来简单，但是往往隐藏着性能陷阱。在实际开发中，我们应该养成避免标量子查询的习惯，优先使用JOIN等更优雅的关联方式。同时SQL优化不仅仅是性能优化，更是代码质量的优化。一个结构清晰、逻辑直观的SQL，不仅性能更好，维护起来也更容易。
参考链接：SQL优化：标量子查询的介绍和改写基础内容
/------------------作者介绍----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
-------------------------------------------------/

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！