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

这篇具有很好参考价值的文章主要介绍了MySQL字段的时间类型该如何选择?千万数据下性能提升10%~30%🚀。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

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字段的时间类型该如何选择?千万数据下性能提升10%~30%🚀

当前时区指的是MySQL服务端本地时区,默认为系统时区,可以进行配置

当前时区发生变化时,读取时间戳会发生变化

比如我的服务端默认系统为东八区(+8:00),当我修改为(+11:00)

[mysqld]
default_time_zone = +11:00

读取时,所有的timestamp都增加3小时

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

如果MySQL时区设置为系统时区(time_zone = SYSTEM)时,进行时区转换会调用系统函数,高并发下开销会很大

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

	@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少,会更倾向于使用索引

三种类型查询时间差不多

根据时间段查询大量数据 (数据量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整形

最后(不要白嫖,一键三连求求拉~)

本篇文章被收入专栏 由点到线,由线到面,构建MySQL知识体系,感兴趣的同学可以持续关注喔

本篇文章笔记以及案例被收入 gitee-StudyJava、 github-StudyJava 感兴趣的同学可以stat下持续关注喔~

案例地址:

Gitee-JavaConcurrentProgramming/src/main/java/G_ThreadLocal

Github-JavaConcurrentProgramming/src/main/java/G_ThreadLocal

有什么问题可以在评论区交流,如果觉得菜菜写的不错,可以点赞、关注、收藏支持一下~

关注菜菜,分享更多干货,公众号:菜菜的后端私房菜

本文由博客一文多发平台 OpenWrite 发布!文章来源地址https://www.toymoban.com/news/detail-711585.html

到了这里,关于MySQL字段的时间类型该如何选择?千万数据下性能提升10%~30%🚀的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL相关的SQL语句、数据库、数据表、字段、类型

    1、 SQL 语句不区分大小写。 SQL语句 用途 描述 mysql -u root -p 连接 MySQL 在命令行窗口中输入 mysql -u root -p 命令,回车,然后输入 MySQL 密码(不要忘记了密码,找回麻烦),再回车就连接上 MySQL 了。最初都是使用 root 用户登录,工作中不能一直使用 root 用户登录。因为 root 权限太

    2024年02月13日
    浏览(95)
  • mysql数据库存储手机号字段选择bigint还是char?

    在MySQL中,存储手机号可以使用多种数据类型,包括bigint、char、varchar等。不同的数据类型有不同的优缺点,因此需要根据实际情况选择合适的数据类型。 bigint类型 bigint类型是一种整数类型,可以存储很大的整数,范围为-9223372036854775808到9223372036854775807。因此,如果使用big

    2024年02月11日
    浏览(80)
  • Java项目中,MySQL数据库中的时间字段用哪个?

    1.timestamp:                 自带时区属性,该类型的字段会随着服务器时区的变化而变化,自动换算成相应时区的时间,存储形式为:yyyy-MM-dd HH:mm:ss,对应的Java类型为java.sql.Timestamp                 缺点:只能存储到2038年的时间。 2.datetime:                 不具备时区

    2024年02月10日
    浏览(43)
  • 【MySQL】MySQL 数据类型,数值、日期和时间、字符串类型,创建数据表,删除数据表

    作者简介: 辭七七,目前大一,正在学习C/C++,Java,Python等 作者主页: 七七的个人主页 文章收录专栏: 七七的闲谈 欢迎大家点赞 👍 收藏 ⭐ 加关注哦!💖💖 MySQL 中定义数据字段的类型对你数据库的优化是非常重要的。 MySQL 支持多种类型,大致可以分为三类:数值、日

    2024年02月15日
    浏览(83)
  • 【⑬MySQL | 数据类型(一)】简介 | 整数 | 浮点 | 定点 | 时间/日期类型

    ✨欢迎来到小K的MySQL专栏,本节将为大家带来MySQL数据类型简介 | 整数 | 浮点 | 定点 | 时间/日期类型的分享 ✨ 0.数据类型简介 数据类型(data_type)是指系统中所允许的数据的类型。MySQL 数据类型定义了列中可以存储什么数据以及该数据怎样存储的规则。 数据库中的每个列都

    2024年02月11日
    浏览(39)
  • 【Flink-Kafka-To-Mongo】使用 Flink 实现 Kafka 数据写入 Mongo(根据对应操作类型进行增、删、改操作,写入时对时间类型字段进行单独处理)

    需求描述: 1、数据从 Kafka 写入 Mongo。 2、相关配置存放于 Mysql 中,通过 Mysql 进行动态读取。 3、此案例中的 Kafka 是进行了 Kerberos 安全认证的,如果不需要自行修改。 4、Kafka 数据为 Json 格式,获取到的数据根据操作类型字段进行增删改操作。 5、读取时使用自定义 Source,写

    2024年02月22日
    浏览(52)
  • sql字段类型和时间格式转换

    在SQL中,字段类型格式转换是指将数据从一种数据类型转换为另一种数据类型的过程。这通常涉及将字符串转换为日期、将数字转换为字符串、将整数转换为浮点数等。 SQL字段类型格式转换的原理如下: 显式转换:显式转换是指用户明确指定要执行的数据类型转换。这可以

    2024年02月08日
    浏览(45)
  • 千万级数据深分页查询SQL性能优化实践

    如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查

    2024年02月11日
    浏览(59)
  • Java中处理千万级数据的最佳实践:性能优化指南

    在今天的数字化时代,处理大规模数据已经成为许多Java应用程序的核心任务。无论您是构建数据分析工具、实现实时监控系统,还是处理大规模日志文件,性能优化都是确保应用程序能够高效运行的关键因素。本指南将介绍一系列最佳实践,帮助您在处理千万级数据时提高

    2024年02月03日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包