MySQL group by 优化

2022-01-28 17:41:03 +08:00
 guangzhouwuyanzu

目的取一段时间内 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
3259 次点击
所在节点    MySQL
16 条回复
enjoychen0318
2022-01-28 18:02:33 +08:00
加个 order by null 试试?
guangzhouwuyanzu
2022-01-28 18:08:22 +08:00
@enjoychen0318 作用不大,还是很慢
xshell
2022-01-28 18:13:35 +08:00
没走索引
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`
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`

第一层耗时多少秒?
sanggao
2022-01-28 18:31:48 +08:00
time 加索引,并且 force use time 这个索引
guangzhouwuyanzu
2022-01-28 18:32:35 +08:00
@blakejia 第一层 3 秒左右

@sanggao time
guangzhouwuyanzu
2022-01-28 18:33:49 +08:00
@sanggao 刚才不小心点错了,

time 是有索引,FORCE INDEX(time)后耗时减少 1s
galileo1214
2022-01-28 18:51:11 +08:00
开窗?
blakejia
2022-01-28 18:59:14 +08:00
整表有多少数据量呢?
23fksd
2022-01-28 20:20:16 +08:00
联合索引+覆盖:CREATE INDEX idx_uid_time_gold ON table_name (uid,`time`,gold);
WhereverYouGo
2022-01-29 09:45:55 +08:00
可以,就在这个帖子里学到了 elt()、interval()、force index()
a222QAQ
2022-01-29 21:42:01 +08:00
@sweetsorrow211 学习+1
opengps
2022-01-30 20:43:51 +08:00
我理解,time 和 uid 应该是个联合索引
whoisix
2022-02-07 15:13:29 +08:00
mark ,学习+1
thinkmore
2022-02-08 17:34:27 +08:00
尝试建立下 index(time,uid ,gold)联合索引,不知道是否可以提供数据供分析

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/831164

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX