背景
- 数据表都很可能会有一两个字段需要保存日期时间数据，那应该用什么 Mysql 类型来保存呢？
- 前面讲过 datetime、timestamp、int 的方式来保存日期时间
本篇文章会对 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' |
'1970-01-01 00:00:01.000000' |
如果是无符号， |
自动初始化 (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
