正文 

当前位置： 首页 > 技术 > java教程

day886.mysql的“饮鸩止渴”提高性能的方法-mysql实战(代码片段)

阿昌喜欢吃黄桃 阿昌喜欢吃黄桃     2023-03-02     382

关键词：

MySQL的“饮鸩止渴”提高性能的方法

HI，我是阿昌，今天学习记录的是关于MySQL的“饮鸩止渴”提高性能的方法的内容。

不知道在实际运维过程中有没有碰到这样的情景：

业务高峰期，生产环境的 MySQL 压力太大，没法正常响应，需要短期内、临时性地提升一些性能。以前做业务护航的时候，就偶尔会碰上这种场景。

用户的开发负责人说，不管用什么方案，让业务先跑起来再说。但，如果是无损方案的话，肯定不需要等到这个时候才上场。

一、短连接风暴

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

如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

在基础架构中说过，MySQL 建立连接的过程，成本是很高的。

  • 除了正常的网络连接三次握手外
  • 还需要做登录权限判断
  • 获得这个连接的数据读写权限。

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

但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。

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 A 还没有提交，所以 MySQL 只能按照回滚事务来处理；而断开 session B 的连接，就没什么大影响。

所以，如果按照优先级来说，你应该优先断开像 session B 这样的事务外空闲的连接。但是，怎么判断哪些是事务外空闲的呢？

session C 在 T 时刻之后的 30 秒执行 show processlist，看到的结果是这样的。

图中 id=4 和 id=5 的两个会话都是 Sleep 状态。

要看事务具体状态的话，可以查 information_schema 库的 innodb_trx 表。

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

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

从服务端断开连接使用的是 kill connection + id 的命令， 一个客户端处于 sleep 状态时，它的连接被服务端主动断开后，这个客户端并不会马上知道。

直到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

从数据库端 主动断开连接可能是有损的 ，尤其是有的应用端收到这个错误后，不重新连接，而是直接用这个已经不能用的句柄重试查询。

这会导致从应用端看上去，“MySQL 一直没恢复”。

可能觉得这是一个冷笑话，但实际上碰到过不下 10 次。

所以，如果是一个支持业务的 DBA，不要假设所有的应用代码都会被正确地处理。

即使只是一个断开连接的操作，也要确保通知到业务开发团队。

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

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

跳过权限验证的方法是：重启数据库，并使用–skip-grant-tables 参数启动。

这样，整个 MySQL 会跳过所有的权限验证阶段，包括连接过程和语句执行过程在内。但是，这种方法特别符合标题里说的“饮鸩止渴”，风险极高，是特别不建议使用的方案。尤其库外网可访问的话，就更不能这么做了。

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

除了短连接数暴增可能会带来性能问题外，实际上，在线上碰到更多的是查询或者更新语句导致的性能问题。其中，查询问题比较典型的有两类：

  • 一类是由新出现的慢查询导致的
  • 一类是由 QPS（每秒查询数）突增导致的。

二、慢查询性能问题

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

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

1、索引没有设计好

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

这种场景一般就是通过紧急创建索引来解决。MySQL 5.6 版本以后，创建索引都支持 Online DDL 了，对于那种高峰期数据库已经被这个语句打挂了的情况，最高效的做法就是直接执行 alter table 语句。比较理想的是能够在备库先执行。

假设现在的服务是一主一备，主库 A、备库 B，这个方案的大致流程是这样的：

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

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

2、语句没写好

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

比如，犯了在只查一行的语句，也执行这么慢？中提到的那些错误，导致语句没有使用上索引。这时，可以通过改写 SQL 语句来处理。

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

比如，语句被错误地写成了 select * from t where id + 1 = 10000，可以通过下面的方式，增加一个语句改写规则。

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

这里，call query_rewrite.flush_rewrite_rules() 这个存储过程，是让插入的新规则生效，也就是说的“查询重写”。

可以用图 4 中的方法来确认改写规则是否生效。

3、选错了索引

在MySQL 为什么有时候会选错索引中提到的情况，MySQL 选错了索引。

