MySQL 5.5 Community Server MySQL 5.6 Community Server Percona Configuration Wizard XtraBackup 搭建主从复制 Great Sites on MySQL Percona MySQL Performance Blog Severalnines 推荐管理工具 Sequel Pro phpMyAdmin 推荐书目 MySQL Cookbook
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html
MySQL 相关项目 MariaDB Drizzle 参考文档 http://mysql-python.sourceforge.net/MySQLdb.html 
这是一个创建于 1357 天前的主题，其中的信息可能已经有所发展或是发生改变。
目的取一段时间内 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 |
第 1 条附言 2022-01-28 18:39:45 +08:00
附上表结构
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 test523 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 这个索引 |
 | 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