22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

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

MySQL建立连接的过程，成本是很高的，除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

在数据库压力比较小的时候，这些额外的成本不明显。但是，短连接模型存在一个风险，就是一旦数据库处理的慢一些，连接数就会暴涨。

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

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

那么，这种情况下，还有没有别的建议呢？有两种方法，但这些方法都是有损的。

第一种方法：先处理到那些占着连接但是不工作的线程

max_connections的计算，不是看谁在running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过kill connection主动踢掉。这个行为跟事先设置wait_timeout的效果是一样的。设置wait_timeout参数表示的是，一个线程空闲wait_timeout这么多秒之后，就会被MySQL直接断开连接。

但是要注意，在show processlist的结果里，踢掉显示为sleep的线程，可能是有损的。

所以，应该尽量断开事务外空闲的连接。那怎么判断事务外空闲呢？

show processlist

图中id=4和id=5的两个回话都是Sleep状态。而要看事务具体状态的话，你可以查information_schema库的innodb_trx表。

这个结果里，trx_mysql_thread_id=4，表示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参数打开，表示这时候数据库只能被本地客户端连接。

 

除了短连接数暴增可能会带来性能问题外，实际上，在线上碰到更多的是查询或者更新语句导致的性能问题。其中，查询问题比较典型的有两类，一个是有新出现的慢查询导致的，一类是由QPS（每秒查询数）突增导致的。

 

慢查询性能问题

MySQL中，会引发性能问题的慢查询，大体有以下三种可能：

1. 索引没设计好；
2. SQL语句没写好；
3. MySQL选错了索引。

索引没设计好

这种场景一般是通过紧急创建索引来解决。MySQL 5.6版本以后，创建索引都支持online DDL了。

比较理想的是能够在备库先执行。假设你现在的服务是一主一备，主库A、备库B，这个方案的大致流程是这样的：

1. 在备库B上执行set sql_log_bin=off，也就是不写binlog，然后执行alter table语句加上索引；
2. 执行主备切换；
3. 这时候主库是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，你可以通过下面的方式，增加一个语句改写规则。

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_fules()这个存储过程，是让新插入的新规则生效。也就是我们说的“查询重写”。可以通过下图中的方法来确认改写规则是否生效。

MySQL选错了索引

这时候，应急方案就是给这个语句加上force index。

同样的，使用查询重写功能，给原来的语句加上force index，也可以解决这个问题。

一般是由前两种情况导致的。

 

QPS突增问题

有时候由于业务突然出现高峰，或者应用程序bug，导致某个语句的QPS突然暴涨，也可能导致MySQL压力过大，影响服务。

新功能出现的bug，最理想的情况就是让业务把这个功能下掉，服务自然就会恢复。

下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用：

1. 一种是由全新业务的bug导致的。假设DB运维是比较规范的，也就是说白名单是一个个加的，这种情况下，如果能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的QPS就会变成0。
3. 如果这个新功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的SQL语句直接写成“select 1”返回。

当然，这个操作风险很高，可能存在两个副作用：

1. 如果别的语句也用到这个SQL语句模板，会有误伤；
2. 改成select 1，可能会导致后面的业务逻辑一起失败。

所以方案3是用于止血的。

 

上一篇：21 | 为什么我只改一行的语句，锁这么多？

下一篇：23 | MySQL是怎么保证数据不丢的？