MySQL SQL一种常用的优化方法

ゝ一纸荒年。 2022-06-01 06:16 250阅读 0赞

下面的SQL 20多秒。如何优化：  
SELECT  ................  
 FROM gg\_bm\_o\_order o\_order  
 LEFT JOIN gg\_bm\_u\_app\_user app\_user ON app\_user.user\_code =  
                                        o\_order.user\_code  
 LEFT JOIN gg\_om\_dm\_site\_img m\_site\_img ON m\_site\_img.SITE\_CODE =  
                                           o\_order.SITE\_CODE  
                                       AND m\_site\_img.SORT\_NO = 1  
 LEFT JOIN gg\_om\_dm\_charging\_gun charging\_gun ON charging\_gun.CHARGING\_PILE\_CODE =  
                                                 o\_order.charging\_pile\_code  
                                             AND charging\_gun.CHARGING\_PILE\_INTER\_CODE =  
                                                 o\_order.charging\_pile\_inter\_code  
 LEFT JOIN gg\_om\_dm\_charging\_pile charging\_pile ON charging\_pile.CHARGING\_PILE\_CODE = o\_order.charging\_pile\_code  
 LEFT JOIN gg\_pay\_invoice\_order\_relation invoice\_order ON invoice\_order.ORDER\_CODE = o\_order.order\_code  
 LEFT JOIN gg\_bm\_o\_order\_suspend o\_suspend ON o\_suspend.order\_code = o\_order.order\_code  
WHERE 1 = 1   AND o\_order.IS\_DELETE <> 2   
and o\_order.order\_state = 4  
 AND (DATE\_FORMAT(o\_order.order\_end\_time,'%Y-%m-%d') <= '2018-01-21' AND DATE\_FORMAT(o\_order.order\_end\_time,'%Y-%m-%d') >= '2017-01-21')   
GROUP BY o\_order.ORDER\_CODE  
ORDER BY orderEndTime desc limit 0,20  
    id  select\_type  table          type    possible\_keys                                             key                              key\_len  ref                                                                      rows  Extra                                                 
\------  -----------  -------------  ------  --------------------------------------------------------  -------------------------------  -------  ---------------------------------------------------------------------  ------  ----------------------------------------------------  
     1  SIMPLE       o\_order        ALL     IDX\_ORDER\_CODE                                            (NULL)                           (NULL)   (NULL)                                                                 509537  Using where; Using temporary; Using filesort          
     1  SIMPLE       app\_user       ref     AK\_USER\_CODE                                              AK\_USER\_CODE                     99       evic.o\_order.USER\_CODE                                                      1  (NULL)                                                
     1  SIMPLE       m\_site\_img     ref     AK\_KEY\_SITE\_CODE\_SORT\_NO,FK\_IMG\_SITE\_CODE                 AK\_KEY\_SITE\_CODE\_SORT\_NO         104      evic.o\_order.SITE\_CODE,const                                                1  (NULL)                                                
     1  SIMPLE       charging\_gun   ref     AK\_KEY\_CHARGING\_PILE\_INTER\_CODE,Index\_DM\_PILE\_INTER\_CODE  AK\_KEY\_CHARGING\_PILE\_INTER\_CODE  108      evic.o\_order.CHARGING\_PILE\_CODE,evic.o\_order.CHARGING\_PILE\_INTER\_CODE       1  (NULL)                                                
     1  SIMPLE       charging\_pile  ref     IDX\_CHARGING\_PILE\_CODE                                    IDX\_CHARGING\_PILE\_CODE           99       evic.o\_order.CHARGING\_PILE\_CODE                                             1  (NULL)                                                
     1  SIMPLE       invoice\_order  ALL     IX\_INVOICEORDER\_ORDERCODE                                 (NULL)                           (NULL)   (NULL)                                                                      1  Using where; Using join buffer (Block Nested Loop)    
     1  SIMPLE       o\_suspend      ref     IX\_ORDERSUSPEND\_ORDERCODE                                 IX\_ORDERSUSPEND\_ORDERCODE        99       evic.o\_order.ORDER\_CODE                                                     1  Using where       
     
