MYSQL中时间类型底层存储，DATETIME ,TIMESTA
2019-10-07 本文已影响0人
梅先森森森森森森
在优化索引时，思考了一个问题，DATE, DATETIME, TIMESTAMP，还有INT存储的时间，在索引中哪个效率更高一些？
索引存储的，如果单纯的测试，而不去了解底层存储的方式和类型就不能断言哪个类型的效率更好一些。
DATE: （YYYY-MM-DD） 范围 '1000-01-01' to '9999-12-31' .DATETIME: （YYYY-MM-DD HH:MM:SS） 范围 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 支持自动更新TIMESTAMP: （YYYY-MM-DD HH:MM:SS）范围 '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 支持自动更新-
INT： (整数) 范围 '1970-01-01 00:00:01' to '2038-01-19 03:14:07'
（这里 DATETIME，TIMESTAMP都支持0-6位的时间精度，这里就不讲了）
注意 TIMESTAMP 会先将用户输入转换成UTC时间进行存储，查询时也会将时间转换成当前UTC时间，所以当数据库服务器的时区跟查询客户端时区不一致时，会出现查询不到的情况。
再看MYSQL对这个几个类型的存储情况。
| 类型 | before MySQL 5.6.4 | Storage as of MySQL 5.6.4 |
|---|---|---|
| DATE | 3 bytes, 小端字节 | 3 bytes, 小端字节 |
| DATE | 3 bytes, 小端字节 | 3 bytes, 小端字节 |
| TIMESTAMP | 4 bytes, 小端字节 | 4 bytes + 毫秒存储, 大端字节 |
| DATETIME | 8 bytes, 小端字节 | 5 bytes + 毫秒存储, 大端字节 |
| INT | 4 bytes | 4 bytes |
- DATE : 3个字节整型 YYYY×16×32 + MM×32 + DD
- TIMESTAMP : 4个字节整型，存储UTC时间秒。
- DATETIME:
1.（5.6.4 之前）8个字节， 其中4个字节整型 YYYY×10000 + MM×100 + DD 和 4个字节整型HH×10000 + MM×100 + SS
2.（5.6.4 之后）
1 bit 符号位 (1= 整数, 0=负数)
17 bits year*13+month (year 0-9999, month 0-12)
5 bits day (0-31)
5 bits hour (0-23)
6 bits minute (0-59)
6 bits second (0-59)
---------------------------
40 bits = 5 bytes
- INT 4个字节整型，存储时间秒
毫秒存储位
| 精度 | 存储空间 |
|---|---|
| 0 | bytes |
| 1，2 | 1 bytes |
| 3，4 | 2 bytes |
| 4，6 | 3 bytes |
对各个时间类型有个整体的了解以后我们分析一下。
其中DATE字段精度不够不参与对比。
对比一下 DATETIME 、 TIMESTAMP、 INT
- 从存储时间范围看 DATETIME > TIMESTAMP = INT
- 从使用方便上看 带有自动更新 DATETIME , TIMESTAMP > INT
- 从时间精度看 DATETIME > TIMESTAMP > INT
- 从存储空间上看,同一精度下DATETIME > TIMESTAMP = INT
- 从查询速度上看，底层都是整型存储，所以 DATETIME = TIMESTAMP = INT
不考虑存储空间优化的情况下，DATETIME是最优的时间存储类型
考虑存储空间的情况下，TIMESTAMP是最优的时间存储类型
参考文档
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
https://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html
