sql优化

Question

select t.checkRequestId,t.customerName,t.identifyType,t.identifyNo,t.mobilePhone1,b.orgName,b.orgNo,c.enterDate,d.createDate,c.checkTimes,d.operateType 
from (select a.checkRequestId,a.enterDate,a.createDate,a.checkTimes from dvcicheckflow a where a.operateType = '04') c 
left join DvciCheckInfo t on c.checkRequestId = t.checkRequestId 
inner join DvciOrganization b on b.orgId = t.orgId 
left join (select createDate,operateType,checkTimes,checkRequestId from DvciCheckFlow where (operatetype = '03' or operatetype = '05')) d on d.checkRequestId = t.checkRequestId 
where d.checkTimes = c.checkTimes 
order by t.orgId,t.checkRequestId,t.customerName,d.createDate;
执行计划
1 PRIMARY <derived2> ALL                                                                                           666762 Using temporary; Using filesort
1 PRIMARY t                eq_ref PRIMARY,idx_dvcicheckinfo_orgid PRIMARY 8 c.checkRequestId 1 Using where
1 PRIMARY b                eq_ref PRIMARY PRIMARY                                         4 datavessel.t.orgid 1
1 PRIMARY <derived3> ref <auto_key0> <auto_key0>                                                13 c.checkTimes,c.checkRequestId 10
3 DERIVED e                 ALL                                                                                             666762 Using where
2 DERIVED a                 ALL                                                                                              666762 Using where

Answer 1

楼主，你至少也要格式化一下你的sql啊，方便大家帮你看SELECT T.CHECKREQUESTID,
       T.CUSTOMERNAME,
       T.IDENTIFYTYPE,
       T.IDENTIFYNO,
       T.MOBILEPHONE1,
       B.ORGNAME,
       B.ORGNO,
       C.ENTERDATE,
       D.CREATEDATE,
       C.CHECKTIMES,
       D.OPERATETYPE
  FROM (SELECT A.CHECKREQUESTID, A.ENTERDATE, A.CREATEDATE, A.CHECKTIMES
          FROM DVCICHECKFLOW A
         WHERE A.OPERATETYPE = '04') C
  LEFT JOIN DVCICHECKINFO T
    ON C.CHECKREQUESTID = T.CHECKREQUESTID
 INNER JOIN DVCIORGANIZATION B
    ON B.ORGID = T.ORGID
  LEFT JOIN (SELECT CREATEDATE, OPERATETYPE, CHECKTIMES, CHECKREQUESTID
               FROM DVCICHECKFLOW
              WHERE (OPERATETYPE = '03' OR OPERATETYPE = '05')) D
    ON D.CHECKREQUESTID = T.CHECKREQUESTID
 WHERE D.CHECKTIMES = C.CHECKTIMES
 ORDER BY T.ORGID, T.CHECKREQUESTID, T.CUSTOMERNAME, D.CREATEDATE;

Answer 2

(OPERATETYPE = '03' OR OPERATETYPE = '05')这句能否改成 （OPERATETYPE in ('03','05') )sql貌似如果用or 会慢。 同时看下你sql在数据库的执行计划 对应表是否有索引。 

Answer 3

一般 OR  可以改写成  UNION ALL