《MySQL实战45讲》——学习笔记22 “大量短连接/慢查询/QPS突增的临时处理方案“

骑猪看日落 2023-09-23 18:05 42阅读 0赞

本篇介绍的内容主要是当MySQL遇到一些"突发问题"影响到性能时，站在DBA的视角，一般会怎么去解决问题；内容主要是偏经验向的问题及解决方案，并不深入一些技术知识点；

例如实际运维过程中可能碰到这样的情景：业务高峰期，生产环境的MySQL压力太大，没法正常响应，需要短期内、临时性地提升一些性能；这时业务开发负责人说，不管你用什么方案，让业务先跑起来再说；但，如果是无损方案的话，肯定不需要等到这个时候才上场；本篇介绍解决这类突发问题的临时方案，并着重说一说它们可能存在的风险；

#### 情况1：短连接风暴 ####

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

回忆下之前的知识：

1. MySQL可以分为Server层和存储引擎层两部分；连接器位于Server层；  
2. 连接器负责跟客户端建立连接、获取权限、维持和管理连接；  
3. 一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限；修改完成后，只有再新建的连接才会使用新的权限设置；  
4. 客户端如果太长时间没动静，连接器就会自动将它断开；  
5. 数据库里面，长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个；

MySQL建立连接的过程，成本是很高的；除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限；因此建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接；

因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的，这些资源会在连接断开的时候才释放；所以如果长连接累积下来，可能导致内存占用太大；建议：1. 定期断开长连接；2. MySQL 5.7及以上版本，每次执行一个比较大的操作后，通过执行 mysql\_reset\_connection 来重新初始化连接资源而无需做重连和权限验证；

当然，由于一些历史原因，当前的业务依旧是短连接方式；一旦数据库处理得慢一些，连接数就会暴涨，连接数超过max\_connections参数时，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”；对于被拒绝连接的请求来说，从业务角度看就是数据库不可用；也就是出现了"短连接风暴";

当"短连接风暴"还是发生了时，作为临时方案，该怎么办呢？

**方案1：先处理掉那些占着连接但是不工作的线程**

通过show processlist查看当前执行的进程，挑选显示为sleep状态的线程；此外，通过查看事务具体状态，SELECT \* FROM \`information\_schema\`.\`INNODB\_TRX\`; 优先kill掉创建已经很久还没提交的长事务；

要注意，上面的kill事务进程的方法是有损的；但是有时候，有损也比数据库对外不可用要好；

**方案2：减少连接过程的消耗**

一种可能的做法，使用–skip-grant-tables参数启动，让数据库跳过所有的权限验证阶段；但这种方法实际是“饮鸩止渴”，风险极高，尤其你的库外网可访问的话，就更不能这么做了；

> 实际上，我们在线上碰到更多的是**查询**或者**更新语句**导致的性能问题；其中，查询问题比较典型的有两类，一类是由新出现的**慢查询**导致的，一类是由**QPS（每秒查询数）突增**导致的；

####  ####

#### 情况2：慢查询性能问题 ####

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

*  索引没有设计好；
 *  SQL语句没写好导致未走索引；
 *  MySQL优化器选错了索引；

对于第一种情况，需要对线上的表执行DDL，如加索引；对于第二种情况，没办法，只能修改SQL语句重新发版；对于第三种情况，可以使用force index或改写SQL引导优化器选择预期的索引，当然也需要发版；

以上问题其实都可以在前期避免，例如执行计划、SQL代码评审、性能测试等；因此**建议上线前，**在测试环境，把慢查询日志（slow log）打开，并且把 long\_query\_time 设置成0，**确保每个语句都会被记录入慢查询日志；每条新的SQL语句都去生产环境分析下执行计划**；

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

#### 情况3：QPS突增问题 ####

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

如果是由一个新功能的bug导致的，当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复；而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用：

1. 一种是由全新业务的bug导致的；这种情况下，可以尝试关闭功能入口，如网络层的nginx屏蔽、服务端的功能开关等；

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

3. 如果这个新增的功能跟主体功能放在一起的，那么可以尝试使用查询重写功能，通过处理语句来限制；如把压力最大的SQL语句直接重写成"select 1"返回，不过要从业务逻辑上充分考虑这种改法的影响；

下篇文章：[《MySQL实战45讲》——学习笔记23 “binlog&redolog 的写入机制/组提交机制“][MySQL_45_23 _binlog_redolog]

本章参考：[22 | MySQL有哪些“饮鸩止渴”提高性能的方法？-极客时间][22 _ MySQL_-]

[MySQL_45_23 _binlog_redolog]: https://blog.csdn.net/minghao0508/article/details/128777866
[22 _ MySQL_-]: https://time.geekbang.org/column/article/75746