当前位置:Gxlcms > mysql > mysql-哪位大神告诉我这个sql怎么优化,有执行计划图

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

时间:2021-07-01 10:21:17 帮助过:19人阅读

mysqlselect优化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

![图片说明](http://img.ask.csdn.net/upload/201506/02/1433209976_499638.png)图片说明图片说明

人气教程排行