MySQL的左外连接速度慢

问题描述：

希望能得到一些帮助与此查询，我在现在工作了一段时间，并不能更快得到任何：MySQL的左外连接速度慢

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年4月1日至昨天约400k行。我使用的是日期表，因此连接将在没有访问的情况下返回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 | 
+------------+--------+

谢谢！

查找`explain`和MySQL手册`解释extended` – goat 2011-01-13 19:55:31

答

你的问题是在这里：

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

另一种选择是单独存储的访问表中的日期和时间部分，在仅使用日期部分加入。

谢谢，做了招。我在访问表中添加了一个`start_date`列，并在其上添加了一个索引。下降到300ms！ – 2011-01-13 20:35:37

答

我认为它主要是由于DATE（）函数慢。您可以将日期列添加到存储整个日期的访问，并编写一个触发器，以便在插入访问或更新其日期时间时自动更新它。这将允许MySQL更好地使用连接中使用的索引。

答

这样的事情如何：从eumiro中选择的结果外连接？

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

编辑：编辑SQL 编辑：另一种选择 - 在线选择，这样的事情：

SELECT date, (select count(*) as 'visits' 
FROM from visits 
where date = DATE(visits.start) and account_id = 40) 
) from dates 
WHERE date >= '2010-12-13' AND date <= '2011-1-13' 
ORDER BY date ASC

MySQL的左外连接速度慢

相关推荐