游戏服务端开发中的MySQL进阶知识（三）--慢查询

本系列文章主要是本人在游戏服务端开发过程中，遇到的一些不那么为人熟知但我又觉得比较重要的MySQL知识的介绍。希望里面浅薄的文字能为了提供一点点的帮助。

慢查询

从我有限的从业经验来看，大多数数据库导致的游戏服务器问题，十有八九慢查询。比如：SQL写的乱七八糟导致的慢查询、表数据太多导致的慢查询、没有使用索引导致的慢查询、突然大并发量导致的慢查询等等。数据库一出现以上这些问题，开发团队免不了要背大锅。所以我认为数据库慢查询应该成为每个服务器开发人员必知必会的知识点。

慢查询事故分享：

这里我分享一下我参与的第一次慢查询定位和优化经历，这是一次非常惨痛的经历：

当时我所在的项目组上线了一个零点秒杀的活动，秒杀活动开启的第一个晚上有大量玩家投诉卡死在活动界面。我们当时按直觉判断肯定是太多人秒杀导致游戏服务器压力过大处理不过来，然后就决定发公告宣布延迟活动和重启游戏服务器。重启服务器之后，玩家再次进行秒杀问题还是如此。这时有个同事说，压力应该主要在MySQL服务器上应该重启MySQL服务器。然后我们又又发公告宣布延迟活动 + 重启游戏服务器 + 重启MySQL服务器。可能是因为大部分玩家都放弃的原因吧，这次问题终于解决。

后期我们进行了复盘发现问题是：游戏服务器后台会在零点执行一个定时统计任务，其中包含许多复杂的SQL语句（各种join、全表查询等等）。刚好这些SQL所操作的表又和秒杀活动需要操作的表完全重合，最终导致数据库大量慢查询、MySQL服务器假死、线上玩家卡在秒杀界面。而出问题那会项目组成员甚至不知道慢查询是个什么东西，只是单纯认为是并发量太大导致服务器压力太大负载不下，然后在没有准确定位根本原因的情况下就天真地用重启游戏服务器和MySQL服务器来解决问题。下图说明了但是MySQL服务器面临的两方面的压力：

image.png

第一次我们只重启游戏服务器，非但没有减轻MySQL服务器的压力，反而导致其压力变得更大了。这是因为：

1. 游戏服务器重启有大量在线玩家被强制下线，这些玩家的数据需要刷进数据库，加重服务器压力（本来上面那些慢查询还没执行完，现在又来新的了）；
2. 游戏服务器重启之后，零点执行的那个定时统计任务会自主判断是否要继续执行（由于上一次重启游戏服务器导致今天的任务中断，所以判断结果是还要执行一遍），这样对于MySQL而言就算是同时执行两个这个定时统计任务了。

在知道慢查询日志这个东西之后，我们还尝试去线上找这个日志来看看，但由于数据库默认不开启慢查询日志的打印，结果可想而知。不过我们还是模拟了当时线上的情况重新测试一遍（这次开了慢查询），表现和当时线上的状况一致。最终才将这个问题全部复盘。

有上面这个案例打底之后，接下就开始慢查询的介绍

慢查询是指那些执行时间过长且涉及行数过多的语句（一般超过配置的慢查询时间和涉及行数才算）。慢查询并不是一定是SELECT语句，其实所有的DML类型的SQL（INSERT、UPDATE、REPLACE、DELETE）都可能是慢查询。根据官网的说明，慢查询日志的监听和打印可能会是一个耗时任务，所以MySQL服务器是默认不开启的慢查询。

慢查询配置（官方文档）：

参数名	作用	默认值	备注
slow_query_log	是否开启slow log慢日志记录	OFF	默认关闭慢查询功能
slow_query_log_file	slow log日志的路径	host_name-slow.log	注意：MySQL服务器要对指定的目录有写权限
long_query_time	当SQL语句执行时间超过该配置时间则算是慢查询，如果有开启慢查询日志的话就会将慢查询SQL记录到慢查询日志中。	10	该值的取值范围是0-10，最小精度单位是微秒（比如可以配成0.001），如果该值配成0那就是所有SQL都是慢查询。
min_examined_row_limit	当一个SQL语句扫描行数小于该值时，不会计入到慢查询中	0	可以屏蔽掉一些偶发性干扰的慢查询（比如一句十分简单且用了索引的select语句也会因为抖动或其他因素变为慢查询，而实际上是没问题的）
log_queries_not_using_indexes	不走索引的查询是否被记录	OFF	不走索引的查询是否被记录，即默认是不走索引就不记录慢查询日志
log_throttle_queries_not_using_indexes	不走索引被记录的语句条数阈值	0	当一分钟不走索引慢查询记录数据超过该值就不再记录，每分钟清0。
log_slow_admin_statements	对服务器管理语句是否进行记录	OFF	管理语句有：ALTER TABLE, ANALYZE TABLE, CHECK TABLE等等，默认不记录
log_output	指定慢查询日志的打印目的地	FILE	该值可选项有FILE,TABLE,NONE（低版本可能没有TABLE这个选项），可以是一个也可以包含多个（多个的话就用逗号分隔，如：TABLE, FILE）。 选择TABLE记录到mysql库中的 slow_log表中； 选择FILE记录到日志文件；NONE禁用日志记录，如果存在NONE则其他均无效。

