vlambda博客
学习文章列表

在业务高峰期，生产环境的 MySQL 压力太大，没法正常响应，这时候就需要在短期内、临时性地提升一些性能。通常引发这种问题的因素有哪些？

短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。数据库的连接成本是很高的，除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限等。

这时存在一个风险，就是一旦数据库处理得慢一些，同时在线的连接数就会暴涨。我们可以通过 max_connections 参数来设置允许的最大连接数，当超过这个连接数时，就会报错提示“too many connections”，在业务上的看就是数据库不可用。

但是，一味的调大 max_connections 的值，也是有风险的，允许了更多的连接，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

那有什么方法可以比较好的解决呢？答案是完全无损的操作是没有的，对于无损的操作肯定在一开始就使用了，不会在业务出现性能问题时才使用。

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

对于那些不处于 running 状态的连接可以通过 kill connection 主动断开它。这个操作和事先设置 wait_timeout 效果差不多。（wait_timeout 的效果是当一个连接空闲设置的秒数之后就自动的断开它。）

当使用 show processlist 获取到了 sleep 状态的连接，是不是就可以随心所欲的 kill connection + id 干掉它？

最好的办法还是要通过 information_schema 库的 innodb_trx 表的查看一下

select * from information_schema.innodb_trx\G

因为有些连接还处于事务中，未提交的状态，如果直接的 kill 掉的话，MySQL 就需要对其回滚，也浪费了系统性能。

所以当发生这样问题的时候，应当优先断开事务外的连接，如果这样还不够的话再考虑断开事务内空闲太久的l。

注意：在断开连接的时候应该与业务配合，站在程序员的角度，我们在使用数据库连接的时候要判断其连接状态，断开的话就需要重连。避免连接被断开了却不做处理，认为“MySQL 一直都没有恢复”。

第二种方法：减少连接过程的消耗

有的业务代码会在短时间内先大量申请数据库连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限验证阶段。跳过权限验证的方法是：重启数据库，并使用 –skip-grant-tables 参数启动，这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。

这是一种极其危险的操作，尤其是对于外网可访问的数据库来说，更不能这么做了。

在 MySQL 8.0 版本里，如果你启用 –skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

慢查询性能问题

针对查询性能慢的问题，主要集中在一下三类上

○ 索引没有设计好；

○ SQL 语句没写好；

○ MySQL 选错了索引。

索引没有设计好

当出现因为索引设计出现慢查询性能问题时候，一般的操作是通过紧急创建索引来解决。对于高峰期数据库已经被这个语句打挂的情况，最高效的的做饭就是直接执行 alter table 。

如果在数据库还没有挂，在可以接收的状态下，比较理想的做法是先在备库下创建索引，然后主备切换。分三步走：

1. 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；

2. 执行主备切换；

3. 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

这是古老的 DDL 方案，但是在紧急情况下是效率最高的操作。在平时做变更的时候可以考虑使用

SQL 语句没写好

比如犯错在第 18 篇中的问题，导致了没能使用上索引。这时除了在业务端修改 sql 以外，在紧急情况下更行之有效的方法可以在 MySQL 通过 query_rewrite，把输入的一种语句该为另外一种模式。

例如语句错误的写成：

-- 这是错误的
select * from t where id + 1 = 10000;

这时候可以添加一条 query_rewrite.rewrite_rules 规则。

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 选错了索引

MySQL 选错索引通常就是在第 10 篇中提到的情况。

紧急情况下就是给语句加上 force index 来指定索引，同样可以使用查询重写功能。

导致慢查询性能问题，出现最多的是索引设计问题和SQL语句没写好(MySQL 选错索引的概率其实比较小的)。为了预发现这样的问题可以做如下操作：

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

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

3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。（我们在前面文章中已经多次用到过 Rows_examined 方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

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

QPS 突增问题

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

在当一个新的功能或者新的 App 的 bug 引起的 QPS 暴涨问题的时候。紧急情况下最理想的情况是让业务把这个功能下掉。

如果“业务端下掉这个功能”的响应速度比较慢，在也可以直接在数据库操作。

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

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

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

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

1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；

2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。

其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。

由此可见，更多的准备，做好规范的运维体系，往往意味着更稳定的系统。

交个胖友认识我~关注在看加分享~