正确做法是转换查询条件值而非索引列，避免在WHERE中对索引列使用函数，应使用FROM_UNIXTIME或UNIX_TIMESTAMP转换比较值以利用索引，提升查询效率。
在MySQL中，日期与时间戳之间的转换其实非常直接，通常通过
UNIX_TIMESTAMP()和
FROM_UNIXTIME()这两个函数来完成。但真正的性能挑战，或者说我们经常会踩的坑，往往出现在将这些转换操作应用到
WHERE子句中时。优化这类查询的关键在于，永远不要对被索引的列使用函数进行操作，而是应该转换你的比较值。
解决方案
日期时间与时间戳的互转：
-
日期时间转时间戳： 使用
UNIX_TIMESTAMP(date_expression)
。例如，SELECT UNIX_TIMESTAMP('2023-10-26 10:30:00');会返回一个整数时间戳。如果你有一个DATETIME
或TIMESTAMP
类型的列，直接传入即可。 -
时间戳转日期时间： 使用
FROM_UNIXTIME(unix_timestamp)
。例如，SELECT FROM_UNIXTIME(1678886400);
会返回一个DATETIME
格式的字符串，通常是YYYY-MM-DD HH:MM:SS
。
WHERE
条件查询优化方案的核心：
避免在
WHERE子句中对索引列使用任何函数。这意味着，如果你有一个名为
created_at的
DATETIME列并且它有索引，那么
WHERE UNIX_TIMESTAMP(created_at) > 1678886400这样的写法是极度低效的。正确的做法是转换你的查询条件值，而不是被查询的列。
示例：
假设你有一个
orders表，其中有一个
created_at(DATETIME类型，已建立索引) 列，你想查询某个时间戳之后的所有订单。
-
错误示范 (会使索引失效):
SELECT * FROM orders WHERE UNIX_TIMESTAMP(created_at) > 1678886400;
这条查询会让MySQL对
created_at
列的每一行都执行UNIX_TIMESTAMP()
函数，然后进行比较，这等同于全表扫描。 -
正确示范 (利用索引):
SELECT * FROM orders WHERE created_at > FROM_UNIXTIME(1678886400);
这里，我们把时间戳
1678886400
转换成DATETIME
格式，然后与created_at
列直接比较。由于created_at
列没有被函数处理，MySQL可以有效地使用其上的索引进行快速查找。
同样的道理，如果你有一个
timestamp_col(INT或BIGINT类型，存储时间戳，已建立索引) 列，你想查询某个日期之后的所有数据：
-
错误示范 (会使索引失效):
SELECT * FROM my_table WHERE FROM_UNIXTIME(timestamp_col) > '2023-03-15 00:00:00';
-
正确示范 (利用索引):
SELECT * FROM my_table WHERE timestamp_col > UNIX_TIMESTAMP('2023-03-15 00:00:00');
为什么在WHERE条件中使用函数会影响查询性能？
这其实是个经典问题，也是很多初学者甚至经验丰富的开发者偶尔会忽略的细节。简单来说，当你对一个被索引的列应用函数时，MySQL的查询优化器就无法利用该列上的索引了。索引，尤其是B-tree索引，其核心在于数据是有序存储的。例如，一个日期列的索引是按照日期大小排序的。当你用
UNIX_TIMESTAMP(date_column)去查询时，你实际上是把
date_column的值“扭曲”了，它不再是索引中那个有序的、可以直接查找的值。
想象一下，你有一本按照姓氏首字母排序的电话簿（这就是索引），现在你突然想找一个名字，但你只知道这个名字的MD5哈希值。你不可能通过哈希值去查这本按姓氏排序的电话簿，对吧？你只能把电话簿里所有的名字都计算一遍MD5哈希值，然后逐个比较。这就是全表扫描的代价。MySQL在遇到这种情况时，不得不放弃使用索引，转而执行全表扫描，然后对每一行数据执行你的函数，再进行比较，这效率自然就下去了。特别是在数据量很大的时候，这种性能损耗是灾难性的。
如何正确利用索引优化日期/时间戳的范围查询？
除了上面提到的转换比较值，针对日期/时间戳的范围查询，我们还有一些非常实用的策略来确保索引的有效利用。我的经验告诉我，范围查询是这类数据类型最常见的查询模式，所以掌握好这部分至关重要。
首先，继续强调核心原则：确保索引列本身不被函数处理。
对于日期范围查询，
BETWEEN操作符是一个非常简洁且高效的选择，因为它天然地支持利用索引。
示例：查询某个日期区间内的订单
假设我们要查询2023年10月1日到2023年10月31日之间的所有订单。
-
使用
DATETIME
或TIMESTAMP
列 (推荐):SELECT * FROM orders WHERE created_at BETWEEN '2023-10-01 00:00:00' AND '2023-10-31 23:59:59';
或者更精确地处理日期边界：
SELECT * FROM orders WHERE created_at >= '2023-10-01 00:00:00' AND created_at < '2023-11-01 00:00:00';
这种写法对于
DATETIME
和TIMESTAMP
类型非常友好，并且能够充分利用created_at
列上的索引。 -
使用
INT
或BIGINT
存储时间戳的列:SELECT * FROM my_table WHERE timestamp_col BETWEEN UNIX_TIMESTAMP('2023-10-01 00:00:00') AND UNIX_TIMESTAMP('2023-10-31 23:59:59');同样，也可以使用
>=
和<
的组合：SELECT * FROM my_table WHERE timestamp_col >= UNIX_TIMESTAMP('2023-10-01 00:00:00') AND timestamp_col < UNIX_TIMESTAMP('2023-11-01 00:00:00');这两种方法都将日期字符串转换为时间戳，确保了
timestamp_col
列在比较时保持其原始的、可被索引利用的形式。
此外，如果你的日期列是
DATE类型，那么查询一整天的数据通常是直接比较：
SELECT * FROM daily_records WHERE record_date = '2023-10-26';
或者查询一个日期范围：
SELECT * FROM daily_records WHERE record_date BETWEEN '2023-10-01' AND '2023-10-31';
这些操作都能很好地利用
record_date上的索引。关键就是，让MySQL在做比较时，看到的始终是索引列的原始值，而不是经过函数处理后的结果。
除了索引，还有哪些策略可以提升日期/时间相关查询的效率？
仅仅依赖索引有时还不够，尤其是在处理海量数据或面临复杂查询场景时。我个人在实践中，除了优化索引的使用，还会考虑以下几点来进一步提升日期/时间相关查询的效率：
1. 数据分区 (Partitioning): 当你的表非常庞大，比如历史数据按日期持续增长，那么数据分区就成了非常有力的工具。你可以根据日期字段（例如
created_at的年份或月份）对表进行分区。这样，当你的查询条件限定在某个日期范围内时，MySQL只需要扫描对应的一个或几个分区，而不是整个表。这能极大地缩小查询范围，提高效率。
比如，一个按年份分区的
orders表，当你查询2023年的订单时，数据库就只去访问2023年的那个物理文件，其他年份的数据完全不用碰。这个效果，比单纯的索引扫描要快得多，因为它直接减少了I/O量。当然，分区策略需要提前规划好，一旦实施，修改起来会比较麻烦。
2. 覆盖索引 (Covering Index): 如果你的查询只需要从表中获取日期/时间相关的列以及其他几个被索引的列，那么创建一个覆盖索引会非常高效。覆盖索引是指一个索引包含了查询所需的所有列，这样MySQL就无需回表（即无需访问实际的数据行）就能获取所有需要的数据。
例如，如果你经常查询
created_at和
user_id，并且
user_id也在索引中，那么一个包含
(created_at, user_id)的复合索引，如果查询只涉及到这两列，就可以成为覆盖索引。查询速度会显著提升，因为所有数据都直接从索引中读取，避免了昂贵的磁盘随机I/O操作。
3. 适当的冗余字段 (Denormalization): 在某些极端情况下，为了优化查询，你可能会考虑在表中增加一些冗余的日期相关字段。比如，如果你的
created_at是
DATETIME类型，但你经常需要按年份、月份或日期进行分组或查询，你可以考虑增加
created_year(INT),
created_month(TINYINT),
created_day(TINYINT) 等字段，并在这些字段上建立索引。
这是一种典型的空间换时间策略。虽然增加了存储空间和数据写入时的维护成本（你需要在插入或更新时同步这些冗余字段），但对于读密集型应用来说，它能极大地简化查询并提高性能，因为它避免了在查询时对
created_at进行
YEAR(),
MONTH(),
DAY()等函数操作。当然，这需要权衡，不适合所有场景。
这些策略都是在索引优化基础之上，根据具体业务场景和数据量级来选择性使用的。没有银弹，但结合起来，往往能解决大部分日期/时间查询的性能瓶颈。
