上海做企业网站的公司西双版纳网站制作公司

上海做企业网站的公司,西双版纳网站制作公司,烟台高新区网站,甘肃省路桥建设集团网站案例背景在巡检过程中根据TOP SQL CPU和TOP SQL LOGICAL都发现此SQL排名第一#xff0c;于是用sql10.sql的脚本收集相关的性能数据后#xff0c;发现了一个典型的标量子查询性能问题。由于SQL语句是核心业务中的核心SQL语句#xff0c;所以执行次数非常多#xff0c;于是导…案例背景在巡检过程中根据TOP SQL CPU和TOP SQL LOGICAL都发现此SQL排名第一于是用sql10.sql的脚本收集相关的性能数据后发现了一个典型的标量子查询性能问题。由于SQL语句是核心业务中的核心SQL语句所以执行次数非常多于是导致逻辑读飙升CPU也随着增加。让我先看看这个罪魁祸首的SQL长什么样原始SQL业务逻辑分析涉及的表结构这个查询主要涉及两个表主表ORDER_DETAIL - 订单明细表存储订单的基本信息关联表ORDER_EXECUTIONDB_LINK - 订单执行记录表通过数据库链接访问记录每个订单的执行情况业务需求分析业务人员需要看到的信息包括订单基本信息客户姓名、部门编码、工位号、订单流水号、订单号、商品信息等执行情况统计每个订单的完成数量和剩余数量过滤条件只显示未完全执行的订单完成数 订单数量看起来需求很简单但是实现起来却有很多坑。让我仔细分析一下这个SQL的逻辑。原始SQLSELECT 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_EXECUTIONDB_LINK cWHERE c.ORDER_NOA.ORDER_NOAND c.DELETE_FLAG0) 完成数,a.QUANTITY -(SELECT count(*)FROM ORDER_EXECUTIONDB_LINK cWHERE c.ORDER_NOA.ORDER_NOAND c.DELETE_FLAG0) 剩余数FROM ORDER_DETAIL AWHERE A.ORDER_NO NOT IN(SELECT B.ORDER_NOFROM(SELECT count(*) 完成数,c.ORDER_NOFROM ORDER_EXECUTIONDB_LINK cWHERE c.DELETE_FLAG0GROUP BY c.ORDER_NO) BWHERE b.完成数A.QUANTITYAND B.ORDER_NOa.ORDER_NO);问题分析标量子查询的陷阱当我第一次看到这个SQL的时候说实话我也有点懵。这个SQL看起来很简单但是仔细分析后发现了几个严重的问题想不通开发人员为什么会这样写可能是复制、粘贴习惯了。1. 标量子查询的逐行执行问题问题根源这个SQL最大的问题就是标量子查询 (SELECT count(*) FROM ORDER_EXECUTIONDB_LINK c WHERE c.ORDER_NOA.ORDER_NO AND c.DELETE_FLAG0)你可能觉得这没什么但是这里有个陷阱标量子查询会对主查询返回的每一行都执行一次想象一下如果主查询返回1000行订单那么这个子查询就要执行1000次。更糟糕的是完成数被计算了两次一次用于显示一次用于计算剩余数所以实际上子查询执行了2000次执行机制-- 伪代码演示标量子查询的执行逻辑FOR 每一行 row IN (ORDER_DETAIL) LOOP执行子查询1: 完成数 (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NOrow.ORDER_NO)执行子查询2: 剩余数 row.QUANTITY - (SELECT count(*) FROM ORDER_EXECUTION WHERE ORDER_NOrow.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_NOFROM ORDER_EXECUTIONDB_LINK cWHERE c.DELETE_FLAG0GROUP BY c.ORDER_NO) BWHERE b.完成数A.QUANTITY AND B.ORDER_NOa.ORDER_NO)这个逻辑的意思是排除那些完成数等于订单数量的订单。但是这种写法有几个问题逻辑不够直观需要仔细分析才能理解当子查询返回NULL值时NOT IN的行为可能不符合预期结构复杂维护困难改写思路从逐行到批量分析了问题后我开始思考如何改写这个SQL。我的思路是将标量子查询改为LEFT JOIN实现批量处理。改写核心思想经过分析我总结出了几个改写原则批量处理替代逐行处理将标量子查询改为LEFT JOIN实现批量关联预聚合数据先统计每个订单号的完成数再与主表关联避免重复计算通过JOIN获取完成数避免重复执行相同的子查询简化过滤条件将复杂的NOT IN改为直观的比较条件改写步骤我的改写思路分为4个步骤第一步创建完成数统计子查询第二步与主表LEFT JOIN关联第三步使用NVL处理NULL值第四步简化过滤条件让我详细解释每个步骤改写后的SQL方案一NOT EXISTS方式推荐-- 改写后的SQLSELECTCUSTOMER_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 ALEFT JOIN (SELECTORDER_NO,COUNT(*) as 完成数FROM ORDER_EXECUTIONDB_LINKWHERE DELETE_FLAG0GROUP BY ORDER_NO) c ON c.ORDER_NO A.ORDER_NOWHERE NOT EXISTS (SELECT 1FROM ORDER_EXECUTIONDB_LINK dWHERE d.ORDER_NO A.ORDER_NOAND d.DELETE_FLAG0HAVING COUNT(*) A.QUANTITY);方案二直接过滤方式更简洁-- 更简洁的改写方案SELECTa.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 aLEFT JOIN (SELECTORDER_NO,COUNT(*) AS 完成数FROM ORDER_EXECUTIONDB_LINKWHERE DELETE_FLAG 0GROUP BY ORDER_NO) c ON c.ORDER_NO a.ORDER_NOWHERE 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_EXECUTIONDB_LINK cWHERE c.ORDER_NOA.ORDER_NO AND c.DELETE_FLAG0)步骤二提取为独立子查询-- 提取为独立的聚合查询SELECT ORDER_NO, COUNT(*) AS 完成数FROM ORDER_EXECUTIONDB_LINKWHERE DELETE_FLAG 0GROUP BY ORDER_NO步骤三使用LEFT JOIN关联-- 通过LEFT JOIN关联LEFT JOIN (SELECT ORDER_NO, COUNT(*) AS 完成数FROM ORDER_EXECUTIONDB_LINKWHERE DELETE_FLAG 0GROUP 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. 避免标量子查询的最佳实践不推荐的做法SELECTorder_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_countFROM orders;推荐的做法SELECTo.order_id,c.customer_name,COALESCE(oi.item_count, 0) item_countFROM orders oLEFT JOIN customers c ON c.customer_id o.customer_idLEFT JOIN (SELECT order_id, COUNT(*) as item_countFROM order_itemsGROUP BY order_id) oi ON oi.order_id o.order_id;推荐的做法SQL的编写尽量少采用复制、粘贴的方式来实现最后是根据业务逻辑梳理清楚后再编写SQL语句可减少SQL的复杂度也可以减少表的多次访问。我的经验标量子查询虽然看起来简单但是往往隐藏着性能陷阱。在写SQL的时候优先考虑JOIN的方式。总结通过这次改写经历讲了讲在真实的生产环境中标量子查询的陷阱。希望在生产环境中可以尽可能的避免类似的SQL语句出现。记住好的SQL不仅要功能正确还要结构清晰、易于维护。标量子查询虽然看起来简单但是往往隐藏着性能陷阱。在实际开发中我们应该养成避免标量子查询的习惯优先使用JOIN等更优雅的关联方式。同时SQL优化不仅仅是性能优化更是代码质量的优化。一个结构清晰、逻辑直观的SQL不仅性能更好维护起来也更容易。