MySQL “让业务跑起来”-饮鸩止渴

业务高峰期，生产环境的MySQL压力太大，没法正常响应，需要短期内、临时性地提升一些性能；
用户的开发负责人说，不管你用什么方案，让业务先跑起来再说。是不是这句话很熟悉啊（嘿嘿嘿~）

今天我们就来聊聊这些临（yin）时（zhen）方（zhi）案（ke），并着重说一说它们可能存在的风险。

短链接风暴

正常的短连接模式就是连接到数据库后，执行很少的SQL语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

• 风险：

  • 就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections参数，用来控制一个MySQL实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

  • 在机器负载比较高的时候，处理现有请求的时间变长，每个连接保持的时间也更长。这时，再有新建连接的话，就可能会超过max_connections的限制。

  • 碰到这种情况时，一个比较自然的想法，就是调高max_connections的值。但这样做是有风险的。因为设计max_connections这个参数的目的是想保护MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到CPU资源去执行业务的SQL请求。

• 解决方案：

  • 先处理掉那些占着连接但是不工作的线程
    • max_connections的计算，不是看谁在running，是只要连着就占用一个计数位置。是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过kill connection主动踢掉。
    • 这个行为跟事先设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是，一个线程空闲wait_timeout这么多秒之后，就会被MySQL直接断开连接
    • 注意：在show processlist的结果里，踢掉显示为sleep的线程，可能是有损的；断开事务的连接，事务就回滚；可以按照优先级来断开事务外的空闲连接。
    • 判断方法：select * from information_schema.innodb_trx\G
    • 
      • 其中trx_mysql_thread_id:4, trx_state:RUNNING 表示id=4的线程还处在事务中
      • 因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接
      • 从服务端断开连接使用的是kill connection + id的命令， 一个客户端处于sleep状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”
      • 从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL一直没恢复”。

  • 减少连接过程的消耗

    • 有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。

    • 跳过权限验证的方法是：

      • 重启数据库，并使用–skip-grant-tables参数启动。这样，整个MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内
      • 在MySQL 8.0版本里，如果你启用–skip-grant-tables参数，MySQL会默认把 --skip-networking参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL官方对skip-grant-tables这个参数的安全问题也很重视

慢查询性能问题

• 索引没有设计好
• SQL语句没写好
• MySQL选错了索引

索引没有设计好

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6版本以后，创建索引都支持Online DDL了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table 语句。
比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的大致流程是这样的

• 在备库B上执行 set sql_log_bin=off，也就是不写binlog，然后执行alter table 语句加上索引；
• 执行主备切换
• 这时候主库是B，备库是A。在A上执行 set sql_log_bin=off，然后执行alter table 语句加上索引。

这个方法比较古老，新方式可以考虑github上gh-ost

语句没写好

这时，我们可以通过改写SQL语句来处理。MySQL 5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。

比如，语句被错误地写成了 select * from t where id + 1 = 10000，你可以通过下面的方式，增加一个语句改写规则。

mysql> insert into query_rewrite.rewrite_rules(pattern, replacement, pattern_database) values ("select from t where id + 1 = ?", "select from t where id = ? - 1", "db1");

call query_rewrite.flush_rewrite_rules();
这里，call query_rewrite.flush_rewrite_rules()这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。你可以用图中的方法来确认改写规则是否生效。

MySQL选错了索引

这时候，应急方案就是给这个语句加上force index。同样地，使用查询重写功能，给原来的语句加上force index，也可以解决这个问题。

如何避免前两种情况（索引没设计好和语句没写好）

• 上线前，在测试环境，把慢查询日志（slow log）打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；

• 在测试表里插入模拟线上的数据，做一遍回归测试；

• 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。（我们在前面文章中已经多次用到过Rows_examined方法了，相信你已经动手尝试过了。

QPS突增问题

有时候由于业务突然出现高峰，或者应用程序bug，导致某个语句的QPS突然暴涨，也可能导致MySQL压力过大，影响服务。最理想的情况是让业务把这个功能下掉；而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用

• 一种是由全新业务的bug导致的。假设你的DB运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

• 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的QPS就会变成0。

• 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的SQL语句直接重写成"select 1"返回

• 关于风险

  • 如果别的功能里面也用到了这个SQL语句模板，会有误伤
  • 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以select 1的结果返回的话，可能会导致后面的业务逻辑一起失败。

小结：

• 短链接风暴，可以通过kill connection + 线程id处理，优先处理空闲连接
• max_connections 的计算，不是看谁在running，是只要连着就占用一个计数位置
• 减少连接过程消耗：跳过权限验证，-skip=grant-tables参数重新启动数据库
• 慢查询
  • 索引问题：关掉binlog，依次备库、主库执行 alter table （成熟方案gh-ost）
  • SQL语句问题：MySQL 5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。
  • MySQL优化器选错索引 ：forceindex 、查询重写（query_rewrite）
• QPS突增：
  • 下掉业务
  • 去掉数据库的白名单
  • 删除业务种数据库账号
  • 把压力最大的SQL语句直接重写成 select 1 返回