MySQL篇-SQL優化實戰-減少子查詢

回顧

上一篇了解了分析SQL使用的explain，可以點擊查看MySQL篇-SQL優化實戰了解我在寫sql的注意事項還有explain的說明，這次拿一段生產使用的sql進行優化說明。從14s優化到2.6s

待優化的SQL

SELECT DISTINCTswpe.tag_number,hca.ACCOUNT_NAME customer_name,sipa.PIN_LOGO area_number,cdla.delivery_header_id,swpe.pack_number,swph.packslip_number,cdpa.transport_mode,date_format(swpe.inware_date,'%Y-%m-%d %H:%i:%s') in_warehouse_date,DATE(cdla.act_delivery_date) act_delivery_date,cdla.plate_number,wbp.PLATFORM_NAME schedule_stage_mir,sooh.order_number,swph.lot_number,milk.ATTRIBUTE14,ifnull((SELECT'Y'FROMcwms_delivery_attachment_all cdaWHEREcda.pack_entity_id = swpe.pack_entity_idAND cda.stock_scan_status = 'Y'LIMIT 1),'N') stock_status,ifnull((SELECTcda.commentsFROMcwms_delivery_attachment_all cdaWHEREcda.pack_entity_id = swpe.pack_entity_idLIMIT 1),NULL) comments,ifnull((SELECTswdh.delivery_numberFROMsfy_wsh_delivery_lines_all swdlINNER JOIN sfy_wsh_delivery_headers_all swdh ON swdl.delivery_header_id = swdh.delivery_header_idWHEREswpe.tag_number = swdl.tag_numberLIMIT 1),NULL) delivery_number,ifnull((SELECTfilter1FROMeos_dict_entryWHEREDICTTYPEID = 'AUTH_CONFIG'LIMIT 1),'Y') zc_power,ppl.PICK_NUMBER pd_number,IF (ifnull((SELECTIF (substr(father.license_number, 1, 2) = 'TP',father.license_number,NULL) tp_numberFROMwms_mtl_onhand_quantities_detail childINNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_idWHEREchild.license_number = swpe.tag_numberLIMIT 1),1) != 1,(SELECTIF (substr(father.license_number, 1, 2) = 'TP',father.license_number,NULL) tp_numberFROMwms_mtl_onhand_quantities_detail childINNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_idWHEREchild.license_number = swpe.tag_numberLIMIT 1),(SELECTIF (substr(oldfather.license_number,1,2) = 'TP',oldfather.license_number,NULL) tp_numberFROMwms_mtl_onhand_quantities_detail childINNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_idINNER JOIN wms_mtl_onhand_quantities_detail oldfather ON father.parent_mq_id = oldfather.mq_idWHEREchild.license_number = swpe.tag_numberLIMIT 1)) tp_number,IF ((SELECTDOWNRACKSTYPEFROMwms_mtl_onhand_quantities_detailWHERElicense_number = swpe.tag_numberLIMIT 1) = 3,'Y','N') type,(SELECTTC_NUMBERFROMcwms_mobile_tray_lines cmtlWHEREcmtl.TAG_NUMBER = swpe.tag_numberORDER BYCREATION_DATE DESCLIMIT 1) tc_number,IF ((SELECTDOWNRACKSTYPEFROMwms_mtl_onhand_quantities_detailWHERElicense_number = swpe.tag_numberLIMIT 1) = 3,1,0) is_scan
FROMcwms_delivery_lines_all cdla
INNER JOIN hz_cust_accounts hca ON cdla.customer_id = hca.cust_account_id
INNER JOIN sfy_oe_order_headers_all sooh ON sooh.oe_header_id = cdla.oe_header_id
INNER JOIN sfy_wsh_pack_entities swpe ON cdla.oe_header_id = swpe.oe_header_id
INNER JOIN sfy_wsh_packslip_headers_all swph ON swpe.header_id = swph.header_id
INNER JOIN cwms_delivery_plan_all cdpa ON cdpa.DELIVERY_HEADER_ID = cdla.DELIVERY_HEADER_ID
LEFT JOIN mtl_secondary_inventories msit ON msit.organization_id = swpe.organization_id
AND msit.secondary_inventory_name = swpe.subinventory_code
LEFT JOIN pick_pack_link ppl ON ppl.TAG_NUMBER = swpe.TAG_NUMBER
LEFT JOIN mtl_item_locations_kfv milk ON swpe.LOCATOR_ID = milk.INVENTORY_LOCATION_ID
AND milk.ENABLE_FLAG = 1
LEFT JOIN sfy_inv_pd_agent sipa ON sipa.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_ID
AND sipa.ORGANIZATION_ID = swpe.ORGANIZATION_ID
LEFT JOIN (SELECTDELIVERY_HEADER_ID,SCHEDULE_STAGE,group_concat(CAR_NUMBER) CAR_NUMBER,group_concat(DISTINCT LOGISTIC_PROVIDER) LOGISTIC_PROVIDERFROMwms_delivery_car_detailGROUP BYDELIVERY_HEADER_ID
) wdcd ON cdpa.DELIVERY_HEADER_ID = wdcd.DELIVERY_HEADER_ID
LEFT JOIN wms_bill_platform wbp ON wbp.PLATFORM_CODE = wdcd.SCHEDULE_STAGE
AND wbp.PLATFORM_ENABLE_FLAG = 1
WHEREswpe.tag_number IS NOT NULL
AND swpe.pack_number IS NOT NULL
AND swpe. STATUS != 'X'
AND cdpa.approve_status = 'Y'
AND cdpa.inv_approve_status = 'Y'
AND hca.account_number = 'GPS21017802'
AND cdla.act_delivery_date = '2024-06-26'
AND ifnull(swpe.delivery_date,cdla.act_delivery_date
) >= cdla.act_delivery_date
ORDER BYmilk.CONCATENATED_SEGMENTS

