目的取一段时间内 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;
1
enjoychen0318 2022-01-28 18:02:33 +08:00
加个 order by null 试试?
|
2
guangzhouwuyanzu OP @enjoychen0318 作用不大,还是很慢
|
3
xshell 2022-01-28 18:13:35 +08:00
没走索引
|
4
Gota 2022-01-28 18:27:50 +08:00
猜测省掉一次 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` |
5
blakejia 2022-01-28 18:30:31 +08:00
select
uid, SUM(gold) as total from `table_name` where `time` > 1640966400 and `time` <= 1642176000 group by `uid` 第一层耗时多少秒? |
6
sanggao 2022-01-28 18:31:48 +08:00
time 加索引,并且 force use time 这个索引
|
7
guangzhouwuyanzu OP |
8
guangzhouwuyanzu OP |
9
galileo1214 2022-01-28 18:51:11 +08:00
开窗?
|
10
blakejia 2022-01-28 18:59:14 +08:00
整表有多少数据量呢?
|
11
23fksd 2022-01-28 20:20:16 +08:00
联合索引+覆盖:CREATE INDEX idx_uid_time_gold ON table_name (uid,`time`,gold);
|
12
WhereverYouGo 2022-01-29 09:45:55 +08:00
可以,就在这个帖子里学到了 elt()、interval()、force index()
|
13
a222QAQ 2022-01-29 21:42:01 +08:00 via Android
@sweetsorrow211 学习+1
|
14
opengps 2022-01-30 20:43:51 +08:00
我理解,time 和 uid 应该是个联合索引
|
15
whoisix 2022-02-07 15:13:29 +08:00
mark ,学习+1
|
16
thinkmore 2022-02-08 17:34:27 +08:00
尝试建立下 index(time,uid ,gold)联合索引,不知道是否可以提供数据供分析
|