MySQL 5.6中Date列按年查询：新增年份列vs原列建索引哪个更高效？

直接给出结论：优先选择方案二（为原Date列创建索引），它的查询效率更优，同时避免了冗余数据的维护成本。以下是具体分析：

方案一的问题 #

• 冗余数据的维护风险与开销：新增col_year列后，必须保证它和col_date的年份严格一致，要么通过触发器同步（会增加插入/更新操作的开销），要么在业务代码中手动维护（容易出现数据不一致的错误）。
• 查询效率无优势：即使给col_year建了索引，你的查询语句同时包含col_year和col_date的范围条件，MySQL 5.6的优化器通常只会选择其中一个索引执行范围扫描，另一个条件需要回表过滤数据，反而可能比直接用Date索引的扫描效率更低。如果要让两个条件都用到索引，需要创建(col_year, col_date)联合索引，这会进一步增加索引的存储空间和维护成本。

方案二的优势 #

• Date类型索引的高效性：MySQL对Date类型的存储和索引优化非常成熟，B-tree索引天然适合范围查询（BETWEEN）。按年份查询本质上是一个连续的日期区间（比如col_date BETWEEN '2020-01-01' AND '2023-12-31'），索引可以直接定位到区间的起始和结束位置，扫描连续的索引页，不需要额外的过滤操作，效率很高。
• 无冗余，易维护：不需要新增列，避免了数据不一致的风险，也减少了表的存储空间和写操作的开销。

验证方法 #

可以通过EXPLAIN语句查看两种方案的执行计划：

• 执行EXPLAIN SELECT * FROM your_table WHERE col_year BETWEEN par_year_a AND par_year_b AND col_date BETWEEN par_date_a AND par_date_b;
• 再执行EXPLAIN SELECT * FROM your_table WHERE col_date BETWEEN par_date_a AND par_date_b;

对比两者的type（是否为range）、key（使用的索引）、rows（预估扫描行数），你会发现方案二的扫描行数更少，执行效率更高。

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