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

我在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倍的时间.我并不感到惊讶,因为通过使用索引,查询可能不得不避免读取时间和单词列,但是不幸的是,索引是如此稀疏,因此它不会带来太多收益.相反,在检索计数时,它将直接扫描转换为随机访问模式.

我只是想确认这就是原因,并且想知道是否有一个“紧凑规则”规定何时将索引用于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 |

+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------------+

非常令人印象深刻！