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

现象

  1. 开发环境：win10 + windows 版 MySQL 8.0.19
  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 自身转换，大大提高了性能。

结论

有关时间字段，统一用时间戳（int类型）保存可以有效避免时区带来的问题。

参考