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

靳谷雪 发表于 4 天前

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

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，不仅性能更好，维护起来也更容易。
参考链接：SQL优化：标量子查询的介绍和改写基础内容
/------------------作者介绍----------------------
姓名：黄廷忠
个人博客: (http://www.htz.pw)
CSDN地址: (https://blog.csdn.net/wwwhtzpw)
博客园地址: (https://www.cnblogs.com/www-htz-pw)
-------------------------------------------------/

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

查看完整版本: SQL优化实战：标量子查询改写外连接的真实案例