MySQL 相关项目
? MariaDB
? Drizzle
参考文档
? http://mysql-python.sourceforge.net/MySQLdb.html
目的取一段时间内 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)联合索引,不知道是否可以提供数据供分析
|
Select Language