1、問題展示

查詢效率：3w條數據，耗費14s
執行結果
優化前的執行計劃

2、問題排查

2.1、操作思路

通過執行計劃看到wms_delivery_car_detail的執行計劃好像有優化空間，先將這張表的關聯移除后查看執行效率，以確認這張表影響的程度
第一次執行計劃分析

2.2、執行結果

查詢效率13.9s，發現移除后并沒有顯著的提高，說明這個子查詢的執行計劃并沒有 很大的效率問題
移除關聯子查詢的執行結果

3、大膽假設，小心求證

3.1、操作思路

那么接下來看看其他執行計劃，發現而且在字段上的子查詢有很多，假如我們把所有字段中的子查詢都移除了會有什么效果——移除了執行計劃中select_type=DEPENDENT SUBQUERY的子查詢后，只需要1.3s就拿到查詢結果了，執行計劃如圖所示，由此可知字段上的子查詢多了，結果集大時會對查詢效率有很大的影響。
移除DEPENDENT SUBQUERY的子查詢后的查詢結果
移除DEPENDENT SUBQUERY的子查詢后的執行計劃
既然找到了問題出現在子查詢上，但這些字段還是要查的，只是我們得換種方式，目的是移除子查詢的情況下依然查詢所需字段，那就要修改為連接查詢的方式，如先把關聯相同表的多個子查詢通過表關聯的方式合并為一次關聯。

3.2、子查詢分析

子查詢表對應的子查詢數量代碼行數
cwms_delivery_attachment_all2個第21至28，34至40
wms_mtl_onhand_quantities_detail2個第86至95，100至112，115至132，138至144
sfy_wsh_delivery_headers_all1個第55至62
cwms_mobile_tray_lines 1個第150至158（因為其中的tag_number是唯一的，經業務確認此處的order by可移除）

3.3、執行結果

修改為連接查詢后，查詢耗時僅需要2.6s，執行計劃如下:
修改后的執行結果
最后的執行計劃

