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