logo标签列表

GROUP BY查询优化

mysqlgroup-byquery-optimizationmyisam
7

数据库使用的是MySQL，引擎为MyISAM。

表定义如下：

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输出：

| 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秒。我想在创建索引之前就尝试过这个方法，但忘记在之后再次更改了。

在explain输出中，Using index解释了加速的原因：

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

（旁问：因子5的下降是否正常？）
大约有2000个用户，因此即使使用文件排序，最终排序也不会影响性能。我尝试过不使用ORDER BY，但时间几乎相同。
6
count(*) 比 count(id) 有更快的性能是因为 MySQL 对于 count(*) 这种情况进行了专门的优化。在 count(id) 情况下，会对数据进行第二次遍历以检索结果，而 count(*) 利用现有的内部行计数器。尽可能使用 count(*)。 - Thomas Jones-Low
6个回答
8

去掉 'game' 键 - 它与 '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
2
我已经尝试过使用该索引以及(user, game, finish)并强制使用它，但速度甚至更慢。 - ypercubeᵀᴹ
奇怪。我感觉你无法通过GROUP BY和ORDER BY的组合来做得更好：如果查询速度太慢，你可能需要创建一个明确的聚合表。出现Using filesort的事实表明ORDER BY无法从任何索引中完成：也许尝试将id添加到索引中？ - Femi
你是指一个 (游戏，完成，用户，ID) 索引吗？ - ypercubeᵀᴹ
好吧，我本来想说试试这个大小是否适合，但如果使用 COUNT(*) 有帮助的话，那么那个方法可能不会有太多好处。 - Femi
2
这个查询的缺点之一是你按聚合排序。这意味着在生成完整结果集之前，无法返回任何行；没有索引可以解决这个问题（至少对于mysql myisam来说）。
不过，你可以相对容易地对数据进行反规范化处理以克服这个问题；例如，你可以添加一个插入/更新触发器，将计数值放入汇总表中，并建立一个索引，以便可以立即开始返回行。
1

EXPLAIN 用于验证查询中是否使用了 (game, finish, user) 索引。在我看来，这似乎是最佳的索引选择。这可能是硬件问题吗？请问您的系统 RAM 和 CPU 是多少？

内存为1GB。CPU是（我认为）AMD Opteron四核3.5GHz。 - ypercubeᵀᴹ
我猜你的瓶颈是内存。我建议将其升级到4GB。 - ic3b3rg
处理包含 900k 行，每行大约 30 字节的表格需要 4GB 的内存？;) 这甚至不到 30MB ;) - matt
1
@lucek，你的数学没错，但现代操作系统的开销会占用大量的RAM。此外，其他正在运行的应用程序也会消耗RAM。4GB是现在相当标准的配置。 - ic3b3rg
@lucek 和 @ic3b3rg：记录一下，这个表还有其他字段。总大小约为80MB。但是该机器仅用作MySQL服务器。 - ypercubeᵀᴹ
1
@ypercube，也许有一个基于软件的建议可以加快您的速度。我认为您的表格、索引和SQL结构都很好，所以我怀疑在那里进行任何调整都不会有帮助。@Thomas Jones-Low关于服务器变量的建议可能会有所帮助。如果没有什么帮助，增加几个额外的GB内存是相当便宜的。 - ic3b3rg
1

我理解大部分时间都花在了从800k行数据中提取和更重要的排序（包括通过读取索引跳过的一次排序）15万行数据上。我怀疑你无法进一步优化它。

提取，是的。排序不行，它不花时间排序。 - ypercubeᵀᴹ
这并不是您的查询计划所建议的。实际上，您的查询也是如此。它们都表明至少需要一个排序。 :-) - Denis de Bernardy
我的意思是，与分组所花费的时间相比，排序所花费的时间非常短。 - ypercubeᵀᴹ
我也无法责怪它这样做...它根据你的查询计划将许多行（可能是表的一半？）分组成了150k行。 :-) - Denis de Bernardy
事实上，我99%确定你在试图优化它时浪费了时间：你当前的三列索引允许直接进入主题，例如获取相关行并按原样分组。然后需要对它们进行排序，这也需要时间。我非常诚实地看到你可以做的其他事情。如果有什么的话，我实际上很惊讶计划者决定使用索引，因为你正在检索表格的20％。 - Denis de Bernardy
1

正如其他人所指出的，您可能已经达到了调整查询本身的能力极限。接下来，您应该查看服务器中 max_heap_table_size 和 tmp_table_size 变量的设置。默认值为16MB，这可能对于您的表来说太小了。

谢谢建议，两个设置都是64M。 - ypercubeᵀᴹ
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接