Mysql|Mysql - 如何决定用 datetime、timestamp、int 哪种类型存储时间戳()

背景

  • 数据表都很可能会有一两个字段需要保存日期时间数据,那应该用什么 Mysql 类型来保存呢?
  • 前面讲过 datetime、timestamp、int 的方式来保存日期时间
如何存储 10位、13位的 unix 时间戳?
date、datetime、timestamp 的区别
本篇文章会对 datetime、timestamp、int 进行比较,然后对一些典型的查询进行基准测试,来决定什么情况下使用哪种数据类型

整体对比表
加粗是缺点
Feature
datetime
timestamp
Int (存储 Unix time)
本地时间表示
Yes
Yes
No,如果要表示为本地时间需要借助转换函数,比如FROM_UNIXTIME()
存储小数秒
Yes,高达 6 位精度
Yes,高达 6 位精度
No
有效范围
'1000-01-01 00:00:00.000000'
to
'9999-12-31 23:59:59.999999
'1970-01-01 00:00:01.000000'
to
'2038-01-19 03:14:07.999999'
如果是无符号,
'1970-01-01 00:00:01.000000;
理论上可到
'2106-2-07 06:28:15'
自动初始化 (MySQL 5.6.5+)
Yes
Yes
No
可读性好
Yes
Yes
No, 必须转换才能知道具体时间点
存储时间值会转换为 UTC 时间
No
Yes
No
可以改成其他了诶性
Yes,
如果结果值在有效时间范围内
Yes
Yes, 如果结果值在有效时间范围内并使用了转换函数
存储要求
(MySQL 5.6.4+)
5 bytes (加上最多 3 个字节的小数秒,如果使用)
4 bytes(加上最多 3 个字节的小数秒,如果使用)
4 bytes (no fractional seconds allowed)
接下来对 int、timestamp、datetime 的性能进行基准测试
  • 这里直接展示结果,不展示过程了(因为只需要关注结果即可)
  • 感兴趣可以看:https://vertabelo.com/blog/
  • 这里会使用 sysbench、mysqlslap 两个性能测试工具

测试一:选择日期范围内的值
下列查询均是从 1,497,421 个可用数据中返回 75,706 行
datetime
SELECT SQL_NO_CACHE measured_on FROM vertabelo.datetimemeasures m WHERE m.measured_on > '2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 00:00:00.0';

Response time (ms) Sysbench mysqlslap
Min 152 296
Max 1261 3203
Average 362 809

timestamp
SELECT SQL_NO_CACHE measured_on FROM vertabelo.timestampmeasures m WHERE m.measured_on > '2016-01-01 00:00:00.0' AND m.measured_on < '2016-02-01 00:00:00.0';

Response time (ms) Sysbench mysqlslap
Min 214 359
Max 1389 3313
Average 431 1004

int(使用 FROM_UNIXTIME 转换函数)
SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE FROM_UNIXTIME(m.measured_on) > '2016-01-01 00:00:00.0' AND FROM_UNIXTIME(m.measured_on) < '2016-02-01 00:00:00.0';

Response time (ms) Sysbench mysqlslap
Min 2472 7968
Max 6554 10312
Average 4107 8527

int
SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE m.measured_on > 1451617200 AND m.measured_on < 1454295600;

Response time (ms) Sysbench mysqlslap
Min 88 171
Max 275 2157
Average 165 514

结论
  • 两个基准测试工具都表明 datime 比 timestamp 和 int(使用转换函数) 快
  • 但是 datetime 并不比直接用 int 数字快
Avg response time (ms) Sysbench 相对于 datetime 的速度 mysqlslap 相对于 datetime 的速度
datetime 362 - 809 -
timestamp 431 19% slower 1004 24% slower
int(使用转换函数) 4107 1134% slower 8527 1054% slower
int 165 55% faster 514 36% faster

测试二:选择星期一的数据
下列查询均是从 1,497,421 个可用数据中返回 221,850 行
datetime
SELECT SQL_NO_CACHE measured_on FROM vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 1874 4343
Max 6168 7797
Average 3127 6103

timestamp
SELECT SQL_NO_CACHE measured_on FROM vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 2688 5953
Max 6666 13531
Average 3653 8412

int(使用 FROM_UNIXTIME 转换函数)
SELECT SQL_NO_CACHE measured_on FROM vertabelo.inttimestampmeasures m WHERE WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 2051 5844
Max 7007 10469
Average 3486 8088
结论
  • 两个基准测试工具都表明 datime 比 timestamp 和 int(使用转换函数) 快
  • 但在这个测试中,int(使用转换函数)比 timestamp 更快
Avg response time (ms) Sysbench 相对于 datetime 的速度 mysqlslap 相对于 datetime 的速度
Datetime 3127 - 6103 -
Timestamp 3653 17% slower 8412 38% slower
INT 3486 11% slower 8088 32% slower

测试三:统计星期一的数据量
下列查询均是从 1,497,421 个可用数据中返回 1 行
datetime
SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.datetimemeasures m WHERE WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 1720 4063
Max 4594 7812
Average 2797 5540

timestamp
SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.timestampmeasures m WHERE WEEKDAY(m.measured_on) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 1907 4578
Max 5437 10235
Average 3408 7102

int(使用 FROM_UNIXTIME 转换函数)
SELECT SQL_NO_CACHE COUNT(measured_on) FROM vertabelo.inttimestampmeasures m WHERE WEEKDAY(FROM_UNIXTIME(m.measured_on)) = 0 #MONDAY;

Response time (ms) Sysbench mysqlslap
Min 2108 5609
Max 4764 9735
Average 3307 7416

结论
  • 两个基准测试工具都表明 datime 比 timestamp 和 int(使用转换函数) 快
  • 但在这个测试中,int(使用转换函数)比 timestamp 更快

Avg response time (ms) Sysbench 相对于 datetime 的速度 mysqlslap 相对于 datetime 的速度
Datetime 2797 - 5540 -
Timestamp 3408 22% slower 7102 28% slower
INT 3307 18% slower 7416 33% slower

最终结论
使用 datetime 应该是绝大多数场景下的最佳选择,因为
  • 它更快
  • 它可读性更好,无需转换
  • 没有时区切换的问题
  • 它仅比 timestamp 多使用 1 个字节,但存储的时间范围却非常大

做抉择
  • 如果只是想存储简单的 unix 时间戳,那么使用 int 是最佳选择,因为它非常快,和使用普通数字一样
  • 而如果要根据时区进行存储日期时间,那么就应该使用 timestamp
  • 否则绝大多数情况下推荐使用datetime
【Mysql|Mysql - 如何决定用 datetime、timestamp、int 哪种类型存储时间戳()】

    推荐阅读