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