5

数据库目前有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

该怎么优化 ?

这是表结构
clipboard.png

2019-01-28 提问
2 个回答
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语句后面的条件字段都加到一个聚合索引中。

0
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

撰写答案

推广链接