前言
在 MySQL 中时间类型的选择有很多,比如:date、time、year、datetime、timestamp...
在某些情况下还会使用整形 int、bigint 来存储时间戳
根据节省空间的原则,当只需要存储年份、日期、时间时,可以使用 year、date、time
如果需要详细的时间,可以选择 datetime、timestamp 或者使用整形来存储时间戳
以下是不同类型的格式、时间范围、占用空间相关信息
| 类型 | 格式 | 范围 | 空间(字节 Byte) |
| --- | --- | --- | --- |
| date | YYYY-MM-DD
| 1000-01-01
to9999-12-31
| 3 |
| time | hh:mm:ss
.fraction
| -838:59:59.000000
to 838:59:59.000000
| 3 |
| year | YYYY
| 1901
to 2155
| 1 |
| datetime | YYYY-MM-DD hh:mm:ss
[.fraction
] | 1000-01-01 00:00:00.000000
to 9999-12-31 23:59:59.499999
| 8 |
| timestamp | 存储为时间戳,显示为 YYYY-MM-DD hh:mm:ss
| 1970-01-01 00:00:01.000000
UTC to 2038-01-19 03:14:07.499999
UTC | 4 |
| int | 时间戳 | | 4 |
| bigint | 时间戳 | | 8 |
本篇文章主要概述 datetime、timestamp 与整形时间戳相关的内容,并在千万级别的数据量中测试它们的性能,最后总结出它们的特点与使用场景
datetime
datetime 不仅可以存储日期、时间,还可以存储小数点后续的毫秒等 YYYY-MM-DD hh:mm:ss
[.fraction
]
比如 datetime (3) 就可以保留三位小数 2023-04-22 20:47:32.000
当 datetime 不保留小数时使用 5 Byte,需要保留小数时多加 3 Byte,总共 8 Byte (5.6.X 之后)
datetime 是最常用的时间类型,在存储、读取的性能和数据库可视化方面都不错,但它只能展示固定的时间,如果在不同时区,看到的时间依旧是固定的,不会随着时间变化
timestamp 时间戳
MySQL 中的 timestamp 能有效的解决时区问题
timestamp 用于存储时间戳,在进行存储时会先将时间戳转换为 UTC
UTC 是世界统一时间,比如我们的时区为东八区,则是在 UTC 的基础上增加八小时
时间戳在进行存储时,先根据当前时区转换成 UTC,再转换成 int 类型进行存储
时间戳在进行读取时,先将 int 类型转换为 UTC,再转换为当前时区
当前时区指的是 MySQL 服务端本地时区,默认为系统时区,可以进行配置
当前时区发生变化时,读取时间戳会发生变化
比如我的服务端默认系统为东八区(+8:00),当我修改为(+11:00)
[mysqld]
default_time_zone = +11:00
读取时,所有的 timestamp 都增加 3 小时
如果 MySQL 时区设置为系统时区(time_zone = SYSTEM)时,进行时区转换会调用系统函数,高并发下开销会很大
@Resource
private JdbcTemplate jdbcTemplate;
@Test
/**
* 10个线程每次查询10次 一次查500条
* timestamp:11,978ms
* datetime:9,057ms
*/
void getTimestamp() throws BrokenBarrierException, InterruptedException {
String timestampSql = "select SQL_NO_CACHE test_timestamp from datetime_test where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00' order by test_timestamp limit 500;";
String dateTimeSql = "select SQL_NO_CACHE test_datetime from datetime_test where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00' order by test_datetime limit 500;";
CountDownLatch countDownLatch = new CountDownLatch(10);
long start = System.currentTimeMillis();
forQuery(timestampSql, countDownLatch);
countDownLatch.await();
//timestamp:11,978ms
System.out.println(MessageFormat.format("timestamp:{0}ms", System.currentTimeMillis() - start));
CountDownLatch countDownLatch2 = new CountDownLatch(10);
start = System.currentTimeMillis();
forQuery(dateTimeSql, countDownLatch2);
countDownLatch2.await();
//datetime:9,057ms
System.out.println(MessageFormat.format("datetime:{0}ms", System.currentTimeMillis() - start));
}
private void forQuery(String timestampSql, CountDownLatch countDownLatch) {
for (int j = 1; j <= 10; j++) {
new Thread(() -> {
for (int i = 0; i < 10; i++) {
jdbcTemplate.queryForList(timestampSql);
}
countDownLatch.countDown();
}).start();
}
}
timestamp 时间戳使用整形进行存储,占用 4Byte 空间
timestamp 范围有限'1970-01-01 00:00:01.000000'
UTC 到'2038-01-19 03:14:07.499999'
UTC ,2038 年 XX 后的时间需要其他解决方案进行处理
timestamp 当时区发生改变时读取数据会有变化,由于存储、读取都需要根据时区对数据进行转换,因此性能也会有一定的开销,同时由于时间有限,还需要提供超出时间后的解决方案
整形时间戳
上文说到 timestamp 存储时间戳使用整形来存储,只是存储、读取会将时间戳转换为当前时区的时间
其实我们还可以通过整形自己进行存储,比如使用 int 直接存储时间戳
但由于 int 整形只有 4B(时间范围有限),在未来可能无法进行存储时间,就需要其他方案解决
为了避免空间太小,可以直接使用 bigint 8B 进行存储
使用整形存储时间戳不需要转换成时区,因此没有转换的性能开销,但无法显示时间、可读性不好,可以由我们自由进行时区转换适合国际化
千万数据测试
为了比较 datetime、timestamp、bigint 的性能,我们需要先搭建环境
案例只测试 innodb 存储引擎有索引的情况,想测试其他情况的同学,可以使用以下脚本函数自由测试
首先拿出一个快过期的云服务器,然后在服务器上启动 MySQL,待会用函数狠狠的把它的 CPU 跑满
搭建环境
查看是否开启函数创建
#开启函数创建
set global log_bin_trust_function_creators=1;
#ON表示已开启
show variables like 'log_bin_trust%';
创建表,表中数据类型为 bigint、datetime、timestamp 进行测试
(先不要创建索引,因为生成的时间是随机无序的,维护索引的开销会很大,等数据跑完后续再生成索引)
CREATE TABLE `datetime_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`username` varchar(255) DEFAULT NULL,
`money` int(11) DEFAULT NULL,
`test_datetime` datetime DEFAULT NULL,
`test_timestamp` timestamp NULL DEFAULT NULL,
`test_bigint` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
随机生成字符串的函数
#分割符从;改为$$
delimiter $$
#函数名ran_string 需要一个参数int类型 返回类型varchar(255)
create function ran_string(n int) returns varchar(255)
begin
#声明变量chars_str默认'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
#声明变量return_str默认''
declare return_str varchar(255) default '';
#声明变量i默认0
declare i int default 0;
#循环条件 i<n
while i < n do
set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
set i=i+1;
end while;
return return_str;
end $$
随机生成整形的函数
#生成随机num的函数
delimiter $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$
编写插入函数
其中使用 UNIX_TIMESTAMP 函数将时间转化为时间戳存入 bigint 中
#插入 从参数start开始 插入max_num条数据(未使用startc)
delimiter $$
create procedure insert_datetime_test(in start int(10),in max_num int(10))
begin
declare i int default 0;
declare random datetime default '2022-01-01 00:00:00';
set autocommit = 0;
repeat
set i = i+1;
set random = DATE_ADD('2022-01-01 00:00:00', INTERVAL FLOOR(RAND() * 31536000) SECOND);
#SQL 语句
insert into datetime_test(username,money,test_bigint,test_datetime,test_timestamp)
values (ran_string(8),rand_num(),UNIX_TIMESTAMP(random),random,random);
until i=max_num
end repeat;
commit;
end $$
执行
#执行插入函数
delimiter ;
call insert_datetime_test(1,10000000);
我生成的是两千万条数据,想生成别的数量也可以设置 call insert_datetime_test(1,10000000)
建索引
alter table datetime_test add index idx_datetime(test_datetime);
alter table datetime_test add index idx_timestamp(test_timestamp);
alter table datetime_test add index idx_bigint(test_bigint);
根据时间段查询数据(需要回表)
与时间相关、最常见的功能就是根据时间段进行查询数据,比如想查询 2022-10-10
这一天的下单数据
为了模拟真实场景,这里将查询列表设置为 *,让 MySQL 回表查询其他数据
(回表:使用二级索引后,需要回表查询聚簇【主键】索引获取全部数据,可能导致随机 IO)
select SQL_NO_CACHE * from datetime_test
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
order by test_datetime
limit 20
> OK
> 时间: 0.038s
select SQL_NO_CACHE * from datetime_test
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
order by test_timestamp
limit 20
> OK
> 时间: 0.034s
select SQL_NO_CACHE * from datetime_test
where test_bigint >= 1665331200 and test_bigint <= 1665417600
order by test_bigint
limit 20
> OK
> 时间: 0.036s
由于数据量比较小,回表次数少、随机 IO 少,会更倾向于使用索引
三种类型查询时间差不多
一般也不会根据时间段一次性查这么多数据,主要是想看下性能
select SQL_NO_CACHE * from datetime_test
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 时间: 37.084s
select SQL_NO_CACHE * from datetime_test
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 时间: 39.558s
select SQL_NO_CACHE * from datetime_test
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 时间: 38.966s
主要的性能开销是需要回表查数据,三种类型性能都差不多 datetime > bigint > timestamp
由于回表的开销可能会影响我们的结果,因此还是要看不回表的案例
根据时间段查询数据(不回表)
select SQL_NO_CACHE test_datetime from datetime_test
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 时间: 8.478s
select SQL_NO_CACHE test_timestamp from datetime_test
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 时间: 9.063s
select SQL_NO_CACHE test_bigint from datetime_test
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 时间: 5.773s
测试不用回表时,三种类型的性能差异还是比较显著的,bigint > datetime > timestamp
但根据时间段不回表的查询场景还是比较少的,除非用联合索引,时间加上另一个需要的值
统计数量
根据时间统计数量的场景还是比较多的:统计某天、某月下单数量等...
统计部分数据
select SQL_NO_CACHE count(*) from datetime_test
where test_datetime >= '2022-10-10 00:00:00' and test_datetime <= '2022-10-11 00:00:00'
> OK
> 时间: 0.053s
select SQL_NO_CACHE count(*) from datetime_test
where test_timestamp >= '2022-10-10 00:00:00' and test_timestamp <= '2022-10-11 00:00:00'
> OK
> 时间: 0.078s
select SQL_NO_CACHE count(*) from datetime_test
where test_bigint >= 1665331200 and test_bigint <= 1665417600
> OK
> 时间: 0.049s
统计所有数据
select SQL_NO_CACHE count(*) from datetime_test
> OK
> 时间: 3.898s
select SQL_NO_CACHE count(*) from datetime_test
> OK
> 时间: 4.152s
select SQL_NO_CACHE count(*) from datetime_test
> OK
> 时间: 3.17s
统计数量 count 可以直接使用二级索引,不需要回表
性能:bigint > datetime > timestamp
经过不回表的测试 bigint 是性能最好的,与 datetime 相比性能提升在 10%30% 之间
总结
当只需要存储年份、日期、时间时,可以使用 year、date、time,尽量使用少的空间
datetime 性能不错,方便可视化,固定时间,可以在不追求性能、方便可视化、不涉及时区的场景使用
timestamp 性能较差,存储时间戳,涉及时区转换(如果是系统时区高并发下性能更差),有时间范围限制,还需要为未来准备解决方案(感觉比较鸡肋)
bigint 性能最好,存储时间戳,不方便可视化,由自己自由转换时区,适合追求性能、国际化(时区转换)、不注重 DB 可视化的场景,还不用考虑时间范围,如果是短期不会超出 2038 年 XX 还可以使用空间更小的 int 整形
原文链接