原sql为:
SELECT
SUM(money)
FROM
wallet
WHERE
user_id IN (SELECT DISTINCT
user_id
FROM
user_recharge
WHERE
group_id = '6789' AND is_del= 0)
AND type = 1
AND state IN (0 , 1)
AND createtime BETWEEN '2017-12-12 00:11:22' AND '2019-05-02 00:11:22';
其执行时间为:5.13s
修改后为:
select sum(bb.money) from
(SELECT DISTINCT
user_id
FROM
user_recharge
WHERE
group_id = '6789' AND is_del= 0) aa inner join wallet bb on aa.user_id= bb.user_id
AND type= 1
AND state IN (0 , 1)
AND createtime BETWEEN '2017-12-12 00:11:22' AND '2019-05-02 00:11:22';
其执行时间为:0.131s
原因:通过explain查看了之前的执行计划得知,是由于in语句中有子查询导致的,执行计划中看到了有用到全表扫描;故将SQL进行了修改,效率大大提升了许多!