MySQL篇-SQL优化实战-减少子查询

回顾

上一篇了解了分析SQL使用的explain，可以点击查看MySQL篇-SQL优化实战了解我在写sql的注意事项还有explain的说明，这次拿一段生产使用的sql进行优化说明。从14s优化到2.6s

待优化的SQL

SELECT DISTINCT
	swpe.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'
            FROM
                cwms_delivery_attachment_all cda
            WHERE
                cda.pack_entity_id = swpe.pack_entity_id
            AND cda.stock_scan_status = 'Y'
            LIMIT 1
        ),
        'N'
    ) stock_status,
    ifnull(
        (
            SELECT
                cda.comments
            FROM
                cwms_delivery_attachment_all cda
            WHERE
                cda.pack_entity_id = swpe.pack_entity_id
            LIMIT 1
        ),
        NULL
    ) comments,
	 ifnull(
	    (
	        SELECT
	            swdh.delivery_number
	        FROM
	            sfy_wsh_delivery_lines_all swdl
	        INNER JOIN sfy_wsh_delivery_headers_all swdh ON swdl.delivery_header_id = swdh.delivery_header_id
	        WHERE
	            swpe.tag_number = swdl.tag_number
	        LIMIT 1
	    ),
	    NULL
	) delivery_number,
	 ifnull(
	    (
	        SELECT
	            filter1
	        FROM
	            eos_dict_entry
	        WHERE
	            DICTTYPEID = 'AUTH_CONFIG'
	        LIMIT 1
	    ),
	    'Y'
	) zc_power,
	 ppl.PICK_NUMBER pd_number,
	
	IF (
	    ifnull(
	        (
	            SELECT
	
	            IF (
	                substr(father.license_number, 1, 2) = 'TP',
	                father.license_number,
	                NULL
	            ) tp_number
	            FROM
	                wms_mtl_onhand_quantities_detail child
	            INNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_id
	            WHERE
	                child.license_number = swpe.tag_number
	            LIMIT 1
	        ),
	        1
	    ) != 1,
	    (
	        SELECT
	
	        IF (
	            substr(father.license_number, 1, 2) = 'TP',
	            father.license_number,
	            NULL
	        ) tp_number
	        FROM
	            wms_mtl_onhand_quantities_detail child
	        INNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_id
	        WHERE
	            child.license_number = swpe.tag_number
	        LIMIT 1
	    ),
	    (
	        SELECT
	
	        IF (
	            substr(
	                oldfather.license_number,
	                1,
	                2
	            ) = 'TP',
	            oldfather.license_number,
	            NULL
	        ) tp_number
	        FROM
	            wms_mtl_onhand_quantities_detail child
	        INNER JOIN wms_mtl_onhand_quantities_detail father ON child.parent_mq_id = father.mq_id
	        INNER JOIN wms_mtl_onhand_quantities_detail oldfather ON father.parent_mq_id = oldfather.mq_id
	        WHERE
	            child.license_number = swpe.tag_number
	        LIMIT 1
	    )
	) tp_number,
	
	IF (
	    (
	        SELECT
	            DOWNRACKSTYPE
	        FROM
	            wms_mtl_onhand_quantities_detail
	        WHERE
	            license_number = swpe.tag_number
	        LIMIT 1
	    ) = 3,
	    'Y',
	    'N'
	) type,
	 (
	    SELECT
	        TC_NUMBER
	    FROM
	        cwms_mobile_tray_lines cmtl
	    WHERE
	        cmtl.TAG_NUMBER = swpe.tag_number
	    ORDER BY
	        CREATION_DATE DESC
	    LIMIT 1
	) tc_number,
	
	IF (
	    (
	        SELECT
	            DOWNRACKSTYPE
	        FROM
	            wms_mtl_onhand_quantities_detail
	        WHERE
	            license_number = swpe.tag_number
	        LIMIT 1
	    ) = 3,
	    1,
	    0
	) is_scan
