22 mysql有那些”饮鸩止渴”提高性能的方法？

22 mysql有那些”饮鸩止渴”提高性能的方法？

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

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

在数据库压力比较小的时候，这些额外的成本并不明显。

但是，短连接模型存在一个风险，就是一旦数据处理得慢一些，连接数就会暴涨。max_connections参数，用来控制一个mysql实例同时存在的连接数的上限，

超过这个值，系统就会拒绝接下来的连接请求，并报错”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的线程，可能是有损的，

	SESSION A	SESSION B	SESSION C
T	begin; insert into t values(1,1);	select *from t where id =1;
T+30S			show processlist;

在上面的例子中，如果断开session A的连接，因为这时候还没有提交，所以mysql只能按照回滚事务来处理，而断开session B的连接，

就没什么大的影响。所以，如果按照优先级来说，应该处理像session B这样的事务外空闲的session。

怎么判断事务外空闲的呢，执行show processlist

其中，处于sleep状态的会话，要看具体的事务状态的话，可以查看

(system@127.0.0.1:3306) [(none)]> select * from information_schema.innodb_trx\G;

因此，如果是连接数过多，你可以优先断开事务外空闲太久的连接，如果这样还不够，再考虑断开事务内空闲太久的连接。

从服务器端断开连接使用的是kill connection +id的命令，一个客户端处于sleep状态时，它的连接被server主动断开后，

这个客户端并不会马上知道，直到客户端发起下一个请求的时候，才会收到这样的错误”ERROR 2013 (HY000): Lost connection to MySQL server during query”。

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

所以，如果是一个支持业务的DBA，不要假设所有的应用代码都会被正确处理，即使只是一个断开连接的操作，也要确保通知到业务开发团队。

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

有的业务代码会在短时间内先大量申请数据连接做备用，如果现在数据库确认是被连接行为打挂了，那么一种可能的做法，是让数据库跳过权限认证阶段。

方法，重启数据库，并使用参数-skip-grant-tables参数启动，这样，这个mysql会跳过所有的权限认证阶段，包括连接过程和语句执行过程在内。

但是，这种方法是符合”饮鸩止渴”，风险极高，是特别不建议的方案，尤其是库外网可以访问的话，就更不能这么做。

在mysql 8.0的版本里，打开参数-skip-grant-tables参数，mysql会默认把-skip-networking参数打开，表示这个时候数据库只能被本地的客户端连接，可见，对安全的问题的重视。

慢查询性能问题

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

1. 索引没有设计好
2. Sql语句没有写好
3. Mysql选错了索引

分析这三种可能，以及应对的方案

导致慢查询的第一种可能，索引没有设计好

这种场景一般就是通过紧急创建索引来解决。Mysql5.6版本以后，创建索引支持online ddl，对于那种高峰期数据库已经被这个语句打挂了情况，最高效的做法就是直接执行alter table语句。

比较理想的是能够在备库先执行，假设现在一主一备，主库A、备库B，

1 在备库B上执行set sql_log_bin=off，不写binlog，然后执行alter table 加上索引

2 执行主备切换

3 在新备库A上执行set sql_log_bin=off，不写binlog，然后alter 加上所以

这是一个古老的ddl方案，平时在做变更的时候，可以考虑类似的go-ost的方案，比如pt、oak等工具

导致慢查询的第二种可能，语句没写好

比如select * from t where id +1=9999;

这种，我们可以通过改写sql来处理，mysql5.7.6之后提供了query_rewrite功能，可以把输入的一种语句改写成为另外一种模式。

这里可以找到query_rewrite的用法

(system@127.0.0.1:3306) [(none)]> show variables like '%plug%';

+---------------+--------------------------+

| Variable_name | Value                    |

+---------------+--------------------------+

| plugin_dir    | /usr/lib64/mysql/plugin/ |

+---------------+--------------------------+

--create table

--create procedure

--mysql -u root -p < install_rewriter.sql

mysql> 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();

这里调用过程，是让插入的规则生效，也就是查询重新

导致慢查询的第三种可能，就是mysql使用错误的索引

这时候的应急方案就是给这个语句加上force index

同样的，使用查询重写功能，给原来的语句加上for index，也可以解决这个问题

 

预发现问题
1上线前，在测试环境，把慢查询(slow log)打开，设置long_query_time=0，确保每个语句都会记录慢查询日志

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

3 观察慢查询日志里每类语句的输出，特别留意Rows_examined字段是否与预期的一致

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

QPS突增问题

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

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

而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。

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

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

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

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

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

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

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

小结

以业务高峰期的性能问题为背景，介绍了一些紧急的处理手段。

这些手段中，既包括了粗暴的拒绝连接和断开连接，也有通过查询重写来绕过一些坑，既有临时的高危方案，又有相对安全的预案。

在实际开发中，我们要尽量避免一些低效的做法，比如大量的使用短连接，同时，做业务开发，要知道，连接异常断开是常有的事，代码里要有正确的重连并重试的机制。

DBA虽然可以通过语句重写来暂时处理问题，但这本身是一个风险极高的操作，做好SQL审计以减少需要这样操作的机会。

 

上起问题解答

SESSION A	SESSION B	SESSION C
begin; select * from t20 where c>=15 and c<=20 order by c desc for update;
	insert into t20 values(11,11,11);(blocked)
		insert into t20 values(6,6,6);(blocked)

SESSION B阻塞已经不用分析，来分析SESSION C为什么会阻塞

1 session A加锁分析，由于是order by c desc，第一个要定位的是索引c上的”最右边的”c=20的行，所以会加上间隙锁(20,25)和next-key lock(15,20]

2 在索引c上向左遍历，要扫描到c=10才停下来，所以next-key lock会加到(5,10]，这个正是阻塞session c的原因

3 在扫描过程中，c+20,c=15,c=10这三个值都存在，由于是select * ,所以会在主键id上加3个行锁。

因此SESSION A的加锁返回

1 索引c上(5,25)

2 主键索引上c=10,c=15,c=20 三个行锁。