以上可以通过SHOW VARIABLES LIKE '%***%'这种形式进行查询，并且可以通过SET GLOBAL ***=?来进行动态修改。这些变量均是全局变量，对MySQL所有库所有用户生效。
查询配置示例：

image.png

我在上面介绍information_schema库的时候就有说过，能用SHOW VARIABLES LIKE '%***%'查询到的东西基本上也可以在information_schema库查到。而且上面这些配置都是全局的，所以通过information_schema库的GLOBAL_VARIABLES表也能查到（某些版本的MySQL可能是在performance_schema库中，可以通过：SELECT table_schema FROM information_schema.TABLES WHERE table_name = 'GLOBAL_VARIABLES'来查询到底在哪个库），如下图：

image.png
image.png

慢查询的判断流程：

这里想强调的是：MySQL服务器是否打印慢查询日志，不是只通过SQL语句的执行速度来判断的，还和上面这些参数有关（源码请戳）。这里上一张判断流程图说明一下：

image.png
判断是否要打印慢查询日志的方法在log.cc中：
image.png

日志格式（官方文档）：

我把long_query_time设置为0（SET GLOBAL long_query_time=0），打印格式设置为FILE和TABLE两种模式（SET GLOBAL log_output='FILE,TABLE'）。下面截图分别是慢查询日志和慢查询表的数据：

慢查询日志.png 慢查询表.png

这里说明一下几个关键的字段：

• Query_time：该SQL语句执行时长（秒），精确到微秒；
• Lock_time：该SQL持有锁的时长（秒），精确到微秒。单纯的select语句不会持有锁（这时该值为0）；
• Rows_sent：发送给客户端的数据行数
• Rows_examined：Server层扫描的数据行数

注意不管是慢查询日志还是慢查询表，记录的信息都是一条一条的慢查询SQL语句。这样的形式直接进行分析会非常麻烦（比如想知道某一条慢查询具体的耗时、锁持有时间，或者对某一张表的慢查询分析）。这里介绍一下业界比较常用的慢查询日志分析工具：

• mysqldumpslow：官方慢查询分析工具，会分析指定的慢查询日志并统计出同一慢查询SQL出现的次数、总耗时等信息（如下图），操作十分简单（Windows的需要安装perl才行，使用说明）。

image.png

• pt-query-digest：业界常用的慢查询日志分析工具。对分表分库的情况有很好的支持；定制化功能很多；并且该工具不止能分析慢查询日志，还能分析一般SQL日志（general_log）和binlog日志等。但是使用门槛比较高，如果应用在线上环境需要一定的上手时间。
• Anemometer：图形化界面，需要在pt-query-digest的基础上才能工作。搭建繁琐

业界对慢查询的通用的处理流程：

• 如果是使用云服务器，可以在MySQL云服务器上设置慢查询日志分析and告警，做到异常就邮件、钉钉甚至是电话告警，云服务厂商都有这个功能（阿里慢查询告警设置）；
• 每周甚至是每天汇总一份慢查询的统计自动发邮件给DBA、运维和开发同学；
• 对有问题的慢查询做分析（有一些慢查询是偶发性的，可以忽略），然后做归零处理。

如何避免和解决慢查询：

如何避免：

当数据库压力过大时（CPU和I/O），更容易出现慢查询。本来一些正常的操作也会因为阻塞而变成慢查询。有效降低数据库压力就是一个标本兼治的方案，慢查询的避免倒是顺带的。降低数据库压力有很多种，这里介绍下我认为比较有效的几种给大家参考一下：

