优化MySQL GROUP BY和JOIN查询的适当索引/优化

Question

我在这方面阅读和搜索了很多，但找不到令人满意的答案，所以我会感激任何帮助。大多数答案都接近我的情况，但并未解决它（尝试跟随解决方案对我没有好处）。

请参见下面的编辑＃2，以获得最佳示例

原始问题如下，但并不能很好地代表我所问的内容。

假设我有两个表，每个表有4列:

- key（int，自动增量） - c1（日期） - c2（长度为3的varchar） - c3（也是长度为3的varchar）

并且我想执行以下查询：

SELECT t.c1, t.c2, COUNT(*)
FROM test1 t
LEFT JOIN test2 t2 ON t2.key = t.key
GROUP BY t.c1, t.c2

两个key字段都作为主键进行索引。我想要得到每个c1、c2分组返回的行数。

当我解释这个查询时，会出现“using temporary; using filesort”的提示。实际上，我执行此查询的表格超过500,000行，这意味着这是一个耗时的查询。

所以我的问题是（假设在查询中没有做错任何事情）：是否有一种方法可以对这个表进行索引，以消除临时/文件排序的使用？

非常感谢您提供的任何帮助。

编辑

以下是表格定义（在此示例中，两个表格相同 - 实际上它们不同，但我不确定这在这一点上是否有区别）：

