百万级mysql数据库使用count、group、order查询非常慢，怎么优化

Question

数据库目前有100万数据（运行了半个月左右）

下面这句运行要6秒（单单where的结果有100万），user_id和time都加索引了

SELECT user_id,count(*) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BY user_id ORDER BY count desc

下面这句也是一样的，要6秒

SELECT user_id,count(*) as count from (
    SELECT user_id from prize_numbers where user_id > 0 and time > 1545972485
)t GROUP BY user_id ORDER BY count desc

该怎么优化 ？

这是表结构

Answer 1

找到问题所在了，又学到一个新知识。

SELECT user_id,count(*) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BY user_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 BY count desc

SELECT user_id,count(*) as count from prize_numbers where user_id > 0 GROUP BY user_id ORDER BY count desc

搜了好些资料，看到了一个叫聚合索引的东西，尝试了下，速度一下子就快了。语句如下：

alter table 表名 add index  索引名 (加索引的列1,加索引的列2) 

例如我的：

alter table prize_numbers add index union_user_id_time (user_id, time) 

聚合索引要加哪些字段，我粗暴的理解为把where语句后面的条件字段都加到一个聚合索引中。

Answer 2

SELECT user_id,count(id) as count from prize_numbers where user_id > 0 and time > 1545972485 GROUP BY user_id ORDER BY count desc