sql - MySQL 左外连接很慢

Question

希望得到有关此查询的一些帮助，我已经处理了一段时间，但无法更快地获得它:

SELECT date, count(id) as 'visits' FROM dates 
LEFT OUTER JOIN visits 
ON (dates.date = DATE(visits.start) and account_id = 40 ) 
WHERE date >= '2010-12-13' AND date <= '2011-1-13' 
GROUP BY date ORDER BY date ASC

该查询大约需要 8 秒才能运行。我在 dates.date、visits.start、visits.account_id 和 visits.start+visits.account_id 上添加了索引，但无法让它运行得更快。

表结构(仅显示访问表中的相关列):

create table visits (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `account_id` int(11) NOT NULL,
    `start` DATETIME NOT NULL,
    `end` DATETIME NULL,
    PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `dates` (
  `date` date NOT NULL,
  PRIMARY KEY (`date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

日期表包含从 2010-1-1 到 2020-1-1 的所有日期(约 3k 行)。访问表包含从 2010 年 6 月 1 日到昨天的大约 40 万行。我正在使用日期表，因此在没有访问的日子里，连接将返回 0 次访问。

我想引用的结果:

+------------+--------+
| date       | visits |
+------------+--------+
| 2010-12-13 |    301 |
| 2010-12-14 |    356 |
| 2010-12-15 |    423 |
| 2010-12-16 |    332 |
| 2010-12-17 |    346 |
| 2010-12-18 |    226 |
| 2010-12-19 |    213 |
| 2010-12-20 |    311 |
| 2010-12-21 |    273 |
| 2010-12-22 |    286 |
| 2010-12-23 |    241 |
| 2010-12-24 |    149 |
| 2010-12-25 |    102 |
| 2010-12-26 |    174 |
| 2010-12-27 |    258 |
| 2010-12-28 |    348 |
| 2010-12-29 |    392 |
| 2010-12-30 |    395 |
| 2010-12-31 |    278 |
| 2011-01-01 |    241 |
| 2011-01-02 |    295 |
| 2011-01-03 |    369 |
| 2011-01-04 |    438 |
| 2011-01-05 |    393 |
| 2011-01-06 |    368 |
| 2011-01-07 |    435 |
| 2011-01-08 |    313 |
| 2011-01-09 |    250 |
| 2011-01-10 |    345 |
| 2011-01-11 |    387 |
| 2011-01-12 |      0 |
| 2011-01-13 |      0 |
+------------+--------+

在此先感谢您的帮助!

Answer 1

你的问题在这里:

ON (dates.date = DATE(visits.start) and account_id = 40 ) 

因为您在 visits.start 上使用了 DATE 函数，MySQL 无法使用索引进行连接。

最好的解决方案可能是将 start_date 和 end_date 列添加到 dates 表并为这些列建立索引。因此，对于日期为 2011-01-01 的行，开始日期为 2011-01-01 00:00:00，结束日期为 2011-01-01 23:59:59。

然后你可以像这样直接加入日期表:

SELECT date, count(id) as 'visits' FROM dates 
LEFT OUTER JOIN visits 
ON (visits.start BETWEEN dates.start_date AND dates.end_date and account_id = 40 ) 
WHERE date >= '2010-12-13' AND date <= '2011-1-13' 
GROUP BY date ORDER BY date ASC

另一种选择是将日期和时间部分分别存储在访问表中，然后仅使用日期部分进行连接。