mysql - `MySQL GROUP BY 使用索引时速度较慢

Question

我在 AWS m4.large(2 个 vCPU，8 GB 内存)上运行，我看到关于 MySQL 和 GROUPBY 的行为有点令人惊讶。我有这个测试数据库:

CREATE TABLE demo (
  time INT,
  word VARCHAR(30),
  count INT
);
CREATE INDEX timeword_idx ON demo(time, word);

我插入 4,000,000 条记录，其中包含(一致的)随机词 "t%s"% random.randint(0, 30000) 和时间 random.randint(0, 86400).

SELECT word, time, sum(count) FROM demo GROUP BY time, word;
3996922 rows in set (1 min 28.29 sec)

EXPLAIN SELECT word, time, sum(count) FROM demo GROUP BY time, word;
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows    | Extra |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------+
|  1 | SIMPLE      | demo  | index | NULL          | timeword_idx | 38      | NULL | 4002267 |       |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------+

然后我不使用索引:

SELECT word, time, sum(count) FROM demo IGNORE INDEX (timeword_idx) GROUP BY time, word;
3996922 rows in set (34.75 sec)

EXPLAIN SELECT word, time, sum(count) FROM demo IGNORE INDEX (timeword_idx) GROUP BY time, word;
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | SIMPLE      | demo  | ALL  | NULL          | NULL | NULL    | NULL | 4002267 | Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+---------+---------------------------------+

正如您所见，通过使用索引，查询花费的时间增加了 3 倍。我并不感到惊讶，因为通过使用索引，查询可能必须避免读取 time 和 word 列，但不幸的是，索引太稀疏了，它不应该获得很多。相反，当涉及到检索 count 时，它将直接扫描转变为随机访问模式。

我只是想确认这就是原因，并想知道是否存在关于何时和索引在用于 GROUP BY 时最终带来更差性能的“紧凑规则”。

编辑:

我遵循了 Gordon Linoff 的回答并使用了:

CREATE INDEX timeword_idx ON demo(time, word, count);

与全扫描相比，“覆盖索引”计算结果快 10 倍:

SELECT word, time, sum(count) FROM demo GROUP BY time, word;
3996922 rows in set (3.36 sec)

EXPLAIN SELECT word, time, sum(count) FROM demo GROUP BY time, word;
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key          | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------------+
|  1 | SIMPLE      | demo  | index | NULL          | timeword_idx | 43      | NULL | 4002267 | Using index |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------------+

非常令人印象深刻!

Answer 1

您有一个合理大小的表，因此问题可能是数据的顺序访问或抖动。使用索引需要遍历索引，然后在数据页中查找数据以获得count。

这实际上可能比只阅读页面并进行排序更糟糕，因为页面没有按顺序阅读。顺序读取比随机读取优化得多。在最坏的情况下，页面缓存已满，随机读取需要刷新页面。如果发生这种情况，可能需要多次读取单个页面。只有 400 万个相对较小的行，除非内存严重受限，否则不太可能出现抖动。

如果这个解释是正确的，那么在索引中包含 count 应该可以加快查询速度:

CREATE INDEX timeword_idx ON demo(time, word, count);