Mysql - 如何决定用 datetime、timestamp、int 哪种类型存储时间戳？-阿里云开发者社区

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

2022-05-15 2309

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

云数据库 RDS MySQL，集群版 2核4GB 100GB

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 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 - 如何决定用 datetime、timestamp、int 哪种类型存储时间戳？

背景

整体对比表

接下来对 int、timestamp、datetime 的性能进行基准测试

测试一：选择日期范围内的值

datetime

timestamp

int（使用 FROM_UNIXTIME 转换函数）

int

结论

测试二：选择星期一的数据

datetime

timestamp

int（使用 FROM_UNIXTIME 转换函数）

结论

测试三：统计星期一的数据量

datetime

timestamp

int（使用 FROM_UNIXTIME 转换函数）

结论

最终结论

做抉择

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像