DATE 字段和 MONTH() YEAR() 函数上的 INDEX

日期/日期时间列上的索引未针对 YEAR(col)、MONTH(col) 函数进行优化是否仍然正确？比尔·卡文给出了相当明确的答案here http://forums.mysql.com/read.php?115,86654,86654#msg-86654，但由于这是十年前的事，我想检查一下。我本以为由于日期列被描述为

一个三字节整数，包装为 YYYY×16×32 + MM×32 + DD

它可以以这样的方式建立索引，可以像多列索引一样进行优化紧密索引扫描 http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html被执行。

难道这个优化还不存在吗？为什么不可能呢？

正确的。一旦 WHERE 子句中的列用函数包装，MySQL 优化器将不会使用索引。简单的推理是，一旦你在列上有索引my_date，当您实际查找不同的值（例如 YEAR(my_date) 的输出）时，数据库认为没有必要在其中进行搜索。

但好消息是，在某些情况下有一个快速而简单的解决方案。

例如，可以使用不同的方法优化以下查询：

SELECT flight_company, count(*)
FROM flight_times
WHERE year(FlightDate) = 2017
group by flight_company

您可以将函数调用替换为将检查相同内容的范围条件，而不是在 WHERE 子句中使用 YEAR() 函数：

SELECT flight_company, count(*) 
FROM flight_times
WHERE FlightDate between '2017-01-01'
and '2017-12-31'
GROUP BY flight_company

但有些函数不能简单地用范围条件替换，例如 YEAR 的情况。例如，如何用条件替换 dayofweek() ？可能更难。 因此，另一种方法是使用MySQL 5.7虚拟（生成）列 https://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html。如果您采用这种方法，您可以在 CREATE TABLE 语句中创建此虚拟列，以实际匹配 dayofweek() 的结果：

Flight_dayofweek tinyint(4)
 GENERATED ALWAYS AS (dayofweek(FlightDate
 VIRTUAL

作为 EverSQL 的联合创始人，我会谦虚地建议您使用EverSQL SQL 查询优化器 http://www.eversql.com?utm_source=so自动优化此类查询。