玖叶教程网

前端编程开发入门

2024年04月23日

MySQL字段的时间类型该如何选择?千万数据下性能提升10%~30%??

前言

在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
limit20
  > 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少，会更倾向于使用索引

三种类型查询时间差不多

根据时间段查询大量数据 （数据量5.5W）

一般也不会根据时间段一次性查这么多数据，主要是想看下性能

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整形


作者：菜菜的后端私房菜
链接：https://juejin.cn/post/7290382189992165415

标签：mysqlrand 

作者:gojiuye , 分类:技术精选 , 浏览:16 , 评论:0

发表评论:

控制面板
您好，欢迎到访网站！
  查看权限
网站分类
最新留言