问题描述

哪位大神告诉我这个sql怎么优化，有执行计划图

EXPLAIN SELECT *,

           CAST(prorder.deliveryfeedback as char) as deliveryfeedback,
           CAST(prorder.pocreatedate as char) as pocreatedate

    FROM (
    SELECT request.fnumber,
           request.purtaskid,
           rd.request_detail_id,
           rd.request_id,
           item.skuid,
           item.sku,
           item.purdesc,
           item.purspec,
           CONCAT(request.purtaskid,'',rd.skuid) as combineid,
           rd.stopkg,
           rd.purpkg,
           rd.reqpurqty,
           rd.requestqty,
           rd.poqty,
           rd.piqty,
           rd.sponroadqty,
           rd.revshipqty,
           rd.podate,
           rd.pidate,
           rd.shipdate,
           rd.revshipdate,
           rd.skulabel,
           rd.reqdeliverydate,
           rd.purpkgid,
           request.fstatus,
           request.urgency,
           date_format(request.checktime, '%Y-%m-%d %T') as checktime,
           sys_user.firstname,
           CONCAT(sys_user.firstname,'',sys_user.lastname) as username,
           Sysuser.email,
           date_format(request.create_date, '%Y-%m-%d') createdate

         FROM rs_request_detail rd
         inner join rs_request request on request.request_id = rd.request_id
         left join bs_item item on rd.skuid = item.skuid
         LEFT JOIN sys_user Sysuser ON Sysuser.userid = request.user_id
         left join sys_user sys_user on item.pmid = sys_user.userid

        WHERE (1 = 1) 

         and item.isvirtual=0
         and request.purtaskid>0
         and request.fstatus in(22,23,30)
         ORDER BY rd.request_id  DESC
            LIMIT 1,5000
        ) T

        LEFT JOIN (
            SELECT
                GROUP_CONCAT(distinct pod.delivery_feedback) deliveryfeedback,
                MAX(pod.delivery_feedback) deliveryfeedbackmax ,
                po.createdate AS pocreatedate,
                po.purtaskid,
                pod.skuid,
                pod.delivery_feedback_remark,
                'CNY' as curno,
                GROUP_CONCAT(distinct po.orderno) orderno,
                FORMAT(SUM((SELECT rate FROM exchange_rate WHERE money_type = po.curno )*pod.price/
                (SELECT rate FROM exchange_rate WHERE money_type = 'CNY')*pod.purqty)/
                SUM(pod.purqty),2)
                as amount
            FROM pr_order po
            INNER JOIN pr_order_detail pod ON po.prorderid = pod.prorderid and po.purtaskid > 0
            where po.purtaskid > 0
            GROUP BY po.purtaskid,pod.skuid ORDER BY NULL
        ) prorder on prorder.purtaskid=T.purtaskid and prorder.skuid=T.skuid 

        LEFT join (
            select
                A.purtaskid,
                A.skuid,
                SUM(A.quantity)  detectquantity,
                SUM(A.batchCheckNum)  batchCheckNum,
                date_format(A.transdate,'%Y-%m-%d %H:%i:%s') transdate,
                date_format(A.detectDate,'%Y-%m-%d %H:%i:%s') detectDate
             from (
                select
                prorder.prorderid,
                detect.detect_id,
                prorder.purtaskid,
                detect.skuid,
                detect.quantity,
                SUM(detectdetail.batchCheckNum) as batchCheckNum,
                detect.transdate,
                IFNULL(detectdetail.detectDate,DATE('9999-01-01')) as detectDate
            from pr_order prorder
            INNER join scm_detect detect on prorder.prorderid = detect.prorderid and prorder.purtaskid>0
            LEFT join scm_detect_detail detectdetail on detectdetail.detect_id =detect.detect_id
            GROUP BY prorder.purtaskid,detect.skuid,detect.detect_id
            ORDER BY NULL
        ) A
          GROUP BY A.purtaskid,A.skuid ORDER BY NULL
        ) detectd on detectd.purtaskid=T.purtaskid and detectd.skuid=T.skuid
          ORDER BY T.request_id DESC

解决方案

这个太复杂了，不建议写这么负责的语句。
建议拆成多条语句，能不连接就不连接。通过程序解决。
否则将来维护，非常困难，并且可扩展性不强。

解决方案二：

解决方案三：

隐式使用了临时表空间 排序造成