【干货实战】SQL太慢,教你调优三板斧-Select篇

不愿透露姓名的大鹏

已于 2024-07-12 11:28:59 修改

阅读量689

点赞数 21

分类专栏：数据库运维文章标签： sql 数据库运维 mysql oracle

于 2024-06-27 21:02:16 首次发布

本文链接：https://blog.csdn.net/qq_31766615/article/details/140022778

版权

运维同时被 2 个专栏收录

10 篇文章 2 订阅

订阅专栏

数据库

8 篇文章 0 订阅

订阅专栏

文章初衷

我们发现在系统上线初期，SQL运行都是正常的，但是到了业务稳定运行以后，在数据量达到一定程度，我们会发现越来越多的慢SQL出现，严重影响用户体验。

慢SQL调优---三板斧第一招【由内到外】

慢SQL调优---三板斧第二招【小表驱动大表】

原始SQL

调整顺序

慢SQL调优---三板斧第三招【条件前置】

原始SQL

优化后的语句

想知道本篇文章是否对您有帮助，让我有更新下一篇的动力。如果有帮助可以评论“有”，非常感谢各位观众老爷们！

慢SQL的定义

慢SQL，即执行时间较长的SQL查询语句，通常指的是在数据库管理系统中执行时间超过预设阈值的SQL语句。这个阈值可以由数据库管理员根据系统性能和业务需求来设定，常见的阈值可能是2秒、5秒或更长。当一个SQL查询的执行时间超过了这个阈值，它就会被标记为“慢查询”。

慢SQL的存在可能表明数据库性能存在瓶颈，如索引设计不合理、查询逻辑复杂、数据量过大、硬件资源不足等问题。它们会占用更多的CPU、I/O资源，影响数据库的响应速度，降低整体系统性能，甚至导致系统响应延迟或崩溃。

为了优化慢SQL，数据库管理员和开发人员通常需要对其进行详细的分析，包括查看执行计划、检查索引使用情况、优化查询语句结构、调整数据库参数等，以提高查询效率和系统性能。此外，定期审查慢查询日志也是发现和解决慢SQL问题的有效手段。

请耐心看完，保证你的优化思路会更加清晰。

⚠️注意：后两个案例更精彩！

最后附上《30个业务场景的SQL优化》、《老司机总结的12条 SQL 优化方案》

【实战】

慢SQL调优---三板斧第一招【由内到外】

话不多说，我们进入实战，以下是我们生产环境的一段查询SQL，它运行耗时在157.84秒，那么大家可以想一想，如果是你，看到这一段SQL会怎么入手呢？

原始SQL

SELECT CL.SCRQSTR,
        TEAM.TEAMCODE AS BMID,
        O.BATCHNO,
        O.STYLE_NO,
        O.PO_ID,
        SUM(CL.QTY)AS QTY,
        MX.MXBRS,
        MXBGS.WORK_SEC AS MXGS,
        O.ORDERTYPE,
        O.FACTORY_NAME,
        O.DIC_ORDER_NO,
        O.DIC_ROW_NO,
        O.ORDER_NO FROM
    (SELECT DISTINCT SCRQSTR,
        QTY,
        LSXNO,
        ORDER_NO,
        PROCESSID,
        BID
    FROM T_DG_CLXX
    WHERE CREATE_DATE>='2024-06-21 13:20:00'
            AND CREATE_DATE<'2024-06-21 13:30:00')CL
INNER JOIN T_TEAM_SUSPEND T1
    ON T1.LSXNO=CL.LSXNO
INNER JOIN T_BASIC_TEAM TEAM
    ON TEAM.ID=T1.ID
INNER JOIN T_PRODUCTION_ORDER O
    ON O.ORDER_NO=CL.ORDER_NO
INNER JOIN PROCESS_PATH PATH
    ON PATH.CID=CL.PROCESSID
        AND PATH.PO_ID=O.PO_ID
        AND PATH.WORKID=3 
INNER JOIN
    (SELECT COUNT(*)AS MXBRS,
        T.ORDER_NO FROM
        (SELECT DISTINCT USERID,
        ORDER_NO
        FROM T_DG_CLXX
        WHERE CREATE_DATE>='2024-06-21 13:20:00'
                AND CREATE_DATE<'2024-06-21 13:30:00'
                AND LSXNO NOT IN(1,16))T
        GROUP BY  T.ORDER_NO)MX
        ON MX.ORDER_NO=CL.ORDER_NO
