windows 下开发测试没有问题,上线后(CentOS 服务器)发现页面上显示的时间比实际时间少了13小时,来看看到底怎么回事儿。

现象

  1. 开发环境:win10 + windows 版 MySQL 8.0.119
  2. 服务器环境:CentOS7 + MySQL 8.0.19
  3. SpringMvc + Hibernate + JQuery + Bootstrap,前后端分离开发
  4. 服务器上 Nginx 代理 Tomcat
  5. 上线后,类型为 timestamp 的字段的值,在保存前取的系统时间,系统时间正常,保存之后,比系统时间少了13小时。

排查

刚开始排查时,只是通过页面来查看时间,后来发现,这样排查是不科学的,通过查看数据库里保存的值与页面上显示的值,才发现从数据库里取出来的值,到页面显示出来,也有可能相差13小时或者14小时,而这些差异,取决于:

  1. MySQL 时区
  2. 操作系统时区
  3. 数据库连接参数 serverTimezone。

各种搭配测试结果

费话不多说,以下是测试情况

序号 MySQL 时区 CentOS 时区 连接参数 serverTimezone 保存到数据库的值 读出来的值
1 +8:00 Asia/Shanghai America/New_York 比实际时间少13小时 比数据库多13小时
2 +8:00 Asia/Shanghai Asia/Shanghai 与实际时间一致 与数据库一致
3 +8:00 Asia/Shanghai 删除该参数 与实际时间一致 与数据库一致
4 +8:00 America/New_York America/New_York 比实际少13小时 与数据库一致
5 +8:00 America/New_York Asia/Shanghai 与实际时间一致 比数据库少13小时
6 +8:00 America/New_York 删除该参数 与实际时间一致 比数据库少13小时
7 默认 America/New_York America/New_York 比实际少13小时 与数据库一致
8 默认 America/New_York Asia/Shanghai 与实际时间一致 比数据库少13小时
9 默认 America/New_York 删除该参数 比实际少14小时 比数据库多1小时
10 默认 Asia/Shanghai America/New_York 比实际少13小时 比数据库多13小时
11 默认 Asia/Shanghai Asia/Shanghai 与实际时间一致 与数据库一致
12 默认 Asia/Shanghai 删除该参数 比实际少14小时 比数据库多14小时

注:应用程序读取数据库中 timestamp 字段的值,在修改服务器时区之后,要重启 Tomcat 之后才会生效。

总结

从上表可以看出,要想让 timestamp 字段保存到数据库的值与读出来的值跟北京时间(时区定义的是中国东八区时间)一致,只有序号 2、3、11 这三种配置才可以,更进一步总结:

  1. MySQL 时区与服务器(这里是CentOS)时区均为中国东八区,MySQL 连接参数要么也是东八区,要么不指定(删除 serverTimezone 参数)。
  2. 服务器(这里是CentOS)时区与数据库连接参数均为中国东八区,MySQL 数据库时区可以保持默认配置。

从上表还可以看出,timestamp 字段的值:

  1. 服务器(这里是CentOS)时区与连接参数 serverTimezone 时区一致的情况,读出来的值与数据库一致,否则会自动做转换。
  2. 保存到数据库的值与连接参数 serverTimezone 指定的时区有关,如果没有该参数,则与 MySQL 默认时区有关。

以下,介绍相关知识点。

相关知识点

MySQL 时区

查看时区:

1
2
3
4
5
6
7
8
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | |
| time_zone | SYSTEM |
+------------------+--------+
2 rows in set, 1 warning (0.00 sec)

MySQL 的时区默认与系统时区一致。

配置 MySQL 默认时区

MySQL使用的time_zone属性是UTC时间即:+00:00,而北京时间比UTC时间早8小时,即:UTC+08:00

永久修改

1
vi /etc/my.cnf

[mysqld] 下面添加一行:default-time_zone = '+8:00'

重启 MySQL 生效:systemctl restart mysqld

临时修改

mysql> set time_zone = ‘+8:00’;
mysql> set global time_zone = ‘+8:00’;

  • 优点:立即生效,不用重启 MySQL
  • 缺点:重启 MySQL 后会失效

配置默认时区后

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> show variables like '%time_zone%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | +08:00 |
+------------------+--------+
2 rows in set (0.03 sec)

mysql> select @@global.time_zone,@@session.time_zone,@@global.system_time_zone;
+--------------------+---------------------+---------------------------+
| @@global.time_zone | @@session.time_zone | @@global.system_time_zone |
+--------------------+---------------------+---------------------------+
| +08:00 | +08:00 | CST |
+--------------------+---------------------+---------------------------+
1 row in set (0.00 sec)

