MySQL datetime字段索引，使用"like"和"between and"哪个性能更好？

Question

我刚发现mysql可以使用like查询日期时间：

like '2013-06-12%'

我想它不能使用索引。我在谷歌上搜索了一下，但没有直接找到这样的主题。所以我进行了一个测试，使用了一个拥有3308614条记录的表格。第一个SQL语句：

SELECT * FROM subscription t WHERE DATE(t.active_time) = '2013-06-30';

我们都知道这个SQL无法使用索引，需要4秒钟才能得到结果。第二个SQL:

SELECT * FROM subscription t WHERE t.active_time LIKE '2013-06-30%';

我不知道它是否可以使用索引，但它也需要4秒钟。 第三个SQL:

SELECT * FROM subscription t WHERE t.active_time > '2007-11-30' AND t.active_time < '2007-12-01';

我们都知道第三种SQL可以使用索引，并且它只需要0.016秒。

因此，我认为当查询日期时间字段时，“like”操作符不能使用索引，因为MySQL应该首先将日期时间字段转换为字符串，然后将字符串发送到“like”命令。 这个理解正确吗？

- bluearrow

---

这个正确吗？--- 是的 - zerkms

你可以查询日期并像这样使用t.active_time = '2013-06-30'。 - Justin

不能使用 t.active_time = '2013-06-30'！！ - bluearrow

Answer 1

假设t.active_time的类型为DATETIME，以下查询由于函数调用而无法使用索引。所有的active_time都必须在与'2013-06-30'比较之前即时转换为DATE值。此字符串在查询开始时首先被转换为DATE值，这只发生一次。

SELECT * FROM subscription t WHERE DATE(t.active_time) = '2013-06-30';

第二个查询也无法使用索引，原因类似。实际上你正在进行字符串比较（因为使用了 LIKE 操作符）。所有的 active_time 值都会被即时转换为字符串。

SELECT * FROM subscription t WHERE t.active_time LIKE '2013-06-30%';

只有最后一个可以使用索引。在这种情况下，字符串'2007-11-30'和'2007-12-01'被转换为DATETIME，因为<和>运算符允许此操作。

SELECT * FROM subscription t WHERE t.active_time > '2007-11-30' AND t.active_time < '2007-12-01';

后者同样适用于“=”和“BETWEEN”运算符。

需要注意的是，所有类型都可以使用“LIKE”运算符与字符串进行比较，但由于它需要隐式转换，因此会遇到上述相同的问题。

“t.active_time = '2013-06-30'”不会按预期工作，因为“'2013-06-30'”被转换为一个“DATETIME”值，也就是说，“'2013-06-30 00:00:00'”。

- RandomSeed

Answer 2

对于这种情况的一个替代方案是执行以下操作：

SELECT *
FROM subscription t
WHERE t.active_time BETWEEN '2013-06-30 00:00:00' AND '2013-06-30 23:59:59';

最后，您将获得该天所有的事件。

- RenatoSz