MySQL实践篇-MySQL有哪些“饮鸩止渴”提高性能的方法？

数据库 评论

MySQL实践篇-MySQL有哪些“饮鸩止渴”提高性能的方法？

短连接风暴

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

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

在数据库压力很小的时候，这些额外的成本并不明显。

但是，短连接模型存在一个风险，就是一旦数据库处理的慢一些，连接数就会暴涨。

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的线程，可能是有损的。

上面这个例子里，如果断开sessionA的连接，因为这个时候sessionA还没有提交，所以MySQL只能按照回滚事务来处理；而断开sessionB的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先断开像sessionB这样的事务外空闲的连接

但是，怎么判断哪些是事务外的空闲呢？sessionC在T时刻之后的30秒执行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选错了索引；

接下来，具体分析一下这三种可能,以及应对的解决方案。

导致慢查询的第一种可能是，索引没有设计好。

导致慢查询的第一种可能是，索引没有设计好

这种场景一般就是通过紧急创建索引解决。MySQL5.6版本以后，创建索引都支持Online DDL了，对于这种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行alter table语句。

比较理想的是能在备库执行。假设你现在的服务是一主一备，主库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语句处理。MySQL5.7提供了query_rewrite功能，可以把输入的一种语句改写成另外一种模式。

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

1
2
3
4

 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，也可以解决这个问题。

上面讨论的由慢查询导致性能问题的三种情况，实际上出现做多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。

  1. 上线前，在测试环境，把慢查询日志(slow log)打开，并且把long_query_time设置成0，确保每个语句都会被记录入慢查询日志；
  2. 在测试表里插入模拟线上的数据，做一遍回归测试；
  3. 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期一致。

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。

如果新增的SQL语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的表结构设计，全量回归测试都是必要的。这时候，你需要工具帮你检查所有的sql语句的返回结果。可以使用开源工具pt-query-digest https://www.percona.com/doc/percona-toolkit/3.0/pt-query-digest.html

QPS突增问题

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

一类情况是，由一个新功能的bug导致的，当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

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

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

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

当然，这个操作的风险很高，需要特别细致。他可能存在两个副作用：

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

Vincent Xin从零开始的程序猿之路

努力变瘦的胖子。