MySQL BETWEEN两个日期时间不使用索引。

Question

explain
SELECT COUNT(*) AS Count, CreatedBy
FROM `Notes`
INNER JOIN Users ON UserID = CreatedBy
INNER JOIN UserRoles ON URoleID = RoleID AND RoleID = 1
WHERE NoteVisible = 1 AND NoteText NOT LIKE '%SOME KEYWORD%' 
      AND Created BETWEEN '2014-02-24 00:00:00' AND '2014-02-24 23:59:59' 
GROUP BY CreatedBy

正如你所看到的，ref是空的，需要遍历23行，而不是只遍历1行。对于这个例子来说，这样做很快，但当我进行1-2个月的范围时，行数变成>10000，它会使页面变慢并且锁定表格。

注意：如果我删除00:00:00和23:59:59，那么它将使用索引，只遍历1行，但我需要选择整天从00:00开始到23:59结束的所有数据。

请帮助我重构此查询以解决此问题或提出任何可能的解决方案。谢谢。

编辑：

用<或>或<=或>=替换BETWEEN并不能解决问题。

- GGio

---

如果您将 BETWEEN 替换为 ('2014-02-24' <= Created AND Created < '2014-02-25')，会发生什么？ - vadchen

已经以不同的方式尝试过，但仍未使用索引。 - GGio

请发布表结构，或者至少在查询中添加列名前缀，这样很难猜出哪个列属于哪个表。NoteVisible 属于 Notes 吗？ - krokodilko

NoteVisible属于笔记，为什么它会属于任何呈现的表格？ - GGio

Answer 1

这个查询使用了索引。
选择类型为range，所用的键是Created

对于范围类型，ref列始终为null，
请参阅文档：http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_range

范围

只检索在给定范围内的行，使用索引选择行。输出行中的键列指示使用的索引。key_len包含已使用的最长键部分。 此类型的ref列为空。

(强调我的)

- krokodilko

---

如果我删除解释，那个范围内就不会有23行，只返回1行。 - GGio

这只是一个估计 - MySql认为有23行。在解析阶段，它不会检查确切的行数，而只是估算。但是无论如何，它认为使用该索引并连接23行比使用主键并连接X行更好。 - krokodilko

有没有办法解决减速问题？ - GGio

Answer 2

当您将日期时间字段与字符串进行大于/小于比较时。如果您使用转换函数（如UNIX_TIMESTAMP（））并将其他日期也转换为unixtimestamp，那么这将起到作用，但会破坏索引的使用。也许更好的解决方案是在表中将日期存储为unix时间戳，并在其上放置索引。

另一种方法是，在您的SQL中添加“limit”也对我有用。

- qingjiuzhubei