• 使用缓存降低数据库压力：对于游戏服务器来说，更新和读取数据的频率是非常惊人的（很多炸服的游戏就是栽在这上面）。很多时候玩家一个操作可能涉及大量数据的更新或者是查询（比如：玩家登录需要加载大量玩家数据；战斗结束需要发放奖品和更新战斗记录等等）。如果这些操作都直接落到数据库上面，那么数据库的压力将十分巨大。
  通过引用缓存中间件（MongoDB、Redis）和游戏服务器进程缓存来合并玩家操作产生的数据库SQL、延迟回写数据库（延迟回写这个不同项目可能有不同要求），可以很大程度上改善这种情况。并且引入缓存之后，玩家读取相关数据也会命中缓存而不是直接打到数据库，这一点对降低服务器压力帮助是非常有效的。如果不想引入缓存中间件，那直接使用进程缓存也是可以的，不过要注意内存空间和释放的问题。许多大厂商对他们的产品（自研或代理）在击穿数据库这方面（即服务器绕过缓存直接访问数据库加载玩家数据）都有严格的要求和限制。他们都会极力避免代码中出现直接操作数据库的行为，最最重要的原因还是为了防止流量洪峰对数据库的直接冲击（即使是MySQL自己也有内置的采用LRU策略的buff pool内存缓存）。
  注意！虽然引入缓存有好处，但是也会有一定的风险。增加缓存不仅会增加系统复杂度，同时也意味着数据冗余，这很可能导致数据不一致。事实也是如此，大部分的回档、数据错乱等问题都是缓存部分代码有bug导致的，所以决定引入缓存之前要预留好充足的测试和压测的时间。

---

• 多服务器分摊压力：上面关于缓存这一点更多是从客户端（相对于数据库）来解决问题，同时我们也可以通过增强数据库服务器性能这一点来实现。比如使用主从模式下的读写分离、分库分表等方法来将压力分摊到多台数据库服务器上。

---

• 避免在线上跑统计：一般来说，游戏业务功能（即玩家直接交互的玩法）不会有过于复杂的SQL出现，无非就是把一个玩家的数据select出来、update一些之类的而且都会带上where条件，join这些操作基本没有。
  复杂SQL主要出现在一些统计work（比如：统计玩家付费和在线时长的关系）上。这些统计工作一般都会在夜深人静的时候偷偷在线上服务器跑，然后第二天把数据给到策划。但是总有一些夜深人不静的时候，比如上面我介绍的零点秒杀活动。一旦高并发+复杂SQL，出问题就是分分钟的事。
  这里给的建议是——尽量不要在线上环境跑这些统计work，把这个工作放到冷备库去做（当然这会有延迟），如果这一点做不到，那就尽量把一条大SQL拆分成多个简单的SQL分批执行，还是做不到的话那我建议跑work的时间定在凌晨3、4点。

---

• 写SQL的时候使用explain分析一下：写复杂SQL的时候使用explain分析SQL看看索引使用、复杂度等情况是非常必要的。根据分析结果和实际功能情况，该加索引的加索引，该拆分就拆分。如果不得不做一些复杂操作需要复杂的SQL，还是强烈建议将大SQL拆分成多个简单的SQL分批执行。不要对自己写SQL的本事盲目自信，要明确知道数据库给你的反馈。

如何解决：

上面说了如何预防慢查询，这里说下如果出现慢查询怎么办。

• 偶发慢SQL：首先要说明一下，不一定所有的慢查询都是异常能够优化的。有些时候MySQL服务器内部的一些动作（比如脏页落盘），会导致MySQL有抖动，这时是可能导致原本正常的SQL变成慢查询。只要SQL不复杂且有用索引、这条SQL只在慢查询中出现过一次并且MySQL当时的表现良好（比如当时的IO、CPU监控和慢查询数量），基本就能确认是这种情况。这种情况可以不用理会。
• SQL一直执行：这种情况一般是死锁导致的，这里可以使用show processlist查看MySQL线程池的状态和正在执行的SQL语句：
  image.png
  当然你也可以从information_schema库里的PROCESSLIST表找到这些信息（有些版本在performance_schema库中）：
  image.png
  其中Time字段代表线程执行在某个状态下的时长（这张表其他字段含义请戳）。当发现一条SQL异常准备，就可以通过kill命令和线程ID来kill掉这个线程，这样有问题的命令将会停止执行，但是要注意客户端是否有重试逻辑。注意如果是使用线程池方案的项目组，可以通过设置thread_pool_stall_limit参数来实现SQL语句最大执行时长的限制（该值仅对使用线程池方案的MySQL生效），至于线程池能否可以kill线程我还没试过，有兴趣的同学可以尝试一下。