mysql sum10万数据_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秒

原文出处：https://www.cnblogs.com/csjdaima/p/10875460.html