慕田峪9158850
找到问题所在了,又学到一个新知识。SELECT user_id,count(*) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BYuser_id ORDER BY count desc如题,查询要6秒,user_id,time都设置了索引。多次尝试后发现以下2句查询速度非常快,在1秒以内。SELECT user_id,count(*) as count from prize_numbers where time > 1545972485 GROUP BY user_id ORDER BYcount descSELECT user_id,count(*) as count from prize_numbers where user_id > 0 GROUP BY user_id ORDER BY countdesc搜了好些资料,看到了一个叫聚合索引的东西,尝试了下,速度一下子就快了。语句如下:alter table 表名 add index 索引名 (加索引的列1,加索引的列2)例如我的:alter table prize_numbers add index union_user_id_time (user_id, time)聚合索引要加哪些字段,我粗暴的理解为把where语句后面的条件字段都加到一个聚合索引中。