a.首先索引列进行了运算，用不到索引。  
b.现在主表中排序查出20条数据，因为这一过程可以用到索引，所以非常快。  
c.与其他表关联，可能会产生更多的数据，所以要group by一下，MySQL中的group by 居然是去重的，还要按照时间排序。  
 SQL中有两个limit，第一个limit是与页面的分页同步，第二个limit只是取多少数据。  
                                           
 SELECT ..............  
   FROM (SELECT .......  
           FROM gg\_bm\_o\_order o\_order  
          WHERE IS\_DELETE <> 2  
            AND order\_state = 4  
            AND order\_end\_time >=STR\_TO\_DATE(CONCAT('2017-01-21', ' 00:00:00'),'%Y-%m-%d %H:%i:%s')  
            AND order\_end\_time <=STR\_TO\_DATE(CONCAT('2018-01-21', ' 23:59:59'),'%Y-%m-%d %H:%i:%s')  
          ORDER BY order\_end\_time DESC LIMIT 0, 20) o\_order  
   LEFT JOIN gg\_bm\_u\_app\_user app\_user ON app\_user.user\_code = o\_order.user\_code  
   LEFT JOIN gg\_om\_dm\_site\_img m\_site\_img ON m\_site\_img.SITE\_CODE =o\_order.SITE\_CODE  
                                         AND m\_site\_img.SORT\_NO = 1  
   LEFT JOIN gg\_om\_dm\_charging\_gun charging\_gun ON charging\_gun.CHARGING\_PILE\_CODE =  
                                                   o\_order.charging\_pile\_code  
                                               AND charging\_gun.CHARGING\_PILE\_INTER\_CODE =  
                                                   o\_order.charging\_pile\_inter\_code  
   LEFT JOIN gg\_om\_dm\_charging\_pile charging\_pile ON charging\_pile.CHARGING\_PILE\_CODE =  
                                                     o\_order.charging\_pile\_code  
   LEFT JOIN gg\_pay\_invoice\_order\_relation invoice\_order ON invoice\_order.ORDER\_CODE =  
                                                            o\_order.order\_code  
   LEFT JOIN gg\_bm\_o\_order\_suspend o\_suspend ON o\_suspend.order\_code =  o\_order.order\_code  
   GROUP BY  o\_order.order\_code  
   ORDER BY o\_order.order\_end\_time  
   LIMIT 0,20;  
      id  select\_type  TABLE          TYPE    possible\_keys                                             KEY                              key\_len  ref                                                            ROWS  Extra                                                 
\------  -----------  -------------  ------  --------------------------------------------------------  -------------------------------  -------  -----------------------------------------------------------  ------  ----------------------------------------------------  
     1  PRIMARY      <derived2>     ALL     (NULL)                                                    (NULL)                           (NULL)   (NULL)                                                           20  USING TEMPORARY; USING filesort                       
     1  PRIMARY      app\_user       ref     AK\_USER\_CODE                                              AK\_USER\_CODE                     99       o\_order.user\_code                                                 1  (NULL)                                                
     1  PRIMARY      m\_site\_img     ref     AK\_KEY\_SITE\_CODE\_SORT\_NO,FK\_IMG\_SITE\_CODE                 AK\_KEY\_SITE\_CODE\_SORT\_NO         104      o\_order.site\_code,const                                           1  (NULL)                                                
     1  PRIMARY      charging\_gun   ref     AK\_KEY\_CHARGING\_PILE\_INTER\_CODE,Index\_DM\_PILE\_INTER\_CODE  AK\_KEY\_CHARGING\_PILE\_INTER\_CODE  108      o\_order.charging\_pile\_code,o\_order.charging\_pile\_inter\_code       1  (NULL)                                                
     1  PRIMARY      charging\_pile  ref     IDX\_CHARGING\_PILE\_CODE                                    IDX\_CHARGING\_PILE\_CODE           99       o\_order.charging\_pile\_code                                        1  (NULL)                                                
     1  PRIMARY      invoice\_order  ALL     IX\_INVOICEORDER\_ORDERCODE                                 (NULL)                           (NULL)   (NULL)                                                            1  USING WHERE; USING JOIN buffer (Block Nested LOOP)    
     1  PRIMARY      o\_suspend      ref     IX\_ORDERSUSPEND\_ORDERCODE                                 IX\_ORDERSUSPEND\_ORDERCODE        99       o\_order.order\_code                                                1  USING WHERE                                           
     2  DERIVED      o\_order        RANGE   IDX\_ORDER\_ORDER\_END\_TIME                                  IDX\_ORDER\_ORDER\_END\_TIME         6        (NULL)                                                       255038  USING INDEX CONDITION; USING WHERE