锐客网

  1. 首页 > it技术 > >

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 哪种类型存储时间戳（）】

    推荐阅读


    上一篇：自动下载MarkDown格式会议论文的程序

    下一篇：Vue|Vue 3 Reactivity System Source Code Reading: `markRaw`