MySQL 8.0.12 datetime vs timestamp 比较

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
运行环境：
CentOS 7.5 + MySQL 8.0.12版本。

MYSQL官方文档：

datetype  storage_required
datetime  5 bytes + fractional seconds storage
timestamp 4 bytes + fractional seconds storage

Fractional Seconds Precision  Storage Required
0                             0 bytes
1, 2                          1 byte
3, 4                          2 bytes
5, 6                          3 bytes

测试：
使用sysbench 1.0.15版本创建10个表，每个表1000万条记录。对不同的表加字段，测试
验证加表后的物理文件大小。

1.测试准备：使用sysbench创建测试数据。
sysbench /usr/share/sysbench/oltp_read_only.lua --mysql-host=10.19.189.221 --mysql-port=3306 --mysql-db=sbtest --mysql-user=backup --mysql-password=backup --table_size=10000000 --tables=10 --threads=128 --time=120 --report-interval=10 --db-driver=mysql prepare

2.对sbtest库下的sbtest1 ---sbtest6加字段lastmodifytime：
sbtest1表加字段lastmodifytime，类型为datetime；
sbtest2表加字段lastmodifytime，类型为datetime(3)；
sbtest3表加字段lastmodifytime，类型为datetime(6)；

sbtest4表加字段lastmodifytime，类型为TIMESTAMP；
sbtest5表加字段lastmodifytime，类型为TIMESTAMP(3)；
sbtest6表加字段lastmodifytime，类型为TIMESTAMP(6)；

登录MySQL：

 alter table sbtest1 add column LastModifyTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '末次更新时间';
 alter table sbtest2 add column LastModifyTime datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '末次更新时间';
 alter table sbtest3 add column LastModifyTime datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '末次更新时间';
 
 alter table sbtest4 add column LastModifyTime TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '末次更新时间';
 alter table sbtest5 add column LastModifyTime TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '末次更新时间';
 alter table sbtest6 add column LastModifyTime TIMESTAMP(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '末次更新时间';

--对sbtest7新增字段类型为datetime(2):
alter table sbtest7 add column LastModifyTime datetime(2) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) COMMENT '末次更新时间';

3.加完字段后使用如下语句优化：
optimize table sbtest1;
optimize table sbtest2;
optimize table sbtest3;
optimize table sbtest4;
optimize table sbtest5;
optimize table sbtest6;
optimize table sbtest7;
4. 查看文件大小：
# du -sb /data/mysql80/sbtest/sbtest{1..7}.ibd
2831155200 /data/mysql80/sbtest/sbtest1.ibd
2868903936 /data/mysql80/sbtest/sbtest2.ibd
2868903936 /data/mysql80/sbtest/sbtest3.ibd
2831155200 /data/mysql80/sbtest/sbtest4.ibd
2868903936 /data/mysql80/sbtest/sbtest5.ibd
2868903936 /data/mysql80/sbtest/sbtest6.ibd
2868903936 /data/mysql80/sbtest/sbtest7.ibd
2436890624 /data/mysql80/sbtest/sbtest8.ibd

--差距：
SELECT 2831155200-2436890624;  -- 394264576
SELECT 2868903936-2831155200;   --  37748736

5.分析：
1.使用datetime和timestamp在物理磁盘占用上是等效的。
2.datetime(3)和datetime(6) 占用的磁盘空间是等效的。timestamp一样。
3.datetime(N)中的N占用的存储空间是一样的。
4.通过sysbench对上述表压测读写、只读、更新等指标差距不大。


结论：
官方文档的表述可能是很久没有更新了。根据测试得出如下结论：
1.datetime和timestamp在磁盘空间的占用上是一致的；
2.datetime和timestmap表微妙和毫秒的时候占用的磁盘空间是一致的。
3.推荐使用datetime类型，不会随着时区的改变变化；timestamp会随时间的变化查看的不一致，适用用跨国业务。
4.在MySQL5.7.22版本测试结论和MySQL 8.0.12版本一致。