我在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 |
+----+-------------+-------+-------+---------------+--------------+---------+------+---------+-------------+
非常令人印象深刻!