MySQL:优化 COUNT(*) 和 GROUP BY

Question

我有一个类似于以下内容的简单 MyISAM 表(为了便于阅读而进行了修剪——实际上，还有更多的列，所有列的宽度都是恒定的，其中一些可以为空):

CREATE TABLE IF NOT EXISTS `history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `time` int(11) NOT NULL,
  `event` int(11) NOT NULL,
  `source` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `event` (`event`),
  KEY `time` (`time`),
);

目前该表仅包含大约 6,000,000 行(其中目前大约有 160,000 行与下面的查询匹配)，但预计会增加。给定一个特定的事件 ID 并按来源分组，我想知道在特定的时间间隔内记录了多少具有该 ID 的事件。查询的答案可能类似于“今天，事件 X 对于源 A 发生了 120 次，对于源 B 发生了 105 次，对于源 C 发生了 900 次。”

我编造的查询确实执行了这个任务，但它执行得非常糟糕，当时间跨度设置为“所有时间”时执行时间超过一分钟，而在短短一周前超过 30 秒:

SELECT COUNT(*) AS count FROM history
WHERE event=2000 AND time >= 0 AND time < 1310563644
GROUP BY source
ORDER BY count DESC

这不是实时使用的，所以即使查询需要一两秒也可以，但几分钟就不行了。解释查询给出了以下内容，这很明显地困扰了我:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE          history ref     event,time      event   4           const   160399  Using where; Using temporary; Using filesort

我尝试过各种多列索引(例如(事件，时间))，但没有任何改进。这似乎是一个常见的用例，我无法想象没有合理的解决方案，但我的谷歌搜索都归结为我已经拥有的查询版本，没有关于如何避免临时(甚至那时)的具体建议，为什么性能如此糟糕)。

有什么建议吗？

Answer 1

你说你尝试过多列索引。您是否也尝试过单列索引，每列一个？

UPDATE:此外，如果分组列也有一个索引...当然，这取决于实际在该列中的 NULL 值的数量，这些值没有被索引。

对于event，MySQL 可以执行UNIQUE SCAN，这是相当快的，而对于time，RANGE SCAN 将被应用，这不是那么快......如果你分开索引，我希望性能比多列索引更好。

此外，也许您可​​以通过按一些预期值/值范围对表进行分区来获得一些东西:

http://dev.mysql.com/doc/refman/5.5/en/partitioning-overview.html