MySQL深入19-暂时提高数据库性能方案
# MySQL深入19-暂时提高数据库性能方案
业务高峰期，生产环境的MySQL压力太大，没法正常响应，需要短期内、临时地提高性能。
由于我们需要保证业务正常执行，这些临时方案是有一个选择性的，也是有一定风险的。
# 短链接风暴
正常的短链接就是连接数据库后，执行很少的SQL语句就断开了，下次又需要执行SQL语句的时候再连接。如果我们使用的是短连接，那么在业务高峰期的时候，就可能出现连接数突然暴涨。
MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。在数据库压力比较小的时候，这些额外的成本可能不明显，但是当连接数很多的时候，这些成本就比较重要了。
max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，如果无法进行连接，那么这个业务相当于就不可用了。
碰到这种情况时，一个比较自然的想法，就是调高 max_connections 的值。但这样做是有风险的。因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。
方案1：处理掉那些占着连接但是不工作的线程
max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。设置 wait_timeout 参数表示一个线程空闲了一段时间后，就被MySQL直接断开连接。
如果断开 session A 的连接，因为这时候 session A 还没有提交，所以 MySQL 只能按照回滚事务来处理；而断开 session B 的连接，就没什么大影响。所以，如果按照优先级来说，你应该优先断开像 session B 这样的事务外空闲的连接。
通过select * from information_schema.INNODB_TRX这个命令进行查看那一个线程处于事务中：
因此，如果是连接数过多，可以优先断开事务外空闲太久的连接；如果这样还不够，再考虑断开事务内空闲太久的连接。
从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
方案2：减少连接过程的消耗
数据库连接的时候权限认证这一阶段的消耗在连接压力大的时候也是不可忽视的。我们可以通过让数据库跳过权限验证阶段来减少连接过程的消耗。
跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。
但是这种虽然能一定程度上解决问题，但是风险很高，不建议使用。
# 慢查询性能问题
在MySQL中，引发性能问题的慢查询：
索引没有设计好；
SQL 语句没写好；
MySQL 选错了索引。
# 索引没有设计好
这种场景一般就是紧急创建索引来解决。对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。
比较理想的是能够在备库先执行，假设现在是主库A、备库B，方案流程如下：
- 在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
- 执行主备切换；
- 这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。
# SQL语句没写好
有时候我们的SQL语句没写好，导致索引失效了。
比如，语句被错误地写成了 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_rules() 这个存储过程，是让插入的新规则生效，也就是我们说的“查询重写”。
# MySQL选错了索引
这时候，应急方案就是给这个语句加上 force index。
同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。
# 小结
索引没设计好和语句没写好的两种情况是完全可以避免的，通过下面这个过程，我们就可以预先发现问题：
- 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
- 在测试表里插入模拟线上的数据，做一遍回归测试；
- 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。
# QPS突增问题
有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。
最理想的情况就是让业务把这个功能下掉，服务自然就会恢复。而下掉一个功能，如果从数据库端处理的话，对应不同的背景，有不同的方法可用：
一种是由全新业务的 bug 导致的。假设你DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。
对于方案3来说存在两个副作用：
- 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；
- 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。