这时候，应急方案就是给这个语句加上 force index。同样地，使用查询重写功能，给原来的语句加上 force index，也可以解决这个问题。

由慢查询导致性能问题的三种可能情况，实际上出现最多的是前两种，即：索引没设计好和语句没写好。而这两种情况，恰恰是完全可以避免的。

比如，通过下面这个过程，可以预先发现问题。

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

不要吝啬这段花在上线前的“额外”时间，因为这会帮你省下很多故障复盘的时间。如果新增的 SQL 语句不多，手动跑一下就可以。而如果是新项目的话，或者是修改了原有项目的 表结构设计，全量回归测试都是必要的。这时候，需要工具帮你检查所有的 SQL 语句的返回结果。比如，可以使用开源工具 pt-query-digest

三、QPS 突增问题

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

当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。

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

它可能存在两个副作用：

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

所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是所有选项里优先级最低的一个方案。同时你会发现，其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

四、总结

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

这些处理手段中，既包括了粗暴地拒绝连接和断开连接，也有通过重写语句来绕过一些坑的方法；既有临时的高危方案，也有未雨绸缪的、相对安全的预案。

在实际开发中，也要尽量避免一些低效的方法，比如避免大量地使用短连接。

同时，如果做业务开发的话，要知道，连接异常断开是常有的事，代码里要有正确地重连并重试的机制。

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

其实，可以看得出来，在文章中提到的解决方法主要集中在 server 层。

是否碰到过，在业务高峰期需要临时救火的场景？又是怎么处理的呢？

主从切换时遇到的问题，主从切换前主库要改为只读，设置只读后，show master status发现binlog一直在变化，当时应用没断开。
主库并不是其他库的从库，怎么搞的呢？
检查业务用户权限发现拥有super权限，查看授权语句原来是grant all on . to user，这里要说的是*.* 权限就太大了，而且这个也很容易被误解，需要特别注意。

对的，readonly对super无效；
一方面是尽量不要给业务super
一方面你做完readonly还会去确认binlog有没有变，这个意识很好哦

《mysql-在mysql服务出现瓶颈时，有哪些“饮鸩止渴”提高性能的方法？》

一：情景　　- 业务高峰期，生产环境的MySQL压力太大，没法正常响应，需要短期内、临时性地提升一些性能。　　-在业务高发时候，Mysql服务压力过大，导致业务受损，用户的开发负责人说，不管你用什么方案，让业务先跑... 查看详情

提高 SQL 查询性能

