MySQL GROUP BY 和 JOIN 查询的正确索引/优化

Question

我已经对此进行了大量阅读和谷歌搜索，但找不到任何令人满意的答案，因此我将不胜感激。我找到的大多数答案都接近我的情况，但没有解决它（并且尝试遵循解决方案并没有给我带来任何好处）。

请参阅下面的编辑 #2 以获取最佳示例

---

[这是最初的问题，但并不能很好地表达我的要求。]

假设我有 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 每个分组中返回的行数。

当我解释这个查询时，我得到“使用临时；使用文件排序”。我执行此查询的实际表超过 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

此查询使用 filesort/temporary。有没有更好的方法来构建这个或索引表来摆脱它？

然后，更棘手的部分：假设我现在不仅要包括击球数的计数，还要包括同一支球队在相同结果的击球数之前的击球数。我认为这会是这样的：

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

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

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

再次感谢。

Answer 1

短语Usingtemporary/filesort通常与JOIN操作中使用的索引无关。有很多示例，您可以设置所有索引（它们显示在 EXPLAIN 中的 key 和 key_len 列中），但您仍然得到 使用临时和使用文件排序。

查看手册中关于使用临时和使用文件排序的内容：

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

在某些情况下，GROUP BY 子句中使用的所有列的组合索引可能有助于摆脱使用文件排序。如果您还发出 ORDER BY，您可能需要添加更复杂的索引。

如果您有一个巨大的数据集，请考虑使用一些标准（例如日期或时间戳）通过 实际分区或简单的WHERE子句。

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。

因此，尝试这个查询并发布说明：

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

Answer 3

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

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

除了对整个表进行分组之外，可能还有其他选择。我注意到你正在执行 SELECT * - 你想从查询中得到什么？

选择不同的 c1、c2
FROM 测试 t
LEFT JOIN test2 t2 ON t2.key = t.key

例如， 可能运行得更快。 （我意识到这只是一个示例查询，但要明白，当您不知道最终目标是什么时，很难优化！）

编辑 - 在进行一些阅读时（http://dev. mysql.com/doc/refman/5.0/en/group-by-optimization.html），我了解到，在正确的情况下，索引可以显着帮助分组依据。

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

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

可能有帮助。

Answer 4

对于 innodb 来说它可以工作，因为索引默认带有你的主键。对于 myisam，您必须拥有密钥，因为索引的最后一列是“key”。这将为优化器提供相同顺序的所有键，并且他可以跳过排序。您不能对索引前缀 theN 进行任何范围查询，这会让您立即回到文件排序。目前正在努力解决类似的问题