mysql - 为什么 MySQL 在使用 DATE (`table` .`column` 时会删除我的索引)

Question

我有一个包含几列的 MySQL innodb 表。其中之一名为“dateCreated”，它是一个 DATETIME 列并且已编入索引。

我的查询:

        SELECT 
            *
        FROM 
            `table1`
        WHERE 
            DATE(`dateCreated`) BETWEEN '2014-8-7' AND '2013-8-7'

MySQL 出于某种原因拒绝使用 dateCreated 列上的索引(即使使用 USE INDEX 或 FORCE INDEX。

但是，如果我将查询更改为:

        SELECT 
            *
        FROM 
            `table1`
        WHERE 
           `dateCreated` BETWEEN '2014-8-7' AND '2013-8-7'

注意 DATE(...) 移除

MySQL 使用索引很好。我可以在不使用 DATE() 函数的情况下进行管理，但这对我来说很奇怪。

我知道 MySQL 可能会索引完整的日期和时间，而当只搜索其中的一部分时，它会变得困惑或发生其他事情。但是必须有一种方法可以使用部分日期(比方说 MONTH(...) 或 DATE(...))并且仍然受益于索引列和避免全表扫描。

有什么想法..？

谢谢。

Answer 1

正如您所观察到的，一旦您将函数应用于该字段，您就会破坏对索引的访问。所以，

如果您不使用 between，它会有所帮助。将函数应用于数据的基本原理是您可以获得与参数匹配的数据。只有2个参数日期和几百个？千？百万？数据行。为什么不扭转这种局面，改变参数以适应数据呢？ (使其成为“可搜索”谓词)

    SELECT 
        *
    FROM 
        `table1`
    WHERE 
         ( `dateCreated` >= '2013-08-07' AND `dateCreated` < '2014-08-07' )

    ;

• 注意 2013-08-07 首先使用，如果也使用 between 则必须如此。如果第一个日期早于第二个日期，则使用 between 不会得到任何结果。
• 另请注意，包含 恰好 12 个月的数据 >= '2013-08-07' AND < '2014-08-07'，我想这就是您要查找的内容。<
• 使用 date(dateCreated) 和 between 的组合将包括 1 天太多，因为将包括“2014-08-07”期间的所有事件。如果您故意想要一年零 1 天，则将 1 天添加到较高的日期，即 < '2014-08-08'