mysql sum count 性能优化 ----测试数据22万条
mysql sum count 性能优化 —-测试数据22万条
原语句：
select
sum(sale_price) as account_money,
count(distinct point_card_no) as use_count,
sum(add_point_value) as point_sum
from tb_rm_coupon_point
where
delete_flg=’0′ and add_point_datetime >=’2019/05/13′ and add_point_datetime < ‘2019/05/20’
执行时间：时间为24.9秒
结果：
注：tb_rm_coupon_point 此表有295288条数据
此语句扫描了24032条数据 得到了结果 and 用了索引扫描
改进查询方法：
语句：
SELECT
SUM( CASE WHEN add_point_datetime >=’2019/05/13′ and add_point_datetime < ‘2019/05/20’ THEN
sale_price ELSE 0 END) as ‘account_money’,
SUM( CASE WHEN add_point_datetime >=’2019/05/13′ and add_point_datetime < ‘2019/05/20’ THEN
add_point_value ELSE 0 END) as ‘point_sum’，
count( CASE WHEN add_point_datetime >=’2019/05/13′ and add_point_datetime < ‘2019/05/20’
THEN true ELSE null END) as ‘use_count’
FROM tb_rm_coupon_point where delete_flg=’0′
执行时间为：10秒左右 相比快了15秒