时区概念

地球总是自西向东自转,东边总比西边先看到太阳,东边的时间也总比西边的早。东边时刻与西边时刻的差值不仅要以时计,而且还要以分和秒来计算。整个地球分为二十四时区,每个时区都有自己的本地时间。在国际无线电通信场合,为了统一起见,使用一个统一的时间,称为通用协调时(UTC, Universal Time Coordinated)。UTC与格林尼治平均时(GMT, Greenwich Mean Time)一样,都与英国伦敦的本地时相同。

关于时间的几个标准:

  1. CST:中国标准时间(China Standard Time),这个解释可能是针对 RedHat Linux。
  2. JST:日本标准时间(Japan Standard Time)。
  3. UTC:协调世界时,又称世界标准时间,简称UTC,从英文国际时间/法文协调时间”Universal Time/Temps Cordonné”而来。中国大陆、香港、澳门、台湾、蒙古国、新加坡、马来西亚、菲律宾、澳洲西部的时间与UTC的时差均为+8,也就是UTC+8。
  4. GMT:格林尼治标准时间(旧译格林威治平均时间或格林威治标准时间;英语:Greenwich Mean Time,GMT)是指位于英国伦敦郊区的皇家格林尼治天文台的标准时间,因为本初子午线被定义在通过那里的经线。

我们国家跨越了东五区、东六区、东七区、东八区、东九区五个时区,一般都统一采用东八区计时时间。

查看 CentOS 服务器时区

1
2
[utomcat@ebs-60027 ~]$ date -R
Sun, 23 Feb 2020 21:55:49 +0800

上面命令 date -R 输出了 +0800 表示东八区,也就是我们国家的时间。相反,如果是 -0800 表示美国旧金山所在的时区,西八区。
我们在安装Linux操作系统的时候,如果地区选择了Asia/Shanghai,那么系统的时区就是东八区。

timedatectl

  • 通过 timedatectl 可以查看服务器具体的时区
  • timedatectl set-timezone timezone 来设置时区
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    [root@ebs-60027 ~]# timedatectl set-timezone Asia/Shanghai
    [root@ebs-60027 ~]# timedatectl
    Local time: Sun 2020-02-16 10:23:04 CST
    Universal time: Sun 2020-02-16 02:23:04 UTC
    RTC time: Sun 2020-02-16 02:23:04
    Time zone: Asia/Shanghai (CST, +0800)
    NTP enabled: n/a
    NTP synchronized: no
    RTC in local TZ: no
    DST active: n/a
    [root@ebs-60027 ~]# timedatectl set-timezone America/New_York
    [root@ebs-60027 ~]# timedatectl
    Local time: Sun 2020-02-16 10:56:48 CST
    Universal time: Sun 2020-02-16 02:56:48 UTC
    RTC time: Sun 2020-02-16 02:56:48
    Time zone: America/New_York (CST, +0800)
    NTP enabled: n/a
    NTP synchronized: no
    RTC in local TZ: no
    DST active: n/a

MySQL timestamp 类型 和 datetime 类型

  • timestamp类型:会自动把时间转成 gmt 时间存储时间,取出时,又会自动转换成服务器的时区。
  • datetime类型: 没有时区概念,对于有跨国业务的数据库,存储时统一转换成 gmt 格式存储,取出时指定时区显示。

关于时区的调优

  • 对于使用timestamp的场景,MySQL 在访问 timestamp 字段时会做时区转换,当time_zone 设置为 system (默认)时,MySQL 访问每一行的 timestamp 字段时,都会通过 libc 的时区函数,获取 Linux 设置的时区,在这个函数中会持有 mutex,当大量并发 SQL 需要访问 timestamp 字段时,会出现 mutex 竞争。MySQL 访问每一行都会做这个时区转换,转换完后释放 mutex,所有等待这个 mutex 的线程全部唤醒,结果又会只有一个线程会成功持有 mutex,其余又会再次 sleep,这样就会导致 context switch 非常高但 qps 很低,系统吞吐量急剧下降。
  • 就是当参数设置 time_zone=system 的时候,查询 timestamp 字段,会调用系统的时区做时区转换,有全局锁 _libc_lock_lock 的保护,导致线程并发环境下,系统性能受限。如果 time_zone=’+8:00’则不会调用系统时区,则不会触发系统时区转换,使用 MySQL 自身转换,大大提高了性能。

参考