MySQL-22：短暂提高性能的方法

刺骨的言语ヽ痛彻心扉 2022-12-27 12:41 98阅读 0赞

#### 22.1短链接风暴 ####

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。

MySQL建立连接的过程：①TCP连接建立，连接器进行身份验证；②身份验证成功，到权限表中加载数据；

**两个参数：** max\_connection 和wait\_timeout。

**1.处理sleep的连接**

通过`show processlist` 可以获取程序列表，以及状态。

通过information\_schema 库的 innodb\_trx 表 来查看事务的状态，这个能表现出kill这个连接是否会有损。

如下情景：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

![在这里插入图片描述][20201211004512901.jpg_pic_center]

11号线程就在事务中，kill掉它，MySQL就只能回滚事务。

**2.减少连接消耗**

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

#### 23.2 慢查询性能问题 ####

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

1.  索引没有设计好；
2.  SQL 语句没写好；
3.  MySQL 选错了索引。

**1. 索引没有设计好**

MySQL 5.6 版本以后，创建索引都支持 Online DDL 了 ， 最高效的做法就是直接执行 alter table 语句。

**古老的方法：**

1.  在备库 B 上执行 set sql\_log\_bin=off，也就是不写 binlog，然后执行 alter table 语句加上索引；
2.  执行主备切换；
3.  这时候主库是 B，备库是 A。在 A 上执行 set sql\_log\_bin=off，然后执行 alter table 语句加上索引。

在需要紧急处理时，上面这个方案的效率是最高的 。你应该考虑类似 gh-ost 这样的方案，更加稳妥。

**2. 语句没写好**

MySQL 5.7 提供了 query\_rewrite 功能，可以把输入的一种语句改写成另外一种模式。

[query\_rewrite插件安装][query_rewrite]

以下场景：

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

![img][]

**3.选错索引**

应急方案最佳为加上force index。

**前期演练：**

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

#### 23.3 QPS 突增问题 ####

最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/89d23ae0e46441b592bd04cede50c2c9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/88f675aaef7e4573a82c8a8b48c95860.png
[20201211004512901.jpg_pic_center]: /images/20221120/11bddb29492c47f882559be153976e54.png
[query_rewrite]: https://blog.csdn.net/yangjianrong1985/article/details/102478893
[img]: /images/20221120/88f6bc784cc2404e9e16c26e9e472581.png