如果记录存在，MySQL 按日期搜索的最快方法

Question

我找到了很多按日期搜索 mysql 记录的方法

方法一:

SELECT id FROM table WHERE datetime LIKE '2015-01-01%' LIMIT 1

方法二(同方法一+ORDER BY):

SELECT id FROM table WHERE datetime LIKE '2015-01-01%' ORDER BY datetime DESC LIMIT 1

方法三:

SELECT id FROM table WHERE datetime BETWEEN '2015-01-01' AND '2015-01-01 23:59:59' LIMIT 1

方法四:

SELECT id FROM table WHERE DATE_FORMAT( datetime, '%y.%m.%d' ) = DATE_FORMAT( '2015-01-01', '%y.%m.%d' )

方法五(我觉得最慢):

SELECT id FROM table WHERE DATE(`datetime`) = '2015-01-01' LIMIT 1

什么是最快的？

在我的例子中，表格有 100 万行，要搜索的日期始终是最近的。

Answer 1

你提到的最快的方法是

SELECT id 
  FROM table 
 WHERE datetime BETWEEN '2015-01-01' AND '2015-01-01 23:59:59'
 LIMIT 1

当您在 datetime 列上创建索引时，这会变得很快。可以随机访问索引以找到第一个匹配行，然后扫描直到最后一个匹配行。所以没有必要读取整个表，甚至整个索引。而且，当您使用 LIMIT 1 时，它只会读取单行。非常快，即使在一张巨大的 table 上也是如此。

您的其他搜索方式对每一行应用一个函数:

• datetime LIKE '2011-01-01%' 将 datetime 转换为每一行的字符串。
• 方法 3、4 和 5 都对每一行的内容使用显式函数，如 DATE()。

使用这些函数可以击败使用索引来查找数据。

专业提示:不要将 BETWEEN 用于日期运算，因为它无法很好地处理结束条件。而是使用

 WHERE datetime >= '2015-01-01' 
   AND datetime <  '2015-01-02'

这与 BETWEEN 的表现一样好，让您不必将 2015-01-01 的最后时刻显式写为 23:59: 59。无论如何，这对于更高精度的时间戳是不正确的。