麒麟v10 上部署 TiDB v5.1.2 生产环境优化实践
893
2023-05-31
MySQL 时间戳类型真的了解吗?早看早避坑!
日期类型是我们在数据库操作中一个较为常见的数据类型,TIMESTAMP 类型相信使用的朋友也不少,但是你真的了解它吗?
本文介绍在 MySQL 中使用 TIMESTAMP 类型遇到的一些潜在问题,最大时间限制相当于埋在未来的坑、因为系统的一些默认规则触发日期自动更新、默认系统时区的性能问题,发现问题的同时,后面也推荐了一些在日期上个人认为不错的方案,供参考。
安装 MySQL
推荐 Docker 的方式本机安装一个 MySQL,步骤也很简单,如下所示,对于学习还是很方便的,已安装的可忽略。
$ docker pull mysql $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 mysql $ docker exec -it mysql-test /bin/sh $ mysql -h localhost -u root -p
一个埋在未来的坑
假设,未来 2038 年某天的你,执行了一条 SQL 更新了一个时间,第一次值为 '2038-01-19 03:14:07' 成功了,第二次值为 '2038-01-19 03:14:08' 报错了说传的值是无效的,中间仅差了一秒,看着挺正常的一个 SQL 啊!Why?
# 第 1 次更新 $ UPDATE user SET birthday = '2038-01-19 03:14:07' WHERE id = 1; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 # 第 2 次更新 $ UPDATE user SET birthday = '2038-01-19 03:14:08' WHERE id = 1; ERROR 1292 (22007): Incorrect datetime value: '2038-01-19 03:14:08' for column 'birthday' at row 1
在 MySQL 中,由于 TIMESTAMP 类型占用的空间为 4 个字节,理论上其能够存储最大的日期为 “2038-01-19 03:14:07”,而在 MySQL 5.6 之后占用的内存空间为 7 个字节,可以精确到毫秒、微秒,但是这个最大日期并没有被改变。
所以我们上面多设置了一秒,就报错了,对于系统而言,哪怕多一点也是不行的,超了就是超了。
这个限制在 MySQL 官方 11.2.2 The DATE, DATETIME, and TIMESTAMP Types[1] 也有描述:
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
小心 TIMESTAMP 的自动更新
假设一张表有 name、birthday 这些字段,这里的自动更新是指当你修改了表中 name 这个字段,但是最后发现 birthday 这个字段被更新为了系统的当前时间。
并且这种情况并不总是会出现,它和 MySQL 系统里的一个规则 **explicit_defaults_for_timestamp** 有关,默认情况下该参数的值为 OFF。
通过以下命令查看。
$ show variables like '%explicit_defaults_for_timestamp%'; +---------------------------------+-------+ | Variable_name | Value | +---------------------------------+-------+ | explicit_defaults_for_timestamp | OFF | +---------------------------------+-------+
但是,这里容易潜在的埋一些坑,有些 MySQL 镜像直接将这个值改为了 ON 就是禁用了功能。例如,通过上面 Docker 方式安装的就已经禁用了该功能。
问题复现
为了复现和讲解这个问题,现在我需要将这个功能给放开,使用如下命令。
$ SET @@SESSION.explicit_defaults_for_timestamp = 'ON';
首先,让我们先创建一个数据库,和一个 user 表,注意下目前对生日字段的定义为 birthday TIMESTAMP NOT NULL。
$ CREATE DATABASE test; $ CREATE TABLE user( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, birthday TIMESTAMP NOT NULL, PRIMARY KEY (id) );
执行 DESC user; 命令,查看当前的表结构,发现 birthday 字段 Extra 这一列多了一些定义,Why?
DESC user; +----------+-------------+------+-----+-------------------+-----------------------------------------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+-------------------+-----------------------------------------------+ | id | bigint | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | birthday | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP | +----------+-------------+------+-----+-------------------+-----------------------------------------------+
如果设置为这样子,意思是修改数据,会把该类型对应的字段变为数据库当前的系统日期。
改规则下,并且一张表中仅有一个字段可以拥有该特性,如果设置两个会报错。
$ CREATE TABLE user( birthday TIMESTAMP NOT NULL, utime TIMESTAMP NOT NULL, ); // 运行之后会得到一个 show variables like '%explicit_defaults_for_timestamp%'; 错误。
往 user 表中插入一条数据。
$ INSERT INTO user(name, birthday) VALUES('Tom', NOW(6));
假设,目前时间点为 T1(当前 T1 的时间为 2021-01-01 06:10:27),查看当前 user 表中的数据。
$ SELECT * FROM user; +----+------+---------------------+ | id | name | birthday | +----+------+---------------------+ | 1 | Tom | 2021-06-06 06:10:27 | +----+------+---------------------+
假设,目前时间点为 T2(当前 T2 的时间为 2021-01-01 06:13:06) 更新 user 表中的 name 为 Tom2,看返回结果 Changed: 1 更新是成功的。
$ UPDATE user SET name = 'Tom2' WHERE id = 1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
再次查询,发现 birthday 字段的值被改变为了 T2 这个时间点,但是明明上面的 SQL 语句没有写更新 birthday 这个字段啊!Why?
$ SELECT * FROM user; +----+------+---------------------+ | id | name | birthday | +----+------+---------------------+ | 1 | Tom2 | 2021-06-06 06:13:06 | +----+------+---------------------+
解决方案
当 explicit_defaults_for_timestamp 这个规则开启时(其值为 OFF),如果我们没有对 TIMESTAMP 类型的字段显性赋值,更新时系统会为我们默认设置为系统当前时间。
如果不清楚这个问题,查找起来简直让人崩溃,明明 SQL 语句没有,还是被更新了。
大多数情况下,这并非我们想要的情况,怎么禁用?
方法一:修改系统参数
将 explicit_defaults_for_timestamp 的值修改为 'ON' 禁用掉该属性。
正在运行的,可以使用 SET @@SESSION.explicit_defaults_for_timestamp = 'ON'; 修改。这里又一个坑,经测试验证一旦表已创建,在设置是无效的。如果是在禁用该规则后创建的表,是可以的。
方法二:修改表结构
对于那些线上正在运行的无法修改的,总不能直接把表删了再改吧。
当 explicit_defaults_for_timestamp 属性为 OFF 的情况下也有两种方法可以禁用,需要修改表结构。
// 指定该列为 NULL,例如 $ ALTER TABLE user MODIFY birthday TIMESTAMP NULL。 // 使用 DEFAULT 为该列指定一个默认值,例如 $ ALTER TABLE user MODIFY birthday TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
最后,根据 MYSQL 官网文档 sysvar_explicit_defaults_for_timestamp[2] 的描述,这个非标准行为已被弃用,希望它们在未来的 MYSQL 版本中被删除。确实挺坑的一个行为,如果不熟读文档,很容易踩坑。
TIMESTAMP 性能问题
TIMESTAMP 类型支持时区转换,这个有利也有弊,当默认为操作系统时区时(time_zone=SYSTEM),查询系统 TIMESTAMP 类型的字段会调用系统时区做时区转换。而这个系统时区需要加锁来保证此时的操作系统时区没有被修改。
当出现并发访问时,势必会出现资源竞争,多线程的上下文切换消耗,性能也会出现下降,下文我们做个性能测试。
查看当前的时区信息,time_zone=SYSTEM 表示此时是操作系统的时区。
$ show variables like "%time_zone%"; +------------------+--------+ | Variable_name | Value | +------------------+--------+ | system_time_zone | UTC | | time_zone | SYSTEM | +------------------+--------+
时区修改
MySQL 默认使用系统时区,修改方法大致分为两种:使用 SQL 命令临时修改,修改配置文件这种是永久修改。
# SQL 命令修改 $ SET time_zone = 'Asia/Shanghai'; # 配置文件 $ vim /etc/mysql/my.cnf default-time_zone = 'Asia/Shanghai'
如果使用 Docker 的可以在 docker run 时修改。通过 -e TZ='Asia/Shanghai' 指定时区,但是这样发现 虽然 SELECT NOW() 没问题,但是执行 show variables like "%time_zone%" 命令 time_zone 还是显示的 SYSTEM。
$ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -e TZ='Asia/Shanghai' mysql
推荐修改文件,首先进入容器内执行 cat /etc/mysql/conf.d/mysql.cnf 命令查看默认配置,拷贝一份到自己的本机电脑,在执行 docker run 时挂载到容器内,这种方式好处是当你有多个配置需要修改时,都可以在配置文件里改。
配置文件也许是这样的:
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql secure-file-priv= NULL default-time_zone = 'Asia/Shanghai' # Custom config should go here !includedir /etc/mysql/conf.d/
最终 docker run 命令如下:
# 注意 /${root}/mysql.cnf 这个是你本机的配置地址 $ docker run -itd --name mysql-test -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v /${root}/mysql.cnf:/etc/mysql/my.cnf mysql
性能测试
MySQL 自带了一个压力测试工具 mysqlslap,可以模拟多个并发客户端来对 MySQL 做压力测试,还是挺不错的,写一些功能,想测试下基本的性能时还是可以用用的。
以下这个语句的意思是模拟 100 个客户端并发,共执行 100,0000 万次查询。
# --number-of-queries 总的测试查询次数 # -c 并发量,模拟多个客户端执行,下例模拟多个客户端执行 “SELECT NOW()” # --create-schema 代表自定义的测试库名称,就是 MySQL 中的数据库名称 $ mysqlslap -u root -p --number-of-queries=1000000 --create-schema=test -c 100 --query='SELECT NOW()'
下面是基于 mysqlslap 做的性能测试结果,在不同的时区下,分别所耗时间,单位(秒),很明显系统时区耗时更长些,两者直接的相差为 25%。这只是耗时上的差异,CPU 信息我没有去看。还有不同的电脑,测试出来的性能差距也会有差异。
------SystemAsia/Shanghaidifference
Average number of seconds to run all queries 35.55s 28.42s 25%
日期该怎么选择?
MySQL 中日期类型存储通常有 3 中方案,使用 INT、TIMESTAMP、DATETIME 下面分别简单总结下。
INT 类型
INT 类型来存储日期类型,存储的就是时间戳类型,例如 2021-01-01 06:10:27 的时间戳为 1609452627000。
数据库实际存储的是一串数字,这种好处是没有时间上下范围限制,性能也比 TIMESTAMP 好,但是这种性能是收效甚微的,一个不友好的问题是,当我们想查看数据做一些问题排查或数据分析时,通常不是很直观的。
TIMESTAMP 类型
TIMESTAMP 类型在存储时会先将本地时区时间转换为 UTC 的时区时间,再讲 UTC 时区时间转为 4 字节 INT 类型存储,本质是和 INT 一样的,都是存储为毫秒数。读取时再次反向的转换为时间戳 TIMESTAMP 类型,会做一些时间的格式化,看起来更直观些。
TIMESTAMP 类型比较大的一个问题是有最大的时间限制,能够有效存储的时间范围为 “'1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'”,2038 年这个时间说远也是很快的,这个是需要考虑的,别为将来埋坑。
TIMESTAMP 类型尽管 5.6 版本之后支持精确到微笑,毫秒后面 6 为,但是 2038 最大时间限制这个问题并没有解决。
它还有一个笔者个人认为隐藏很的问题是,当你把一个字段的定义为 birthday DATETIME NOT NULL 且触发了它的自动更新规则时,很容易掉坑里。可怕的是开发和生产环境配置不一致,这种问题前期就发现不了,除非踩过这个坑。
DATETIME 类型
它的时间范围为 '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. 这个时间目前是够我们用的了。当然,你要说我要存储 “三国时期张飞” 什么时候出生,那这 160 年生日也是存储不了的。
DATETIME 类型它不会存储时区信息,当然这个问题,也不一定义非要在数据层解决不可,也不是什么大不了的问题,想做这种国际化的跨时区的,由中间层服务(Node.js 就很适合)统一解决也可。我认为这个日期类型它能解决上面我们使用 TIMESTAMP 遇到的那些问题。
CREATE TABLE user( id BIGINT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, birthday DATETIME NOT NULL, ctime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), utime DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), PRIMARY KEY (id) );
假设,我们插入一条数据,birthday 传入 2039 年使用 DATETIME 是没问题的,同时也可以看下 ctime、utime 时间,这个精确度也是我们定义的。
+----+------+---------------------+----------------------------+----------------------------+ | id | name | birthday | ctime | utime | +----+------+---------------------+----------------------------+----------------------------+ | 1 | Tom | 2039-01-01 22:00:28 | 2021-01-01 22:00:28.112048 | 2021-01-01 22:00:28.112048 | +----+------+---------------------+----------------------------+----------------------------+
参考资料
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。