--- theme: healer-readable --- ### 前言在MySQL中时间类型的选择有很多，比如：date、time、year、datetime、timestamp... 在某些情况下还会使用整形int、bigint来存储时间戳根据节省空间的原则，当只需要存储年份、日期、时间时，可以使用year、date、time 如果需要详细的时间，可以选择datetime、timestamp或者使用整形来存储时间戳以下是不同类型的格式、时间范围、占用空间相关信息 | 类型 | 格式 | 范围 | 空间（字节Byte） | | --------- | ----------------------------------------- | ------------------------------------------------------------ | ---------------- | | date | *`YYYY-MM-DD`* | `1000-01-01` to`9999-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，再转换为当前时区** ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/923f796b39e34b798c772c7acb85461b~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=1163&h=241&s=12588&e=png&b=fcf6f4) 当前时区指的是MySQL服务端本地时区，默认为系统时区，可以进行配置当前时区发生变化时，读取时间戳会发生变化比如我的服务端默认系统为东八区（+8:00），当我修改为（+11:00） ```shell [mysqld] default_time_zone = +11:00 ``` 读取时，所有的timestamp都增加3小时 ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/194e7ec3b43c4a6b9c981f08d009c8bd~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=381&h=138&s=8328&e=png&b=fcfbfb) 如果MySQL**时区设置为系统时区（time_zone = SYSTEM）时，进行时区转换会调用系统函数，高并发下开销会很大** ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/0f91a19eb6574f06b16fde6d39494b76~tplv-k3u1fbpfcp-jj-mark:0:0:0:0:q75.image#?w=320&h=203&s=6739&e=png&b=fdfdfd) ```java @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跑满 #### 搭建环境查看是否开启函数创建 ```sql #开启函数创建 set global log_bin_trust_function_creators=1; #ON表示已开启 show variables like 'log_bin_trust%'; ``` 创建表，表中数据类型为bigint、datetime、timestamp进行测试（先不要创建索引，因为生成的时间是随机无序的，维护索引的开销会很大，等数据跑完后续再生成索引） ```sql 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; ``` 随机生成字符串的函数 ```sql #分割符从;改为$$ 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 根据时间段查询少量数据 ```sql 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）一般也不会根据时间段一次性查这么多数据，主要是想看下性能 ```sql 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 由于回表的开销可能会影响我们的结果，因此还是要看不回表的案例 #### 根据时间段查询数据（不回表） ```sql 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 但根据时间段不回表的查询场景还是比较少的，除非用联合索引，时间加上另一个需要的值 #### 统计数量根据时间统计数量的场景还是比较多的：统计某天、某月下单数量等... 统计部分数据 ```sql 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 ``` 统计所有数据 ```sql 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整形（也可以使用无符号int）** ### 最后（不要白嫖，一键三连求求拉~） 😁我是菜菜，热爱技术交流、分享与写作，喜欢图文并茂、通俗易懂的输出知识 📚在我的博客中，你可以找到Java技术栈的各个专栏：Java并发编程与JVM原理、Spring和MyBatis等常用框架及Tomcat服务器的源码解析，以及MySQL、Redis数据库的进阶知识，同时还提供关于消息中间件和Netty等主题的系列文章，都以通俗易懂的方式探讨这些复杂的技术点 🏆除此之外，我还是掘金优秀创作者、腾讯云年度影响力作者、华为云年度十佳博主.... 👫我对技术交流、知识分享以及写作充满热情，如果你愿意，欢迎加我一起交流（vx:CaiCaiJava666），也可以持续关注我的公众号：菜菜的后端私房菜，我会分享更多技术干货，期待与更多志同道合的朋友携手并进，一同在这条充满挑战与惊喜的技术之旅中不断前行 🤝如果觉得菜菜写的不错，可以点赞、关注、收藏支持一下~ 本篇文章被收入专栏 [由点到线，由线到面，构建MySQL知识体系](https://juejin.cn/column/7158476727939760135)，感兴趣的同学可以持续关注喔本篇文章笔记以及案例被收入 [gitee-StudyJava](https://link.juejin.cn?target=https%3A%2F%2Fgitee.com%2Ftcl192243051%2FStudyJava)、 [github-StudyJava](https://link.juejin.cn?target=https%3A%2F%2Fgithub.com%2FTc-liang%2FStudyJava) 感兴趣的同学可以stat下持续关注喔~