带有索引的mysql datetime字段的范围为"like"与"between and"之间的性能 [英] mysql datetime field with index get a range 'like' vs. 'between and' performance

问题描述

我只是发现mysql可以使用类似的查询日期时间:

I just find mysql can query datetime using like:

like '2013-06-12%'

我认为它不能使用索引.我用Google搜索，但无法直接找到这样的主题.因此，我使用带有3308614记录的表进行了测试.第一个SQL:

I think it can not use the index. I Google it, but can not find such subject directly. So I have a test using a table with 3308614 records. The first SQL:

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

我们都知道，此SQL无法使用索引，并且需要4秒钟才能获得结果. 第二条SQL:

All we know this SQL can not use the index and it takes 4 seconds to get the result. The second SQL:

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

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

I don't know if it can use the index, but it takes 4 seconds too. The third SQL:

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

我们都知道，第三个SQL可以使用索引，并且需要0.016秒.

All we know the third SQL can use the index, and it takes 0.016 second.

所以我认为在查询datetime字段时'like'不能使用索引，因为mysql应该先将datetime字段转换为字符串，然后将字符串发送给like命令. 这是正确的吗?

So I think 'like' can not use the index when query the datetime field, because mysql should convert datetime field to string first and send the string to like command. Is this correct ?

推荐答案

假定t.active_time的类型为DATETIME，

由于函数调用，以下查询无法使用索引.与'2013-06-30'进行比较之前，必须将所有active_time即时转换为DATE值.该字符串首先转换为DATE值，并且仅在查询开始时发生一次.

The following query cannot use an index because of the function call. All active_time must be converted on-the-fly to a DATE value before comparison with '2013-06-30'. This string is converted to a DATE value in the very first place, and this happens only once at the very beginning of the query.

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

由于类似的原因，第二个查询也不能使用索引.您实际上是在进行字符串比较(由于LIKE运算符).所有active_time值都将即时转换为字符串.

The second query cannot use an index either for similar reasons. You are actually making a string comparison (because of the LIKE operator). All active_time values are converted to a string on-the-fly.

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

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

Only the last one can use an index. The strings '2007-11-30' and '2007-12-01' are cast to DATETIME in this case, because the < and > operators allow this.

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

后者也适用于=和BETWEEN运算符.

The latter also applies to the = and BETWEEN operators.

有关信息，由于需要隐式转换，因此可以使用LIKE运算符将所有类型与字符串进行比较，从而遇到上述相同的问题.

For information, all types can be compared to a string with the LIKE operator, suffering from the same problem as described above, because of the implicit conversion it requires.

t.active_time = '2013-06-30'不能按预期方式工作，因为'2013-06-30'被强制转换为DATETIME值，即'2013-06-30 00:00:00'.

t.active_time = '2013-06-30' does not work as expected because '2013-06-30' is cast to a DATETIME value, that is to say '2013-06-30 00:00:00'.

这篇关于带有索引的mysql datetime字段的范围为"like"与"between and"之间的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！