jQuery.的改进细节

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

栽蓝坟艘案例背景

在巡检过程中，通过TOP SQL CPU和TOP SQL LOGICAL发现此SQL排名第一。于是使用sql10.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_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时，我也有点困惑。虽然看起来很简单，但仔细分析后发现了几个严重的问题，可能是因为开发人员习惯了复制粘贴。

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'

WHERE c.DELETE_FLAG='0' GROUP BY c.ORDER_NO ) B WHERE b.完成数=A.QUANTITY AND B.ORDER_NO=a.ORDER_NO ) 这个逻辑的意义是：筛选出那些完成数量不等于订单总量的订单。但是这种写法存在几个问题： - 逻辑不够直接，需要仔细分析才能理解。 - 当子查询返回空值时，NOT IN的操作可能不符合预期。 - 结构复杂，维护难度大。

改写思路：从“逐行处理”到“批量处理” 在深入分析了上述问题后，我考虑如何对这个SQL语句进行优化。我的主要想法是：将标量子查询改为LEFT JOIN，从而实现批量处理。 改写核心原则 经过详细分析，我总结了几条关键的改写准则： - 批量处理代替逐行处理：将标量子查询转换为LEFT JOIN，以实现批量关联。 - 预聚合数据：先统计每个订单号的完成数量，再与主表进行关联。 - 避免重复计算：通过JOIN获取完成数量，避免多次执行相同的子查询。 - 简化过滤条件：将复杂的NOT IN改为更直观的比较条件。

改写步骤 我的改写过程分为四个阶段： 1. 创建完成数统计的子查询。 2. 与主表进行LEFT JOIN关联。 3. 使用NVL处理空值。 4. 简化过滤条件。

详细解释每个步骤： 改写后的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操作中的空值陷阱。 - 执行计划相对稳定。 - 缺点： - SQL结构较为复杂。 - 需要额外的子查询验证。 - 方案二：直接过滤方式（我的推荐） - 优点： - SQL结构简洁，易于理解和维护。 - 使用NVL函数处理空值，语义清晰。 - 过滤条件直观：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改为直观的比较条件

改写步骤详解

1. 识别标量子查询

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

2. 提取为独立子查询

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

3. 使用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

4. 处理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，不仅性能更好，维护起来也更容易。

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：Jquery Query EXECUTION left join customers