SQL优化案例-减少对字段进行函数处理

select  GJXX.* , RYXX.*  from 

( select TLDP.*,CF.ZDMC CF_ZDMC,CF.SSSFDM CF_SSSFDM,CF.SSSFMC CF_SSSFMC,CF.SSDSDM CF_SSDSDM,CF.SSDSMC CF_SSDSMC,CF.SSQXDM CF_SSQXDM,CF.SSQXMC CF_SSQXMC ,DD.ZDMC DD_ZDMC,DD.SSSFDM DD_SSSFDM,DD.SSSFMC DD_SSSFMC,DD.SSDSDM DD_SSDSDM,DD.SSDSMC DD_SSDSMC,DD.SSQXDM DD_SSQXDM,DD.SSQXMC DD_SSQXMC 

    from 

 (select * from fk_rysc_tldp where date_format(THIS_RKSJ,'%Y-%m-%d %H:%i:%s') >='2018-03-03 09:16:52' ) TLDP   ----956538 hash:id_no

   LEFT JOIN

  (select * from fk_rysc_zd_jtzd where gjlx='GJ02') CF  on TLDP.FROM_STATION_NAME=CF.ZDDM  ---4364 REPLICATED

   LEFT JOIN  

  (select * from fk_rysc_zd_jtzd where gjlx='GJ02') DD  on TLDP.TO_STATION_NAME=DD.ZDDM 

) GJXX

 left join 

( select RY.ID RY_ID, ZJXX.ZJLX RY_ZJLX,ZJXX.ZJH RY_ZJH,RY.GJ RY_GJ,RY.XB RY_XB,RY.XBDM RY_XBDM,RY.MZ RY_MZ,RY.MZDM RY_MZDM,RY.CSRQ RY_CSRQ,RY.JGDM RY_JGDM,RY.JG RY_JG,RY.HJDDM RY_HJDDM,RY.HJD RY_HJD 

    from 

  (select JBXXID,ZJLX,ZJH from fk_rysc_zjxx where ZJLX='111') ZJXX   

left join fk_rysc_ryjbxx RY on ZJXX.JBXXID=RY.ID 

) RYXX  

ON GJXX.ID_NO=RYXX.RY_ZJH )t

order by date_format(GJXX.THIS_RKSJ,'%Y-%m-%d %H:%i:%s') ASC,GJXX.ID  ASC  limit 1,100 ;

select  GJXX.* , RYXX.*  from 

( select TLDP.*,CF.ZDMC CF_ZDMC,CF.SSSFDM CF_SSSFDM,CF.SSSFMC CF_SSSFMC,CF.SSDSDM CF_SSDSDM,CF.SSDSMC CF_SSDSMC,CF.SSQXDM CF_SSQXDM,CF.SSQXMC CF_SSQXMC ,DD.ZDMC DD_ZDMC,DD.SSSFDM DD_SSSFDM,DD.SSSFMC DD_SSSFMC,DD.SSDSDM DD_SSDSDM,DD.SSDSMC DD_SSDSMC,DD.SSQXDM DD_SSQXDM,DD.SSQXMC DD_SSQXMC 

    from 

  (select * from fk_rysc_tldp where THIS_RKSJ >='2018-03-03 09:16:52' ) TLDP  

   LEFT JOIN

  (select * from fk_rysc_zd_jtzd where gjlx='GJ02') CF  on TLDP.FROM_STATION_NAME=CF.ZDDM  

   LEFT JOIN  

  (select * from fk_rysc_zd_jtzd where gjlx='GJ02') DD  on TLDP.TO_STATION_NAME=DD.ZDDM 

) GJXX

left join 

( select RY.ID RY_ID, ZJXX.ZJLX RY_ZJLX,ZJXX.ZJH RY_ZJH,RY.GJ RY_GJ,RY.XB RY_XB,RY.XBDM RY_XBDM,RY.MZ RY_MZ,RY.MZDM RY_MZDM,RY.CSRQ RY_CSRQ,RY.JGDM RY_JGDM,RY.JG RY_JG,RY.HJDDM RY_HJDDM,RY.HJD RY_HJD 

    from 

  (select JBXXID,ZJLX,ZJH from fk_rysc_zjxx where ZJLX='111') ZJXX  

   left join fk_rysc_ryjbxx RY on ZJXX.JBXXID=RY.ID 

        where   RKSJ>='2018-03-03 09:16:52'

) RYXX  

ON GJXX.ID_NO=RYXX.RY_ZJH 

order by GJXX.THIS_RKSJ ASC,GJXX.ID  ASC LIMIT 1,100;

优化前

优化后

00:00:40.94

00:00:26.51