mysql 左外连接 count_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秒钟才能运行.我已在date.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 |

+------------+--------+

在此先感谢您的帮助！

解决方法:

您的问题在这里：

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

由于您在visits.start上使用DATE函数,因此MySQL无法使用索引进行联接.

最好的解决方案可能是在日期表中添加start_date和end_date列并为这些列建立索引.因此,对于日期为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

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

标签：performance,sql,mysql

来源： https://codeday.me/bug/20191105/1995717.html