MySQL in max的优化

小鱼儿 2022-05-31 13:49 127阅读 0赞

MySQL in max的优化，循环的次数从1361\*124降到124\*1，性能大大提升。

SELECT 0 AS initalBalance,  
       0 AS receivabeBills,  
       0 AS receivabeFee,  
       0 AS paidUpBills,  
       0 AS paidUpFee,  
       SUM(IFNULL(gg.CLOSING\_BALANCE, 0)) AS closeBalance,  
       SUM(IFNULL(gg.SUM\_CHARGING\_POWER, 0)) AS sumPower,  
       SUM(IFNULL(gg.SUM\_PAID\_UP\_BILLS, 0)) AS sumPaidUpBills,  
       SUM(IFNULL(gg.SUM\_PAID\_UP\_FEE, 0)) AS sumPaidUpFee,  
       gg.IS\_PUBLISH AS isPublish  
  FROM gg\_report\_charging gg  
 WHERE gg.IS\_DELETE = -1  
   AND DATE\_FORMAT(gg.REPORT\_DATE, '%Y-%m-%d') IN  
       (SELECT MAX(gg.REPORT\_DATE) AS reportDate  
          FROM gg\_report\_charging gg  
         WHERE gg.IS\_DELETE = -1  
           AND gg.REPORT\_DATE >= CONCAT('2018-01-01', ' 00:00:00')  
           AND gg.REPORT\_DATE < DATE\_ADD(CONCAT('2018-01-01', ' 00:00:00'), INTERVAL 1 MONTH)  
           AND gg.OPERATING\_UNIT\_CODE = '0900')  
   AND gg.OPERATING\_UNIT\_CODE = '0900';  
     
    id  select\_type         table   type    key                       key\_len  ref       rows  Extra                       
\------  ------------------  ------  ------  ------------------------  -------  ------  ------  --------------------------  
     1  PRIMARY             gg      ALL     (NULL)                    (NULL)   (NULL)    1361  Using where                 
     2  DEPENDENT SUBQUERY  gg      range   ind\_gg\_REPORT\_CHARGING    108      (NULL)     124  Using where; Using ind    
       
  
  
EXPLAIN  
SELECT 0 AS initalBalance,  
       0 AS receivabeBills,  
       0 AS receivabeFee,  
       0 AS paidUpBills,  
       0 AS paidUpFee,  
       SUM(IFNULL(gg.CLOSING\_BALANCE, 0)) AS closeBalance,  
       SUM(IFNULL(gg.SUM\_CHARGING\_POWER, 0)) AS sumPower,  
       SUM(IFNULL(gg.SUM\_PAID\_UP\_BILLS, 0)) AS sumPaidUpBills,  
       SUM(IFNULL(gg.SUM\_PAID\_UP\_FEE, 0)) AS sumPaidUpFee,  
       gg.IS\_PUBLISH AS isPublish  
  FROM gg\_report\_charging gg  
 WHERE gg.IS\_DELETE = -1  
   AND gg.REPORT\_DATE =  
       (SELECT gg.REPORT\_DATE  
          FROM gg\_report\_charging gg  
         WHERE gg.IS\_DELETE = -1  
           AND gg.REPORT\_DATE >= CONCAT('2018-01-01', ' 00:00:00')  
           AND gg.REPORT\_DATE < DATE\_ADD(CONCAT('2018-01-01', ' 00:00:00'), INTERVAL 1 MONTH)  
           AND gg.OPERATING\_UNIT\_CODE = '0900'  
         ORDER BY REPORT\_DATE DESC LIMIT 1)  
   AND gg.OPERATING\_UNIT\_CODE = '0900';  
    id  select\_type  table   type   key                       key\_len  ref                  rows  Extra                                 
\------  -----------  ------  ------ ------------------------  -------  -----------------  ------  ------------------------------------  
     1  PRIMARY      gg      ref    ind\_gg\_REPORT\_CHARGING  108      const,const,const       1  Using ind condition; Using where    
     2  SUBQUERY     gg      range  ind\_gg\_REPORT\_CHARGING  108      (NULL)                124  Using where; Using ind