GROUP BY 查询优化

发布于 2024-11-08 12:00:00 字数 1577 浏览 5 评论 0原文

数据库是带有MyISAM引擎的MySQL。

表定义：

CREATE TABLE IF NOT EXISTS  matches  (
   id  int(11) NOT NULL AUTO_INCREMENT,
   game  int(11) NOT NULL,
   user  int(11) NOT NULL,
   opponent  int(11) NOT NULL,
   tournament  int(11) NOT NULL,
   score  int(11) NOT NULL,
   finish  tinyint(4) NOT NULL,
  PRIMARY KEY ( id ),
  KEY  game  ( game ),
  KEY  user  ( user ),
  KEY  i_gfu ( game , finish , user )
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3149047 ;

我已在 (game, finish, user) 上设置了索引，但此 GROUP BY 查询仍需要 0.4 - 0.6 秒才能运行：

SELECT user AS player
     , COUNT( id ) AS times
FROM matches
WHERE finish = 1
  AND game = 19
GROUP BY user
ORDER BY times DESC

EXPLAIN< /code> 输出：

| id | select_type | table   | type | possible_keys | key   | key_len | 
|  1 |  SIMPLE     | matches |  ref | game,i_gfu    | i_gfu |    5    | 

|  ref        |   rows |   Extra                                      |
| const,const | 155855 | Using where; Using temporary; Using filesort |

有什么方法可以让它更快吗？该表大约有 800K 条记录。

编辑：我将 COUNT(id) 更改为 COUNT(*) ，时间下降到 0.08 - 0.12 秒。我想我在创建索引之前已经尝试过，但之后忘记再次更改它。

在解释输出中，使用索引解释了加速情况：（

|   rows |   Extra                                                   |
| 168029 | Using where; Using index; Using temporary; Using filesort |

附带问题：下降 5 倍是否正常？）

大约有 2000 个用户，所以最终的排序，即使它使用文件排序，这不会损害性能。我尝试不使用 ORDER BY ，但仍然需要几乎相同的时间。

原文

Database is MySQL with MyISAM engine.

Table definition:

CREATE TABLE IF NOT EXISTS  matches  (
   id  int(11) NOT NULL AUTO_INCREMENT,
   game  int(11) NOT NULL,
   user  int(11) NOT NULL,
   opponent  int(11) NOT NULL,
   tournament  int(11) NOT NULL,
   score  int(11) NOT NULL,
   finish  tinyint(4) NOT NULL,
  PRIMARY KEY ( id ),
  KEY  game  ( game ),
  KEY  user  ( user ),
  KEY  i_gfu ( game , finish , user )
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3149047 ;

I have set an index on (game, finish, user) but this GROUP BY query still needs 0.4 - 0.6 seconds to run:

SELECT user AS player
     , COUNT( id ) AS times
FROM matches
WHERE finish = 1
  AND game = 19
GROUP BY user
ORDER BY times DESC

The EXPLAIN output:

| id | select_type | table   | type | possible_keys | key   | key_len | 
|  1 |  SIMPLE     | matches |  ref | game,i_gfu    | i_gfu |    5    | 

|  ref        |   rows |   Extra                                      |
| const,const | 155855 | Using where; Using temporary; Using filesort |

Is there any way I can make it faster? The table has about 800K records.

EDIT: I changed COUNT(id) into COUNT(*) and the time dropped to 0.08 - 0.12 seconds. I think I've tried that before making the index and forgot to change it again after.

In the explain output the Using index explains the speeding up:

|   rows |   Extra                                                   |
| 168029 | Using where; Using index; Using temporary; Using filesort |

(Side question: is this dropping of a factor of 5 normal?)

There are about 2000 users, so the final sorting, even if it uses filesort, it doesn't hurt performance. I tried without ORDER BY and it still takes almost same time.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

毅然前行 2024-11-15 12:00:00

摆脱“游戏”键 - 它与“i_gfu”是多余的。由于 'id' 是唯一的 count(id) 仅返回每个组中的行数，因此您可以摆脱它并将其替换为 count(*)。尝试这种方式并粘贴 EXPLAIN 的输出：

SELECT user AS player, COUNT(*) AS times
FROM matches
WHERE finish = 1
AND game = 19
GROUP BY user
ORDER BY times DESC

Get rid of 'game' key - it's redundant with 'i_gfu'. As 'id' is unique count(id) just returns number of rows in each group, so you can get rid of that and replace it with count(*). Try it that way and paste output of EXPLAIN:

SELECT user AS player, COUNT(*) AS times
FROM matches
WHERE finish = 1
AND game = 19
GROUP BY user
ORDER BY times DESC

回复收藏 0 原文

攒眉千度 2024-11-15 12:00:00

呃，艰难。尝试重新排序索引：将 user 列放在前面（因此将索引设置为 (user, finish, game)），因为这样会增加 GROUP BY 使用索引的机会。但是，一般来说，如果您将使用的聚合函数限制为 MIN 和 MAX，GROUP BY 只能使用索引（请参见 http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html 和 http://dev.mysql.com/doc/refman/5.5 /en/loose-index-scan.html）。您的订购方式也没有真正帮助。