Select SUM(fen) sum from jifen where type='qiandao' and DATE_FORMAT( nl_time, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) and nl_uid in (select uid from user where user_type='2') ## 数据太大查询速度太慢,3分钟都出不来,建立的索引无用!该怎么优化下
Select SUM(fen) sum from jifen inner join user ur on nl_uid = ur.uid and ur.user_type = '2' where type = 'qiandao' and nl_time between DATE_ADD(curdate(),interval -day(curdate())+1 day) and date_add(curdate()-day(curdate())+1,interval 1 month); -- and DATE_FORMAT(nl_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') -- and nl_uid in (select uid from user where user_type = '2')
Select SUM(fen) sum
from jifen inner join user ur on nl_uid = ur.uid and ur.user_type = '2'
where type = 'qiandao'
and nl_time between DATE_ADD(curdate(),interval -day(curdate())+1 day) and date_add(curdate()-day(curdate())+1,interval 1 month);
-- and DATE_FORMAT(nl_time, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m')
-- and nl_uid in (select uid from user where user_type = '2')
性能问题2:in子查询,修改为exists或用join代替。