17370845950

时间字段设计关键在于使用方式而非类型选择；推荐国内业务用datetime(3)，全球高频场景用bigint存毫秒时间戳；需结合查询模式建联合索引、按月RANGE分区，并避免对时间字段使用函数导致索引失效。

时间字段设计直接影响查询性能和业务可维护性，关键不在“用什么类型”，而在于“怎么用”——类型选对只是基础，分区、索引、查询写法、时区处理才是效率分水岭。

时间字段类型选择：datetime vs timestamp vs bigint

datetime：范围大（1000–9999年），精度高（支持微秒），不自动时区转换，适合记录业务发生时间（如订单创建时间）；
timestamp：范围小（1970–2038），自动转为UTC存储、按会话时区读取，适合记录系统行为时间（如登录日志），但要注意2038问题；
bigint（存毫秒/微秒时间戳）：无时区歧义、排序快、跨语言兼容好，但丧失原生日期函数支持，需额外转换。

建议：国内业务优先用 datetime(3)（毫秒精度），兼顾可读性、范围与精度；高频写入+全球服务场景可考虑 bigint 存毫秒时间戳，配合应用层统一时区处理。

索引与分区：让时间查询真正变快

单纯给时间字段加索引不够，要结合查询模式设计：

• 高频查“最近N天”的场景（如 dashboard 实时统计）：建 联合索引 (status, create_time)，把过滤性强的字段放前面，时间放后，避免索引失效
• 单表数据超千万且按月归档：用 RANGE 分区 按月份拆分（如 PARTITION BY RANGE (YEAR(create_time)*100 + MONTH(create_time))），查询时能直接命中分区，跳过90%以上数据
• 避免在 WHERE 中对时间字段做函数操作：如 WHERE DATE(create_time) = '2025-05-01' 会导致索引失效；应改写为 WHERE create_time >= '2025-05-01' AND create_time

查询写法避坑：这些细节让索引形同虚设

很多慢查不是没建索引，而是写法绕过了索引：

• 用 BETWEEN 要小心边界：它包含两端，但若字段含微秒，BETWEEN '2025-05-01' AND '2025-05-01 23:59:59' 会漏掉毫秒部分，推荐用左闭右开区间
• 分页查最新数据时，别只依赖 ORDER BY create_time DESC LIMIT 1000,20：偏移量越大越慢；改用“游标分页”，记录上一页最大时间值，下次查 WHERE create_time
• 统计类查询慎用 GROUP BY DATE(create_time)：可提前在表中增加生成列 create_date DATE AS (DATE(create_time)) STORED 并对其建索引，加速聚合

时区与一致性：别让“北京时间”变成“数据库时间”

开发环境用本地时区、生产库用 UTC 是常见配置错误源头：

• 应用层统一使用 UTC 时间戳生成和解析，数据库 全量存 UTC（无论 datetime 还是 bigint），展示时再转本地时区
• 禁止在 SQL 中用 NOW() 或 CURDATE() 做业务逻辑判断——它们依赖 session time_zone，易导致测试/生产结果不一致
• 如必须存本地时间（如营业时间规则），用 datetime + 显式时区字段（如 store_id + open_time + timezone_offset），而非依赖数据库自动转换

不复杂但容易忽略。时间字段不是填个类型就完事，它是查询路径上的第一个关卡，设计时多想一步过滤场景、分区节奏、时区归属，后面省下的不只是几秒响应，更是半夜被叫醒排查慢查的次数。