能否为date字段创建基于YEAR()函数的索引？附查询优化场景

先直接给你拍板：完全可行！不过咱们得结合你对比的三种查询写法的性能差异一起唠唠，帮你选最优的优化方案。

一、函数索引的有效性 #

主流数据库（比如MySQL 5.7+、PostgreSQL、SQL Server等）都支持函数索引——也就是基于字段的函数计算结果创建的索引。你写的语句：

CREATE INDEX indexDATE ON table (YEAR(date));

在支持的数据库里是完全生效的：当你用YEAR(date) = '2017'查询时，数据库会直接调用这个索引，不用全表扫描去计算每一行的年份值，能大幅提升查询速度。

二、三种查询写法的性能对比 #

咱们逐个拆解你对比的三种写法，从性能优先级排序：

1. 最优方案：date >= '2017-01-01' AND date < '2018-01-01'（替代你写的BETWEEN）
   这是效率最高的写法！如果你的date字段本身有普通的B-tree索引，这个范围查询会直接命中索引，数据库能快速定位到2017年的所有数据。
   👉 小提示：别用BETWEEN '2017-1-1' AND '2017-12-31'，如果date是带时分秒的datetime类型，会漏掉2017-12-31 00:00:01到2017-12-31 23:59:59的数据，换成>= '2017-01-01' AND < '2018-01-01'更严谨。

2. 次优（仅限特定场景）：LIKE '2017%'
   这个写法只有当date字段是字符串类型（比如存储为'2017-05-01'）时，才可能用到前缀索引；如果date是标准的date/datetime类型，数据库会先把所有日期转成字符串再匹配，这时候普通的日期索引完全用不上，属于全表扫描，性能拉胯，不推荐。

3. 依赖函数索引的方案：YEAR(date) = '2017'
   如果没有函数索引，数据库会对每一行计算YEAR(date)再做比较，妥妥的全表扫描，性能最差；但创建了你说的函数索引后，就能用上索引提速，不过效率还是不如第一种直接用范围查询的方案——毕竟函数索引存储的是计算后的年份值，而原始日期索引的范围匹配逻辑更直接，维护成本也更低。

三、额外注意事项 #

• 不同数据库对函数索引的细节要求有差异：比如MySQL需要确保用的是InnoDB引擎，PostgreSQL要保证函数是确定性函数（YEAR()满足这个要求，没问题）。
• 如果你的date字段已经有普通索引，优先用范围查询的写法，没必要额外创建函数索引——既节省存储空间，又减少索引维护的开销（比如插入/更新数据时，函数索引也要同步更新）。

内容的提问来源于stack exchange，提问作者user9362338