INNER JOIN
    (SELECT SUM(CASE
        WHEN T.WORK_SEC>600 THEN
        600
        WHEN T.WORK_SEC<10 THEN
        150
        ELSE T.WORK_SEC END)AS WORK_SEC,ORDER_NO FROM
        (SELECT DISTINCT SCRQ,
        WORK_SEC,
        MODULE_ID,
        SEAT_CODE,
        PIECES,
        USERID,
        LSXNO,
        ORDER_NO
        FROM T_DG_CLXX
        WHERE CREATE_DATE>='2024-06-21 13:20:00'
                AND CREATE_DATE<'2024-06-21 13:30:00'
                AND LSXNO NOT IN(1,16))T
        GROUP BY  T.ORDER_NO)MXBGS
        ON MXBGS.ORDER_NO=CL.ORDER_NO
GROUP BY  CL.SCRQSTR,TEAM.TEAMCODE,O.BATCHNO,O.STYLE_NO,O.PO_ID,MX.MXBRS,MXBGS.WORK_SEC,O.ORDERTYPE,O.FACTORY_NAME,O.DIC_ORDER_NO,O.DIC_ROW_NO,O.ORDER_NO

你是不是也觉得很棘手？感觉有业务逻辑，关联了很多表，也有很多子查询。

首先我们可以先对SQL中的子查询进行执行，看是否存在慢SQL，从内到外去剖析。比如以上这段SQL可以单独执行子查询，查看所需耗时。

首先第一个子查询,但是我们不知道这个表有多少数据量，所以我们需要截取前面10条即可，mysql添加条件limit 10,如果是Oracle就是where rownum < 11，执行发现，SQL超过30秒都没有出结果。

SELECT DISTINCT SCRQSTR,
        QTY,
        LSXNO,
        ORDER_NO,
        PROCESSID,
        BID
    FROM T_DG_CLXX
    WHERE CREATE_DATE>='2024-06-21 13:20:00'
            AND CREATE_DATE<'2024-06-21 13:30:00' LIMIT 10;

因此可以判断这个SQL语句异常，可以通过EXPLAIN 来查看是否走索引了，再看一下T_DG_CLXX表有多少数据量。

通过EXPLAIN发现SQL没有走索引，说明CREATE_DATE字段没有建立索引。另外查询发现此表有3700万条数据量，也发现此表会每年进行数据归档，必须保留1年数据。

那么小伙伴知道接下来要做什么了吗？

--------------------------------------------------------------------------------------

没错，建立索引！

建立索引以后，查询为0.1秒左右。

然后将原先业务SQL重新执行，查询时间为0.135秒，效率提升1169倍

这个案例很简单，CREATE_DATE字段因为是后面业务需要，属于新增的字段。增加字段忘记增加索引，后面出现问题过一次，但是研发又忘记添加索引了，后面我们运维团队通过慢SQL预警发现了这条SQL异常。

慢SQL调优---三板斧第二招【小表驱动大表】

前面那个案例是不是很简单，下面这个看到调优结果也会觉得很简单，但是很经典。

给我一个支点，我能翘起整个🌍

咳咳，言归正传，下面这段SQL执行时间为91.826秒，大家看看有没有什么思路？

原始SQL

SELECT '2024-06' AS NYTIME,CASE
    WHEN LENGTH(CL.USERID)<4 THEN
    CONCAT('0',CL.USERID)ELSE CL.USERID
    END AS GH,CL.CID AS GXBH,CL.SL AS SJCL,W.USERNAME,W.BMID AS SSTEAM,TM.TEAMNAME AS SSTEAMNAME,PATH.GXMC,PATH.WID,PATH.GZZX,PATH.PRICE+IFNULL(UPR.YPRICE,0)AS GJ,CL.BMID AS TEAMCODE,TM1.TEAMNAME,CL.WORKID,CL.STYLE_NO,CRA.GZ,CL.BATCHNO,O.PO_ID,O.ORDER_NO,CL.GPH,CL.PDC_DATE,0 AS LJWJCL
FROM EMPLOYEE_OUTPUT_DAILY CL
LEFT JOIN T_ERAL_WORKER W
    ON W.USERID=CL.USERID
        OR CONCAT('0',CL.USERID)=W.USERID
LEFT JOIN T_BASIC_TEAM TM
    ON TM.TEAMCODE=W.BMID
        AND TM.STATUS=1
