MySQL group by 优化

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

Supplement 1 · Jan 28, 2022

附上表结构

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;

16 replies • 2022-02-08 17:34:27 +08:00

enjoychen0318

Jan 28, 2022

加个 order by null 试试？

test523

Jan 28, 2022

@enjoychen0318 作用不大，还是很慢

xshell

Jan 28, 2022

没走索引

Gota

Jan 28, 2022

猜测省掉一次 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`