-- 修改后的sql
SELECT DISTINCTswpe.tag_number,hca.ACCOUNT_NAME customer_name,sipa.PIN_LOGO area_number,cdla.delivery_header_id,swpe.pack_number,swph.packslip_number,cdpa.transport_mode,date_format(swpe.inware_date,'%Y-%m-%d %H:%i:%s') in_warehouse_date,DATE(cdla.act_delivery_date) act_delivery_date,wbp.PLATFORM_NAME schedule_stage_mir,sooh.order_number,swph.lot_number,milk.ATTRIBUTE14,ifnull(cdaa.stock_scan_status, 'N') stock_status,ifnull(cdaa.comments, NULL) comments,ifnull(swdh.delivery_number, NULL) delivery_number,ifnull((SELECTfilter1FROMeos_dict_entryWHEREDICTTYPEID = 'AUTH_CONFIG'LIMIT 1),'Y') zc_power,ppl.PICK_NUMBER pd_number,IF (-- wmoqd.TP_NUMBER是原關聯fater.license_number或者oldfather.license_number的值substr(wmoqd.TP_NUMBER, 1, 2) = 'TP',wmoqd.TP_NUMBER,NULL) tp_number,IF (wmoqd.DOWNRACKSTYPE = 3, 'Y', 'N') type,cmtl.TC_NUMBER tc_number,IF (wmoqd.DOWNRACKSTYPE = 3, 1, 0) is_scan
FROMcwms_delivery_lines_all cdla
INNER JOIN hz_cust_accounts hca ON cdla.customer_id = hca.cust_account_id
INNER JOIN sfy_oe_order_headers_all sooh ON sooh.oe_header_id = cdla.oe_header_id
INNER JOIN sfy_wsh_pack_entities swpe ON cdla.oe_header_id = swpe.oe_header_id
INNER JOIN sfy_wsh_packslip_headers_all swph ON swpe.header_id = swph.header_id
INNER JOIN cwms_delivery_plan_all cdpa ON cdpa.DELIVERY_HEADER_ID = cdla.DELIVERY_HEADER_ID
LEFT JOIN mtl_secondary_inventories msit ON msit.organization_id = swpe.organization_idAND msit.secondary_inventory_name = swpe.subinventory_code
LEFT JOIN pick_pack_link ppl ON ppl.TAG_NUMBER = swpe.TAG_NUMBER
LEFT JOIN mtl_item_locations_kfv milk ON swpe.LOCATOR_ID = milk.INVENTORY_LOCATION_IDAND milk.ENABLE_FLAG = 1
LEFT JOIN sfy_inv_pd_agent sipa ON sipa.CUST_ACCOUNT_ID = hca.CUST_ACCOUNT_IDAND sipa.ORGANIZATION_ID = swpe.ORGANIZATION_ID
LEFT JOIN (SELECTDELIVERY_HEADER_ID,SCHEDULE_STAGE,group_concat(CAR_NUMBER) CAR_NUMBER,group_concat(DISTINCT LOGISTIC_PROVIDER) LOGISTIC_PROVIDERFROMwms_delivery_car_detailGROUP BYDELIVERY_HEADER_ID
) wdcd ON cdpa.DELIVERY_HEADER_ID = wdcd.DELIVERY_HEADER_ID
LEFT JOIN wms_bill_platform wbp ON wbp.PLATFORM_CODE = wdcd.SCHEDULE_STAGEAND wbp.PLATFORM_ENABLE_FLAG = 1
-- 主要改動在這里：從子查詢遷移到下面左關聯
LEFT JOIN cwms_delivery_attachment_all cdaa ON cdaa.PACK_ENTITY_ID=swpe.PACK_ENTITY_ID
LEFT JOIN wms_mtl_onhand_quantities_detail wmoqd ON wmoqd.LICENSE_NUMBER=swpe.TAG_NUMBER AND wmoqd.ORGANIZATION_ID=swpe.ORGANIZATION_ID
LEFT JOIN sfy_wsh_delivery_lines_all swdl ON swdl.TAG_NUMBER=swpe.TAG_NUMBER
LEFT JOIN sfy_wsh_delivery_headers_all swdh ON swdh.DELIVERY_HEADER_ID=swdl.DELIVERY_HEADER_ID
LEFT JOIN cwms_mobile_tray_lines cmtl ON cmtl.TAG_NUMBER=swpe.TAG_NUMBER
WHEREswpe.tag_number IS NOT NULL
AND swpe.pack_number IS NOT NULL
AND swpe. STATUS != 'X'
AND cdpa.approve_status = 'Y'
AND cdpa.inv_approve_status = 'Y'
AND hca.account_number = 'GPS21017802'
AND cdla.act_delivery_date = '2024-06-26'
AND ifnull(swpe.delivery_date,cdla.act_delivery_date
) >= cdla.act_delivery_date
GROUP BY swpe.TAG_NUMBER
ORDER BYmilk.CONCATENATED_SEGMENTS

總結

  1. 當結果集字段中有好幾個相同表的子查詢時，將子查詢修改為連接查詢的效率提升會比較大（相當于一行記錄處理一次查詢）
  2. 有時候執行計劃可能無法直接看出修改哪里能提升，但能給我們提供優化的思路
  3. 在執行計劃中看到每個表都走索引了，但是卻還是很慢，那我們可以一段一段的、一表一表的排除，找到問題點在哪，而多快能找到主要就取決于經驗還有對表的熟悉程度了。

