MySQL group by 优化

[test523]

目的取一段时间内 uid 对应消耗，然后划分区间

发现 group by uid返回的数据越多越慢，这个从 SQL 下手有优化空间吗

MySQL 5.7

select
  elt(interval(total, null, 300), '-INF~300', '300~INF') as section,
  count(*) AS total
from (
  select
    uid,
    SUM(gold) as total
  from `table_name`
  where `time` > 1640966400 and `time` <= 1642176000 group by `uid`
) as `tmp` group by `section`;

-- 执行了 3-4 秒

返回结果：
-INF~300	46319
300~INF		15060

EXPLAIN 结果:

select_type	table	type	possible_keys	rows	rows
PRIMARY	<derived2>	ALL		217073	Using temporary; Using filesort
DERIVED	table_name	index	time,uid	434146	Using where

附上表结构

CREATE TABLE `table_name` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `time` int(11) unsigned NOT NULL DEFAULT '0',
  `uid` bigint(20) unsigned NOT NULL DEFAULT '0',
  `gold` bigint(20) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `time` (`time`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

[enjoychen0318]

加个 order by null 试试？

[test523]

@enjoychen0318 作用不大，还是很慢

[xshell]

没走索引

[Gota]

猜测省掉一次 group 会不会好一点? 不过我没数据也不好试, 写出来大概是这样.

select
SUM(lt300) as lt300,
SUM(gt300) as gt300
from (
select
IF(SUM(gold) < 300, 1, 0) as lt300,
IF(SUM(gold) < 300, 0, 1) as gt300
from `table_name`
where `time` > 1640966400 and `time` <= 1642176000
group by `uid`
) as `tmp`

[blakejia]

select
uid,
SUM(gold) as total
from `table_name`
where `time` > 1640966400 and `time` <= 1642176000 group by `uid`

第一层耗时多少秒？

[sanggao]

time 加索引，并且 force use time 这个索引

[test523]

@blakejia 第一层 3 秒左右

@sanggao time

[test523]

@sanggao 刚才不小心点错了，

time 是有索引，FORCE INDEX(time)后耗时减少 1s

[galileo1214]

开窗？

[blakejia]

整表有多少数据量呢？

[23fksd]

联合索引+覆盖：CREATE INDEX idx_uid_time_gold ON table_name (uid,`time`,gold);

[Masonnn]

可以，就在这个帖子里学到了 elt()、interval()、force index()

[a222QAQ]

@sweetsorrow211 学习+1

[opengps]

我理解，time 和 uid 应该是个联合索引

[whoisix]

mark ，学习+1

[thinkmore]

尝试建立下 index(time,uid ，gold)联合索引,不知道是否可以提供数据供分析