MySQL字段的时间类型该如何选择?千万数据下性能提升10%~30% _百...
发布网友
发布时间：2024-09-26 17:05
共1个回答
热心网友
时间：2024-10-04 07:52
根据节省空间的原则，当只需要存储年份、日期、时间时，可以使用year、date、time。如果需要详细的时间，可以选择datetime、timestamp或者使用整形存储时间戳。
以下是不同类型的格式、时间范围、占用空间相关信息。
本文主要概述datetime、timestamp与整形时间戳相关的内容，并在千万级别的数据量中测试它们的性能，最后总结出它们的特点与使用场景。
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）时，读取时，所有的timestamp都增加3小时。如果MySQL时区设置为系统时区（time_zone = SYSTEM）时，进行时区转换会调用系统函数，高并发下开销会很大。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跑满。搭建环境：查看是否开启函数创建，创建表，表中数据类型为bigint、datetime、timestamp进行测试（先不要创建索引，因为生成的时间是随机无序的，维护索引的开销会很大，等数据跑完后续再生成索引）。随机生成字符串的函数，随机生成整形的函数，编写插入函数，其中使用UNIX_TIMESTAMP函数将时间转化为时间戳存入bigint中，执行。我生成的是两千万条数据，想生成别的数量也可以设置call insert_datetime_test(1,10000000)。建索引根据时间段查询数据（需要回表）：与时间相关、最常见的功能就是根据时间段进行查询数据，比如想查询2022-10-10这一天的下单数据。为了模拟真实场景，这里将查询列表设置为*，让MySQL回表查询其他数据（回表：使用二级索引后，需要回表查询聚簇【主键】索引获取全部数据，可能导致随机IO）。根据时间段查询少量数据，由于数据量比较小，回表次数少、随机IO少，会更倾向于使用索引。三种类型查询时间差不多。根据时间段查询大量数据（数据量5.5W），一般也不会根据时间段一次性查这么多数据，主要是想看下性能。主要的性能开销是需要回表查数据，三种类型性能都差不多datetime > bigint > timestamp。由于回表的开销可能会影响我们的结果，因此还是要看不回表的案例。根据时间段查询数据（不回表）：测试不用回表时，三种类型的性能差异还是比较显著的，bigint > datetime > timestamp。但根据时间段不回表的查询场景还是比较少的，除非用联合索引，时间加上另一个需要的值。统计数量：根据时间统计数量的场景还是比较多的：统计某天、某月下单数量等...统计部分数据，统计所有数据，统计数量count可以直接使用二级索引，不需要回表，性能：bigint > datetime > timestamp。经过不回表的测试bigint是性能最好的，与datetime相比性能提升在10%~30%之间。
总结：当只需要存储年份、日期、时间时，可以使用year、date、time，尽量使用少的空间。datetime性能不错，方便可视化，固定时间，可以在不追求性能、方便可视化、不涉及时区的场景使用。timestamp性能较差，存储时间戳，涉及时区转换（如果是系统时区高并发下性能更差），有时间范围限制，还需要为未来准备解决方案（感觉比较鸡肋）。bigint性能最好，存储时间戳，不方便可视化，由自己自由转换时区，适合追求性能、国际化（时区转换）、不注重DB可视化的场景，还不用考虑时间范围，如果是短期不会超出2038年XX还可以使用空间更小的int整形。
