欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2023-12-26 09:07:45
...
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

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

上一篇:

下一篇: