微信公众号：欢少的成长之路

小知识，大挑战！本文正在参与“程序员必备小知识”创作活动。

大家好，前面几章我们介绍了关于锁的规则优化问题。今天我们介绍一下MySQL的那些提升性能的方法？

案例

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

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

max_connections

这个参数是控制最大连接数的，一旦数据库处理得慢一些，连接数就会暴涨。超过这个值，系统就会拒绝接下来的连接请求，并报错提示“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

开启一个事务并且插入一个1的记录

sessionB

查询id为1的数据

sessionC

查询状态

如果我们把sessionA关掉，那么MySQL事务就需要回滚sessionA执行的事务，

如果我们把sessionB关掉，好像对MySQL的确没有太大的影响

结论： 应该先关掉在事务外空闲的连接，然后再关掉事务内的空闲连接，最终关掉那些无关紧要的查询语句。

那么我们怎么判断是不俗事务外的空间连接呢？

通过执行 show processlist 根据输出结果进行讨论分析。在这里插入图片描述图中 id=4 和 id=5 的两个会话都是 Sleep 状态，显然单凭这一条方式是万万不够的。那么我们就需要查询每一个ID对应的事务结果。看看事务内的执行逻辑。

看事务具体状态的话，你可以查 information_schema 库的 innodb_trx 表。 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-awMBdQWT-1632231971077)(MySQL%E6%9C%89%E5%93%AA%E4%BA%9B%E6%8F%90%E9%AB%98%E6%80%A7%E8%83%BD%E7%9A%84%E6%96%B9%E6%B3%95.assets/ca4b455c8eacbf32b98d1fe9ed9876e8.png)]

这个结果里，trx_mysql_thread_id=4，表示 id=4 的线程还处在事务中。

这个这两种方式就可以断定，当前这个链接还在事务内正在执行。那么根据我们上文总结的优先级。就可以先排除这条ID连接了。通过这种方式可以查到没有在事务内的正在执行的ID

然后再通过kill connections id 即可。

服务端控制客户端强行断开之后，客户端并没有立马提示，而是等下次执行当前连接查询数据的时候才会提示 ERROR 2013 (HY000): Lost connection to MySQL server during query

从数据库端主动断开连接可能是有损的，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去，“MySQL 一直没恢复”。

减少连接过程的消耗。

下面我们介绍一下 –skip-grant-tables 这个参数的用法。

有些业务为了提升并发性能，考虑先从数据库中提前申请连接。这样就可以省去连接，校验等时间的限制。

那么想执行当前方法就需要重启数据库，并使用–skip-grant-tables 参数启动。这样整个MySQL就会跳过所有的权限验证（包括语句执行）。

利弊

这样的确可以解决性能问题，但是这样做是非常危险的。如果暴露在外网的话，就更不能这么做了。

在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。可见，MySQL 官方对 skip-grant-tables 这个参数的安全问题也很重视。

慢查询

先说一下影响性能的几大因素

因为索引问题，导致回表过多，扫描过多
SQL问题，导致慢
MySQL引擎选错了索引。这里不懂的话去我MySQL学习专栏查找引导那一篇

下面我们一个一个分析

索引问题

如果是索引问题的话，那就必须重新设计索引了。因为是在生产库动数据结构，所以玩玩小心。肯定是不能随随便便就动的了。

单库

MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。

多库

最好的就是能够在备库先执行。假设你现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
执行主备切换；
这时候主库是 B，备库是 A。在 A 上执行 set sql_log_bin=off，然后执行 alter table 语句加上索引。

这是一个“古老”的 DDL 方案。平时在做变更的时候，你应该考虑类似 gh-ost 这样的方案，更加稳妥。但是在需要紧急处理时，上面这个方案的效率是最高的。

SQL问题

这种情况还是比较好解决的。一般不会出现在生产库发生。不过也有，下面我们介绍一下吧。

新手问题的SQL的话没啥好说的，多跑跑explain吧。一些上点技术含量的话。那就是前几篇介绍的索引莫名失效问题。主要从三个方面阐述了。隐式转换，隐式编码，函数操作等失效原因。

我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能，可以把输入的一种语句改写成另外一种模式

比如，语句被错误地写成了 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();

query_rewrite.flush_rewrite_rules 这个是一个存储过程，是为了让上面的insert生效。先验证一下是否生效在这里插入图片描述

改写成功！

选错索引

选错索引这里我们也介绍过了。来自《优化器选错索引，导致线上瘫痪》，可以先去复习一下。这里便于理解。

回到主题。

如果索引没有按照我们的思路选择索引的话，我们常用的写法就是加一个 force index。引导优化器选择索引。

同样的，我们这个地方解决这个选错索引的话也可以通过这种方式。

开发场景中常见的还是索引问题和SQL问题。一般MySQL不会选错的。这两种往往是比较好预防的。

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

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

QPS

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

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

而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。我这里再和你展开说明一下。

一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。
如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。
如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

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

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

所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。

同时你会发现，其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

总结

今天介绍了优化使用MySQL的几种方式。以及常见的问题解决访问方案。都是线上的问题。

感谢各位支持，今天就到这里吧。有啥疑问可以通过公众号【欢少的成长之路】给我私信吧

MySQL有哪些提升性能的方法呢？

案例