FROM
    cwms_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 (
    SELECT
        DELIVERY_HEADER_ID,
        SCHEDULE_STAGE,
        group_concat(CAR_NUMBER) CAR_NUMBER,
        group_concat(DISTINCT LOGISTIC_PROVIDER) LOGISTIC_PROVIDER
    FROM
        wms_delivery_car_detail
    GROUP BY
        DELIVERY_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
WHERE
    swpe.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 BY
    milk.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 DISTINCT
    swpe.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(
        (
            SELECT
                filter1
            FROM
                eos_dict_entry
            WHERE
                DICTTYPEID = '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
FROM
    cwms_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 (
    SELECT
        DELIVERY_HEADER_ID,
        SCHEDULE_STAGE,
        group_concat(CAR_NUMBER) CAR_NUMBER,
        group_concat(DISTINCT LOGISTIC_PROVIDER) LOGISTIC_PROVIDER
    FROM
        wms_delivery_car_detail
    GROUP BY
        DELIVERY_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
-- 主要改动在这里：从子查询迁移到下面左关联
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
WHERE
    swpe.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 BY
    milk.CONCATENATED_SEGMENTS

总结

  1. 当结果集字段中有好几个相同表的子查询时，将子查询修改为连接查询的效率提升会比较大（相当于一行记录处理一次查询）
  2. 有时候执行计划可能无法直接看出修改哪里能提升，但能给我们提供优化的思路
  3. 在执行计划中看到每个表都走索引了，但是却还是很慢，那我们可以一段一段的、一表一表的排除，找到问题点在哪，而多快能找到主要就取决于经验还有对表的熟悉程度了。

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：http://www.mfbz.cn/a/769871.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

ELFK简介

ELFK简介

👨‍🎓博主简介 🏅CSDN博客专家   🏅云计算领域优质创作者   🏅华为云开发者社区专家博主   🏅阿里云开发者社区专家博主 💊交流社区：运维交流社区 欢迎大家的加入&#xff01…
阅读更多...
K8S学习教程(二):在 PetaExpress KubeSphere容器平台部署高可用 Redis 集群

K8S学习教程(二):在 PetaExpress KubeSphere容器平台部署高可用 Redis 集群

前言 Redis 是在开发过程中经常用到的缓存中间件，为了考虑在生产环境中稳定性和高可用，Redis通常采用集群模式的部署方式。 在制定Redis集群的部署策略时，常规部署在虚拟机上的方式配置繁琐并且需要手动重启节点，相较之下&#…
阅读更多...
java基础：方法

java基础：方法

一、方法 1、Java方法是语句的集合，它们在一起执行一个功能。 方法是解决一类问题的步骤的有序集合方法包含于类或对象中方法在程序中被创建，在其他地方被引用 2、设计方法的原则：方法的本意是功能块，就是实现某个功能的语句块…
阅读更多...
layui+jsp项目中实现table单元格嵌入下拉选择框功能，下拉选择框可手动输入内容或选择默认值，修改后数据正常回显。

layui+jsp项目中实现table单元格嵌入下拉选择框功能，下拉选择框可手动输入内容或选择默认值，修改后数据正常回显。

需求 table列表中的数据实现下拉框修改数据，当默认的下拉框不符合要求时，可手动输入内容保存。内容修改后表格显示修改后的值同时表格不刷新。 实现 layui框架下拉框组件只能选择存在的数据，不支持将输入的内容显示在input中的功能&#x…
阅读更多...
什么牌子的无线领夹麦克风好，一篇了解哪种领夹麦性价比高

什么牌子的无线领夹麦克风好，一篇了解哪种领夹麦性价比高

随着5G技术的广泛应用，短视频平台迎来了前所未有的发展机遇，几乎每个地方都有人在记录生活，分享故事。在这样的背景下，户外直播和视频创作的需求急剧增长，然而，户外的复杂声场仅靠普通手机的录音功能实在难…
阅读更多...
计算机网络之局域网

计算机网络之局域网

目录 1.局域网的基本概念 2.LAN的特性 3.局域网特点 4.拓扑结构 5.传输媒体的选择 6.传输媒体 7.传输技术 8.传输技术距离问题 9.LAN的逻辑结构 10.局域网工作原理 上篇文章内容：OSI七层体系结构 1.局域网的基本概念 局域网 是将分散在有限地 理范围内&…
阅读更多...
Robust Test-Time Adaptation in Dynamic Scenarios--论文阅读

Robust Test-Time Adaptation in Dynamic Scenarios--论文阅读

论文笔记 资料 1.代码地址 https://github.com/BIT-DA/RoTTA 2.论文地址 https://arxiv.org/abs/2303.13899 3.数据集地址 coming soon 1论文摘要的翻译 测试时间自适应(TTA)旨在使预先7训练的模型适用于仅具有未标记测试数据流的测试分布。大多数以前的TTA方法已经在…
阅读更多...
SQL Server特性

SQL Server特性

一、创建表 在sql server中使用create table来创建新表。 create table Customers( id int primary key identity(1,1), name varchar(5) ) 该表名为Customers其中包含了2个字段，分别为id（主键）以及name。 1、数据类型 整数类型&#xff…
阅读更多...
NAT地址转换实验，实验超简单

NAT地址转换实验，实验超简单

实验拓扑 实验目的 将内网区域&#xff08;灰色区域&#xff09;的地址转换为172.16.1.0 实验过程 配置静态NAT&#xff08;基于接口的静态NAT&#xff09; R1配置 <Huawei>sys Enter system view, return user view with CtrlZ. [Huawei]sysname R1 [R1]un in en I…
阅读更多...
探索 Apache Paimon 在阿里智能引擎的应用场景

探索 Apache Paimon 在阿里智能引擎的应用场景

摘要&#xff1a;本文整理自Apache Yarn && Flink Contributor&#xff0c;阿里巴巴智能引擎事业部技术专家王伟骏&#xff08;鸿历&#xff09;老师在 5月16日 Streaming Lakehouse Meetup Online 上的分享。内容主要分为以下三个部分&#xff1a; 一、 阿里智能引擎…
阅读更多...
流程表单设计器开源优势多 助力实现流程化！

流程表单设计器开源优势多 助力实现流程化！

实现流程化办公是很多职场企业的发展目标。应用什么样的软件可以实现这一目的&#xff1f;低代码技术平台、流程表单设计器开源的优势特点多&#xff0c;在推动企业降本增效、流程化办公的过程中作用明显&#xff0c;是理想的软件平台。那么&#xff0c;流程表单设计器开源的优…
阅读更多...
VS开发QT程序图标修改

VS开发QT程序图标修改

VS开发QT程序图标修改 1.双击打开UI界面 2.选择编辑资源 3.添加文件 4.选择ico文件 5.ok确定 6.点击保存 7.选择windowsIcon,倒三角图标 8.选择资源 9.选择图标&#xff0c;点击ok 10.保存 编译运行&#xff1a; 任务栏&#xff1a; 或者代码设置: 添加图标后&#xff0c;打…
阅读更多...
Qt中文乱码如何解决

Qt中文乱码如何解决

目录 一、使用建议 二、其它设置 一、使用建议 Qt对中文的支持不是很友好&#xff0c;使用QtCreator会出现各种乱七八糟的中文代码问题&#xff0c;如何处理这种问题&#xff1f; &#xff08;1&#xff09;粘贴别人的代码时&#xff0c;先在记事本里粘贴一遍&#xff0c;再…
阅读更多...
应用于空气和液体抑菌的静态UVC LED抑菌模组-WH-UVC001-VO

应用于空气和液体抑菌的静态UVC LED抑菌模组-WH-UVC001-VO

WH-UVC001-VO是一款用于空气和液体抑菌的静态UVC LED抑菌模组。适用于带水箱、密闭的腔体结构。可安装于顶部、侧壁及底部&#xff0c;出光面符合IP65的防水要求&#xff0c;即使安装于水箱底部也不用担心漏水。 使用的UVC LED的波长范围为260-280nm&#xff0c;具有优良高效的…
阅读更多...
线上网络课堂知识付费小程序源码系统 带的安装代码包以及搭建部署教程

线上网络课堂知识付费小程序源码系统 带的安装代码包以及搭建部署教程

系统概述 本系统是一款专为线上教育设计的全栈解决方案&#xff0c;集课程管理、用户管理、支付系统、互动交流于一体&#xff0c;旨在帮助内容创作者轻松搭建知识付费平台&#xff0c;实现内容变现。系统基于成熟的技术栈&#xff08;如Node.js、Vue.js等&#xff09;开发&am…
阅读更多...
奥比中光astra_pro相机使用记录

奥比中光astra_pro相机使用记录

一、信息获取 1、官网 用于了解产品信息 http://www.orbbec.com.cn/sys/37.html 2、开发者社区 咨询问题下载开发部https://developer.orbbec.com.cn/ 二 、windowvs19 1、相机型号 orbbec_astro_pro 根据对应的型号找到需要的包工具 踩坑1&#xff0c;因为这个相机型号…
阅读更多...
OpenSSH远程代码执行漏洞风险通告

OpenSSH远程代码执行漏洞风险通告

今日&#xff0c;亚信安全CERT监控到安全社区研究人员发布安全通告&#xff0c;披露了OpenSSH远程代码执行漏洞(CVE-2024-6387)。该漏洞发生在OpenSSH < 4.4p1 且未安装CVE-2006-5051/CVE-2008-4109补丁或8.5p1< OpenSSH < 9.8p1上。 目前厂商官方已针对相关漏洞进行…
阅读更多...
【HarmonyOS4学习笔记】《HarmonyOS4+NEXT星河版入门到企业级实战教程》课程学习笔记（二十）

【HarmonyOS4学习笔记】《HarmonyOS4+NEXT星河版入门到企业级实战教程》课程学习笔记（二十）

课程地址&#xff1a; 黑马程序员HarmonyOS4NEXT星河版入门到企业级实战教程&#xff0c;一套精通鸿蒙应用开发 &#xff08;本篇笔记对应课程第 30 节&#xff09; P30《29.数据持久化-用户首选项》 实现数据持久化在harmonyOS中有很多种方式&#xff0c;比较常见的是以下两…
阅读更多...
大模型剪枝概述

大模型剪枝概述

近年来&#xff0c;随着Transformer、MOE架构的提出&#xff0c;使得深度学习模型轻松突破上万亿规模参数&#xff0c;从而导致模型变得越来越大&#xff0c;因此&#xff0c;我们需要一些大模型压缩技术来降低模型部署的成本&#xff0c;并提升模型的推理性能。而大模型压缩主…
阅读更多...
游戏推荐: 植物大战僵尸杂交版

游戏推荐: 植物大战僵尸杂交版

下载地址网上一搜就有. 安装就能玩. 2是显血. 4显示植物血, 5是加速. 都是左手主键盘的按钮, 再按是取消. 比较刺激: ps: 设置里面还能打开自动收集阳光和金币.
阅读更多...
最新文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发 尧图建网站