】提高SQL查询性能【英文标题】：IncreaseSQLQueryPerformance【发布时间】：2011-07-2007:22:27【问题描述】：Sql：selectdistinctDateAdd(Day,DateDiff(Day,0,m.Receive_date),0)asDate,(selectcount(*)fromRaw_MatsAwhereDateAdd(Day,DateDiff(Day,0,A.Receive_date 查看详情

day683.aprendpoint组件：tomcatapr提高i/o性能的秘密-深入拆解tomcat&jetty(代码片段)

AprEndpoint组件：TomcatAPR提高I/O性能的秘密Hi，阿昌来也，今天学习关于的是针对TomcatAPR提高I/O性能的秘密，也就是AprEndpoint组件的实现。使用Tomcat时，会在启动日志里看到这样的提示信息：TheAPRbasedApacheTomcatN... 查看详情

day781.stream如何提高遍历集合效率-java性能调优实战(代码片段)

Stream如何提高遍历集合效率Hi，我是阿昌，今天学习记录的是关于Stream如何提高遍历集合效率。在Java8中，Collection新增了两个流方法，分别是Stream()和parallelStream()。通过英文名不难猜测，这两个方法肯定和Stream... 查看详情

提高 PostgreSQL 查询性能

】提高PostgreSQL查询性能【英文标题】：ImprovePostgreSQLqueryperformance【发布时间】：2013-02-1720:05:11【问题描述】：在我的服务器上运行此查询时速度很慢，我不明白为什么。谁能帮我弄清楚？查询：SELECT"t_dat"."t_year"AS"c0","t_dat"."t_mont... 查看详情

day819.缓存优化系统性能-java性能调优实战(代码片段)

...，今天学习记录的是关于缓存优化系统性能。缓存是提高系统性能的一项必不可少的技术，无论是前端、还是后端，都应用到了缓存技术。前端使用缓存，可以降低多次请求服务的压力；后端使用缓存，可... 查看详情

day819.缓存优化系统性能-java性能调优实战(代码片段)

...，今天学习记录的是关于缓存优化系统性能。缓存是提高系统性能的一项必不可少的技术，无论是前端、还是后端，都应用到了缓存技术。前端使用缓存，可以降低多次请求服务的压力；后端使用缓存，可... 查看详情

如何提高 Oracle 中仅加载最近 5 天内修改的项目的 SQL 性能？

】如何提高Oracle中仅加载最近5天内修改的项目的SQL性能？【英文标题】：HowtoimproveperformanceofSQLinOraclethatloadsonlyItemsmodifiedduringlast5days?【发布时间】：2018-09-1817:05:02【问题描述】：我有一个表M有CREATED_AT和MODIFIED_AT列（日期），这... 查看详情

day786.nio的优化实现原理-java性能调优实战(代码片段)

...高并发的场景下，可以通过设置NIO线程模型，来提高系统的网络通信性能。可以通过一个性能对比测试来看看在 查看详情

day808.设计模式一些列问题-java性能调优实战(代码片段)

...些高并发场景中，线程协作相关的设计模式可以大大提高程序的运行性能。1、除了之前那些实现单例模式的方式，还知道其它实现方式吗& 查看详情

性能测试系列学习day1

性能测试的最终目标是为了最大限度的满足用户的需求，我们通常为了达到以下目标而进行性能测试：(1)评估系统的能力：测试中得到的负荷和响应时间数据可以被用于验证所计划的模型的能力，并帮助作出决策；(2)寻找系统瓶... 查看详情

php学习day7--函数的相关知识

...之后可以直接进行调用，可以很大的减少代码的从用性，提高页面性能和可读性。　　1.函数的定义　　在php中函数的定义方式为：　　　　　　function name（$形参1，$形参2.....）{　　　　　　　　要执行的代码　　　　　　... 查看详情

noip2011提高组day1+day2解题报告

Day1T1铺地毯https://www.luogu.org/problem/show?pid=1003【题目分析】   全部读入以后从最后一个往前找，找到一个矩形的范围覆盖了这个点，那这个矩形就是最上面的地毯，输出即可#include<iostream>#include<cstdio>#include<c... 查看详情

noip2011提高组day2

自测时间：2017.4.128:15——11:45实际得分：100+0+0=100期望得分：100+100+60=260T2符合要求的总价值*符合要求的总个数  写成：符合要求的总价值*区间总个数T3正解贪心，DP式子退错了http://cogs.pro/cogs/page/page.php?aid=16 T1计... 查看详情

2015noip提高组day2

...从起点出发,每一步跳向相邻的岩石,直至到达终点。为了提高比赛难度,组委会计划移走一些岩石,使得选手们在比赛过程中的最短 查看详情

深度学习性能提高

性能提高分为四个部分： 1. 通过数据提升性能 2. 通过算法提升性能 3. 通过算法调参提升性能 4. 通过嵌套模型提升性能 通常来讲，随着列表自上而下，性能的提升也将变小。例如，对问题进行... 查看详情

day01-项目介绍与环境搭建(代码片段)

...目开发的完整流程，增长开发经验了解需求分析的过程，提高分析和设计能力对所学的技术进行灵活应用，提高编码能力解决各种异常情况，提高代码调试能力3.软件开发整体介绍3.1软件开发流程3.2角色分工3.3软件环境开发环境... 查看详情

提高计时方法的性能

】提高计时方法的性能【英文标题】：Improveperformanceoftimingmethod【发布时间】：2018-04-2510:56:42【问题描述】：我使用这个nodejs模块来测量/分析我的应用程序的执行部分需要多长时间。//polyfillforwindow.performance.nowvarperformance=global.per... 查看详情