DATETIME vs TIMESTAMP vs INT ,怎么选?
在做表结构设计时,对日期字段的存储,开发人员通常会有3钟选择:DATETIME,TIMESTAMP,INT。
INT 类型就是直接存储 ‘1970-01-01 00:00:00’ 到现在的毫秒数，本质和 TIMESTAMP 一样，因此用 INT 不如直接使用 TIMESTAMP。
当然，有些同学会认为 INT 比 TIMESTAMP 性能更好。但是，由于当前每个 CPU
每秒可执行上亿次的计算，所以无须为这种转换的性能担心。更重要的是，在后期运维和数据分析时，使用 INT 存储日期，是会让 DBA
和数据分析人员发疯的，INT的可运维性太差。
- 也有的同学会热衷用类型 TIMESTEMP 存储日期，因为类型 TIMESTAMP 占用 4 个字节，比 DATETIME 小一半的存储空间。
但若要将时间精确到毫秒，TIMESTAMP 要 7 个字节，和 DATETIME 8 字节差不太多。另一方面，现在距离 TIMESTAMP 的最大值‘2038-01-19 03:14:07’已经很近，这是需要开发同学好好思考的问题。
- 总的来说，我建议你使用类型 DATETIME。
对于时区问题，可以由前端或者服务这里做一次转化，不一定非要在数据库中解决。
不要忽视 TIMESTAMP 的性能问题
前面已经提及，TIMESTAMP 的上限值 2038 年很快就会到来，那时业务又将面临一次类似千年虫的问题。另外，TIMESTAMP
还存在潜在的性能问题。虽然从毫秒数转换到类型 TIMESTAMP 本身需要的 CPU
指令并不多，这并不会带来直接的性能问题。但是如果使用默认的操作系统时区，则每次通过时区计算时间时，要调用操作系统底层系统函数
__tz_convert()，而这个函数需要额外的加锁操作，以确保这时操作系统时区没有修改。所以，当大规模并发访问时，由于热点资源竞争，会产生两个问题。
- 性能不如 DATETIME： DATETIME 不存在时区转化问题。
- 性能抖动： 海量并发时，存在性能抖动问题。
为了优化 TIMESTAMP 的使用，强烈建议你使用显式的时区，而不是操作系统时区。比如在配置文件中显示地设置时区，而不要使用系统时区：
[mysqld]
time_zone = “+08:00”
通过mysqlslap来测试TIMESTAMP,DATETIME的性能。如下:
比较time_zone为System和Asia/Shanghai的性能对比
mysqlslap -uroot --number-of-queries=1000000 --concurrency=100 --query=‘SELECT NOW()’
结论:
如上图测试发现,显示时区 性能远远好于 直接使用操作系统的时区,什么日期字段推荐,DATETIME,没有时区转化。即便使用TIMESTAMP.也要在数据库使用显示地配置时区,而不是系统时区。
表结构设计规范:
每一条记录都要有一个时间字段;强烈推荐 每张核心业务表都增加一个DATETIME类型的last_modify_date字段,并设置修改自动更新机制,即便标识每条记录最后修改的时间。
列如:在前面的表 User中的字段last_modify_date.就是用于表示最后一次的修改时间。
题外话: 这里也可以 设置更新时间 更新人ID last_modify_date,last_modify_id
CREATE TABLE User (
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
sex CHAR(1) NOT NULL,
password VARCHAR(1024) NOT NULL,
money INT NOT NULL DEFAULT 0,
register_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
last_modify_date DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
CHECK (sex = ‘M’ OR sex = ‘F’),
PRIMARY KEY(id)
);
通过字段last_modify_date 定义 ON UPDATE CURRENT_TIMESTAMP(6), 那么每次这条记录,则都会自动更新last_modify_date为当前时间。
设计好处: 知道用户最近更新时间, 比如订单系列的表 方便对 超时的订单做处理, 可以根据用户最后资金修改的时间 做资金核对。
总结
- 日期类型通常就是使用 DATETIME 和 TIMESTAMP 两种类型，然而由于类型 TIMESTAMP
存在性能问题，建议你还是尽可能使用类型 DATETIME。我总结一下今天的重点内容： - MySQL 5.6 版本开始 DATETIME 和 TIMESTAMP 精度支持到毫秒；
- DATETIME 占用 8 个字节，TIMESTAMP 占用 4 个字节，DATETIME(6) 依然占用 8
个字节，TIMESTAMP(6) 占用 7 个字节； - TIMESTAMP 日期存储的上限为 2038-01-19 03:14:07，业务用 TIMESTAMP 存在风险；
- 使用 TIMESTAMP 必须显式地设置时区，不要使用默认系统时区，否则存在性能问题，推荐在配置文件中设置参数 time_zone =
‘+08:00’； - 推荐日期类型使用 DATETIME，而不是 TIMESTAMP 和 INT 类型；
- 表结构设计时，每个核心业务表，推荐设计一个 last_modify_date 的字段，用以记录每条记录的最后修改时间。
结语:
任何的字段类型都必须符合当前业务实际的需求，
------------------心向暖阳,春暖花开
-----一个爱记录笔记的人,这是唯一的渴望