MYSQL45讲学习笔记

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

我们在实际开发中可能会遇到这种情况：业务高峰期，MYSQL压力较大，客户端长时间没有收到响应。

这时可能需要一些短时间内提高性能的方法，让业务功能暂时恢复。

短连接风暴

短连接模式就是在连接上MYSQL之后，执行很少的SQL语句就重连，下次需要再重连。

如果使用的是短连接，在业务高峰期，连接数可能会激增。

MYSQL连接的成本是相当高的，除了网络的三次握手之外，还需要做登录权限验证和读写权限验证，在数据库压力比较小的时候，这些额外的成本并不明显。

使用短连接模型有一个隐患，就是在业务高峰期，一旦数据库处理的慢一些，连接数就会暴涨，MYSQL的max_connections参数，用来控制一个MYSQL实例能够同时存在的连接数上限，一旦超过这个数值，系统就会拒绝之后的连接，并且报 "Too mant connections" 错误，从业务角度来看就是数据库不可用。

出现这个问题，我们有什么解决办法，是否可以尝试将 max_connections 参数调大？

如果我们将 max_connections 参数调大，那么后来的请求可以连接到系统，但是这样可能会进一步增大系统的负载，系统资源会大量消耗在 登录权限验证以及读写权限验证上，并且会使原来已经连接的进程无法拿到CPU资源 去处理SQL请求。

那么我们还有什么办法呢？

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

max_connections 参数的计数规则是，不论线程是不是 running 状态，只要连接上就计数，因此我们可以通过 kill connection + id 来主动断开那些不工作的线程。这和事先设置 wait_timeout 的效果是一样的，wait_timeout 指线程空闲多少秒就主动断开。

但是我们主动 kill connection 是有损的，我们下面来看个例子。

在这个例子里，如果直接踢掉 session A ,那么session A会被回滚；而 session B断开连接，就没什么大影响。所以我们应该优先踢掉 session B,换言之，我们应该优先 踢掉事务外空闲的连接，而后在考虑事务内的连接。

那么如何查看连接是在事务内还是事务外呢？

我们可以通过 show processlist 命令来查看，这是 session C 在T时刻30S 查询出来的结果。

图中 id = 4 和 id = 5 的会话都是sleep状态，那么如何查看事务具体的状态呢？

我们可以查看 information_scheme.innodb_trx表。

上图显示 trx_mysql_thread_id 为4，表示我们 ID 为4 的线程处在事务中。

我们使用 “kill conneciont + id”踢掉线程时需要注意，我们主动断开连接后，客户端并不会立刻知道，会在下一次请求时报“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

这个从应用端的角度看就是，MYSQL服务不可用，因此我们在执行  “kill conneciont + id”操作时，应该通知到相关的团队。

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

我们可以跳过连接时的登录权限验证和读写权限验证。

重启数据库，并使用参数-skip-grant-tables ，这样整个MYSQL数据库会跳过权限验证。

但是这样做，风险极高，一般不推荐使用。如果系统是外网服务，那就更不能这样做了。

除了短连接暴增带来的性能问题外，实际上开发中遇到的更多的是 慢查询和QPS激增问题。

慢查询性能问题

导致慢查询的问题一般有三种：

1.索引没有设计好

2.SQL语句写错了

3.MYSQL选错索引。

1.索引没有设计好

对于索引没有设计好的情况，因为MYSQL支持 online DDL,所以我们可以直接执行 alter table 命令。

比较理想的情况是在备库先执行。假设你的数据库是一主一备，主库A，备库B，那么流程大致是这样的：

1.在备库执行 set sql_log_bin = off， 也就是不写 binlog，然后执行 alter table 语句。

2.将备库切换为主库。

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

这是一种古老的 DDL 方式，平常在做变更的时候，我们应该考虑类似 gh-ost 这样的方案，更加稳妥，但是在需要紧急处理时，上述方案的效率是最高的。

2.SQL语句写错了

例如有些SQL语句对条件字段做了运算，导致无法使用索引。

MYSQL 5.7提供了query_rewrite功能，可以把SQL语句 改写成 另外一种模式。

比如，对于 select * from t where id + 1 = 10000,我们可以通过下面的SQL语句，添加一个改写规则。

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(),是让我们的存储过程生效。可以通过下图中的方式来确认是否生效。

3.MYSQL选错索引

对于MYSQL 选错索引的情况，我们可以使用 force index 来强制使用某索引。

同样可以使用query_rewrite 功能，来给某语句加上 force index。

我们上面讨论的慢查询性能问题，其实出现最多的还是前两种，同时，这两种情况，我们完全可以避免。

1.在测试环境，将慢查询日志(slow_log)打开，并且将 long_query_time 设置为0，这样每条SQL语句都会被记录下来。

2.在测试环境模拟线上数据，做一遍回归测试。

3.观察慢查询里每条SQL语句的输出，特别留意 rows_examind 参数，验证扫描行数是否与预期一致。

QPS 突增问题

如果这个问题是由于新增业务的bug导致的，那么最理想的情况就是 让业务下掉这个功能。

小结

本节介绍了一些MYSQL短时间内提高性能的方法，有一些简单粗暴的方法，比如：跳过权限验证，踢掉线程；也有一些比较稳妥并且可能会常用的方法，比如：online DDL,使用query_rewrite功能，强制使用索引等。

本节所学的处理机制都发生在server层，后面我们会学到innodb 层的处理机制。