本文來自互聯網用戶投稿，該文觀點僅代表作者本人，不代表本站立場。本站僅提供信息存儲空間服務，不擁有所有權，不承擔相關法律責任。
如若轉載，請注明出處：http://www.pswp.cn/diannao/40652.shtml
繁體地址，請注明出處：http://hk.pswp.cn/diannao/40652.shtml
英文地址，請注明出處：http://en.pswp.cn/diannao/40652.shtml

如若內容造成侵權/違法違規/事實不符，請聯系多彩編程網進行投訴反饋email:809451989@qq.com，一經查實，立即刪除！

相關文章

VBA中類的解讀及應用第十三講:限制復選選擇，窗體模塊的搭建

VBA中類的解讀及應用第十三講:限制復選選擇，窗體模塊的搭建

《VBA中類的解讀及應用》教程【10165646】是我推出的第五套教程，目前已經是第一版修訂了。這套教程定位于最高級，是學完初級，中級后的教程。 類，是非常抽象的，更具研究的價值。隨著我們學習、應用VBA的深入&#xff0…
閱讀更多...
02-部署LVS-DR群集

02-部署LVS-DR群集

1.LVS-DR工作原理 LVS-DR模式，Director Server作為群集的訪問入口，不作為網購使用，節點Director Server 與 Real Server 需要在同一個網絡中，返回給客戶端的數據不需要經過Director Server 為了響應對整個群集的訪問，…
閱讀更多...
Java WebService記

Java WebService記

Web Services開發 常用的 Web Services 框架有 Apache Axis1 、 Apache Axis2 、 Apache CXF ，而 Apache Axis1 已經逐漸被淘汰所以本文不會討論，重點關注 Apache Axis2 及 Apache CXF 。 Apache Axis2 在IDEA中新建 Axis2Demo 項目后右鍵選擇 添加框架…
閱讀更多...
每天10個js面試題(一)

每天10個js面試題(一)

1.js基本數據類型？ JavaScript 共有八種數據類型，分別是Undefined、Null、Boolean、Number、String、Object、Symbol、BigInt。其中 Symbol 和 BigInt 是 ES6 中新增的數據類型 2.let、const、var的區別？ let和const有暫時性死區&#xff0…
閱讀更多...
開源模型應用落地-LangChain高階-智能體探究-agent類型（一）

開源模型應用落地-LangChain高階-智能體探究-agent類型（一）

一、前言 大模型具有非常強大的功能,可以解答疑問、撰寫報告和文檔、總結內容、進行翻譯等各種日常工作任務。然而，大模型還可以應用于更多的場景,發揮出更強大的作用。 通過智能體，我們可以實現許多有價值的事情，比如：在日常生活中，我們能借助智能體實現智能家居的自動化…
閱讀更多...
【滲透入門】SQL注入

【滲透入門】SQL注入

聲明：本文檔或演示材料僅供教育和教學目的使用，任何個人或組織使用本文檔中的信息進行非法活動，均與本文檔的作者或發布者無關。 文章目錄 什么是sql注入sql注入舉例防御方式練習靶場 什么是sql注入 SQL注入是一種網絡安全漏洞，攻…
閱讀更多...
【Android源碼】Gerrit安裝

【Android源碼】Gerrit安裝

前言 如果你打開 https://android.googlesource.com/platform/manifest，就會發現，google官方管理Android源碼，使用的是Gerrit。Android系統源碼是非常大的，用Git肯定是不適合。對于大型項目，得用Gerrit，今…
閱讀更多...
NoSQL之Redis高可用與優化

NoSQL之Redis高可用與優化

一、Redis高可用 在web服務器中，高可用是指服務器可以正常訪問的時間，衡量的標準是在多長時間內可以提供正常服務（99.9%、99.99%、99.999%等等）。 但是在Redis語境中，高可用的含義似乎要寬泛一些，除了保證…
閱讀更多...
二叉樹的鏈式訪問 與 二叉樹專題

二叉樹的鏈式訪問 與 二叉樹專題

目錄 二叉樹的前、中、后序遍歷求二叉樹第K層節點的個數二叉樹查找值為x的節點leetcode相同的樹對稱二叉樹二叉樹的前序遍歷另一棵子樹牛客 二叉樹的遍歷 二叉樹的前、中、后序遍歷 1.前序遍歷：先訪問根節點，再訪問左子樹，最后訪問右子樹 根…
閱讀更多...
【備忘】fastadmin 如何獲取列表選中行的pk

