短连接风暴/慢查询/QPS突增问题空事务获取与事务判断sql

妖狐艹你老母 2022-03-25 14:56 403阅读 0赞

### 短连接风暴问题 ###

问题：业务高峰期，生产环境的 MySQL 压力太大，没法正常响应，需要短期内、临时性地提升一些性能  
方法一：优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。  
kill id;  
首先获取事务空闲太久的连接：show processlist，command为sleep的就会处于空闲状态的连接  
事务内连接和事务外连接区分：select \* from information\_schema.innodb\_trx\\G;  
所有的row中：  
trx\_mysql\_thread\_id=4，表示 id=4 的线程还处在事务中。  
为什么优先断开事务外的连接，现在假设有两个session  
session1  
begin;  
insert into t values(1,1);

sessionB  
select \* from t where id=1;

假设两个session都被mysql强制断开，session2还好，但是session1因为在事务中，并且事务没有被提交，所以session1的整个事务会被回滚，这个时候可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”。

方法二：  
跳过权限验证减少连接过程的消耗。  
有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。  
跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。  
这种方法特别风险极高，是我特别不建议使用的方案。尤其你的库外网可访问的话，就更不能这么做了。  
在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL 官方对 skip-grant-tables 这个参数的安全问题也很重视。

### 慢查询性能问题 ###

索引没有设计好；  
于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。  
比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：  
在备库 B 上执行 set sql\_log\_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；  
执行主备切换；  
这时候主库是 B，备库是 A。在 A 上执行 set sql\_log\_bin=off，然后执行 alter table 语句加上索引。  
这是一个“古老”的 DDL 方案。平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

SQL 语句没写好；  
改写 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() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。你可以用图 4 中的方法来确认改写规则是否生效。  
select \* from t where id+1=10000;  
show warnings;

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

由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，我们就可以预先发现问题。

上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long\_query\_time 设置成 0，确保每个语句都会被记录入慢查询日志；  
在测试表里插入模拟线上的数据，做一遍回归测试；  
观察慢查询日志里每类语句的输出，特别留意 Rows\_examined 字段是否与预期一致。（我们在前面文章中已经多次用到过 Rows\_examined 方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

### QPS 突增问题 ###

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。  
我之前碰到过一类情况，是由一个新功能的 bug 导致的。当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。  
而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。我这里再和你展开说明一下。  
一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。  
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成0。  
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。  
当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：  
如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；  
很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。  
 所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。

说明：部分内容借鉴于极客时间 mysql实战45讲。