优化缓慢的MySQL计数查询和按天分组

Question

我正在跟踪一个网站的点击量，数据存储在以下MySQL MyISAM表中：

CREATE TABLE `track_hits` (
  `hit_id` int(10) unsigned NOT NULL auto_increment,
  `referer` varchar(255) default NULL,
  `referer_checksum` int(10) default NULL,
  `domain_checksum` int(10) default NULL,
  `referer_local` enum('Yes','No') default NULL,
  `request` varchar(255) default NULL,
  `request_checksum` int(10) default NULL,
  `embed_id` int(10) unsigned default NULL,
  `embed_user_id` int(10) unsigned default NULL,
  `embed_campaign_id` int(10) unsigned default NULL,
  `date` datetime default NULL,
  `day_checksum` int(10) default NULL,
  `visit_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`hit_id`),
  KEY `referer_checksum` (`referer_checksum`),
  KEY `date` (`date`),
  KEY `visit_id` (`visit_id`),
  KEY `embed_user_id` (`embed_user_id`),
  KEY `embed_campaign_id` (`embed_campaign_id`),
  KEY `day_checksum` (`day_checksum`),
  KEY `domain_checksum` (`domain_checksum`),
  KEY `embed_id` (`embed_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这个表格中有超过500万行数据。

我想要在某个日期范围内，按照每天、每个活动(embed_campaign_id)和不同的visit_id计算总点击数和总独立用户数。我用以下查询语句实现：

SELECT COUNT(DISTINCT h.`visit_id`) AS `visits`, COUNT(h.`hit_id`) AS `hits`, `date`
FROM (`track_hits` h)
WHERE `h`.`embed_campaign_id` = '31'
AND `h`.`date` >= '2012-10-07 07:00:00'
AND `h`.`date` <= '2012-11-07 07:59:59'
GROUP BY `h`.`day_checksum`

运行时间大约为15-25秒。

day_checksum是日期的crc32编码版本，例如"2012-11-07"。我已将GROUP BY替换为DATE(h.date)，但没有提高速度。

EXPLAIN返回：

id  select_type table   type    possible_keys           key                 key_len     ref     rows        extra
1   SIMPLE      h       ref     date,embed_campaign_id  embed_campaign_id   5           const   1648683     Using where; Using filesort

我考虑每天使用摘要表，但该网站是本地化的，数据库中的所有日期都是GMT时间。因此，从美国东部时间10/07 @ 7PM到11/07 @7PM与太平洋时间10/07 @ 7PM到11/07 @7PM需要返回不同的计数。

有没有什么方法可以加快这个过程？

- SammyK

Answer 1

你每列都有一个索引。我认为使用复合（多列）索引可以获得更好的性能。

http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

类似于这样:

KEY compositeIndex (embed_campaign_id, date, day_checksum, visit_id, hit_id)

- edze

---

非常好的观点。不过，我建议在where子句中使用(embed_campaign_id, date)。 - Olaf Dietsche

Answer 2

只是一些猜测：

• 进行anaylize table
• 将引擎更改为InnoDB
• date不在group by中，可以完全省略它，用day_checksum替换它，或者同时更改为date(date)
• 删除from (track_hits h)周围的括号
• 确保瓶颈不是硬件问题

最后，每天一次15-25秒的等待时间并不算长。

- Olaf Dietsche