【備忘】fastadmin 如何獲取列表選中行的pk

去官方搜沒搜出來，還得是萬能的網友厲害。 //獲取選中項 $(document).on("click", ".btn-selected", function () {// 獲取選中項idsconsole.log(JSON.stringify(Table.api.selectedids(table)));// 獲取選中項所有數據console.log(JSON.strin…
閱讀更多...
輸入一個整數n，輸出n的約數為質數的數？兩個問題n的約數問題和n的質數問題

輸入一個整數n，輸出n的約數為質數的數？兩個問題n的約數問題和n的質數問題

輸入一個整數n，輸出n的約數為質數的數？ 一.首先解決n的質數的問題（1）枚舉法（2）埃氏篩 二.解決n的質數約數問題 一.首先解決n的質數的問題 （1）枚舉法 考慮質數的定義：在大…
閱讀更多...
conda中創建環境并安裝tensorflow1版本

conda中創建環境并安裝tensorflow1版本

conda中創建環境并安裝tensorflow1版本 一、背景二、命令三、驗證一下 一、背景 最近需要使用tensorflow1版本的，發個記錄！ 二、命令 conda create -n tf python3.6 #創建tensorflow虛擬環境 activate tf #激活環境，每次使用的時候都…
閱讀更多...
理解策略梯度方法：從REINFORCE到PPO

理解策略梯度方法：從REINFORCE到PPO

今年2月的時候，導師突然告訴我Ron William離世了。他算是我導師的 a life time friend，關系很好，我做畢業論文的時候，他還來參與了論文的答辯。Ron是一個很友善的老頭，和他在強化學習領域的影響力比起來，本…
閱讀更多...
汽車信息安全--數據安全：圖像脫敏

汽車信息安全--數據安全：圖像脫敏

General 隨著車聯網的發展，汽車越來越智能化，就像是一部“裝著四個輪子的手機”。 有人說，智能手機就如同一部竊聽器，無論你開機或者關機，它都會無時不刻地監聽著用戶的一舉一動。 可想而知，智能車輛上…
閱讀更多...
馬工程刑法期末復習筆記重點2

馬工程刑法期末復習筆記重點2

馬工程刑法期末復習筆記重點2
閱讀更多...
SpringBoot 參數校驗

SpringBoot 參數校驗

參數校驗 引入springvalidation依賴 <dependency><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-validation</artifactId> </dependency>參數前添加Pattern public Result registry(Pattern(regexp &qu…
閱讀更多...
Java面向對象練習(2.商品類)(2024.7.4)

Java面向對象練習(2.商品類)(2024.7.4)

商品類 package Supermarket20240704;public class Commodity {private String name;private double price;private int inventory;public Commodity(){};public Commodity(String name, double price, int inventory){this.name name;this.price price;this.inventory inv…
閱讀更多...
Java核心技術【十九】Iterator與增強for循環

Java核心技術【十九】Iterator與增強for循環

Java中的Iterator與增強for循環 在Java編程中&#xff0c;迭代是處理集合元素的一種常見操作。Java提供了多種迭代集合元素的方式&#xff0c;其中最常用的兩種是Iterator和增強for循環&#xff08;也稱為“for-each”循環&#xff09;。本文將深入探討這兩種迭代方式的特性和…
閱讀更多...
CLAM用于弱監督WSI分析

CLAM用于弱監督WSI分析

計算病理學&#xff08;computational pathology&#xff09;下的深度學習方法需要手動注釋大型 WSI 數據集&#xff0c;并且通常存在領域適應性和可解釋性較差的問題。作者報告了一種可解釋的弱監督深度學習方法&#xff0c;只需要WSI級標簽。將該方法命名為聚類約束注意力多實…
閱讀更多...
Perl 格式化輸出：提升代碼可讀性的技巧

Perl 格式化輸出：提升代碼可讀性的技巧

引言 Perl 是一種功能強大的腳本語言&#xff0c;廣泛用于文本處理、系統管理、網絡編程等多個領域。在 Perl 編程中&#xff0c;代碼的格式化輸出不僅有助于提升代碼的可讀性&#xff0c;還能增強程序的用戶體驗。本文將詳細介紹如何在 Perl 中實現代碼的格式化輸出。 Perl …
閱讀更多...
最新文章

Copyright @ 2022~2023