减少表扫描的经典写法

电玩女神 2022-06-01 01:16 75阅读 0赞

优化前：  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE(), '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE(), '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 1 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 1 DAY, '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 2 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 2 DAY, '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 3 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 3 DAY, '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 4 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 4 DAY, '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 5 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 5 DAY, '%Y-%m-%d')  
UNION ALL  
SELECT IFNULL(SUM(charge\_quantity), 0) chargeQuantityTotle,  
       COUNT(1) num,  
       DATE\_FORMAT(SYSDATE() - INTERVAL 6 DAY, '%d') DAY  
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND DATE\_FORMAT(CHARGE\_end\_TIME, '%Y-%m-%d') =  
       DATE\_FORMAT(SYSDATE() - INTERVAL 6 DAY, '%Y-%m-%d');  
    id  select\_type   table                 type    possible\_keys  key     key\_len  ref       rows  Extra              
\------  ------------  --------------------  ------  -------------  ------  -------  ------  ------  -----------------  
     1  PRIMARY       gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     2  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     3  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     4  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     5  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     6  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
     7  UNION         gg\_BM\_O\_ORDER         ALL     (NULL)         (NULL)  (NULL)   (NULL)  387610  Using where        
(NULL)  UNION RESULT  <union1,2,3,4,5,6,7>  ALL     (NULL)         (NULL)  (NULL)   (NULL)  (NULL)  Using temporary     
  
  
优化后：  
CREATE INDEX ind\_gg\_BM\_O\_ORDER\_CHARGE\_end\_TIME ON gg\_BM\_O\_ORDER(CHARGE\_end\_TIME);   
 SELECT   
      IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=0 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle0,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=0 THEN  
       1   
      ELSE NULL END) num0,  
      IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=1 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle1,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=1 THEN  
       1   
      ELSE NULL END) num1,       
       IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=2 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle2,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=2 THEN  
       1   
      ELSE NULL END) num2,     
       IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=3 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle3,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=3 THEN  
       1   
      ELSE NULL END) num3,     
        IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=4 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle4,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=4 THEN  
       1   
      ELSE NULL END) num4,     
       IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=5 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle5,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=5 THEN  
       1   
      ELSE NULL END) num5,      
       IFNULL(SUM(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=6 THEN  
        IFNULL(charge\_quantity, 0)   
      ELSE 0 END),0) chargeQuantityTotle6,  
      COUNT(CASE WHEN DATEDIFF(CURDATE(),CHARGE\_end\_TIME)=6 THEN  
       1   
      ELSE NULL END) num6                          
  FROM gg\_BM\_O\_ORDER  
 WHERE IS\_DELETE <> 2  
   AND order\_state IN (3, 4)  
   AND CHARGE\_end\_TIME >=STR\_TO\_DATE(CURDATE()-6,'%Y%m%d');   
    id  select\_type  table          type    possible\_keys                      key                                key\_len  ref       rows  Extra                                 
\------  -----------  -------------  ------  ---------------------------------  ---------------------------------  -------  ------  ------  ------------------------------------  
     1  SIMPLE       gg\_BM\_O\_ORDER  range   ind\_gg\_BM\_O\_ORDER\_CHARGE\_end\_TIME  ind\_gg\_BM\_O\_ORDER\_CHARGE\_end\_TIME  6        (NULL)   28046  Using index condition; Using where