MySQL45讲之生产环境下的性能问题 - flowers

前言

本文介绍一些常见的性能问题，以及在生产环境下应该如何解决。

1. 短连接风暴

当由于大量短连接造成数据库性能低时，首先考虑一些无损安全的解决方案，如果是必须立刻提升一些数据库性能，那么可以考虑下面的方案。

第一种方法：处理掉占着连接但空闲的线程

类似 Mysql wait_timeout 的逻辑，当检测到线程空闲（sleep状态）超过一定时间，就在服务端主动断开与客户端的连接，为后续新到来的连接腾出位置。

从数据库端断开连接的方法是有损的，因为应用端收到这个错误后，不会重新连接，而是用这个不能再用的句柄继续重试查询。这会导致从应用端看上去，“mysql一直没恢复”。

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

一种减少损耗的方法是连接时不进行权限验证，即以 –skip-grant-tables 的方式重启 Mysql，这样后续的连接将不需要再进行权限验证。但是这样显然很不安全，所以 MySQL8.0 在开启 –skip-grant-tables 模式后，只允许本地客户端连接 MySQL。

2. 慢查询问题

慢查询问题主要分三种情况：

• 索引设计不好
• SQL语句没写好
• 数据库选错索引

第一种情况：索引设计不好

原文复制：

MySQL5.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语句没写好

MySQL5.7 提供了 query_rewrite 插件实现 SQL 重写的功能，安装 query_rewrite 插件后，会多一个 query_rewrite 数据库，然后可以该数据库的 rewrite_rules 表中新增重写规则，举例：

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

第三种情况：数据库选错索引

在 SQL 语句上加上 force index 来强制选择索引，同样可以使用 query_rewrite 插件重写语句来解决。

原文复制：

上面的三种可能情况，出现最多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。比如，通过下面这个过程，我们就可以预先发现问题。

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

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

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

参考

• [1] MySQL有哪些“饮鸩止渴”提高性能的方法