MySQL临时提高性能方案

参考自 极客时间 - 22 | MySQL有哪些“饮鸩止渴”提高性能的方法？

问题一：短连接风暴

短连接：连接到数据库执行很少SQL后就被断开。

在业务高峰期，可能会出现连接数暴涨的问题。

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

1.1 调大max_connections 连接数

1.2 处理保持连接的不工作线程（减少连接）

如何识别不工作线程？

首先，使用show processlist 查看连接状态。例如

然后，识别空闲线程。

select * from information_schema.innodb_trx;

根据trx_mysql_thread_id 使用kill connection [id] 进行连接的关闭。

但是注意，此方法是业务有损的。

• 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。
• 也有部分应用，不会重新创建连接，会一直使用失效的连接去进行查询。

1.3 减少连接过程的消耗

骚操作，跳过数据库权限验证阶段。

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

在 MySQL 8.0 版本里，如果你启用–skip-grant-tables 参数，MySQL 会默认把 --skip-networking 参数打开，表示这时候数据库只能被本地的客户端连接。

问题二 慢查询

性能问题的查询SQL的原因大致如下：

1. 索引没有设计好。
2. 查询语句没有写好。
3. MySQL选错索引。

2.1 索引没有设计好

可以尝试直接加索引，5.6以上支持Online DDL，比较高效；

备选方案：假设数据库为一主（A）一备（B）。

在备库 B 上执行 set sql_log_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；

执行主备切换；

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

实际不紧急场景，可以考虑使用gh-ost进行Online DDL。

2.2 查询语句没有写好

• 条件字段函数操作（where 语句中写函数操作）

  索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

  优化器并不是要放弃使用这个索引，而是会判断遍历主键还是遍历对应普通索引树，而判断的依据就是索引对应字段的大小。存储空间小的索引，可以在同一个数据页内存放下更多数据。

• 隐式类型转换

  会导致其不走索引。

  数据类型转换的规则：首先明确的是MySQL中，字符串与数字的比较，会将字符串转成数字。

• 隐式字符编码转换

  utf8mb4 与 utf8 比较时会把 utf8转换成utf8bm4，因为utf8mb4是utf8的超集。

2.3 索引选错

• 应急方案：加force index

预防方案（检测SQL运行时长）：

1. 上线前，在测试环境，把慢查询日志（slow log）打开，并且把 long_query_time 设置成 0，确保每个语句都会被记录入慢查询日志；
2. 在测试表里插入模拟线上的数据，做一遍回归测试；
3. 观察慢查询日志里每类语句的输出，特别留意 Rows_examined 字段是否与预期一致。（我们在前面文章中已经多次用到过 Rows_examined 方法了，相信你已经动手尝试过了。如果还有不明白的，欢迎给我留言，我们一起讨论）。

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

问题三 QPS 突增问题

如果是bug导致QPS过高，可以单独下掉对应功能的数据库用户。

或者call query_rewrite.flush_rewrite_rules() 直接重写"select 1"返回