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

背景

• 数据表都很可能会有一两个字段需要保存日期时间数据，那应该用什么 Mysql 类型来保存呢？
• 前面讲过 datetime、timestamp、int 的方式来保存日期时间

如何存储 10位、13位的 unix 时间戳？

date、datetime、timestamp 的区别

本篇文章会对 datetime、timestamp、int 进行比较，然后对一些典型的查询进行基准测试，来决定什么情况下使用哪种数据类型

整体对比表

加粗是缺点

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

• 这里直接展示结果，不展示过程了（因为只需要关注结果即可）
• 感兴趣可以看：https://域名/blog/
• 这里会使用 sysbench、mysqlslap 两个性能测试工具

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

下列查询均是从 1,497,421 个可用数据中返回 75,706 行

datetime

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名timemeasures m
WHERE
    域名ured_on > \'2016-01-01 00:00:00.0\'
        AND 域名ured_on < \'2016-02-01 00:00:00.0\';

timestamp

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名stampmeasures m
WHERE
    域名ured_on > \'2016-01-01 00:00:00.0\'
        AND 域名ured_on < \'2016-02-01 00:00:00.0\'; 

int（使用 FROM_UNIXTIME 转换函数）

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名imestampmeasures m
WHERE
    FROM_UNIXTIME(域名ured_on) > \'2016-01-01 00:00:00.0\'
        AND FROM_UNIXTIME(域名ured_on) < \'2016-02-01 00:00:00.0\';

int

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名imestampmeasures m
WHERE
    域名ured_on > 1451617200
        AND 域名ured_on < 1454295600;

结论

• 两个基准测试工具都表明 datime 比 timestamp 和 int（使用转换函数） 快
• 但是 datetime 并不比直接用 int 数字快

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

下列查询均是从 1,497,421 个可用数据中返回 221,850 行

datetime

SELECT SQL_NO_CACHE measured_on
FROM
    域名timemeasures m
WHERE
    WEEKDAY(域名ured_on) = 0 #MONDAY;

timestamp 

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名stampmeasures m
WHERE
    WEEKDAY(域名ured_on) = 0 #MONDAY;

int（使用 FROM_UNIXTIME 转换函数） 

SELECT SQL_NO_CACHE
    measured_on
FROM
    域名imestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(域名ured_on)) = 0 #MONDAY;

结论

• 两个基准测试工具都表明 datime 比 timestamp 和 int（使用转换函数） 快
• 但在这个测试中，int（使用转换函数）比 timestamp 更快

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

下列查询均是从 1,497,421 个可用数据中返回 1 行

datetime

SELECT SQL_NO_CACHE 
    COUNT(measured_on)
FROM
    域名timemeasures m
WHERE
    WEEKDAY(域名ured_on) = 0 #MONDAY;

timestamp 

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    域名stampmeasures m
WHERE
    WEEKDAY(域名ured_on) = 0 #MONDAY;

int（使用 FROM_UNIXTIME 转换函数） 

SELECT SQL_NO_CACHE
    COUNT(measured_on)
FROM
    域名imestampmeasures m
WHERE
    WEEKDAY(FROM_UNIXTIME(域名ured_on)) = 0 #MONDAY;

结论 

• 两个基准测试工具都表明 datime 比 timestamp 和 int（使用转换函数） 快
• 但在这个测试中，int（使用转换函数）比 timestamp 更快

最终结论

使用 datetime 应该是绝大多数场景下的最佳选择，因为

• 它更快
• 它可读性更好，无需转换
• 没有时区切换的问题
• 它仅比 timestamp 多使用 1 个字节，但存储的时间范围却非常大

做抉择

• 如果只是想存储简单的 unix 时间戳，那么使用 int 是最佳选择，因为它非常快，和使用普通数字一样
• 而如果要根据时区进行存储日期时间，那么就应该使用 timestamp
• 否则绝大多数情况下推荐使用  datetime