LEFT JOIN T_PRODUCTION_ORDER O
    ON O.ORDER_NO=CL.ORDER_NO LEFT JOIN
    (SELECT MAX(CID)AS CID,
        GPH,
        PO_ID
    FROM PROCESS_PATH
    WHERE PO_ID IN
        (SELECT DISTINCT PO_ID
        FROM T_PIECES_WAGE
        WHERE NYTIME='2024-06')AND XS=1
        GROUP BY  GPH,PO_ID)PA1
        ON PA1.PO_ID=O.PO_ID
        AND PA1.GPH=CL.GPH
LEFT JOIN PROCESS_PATH PATH
    ON PATH.CID=PA1.CID
LEFT JOIN T_BASIC_TEAM TM1
    ON TM1.TEAMCODE=CL.BMID
        AND TM.STATUS=1
LEFT JOIN T_WORKSHOP_CRAFT CRA
    ON CRA.WID=PATH.WID
LEFT JOIN T_UNUSUAL_PRICE UPR
    ON UPR.YID=PATH.YID
WHERE CL.PDC_DATE
    BETWEEN '2024-06-01'
        AND '2024-06-30'
        AND CL.GPH IS NOT NULL
        AND CL.BMID IS NOT NULL
        AND CL.WORKID IN(3,4,5)AND CL.TYPE!='终检'
        AND CL.STYLE_NO LIKE '%6024704085%'
        AND CL.BMID='3009020803'
ORDER BY  CL.PDC_DATE

那首先通过由内到外，将子查询进行执行，发现都是1秒以内可以出结果，那么可以排除子查询里面的问题了。然后里面没有什么复杂的函数，主要都是表关联语句，总共关联了8个表。其实从这里以及看出语句非常不健康了，从表的设计和业务逻辑的关系。

但是我们在不梳理业务逻辑的情况下，不去进行重新设计表结构，有什么办法实现SQL优化呢？

答案是有的，就像标题里面小表驱动大表。我们可以通过以下语句查询表的记录数。

其中可以看到主表有106.5万条数据，而关联的第一个表为2.9万数据量。因为后面6个表都是按照顺序关联，所以我把主表和第一个关联的表进行替换

调整顺序

从原先的91秒多到现在1.4秒，没有做索引，没有调整逻辑，只是调换了主表和第二个关联表的顺序就有大幅度提升。

虽然这个调优看着很浅显，但是这个例子很典型，可以让刚入门的同学记住🤔

慢SQL调优---三板斧第三招【条件前置】

SQL如下，没有子查询，总共4个表进行关联，查询耗时为62.604秒，请问怎么优化？

原始SQL

SELECT
	MIN(TLDATE) SCRQSX
FROM
	T_PRODUCTION_ORDER B
	INNER JOIN T_PRODUCT_DAY D ON B.BATCHNO = D.BATCHNO
	INNER JOIN T_PRODUCT_DAY_DETAIL DE ON DE.PID = D.PID
	INNER JOIN T_ORDER_TLS A ON A.PO_ID = DE.PO_ID
WHERE
	B.BATCHNO = 'Y2024YA-0029'
	AND D.BMID = '3009020807'
	AND D.WORKID = 3
GROUP BY
	D.BMID,
	B.BATCHNO

线索如下：

根据线索大家有什么思路？

-------------------------------------------------------------------------------------------------------------------------------

好的，没有子查询，那么是否可以通过小表驱动大表呢？

因为有逻辑顺序，比较好的方式就是T_ORDER_TLS为主表，进行关联，结果如下，没有明显变化。

从这里我们可以看出来，我们对4个表进行关联，数据量有几十上百万，但是我们看到后面有限制条件，那我们能不能先通过条件进行过滤呢？

从条件我们可以看出只有B表和D表，并且它们之间有关联，所以我们可以这样

优化后的语句

SELECT MIN(TLDATE) SCRQSX from
    (SELECT DISTINCT B.BATCHNO,
        BMID,
        PO_ID
    FROM T_PRODUCTION_ORDER B
    INNER JOIN T_PRODUCT_DAY D
        ON B.BATCHNO = D.BATCHNO
    WHERE B.BATCHNO = 'Y2024YA-0029'
            AND D.BMID = '3009020807'
            AND D.WORKID = 3) T
INNER JOIN T_PRODUCT_DAY_DETAIL DE
    ON T.PO_ID = DE.PO_ID
INNER JOIN T_ORDER_TLS A
    ON A.PO_ID = DE.PO_ID
GROUP BY  T.BMID, T.BATCHNO

先过滤条件，过滤后只有几千条，然后再进行结果集关联，查询结果仅需0.129秒，这个还和网络延时有关系。那么开发在写代码的时候，把条件的常量换成变量即可，语句结构不用进行变化。