问题描述

关于这个sql应该如何去优化，谁可以指点一下方向和给出一些参考资料

SELECT *,
<!-- 若过滤条件含有 供应商反馈交期,后面的语句采用INNER JOIN链接过滤 -->
CAST(T.deliveryfeedback as char) as deliveryfeedback,
CAST(T.pocreatedate as char) as pocreatedate

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
,
prorder.deliveryfeedback,
prorder.deliveryfeedbackmax,
prorder.pocreatedate,
prorder.curno,
prorder.orderno,
prorder.amount

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

        <if test="filtersqlPo.length() != 0 or pod_deliveryfeedback.length() != 0"> <!--  若过滤条件含有 供应商反馈交期,采用INNER JOIN链接过滤  -->
        INNER JOIN (
            SELECT
                GROUP_CONCAT(distinct pod.delivery_feedback order by 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 order by 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
            <if test="filtersqlPo.length() != 0">
              ${filtersqlPo}
            </if>
            GROUP BY po.purtaskid,pod.skuid ORDER BY NULL
        ) prorder on prorder.purtaskid=request.purtaskid and prorder.skuid=item.skuid
             <if test="pod_deliveryfeedback.length() != 0">
                  ${pod_deliveryfeedback}
             </if>
         </if>
        WHERE (1 = 1)
        <if test="filtersql.length() != 0">
              ${filtersql}
        </if>
         and item.isvirtual=0
         and request.purtaskid>0
         and request.fstatus in(22,23,30)
         ORDER BY rd.request_id  DESC
            LIMIT ${offset},${limit}
        ) T
        <if test="filtersqlPo.length() == 0 and pod_deliveryfeedback.length() == 0">   <!--  若过滤条件不包含有 供应商反馈交期,采用LEFT JOIN链接过滤  -->
        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
        </if>
        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
 </select>

解决方案

通过查看执行计划查看你的q所耗费的资源在哪里就知道优化了,另外优化的一大原则就是 尽量使用索引
如何查看执行计划,你自己百度吧.

解决方案二：

通过查看执行计划查看你的q所耗费的资源在哪里就知道优化了,另外优化的一大原则就是 尽量使用索引
如何查看执行计划,你自己百度吧.

解决方案三：

http://bbs.csdn.net/topics/390677873