CREATE TABLE `test1` (
 `key` int(11) NOT NULL auto_increment,
 `c1` date NOT NULL,
 `c2` varchar(3) NOT NULL,
 `c3` varchar(3) NOT NULL,
 PRIMARY KEY  (`key`),
 UNIQUE KEY `c1` (`c1`,`c2`),
 UNIQUE KEY `c2_2` (`c2`,`c1`),
 KEY `c2` (`c2`,`c3`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

完整的EXPLAIN语句：

id   select_type  table  type    possible_keys  key      key_len  ref             rows   Extra
1    SIMPLE       t      ALL     NULL           NULL     NULL     NULL            2      Using temporary; Using filesort
1    SIMPLE       t2     eq_ref  PRIMARY        PRIMARY  4        tracking.t.key  1      Using index

这只是我的示例表格。在我的真实表格中，对于 t 的行，可能会显示 500,000+（尽管可能与其他事情有关）。

---

编辑#2

这里有一个更具体的例子，以更好地解释我的情况。

假设我有有关小联盟棒球比赛的数据。我有两个表格。一个包含比赛数据：

CREATE TABLE `ex_games` (
 `game_id` int(11) NOT NULL auto_increment,
 `home_team` int(11) NOT NULL,
 `date` date NOT NULL,
 PRIMARY KEY  (`game_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

另一个包含每场比赛中打击数据的数据集：

CREATE TABLE `ex_atbats` (
 `ab_id` int(11) NOT NULL auto_increment,
 `game` int(11) NOT NULL,
 `team` int(11) NOT NULL,
 `player` int(11) NOT NULL,
 `result` tinyint(1) NOT NULL,
 PRIMARY KEY  (`hit_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

我有两个问题。让我们从简单的开始：我想返回一个游戏列表，并计算每个游戏中的打数。所以我认为我会做这样的事情：

SELECT date, home_team, COUNT(h.ab_id) FROM `ex_atbats` h
LEFT JOIN ex_games g ON g.game_id = h.game
GROUP BY g.game_id

这个查询使用了文件排序/临时表。有没有更好的方法来构建它或者为表格建立索引以消除这种情况？

然后，更棘手的部分：假设我现在不仅想包括击球数的计数，还要包括由同一队伍的相同结果的上一个击球之前的击球数的计数。我认为这会是类似于：

SELECT g.date, g.home_team, COUNT(ab.ab_id), COUNT(ab2.ab_id) FROM `ex_atbats` ab
LEFT JOIN ex_games g ON g.game_id = ab.game
LEFT JOIN ex_atbats ab2 ON ab2.ab_id = ab.ab_id - 1 AND ab2.result = ab.result
GROUP BY g.game_id

这是构造查询的正确方法吗？这也使用了文件排序/临时表。

那么，完成这些任务的最佳方式是什么？

再次感谢。

- Ben

---

是的，没有运气 - 仍然给我相同的临时/文件排序。 - Ben

当前的编辑只会返回一个未经筛选的数字列表。也许你想使用 SELECT t.c1, t.c2, count(*) 代替？ - Doug Kress

explain命令在key和key_len列中显示了什么？请给我们展示完整的表定义。 - sanmai

好的，表定义和 EXPLAIN 现在已经在原帖中。 - Ben

在我的实际实现中，我确实从test2中取了1个字段，但大多数选定的字段都来自test1。 - Ben

Answer 1

“Using temporary/filesort” 的短语通常与在 JOIN 操作中使用的索引无关。有许多例子，即使您设置了所有索引（它们显示在 EXPLAIN 的 key 和 key_len 列中），但仍然会出现“Using temporary”和“Using filesort”。

请查看手册关于“Using temporary”和“Using filesort”的说明：

• MySQL 如何使用内部临时表
• ORDER BY 优化

在 GROUP BY 子句中使用所有列的组合索引可能有助于在某些情况下消除“Using filesort”。如果您还发出 ORDER BY，则可能需要添加更复杂的索引。

如果你有一个庞大的数据集，考虑使用某些标准（如日期或时间戳）进行分区，可以使用实际分区或简单的WHERE子句。

- sanmai

Answer 2

首先，表的定义很重要。使用两个主键进行连接是一回事，使用一侧的主键和另一侧的非唯一键进行连接是另一回事，等等。表使用的引擎类型也很重要，因为InnoDB引擎与MyISAM引擎对待主键的方式不同。

---

我注意到的是，在表test1上，(c1,c2)组合是唯一的，并且字段不可为空。这使得您的查询可以重写为：

SELECT t.c1, t.c2, COUNT(*)
FROM test1 t
LEFT JOIN test2 t2 ON t2.key = t.key
GROUP BY t.key

在使用相同字段进行JOIN和GROUP BY时，将会得到相同的结果。请注意，MySQL允许您在SELECT列表中使用未在GROUP BY列表中的字段，而无需对它们进行聚合函数。这在大多数其他系统中是不允许的，并被一些人视为错误。但在这种情况下，这是一个非常好的功能。每行可以通过(key)或(c1,c2)进行标识，因此使用其中之一进行分组不应该有影响。

---

另外要注意的一点是，在使用LEFT JOIN时，通常会使用右侧表的连接列进行计数：COUNT(t2.key)，而不是COUNT(*)。原始查询将在test1中的记录的该列中返回1，这些记录在test2中没有匹配的记录，因为它计算行数，而你可能希望计算test2中相关记录的数量，并在这些情况下显示0。

所以，请尝试这个查询并发布EXPLAIN结果。

SELECT t.c1, t.c2, COUNT(t2.key)
FROM test1 t
LEFT JOIN test2 t2 ON t2.key = t.key
GROUP BY t.key

- ypercubeᵀᴹ

---

谢谢，ypercube，这很有帮助。不幸的是，我搞砸了我的实际问题的抽象化，而我的实际实现并不包含这个独特的功能。因此我在上面添加了第二次编辑，以更好地澄清我的情况。那就是我最初应该问的问题。再次感谢您的帮助。 - Ben

Answer 3

索引有助于连接，但仍需要进行完整排序才能进行分组。基本上，它仍然必须处理集合中的每个记录。

当然，添加where子句并限制集合会运行得更快。只是它不能给你想要的结果。

可能有其他选项可以避免对整个表进行分组。我注意到您正在执行SELECT * - 您想从查询中获取什么？

例如，以下查询可能会运行得更快（我知道这只是一个示例查询，但是当您不知道最终目标时很难进行优化！）：

SELECT DISTINCT c1, c2 FROM test t LEFT JOIN test2 t2 ON t2.key = t.key

编辑 - 在阅读一些资料后（http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html），我了解到，在正确的情况下，索引可以在group by中大大帮助。

我看到的是它需要是已排序的索引（如BTREE），而不是HASH。也许：

CREATE INDEX c1c2 IN t (c1, c2) USING BTREE;

可能会有所帮助。

- Doug Kress

---

谢谢，Doug。实际使用要复杂得多，所以我试图将其简化为我认为的主要问题。不幸的是，我不认为DISTINCT会起作用，因为我想做的是（除其他事项外）计算每个c1和c2组合返回的行数。我将编辑原帖子，使其更清晰。 - Ben

回复您的编辑：我也读到了同样的内容，并且在两个表上都有(c1,c2)的BTREE索引。不幸的是，这并没有改变任何事情。 - Ben

Answer 4

对于InnoDB，它可以工作，因为索引默认携带您的主键。对于MyISAM，您必须将键作为索引的最后一列，并将其命名为“key”。这将使优化器按相同顺序获取所有键，并且可以跳过排序。您不能在索引前缀上执行任何范围查询，否则会回到文件排序。目前正在与类似问题进行斗争。

- anonymous