MySQL group by 优化

test523 Jan 28, 2022 4304 views

This topic created in 1570 days ago, the information mentioned may be changed or developed.

目的取一段时间内 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

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`