UDN-企业互联网技术人气社区

板块导航

浏览  : 1025
回复  : 1

[资源分享] 揭秘MySQL Now函数、时区与夏令时

[复制链接]
瞌睡虫的头像 楼主
发表于 2016-7-7 09:09:35 | 显示全部楼层 |阅读模式
  
1.webp.jpg


  前言

  MySQL时区问题Inside君关注的并不多,仅知道timestamp带有时区属性。但MySQL夏令时问题怎么处理,终于有同学给了一个靠谱的说法。

  PS:Inside君亲授的2016年最好的MySQL数据库网络培训又来了即将开始,还剩最后2周时间,小伙伴们抓住最后的报名时间,目前微店有300元优惠券可供使用。参加过姜老师培训班,有些同学薪资都上涨了至少50%+,而有几位OracleDBA也都成功转型MySQLDBA。

  PS又PS:话说昨天发表的红颜弹指老,刹那芳华——写在实况足球20周年前还是勾起了不少同学的回忆,谁能组织下,来个弄个线下IT技术人的实况俱乐部の~~~

  正文

  now()是MySQL中使用频率非常高的函数,日常使用中基本不会遇到性能问题。不过,最近部署在美国的MySQL服务器却遇到了now()的性能问题,高并发下抓到的TopSQL都是SELECTnow();

  通常来说,使用now()或current_timestamp()等获取带有时区信息的时间时,数据库会在server层解析填充其field时,通过内核层将gmt时间转化为所需的time类型。而转换是通内核层的libc动态库的接口__tz_convert()来实现。这个实现一个致命的缺点是:串行化。若像支付宝这种压力比较大的场景性能问题就暴露了。MySQL安装好后默认使用的os的时区文件:

  dbadmin@(none)08:39:18>showvariableslike'%time_zone%';

  +------------------+--------+

  |Variable_name|Value|

  +------------------+--------+

  |system_time_zone|PDT|

  |time_zone|SYSTEM|

  +------------------+--------+

  知道了原因,解法自然是知道的,设置为一个固定的时区信息呗。当然,国内的MySQL是完全可以这样做的,配置文件改成default-timezone='+8:00',重启即可。因为在中国的法定时间中,是没有夏令时这个概念的(当然之前存在过非常短的一段时间,又被废了)。设置timezone后,不需要再通过__tz_convert()来转换,各个线程之间并无锁冲突,now()的影响将微乎其微。性能自然获得大大提升。公司有人实验过,高并发下now()性能有8倍之差。

  然而,当服务器放在美国,尤其是你的主要服务对象又是国际用户时,必须要考虑夏令时问题。不能直接设置为'-7:00'或'-8:00'了事,否则不能自动在夏令时和冬令时之间进行切换。

  那既然MySQL会读取操作系统的时区信息,我们可不可以直接设置time_zone为时区所在城市呢?这样由系统来帮我们解决夏令时问题。事实上,可以的,但是需要准备工作。我们来试一下:

  dbadmin@(none)08:06:45>settime_zone='Los_Angeles';

  ERROR1298(HY000):Unknownorincorrecttimezone:'Los_Angeles'

  dbadmin@(none)08:06:56>settime_zone='LosAngeles';

  ERROR1298(HY000):Unknownorincorrecttimezone:'LosAngeles'

  dbadmin@(none)08:08:44>settime_zone="America/Los_Angeles";

  ERROR1298(HY000):Unknownorincorrecttimezone:'America/Los_Angeles'

  看来貌似是不行,但是可以注意到报错信息是Unknownorincorrecttimezone。就是说数据库不知道这个时区信息,那数据库已知的时区信息存在哪里呢?

  mysql>showtableslike'%time_zone%';

  +-------------------------------+

  |Tables_in_mysql(%time_zone%)|

  +-------------------------------+

  |time_zone|

  |time_zone_leap_second|

  |time_zone_name|

  |time_zone_transition|

  |time_zone_transition_type|

  +-------------------------------+

  5rowsinset(0.01sec)

  上述5张表在数据库mysql目录下,运行SHOWTABLES命令即可看到。然后查询这5张表会发现内容全部为空,说明目前数据库没有存这些时区信息。根据官方文档的指引(http://dev.mysql.com/doc/refman/5.7/en/time-zone-support.html),通过下述命令可以将相关时区信息导入到MySQL的系统库:

  shell>mysql_tzinfo_to_sqltz_filetz_name|mysql-urootmysql

  mysql>SELECT*FROMtime_zone_name

  ->WHEREnameLIKE'America/%'limit3;

  +-------------------+--------------+

  |Name|Time_zone_id|

  +-------------------+--------------+

  |America/Adak|55|

  |America/Anchorage|56|

  |America/Anguilla|57|

  ......

  有人会问,这样不使用操作系统的时区信息,而是通过自己指定的方式,会不会失去夏令时机制?回答是:指定城市的方式不会失去,而直接指定-7:00会失去。咨询了下公司MySQL内核专家得到的答案为:目前在MySQL数据库中,在初始化time_zone相关表元信息以后,MySQL就可以自己完成夏令时(DaylightSavingTime)和闰秒(LeapSecond)的修正,不需要额外的服务器。

  比如今年的夏令时为,美国2016年夏令时将于2016年03月13日当地时间早上02:00(北京时间14:00)开始,至2016年11月6日结束,届时美国的交易时间将较冬令时提前1个小时:

  mysql>SELECTCONVERT_TZ('2016-03-1301:30:00','-8:00','America/Los_Angeles')ASTIME\G

  ***************************1.row***************************

  TIME:2016-03-1301:30:00

  1rowinset(0.00sec)

  mysql>SELECTCONVERT_TZ('2016-03-1302:30:00','-8:00','America/Los_Angeles')ASTIME\G

  ***************************1.row***************************

  TIME:2016-03-1303:30:00

  1rowinset(0.00sec)

  不过上图前提是你已经把时区信息注入到了MySQL,不然函数convert_tz会返回NULL值。

  当然还有一种解决办法,就是程序在连接时指定timezone。因为MySQL客户端在连接db时,如果不指定时区信息,会直接使用MySQL服务器端,这一点和Oracle不一样。但如果客户端在连接时指定的时区,则不会使用DB端的时区信息,这样也会减轻DB的压力,但是应用要进行改造,主要是统一数据源的改造。java的连接串中两个关键参数:

  useLegacyDatetimeCode=false&serverTimezone=America/Los_Angeles

  时间设置对Oracle、MySQLtimestamp的不同影响,敬请下回分解。

文章来源:InsideMySQL
文章作者:姜承尧

发表于 2016-7-7 09:28:57 | 显示全部楼层
顶         
使用道具 举报

回复

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部