写点什么

MySQL 有哪些“饮鸩止渴”提高性能的方法?

  • 2025-07-28
    福建
  • 本文字数:2360 字

    阅读完需:约 8 分钟

有时候,在业务高峰期,生产环境的 MySQL 压力太大,没法正常响应,需要短期内、临时性地提升一些性能。本文就来讲讲一些临时方法,并着重说它们可能存在的风险。


短连接风暴


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

MySQL 建立连接的过程成本很高,在数据库压力比较小的时候,这些额外成本并不明显。

短连接模型存在一个风险,就是一旦数据库处理慢一些,连接数就会暴涨。max_connections 参数用来控制一个 MySQL 实例同时存在的连接数的上限,超过这个值,系统就会拒绝接下来的连接请求并报错。对于被拒绝连接的请求来说,从业务角度看就是数据库不可用。

当机器负载较高,处理现有请求时间变长,每个连接保持的时间也更长,这时来的新连接很可能就超过数量限制。此时如果调高参数,那么系统负载可能进一步加大,大量的资源耗费在权限验证等逻辑上,结果可能更差。

这里还有两种方法,但都是有损的。


方法一:先处理掉那些占着连接但是不工作的线程


对于不需要保持的连接,可以通过 kill connection 主动剔除,这个行为和事先设置 wait_timeout 效果相同。wait_timeout 参数表示线程空闲多少时间后会被 MySQL 直接断开连接。

在 show processlist 的结果里,踢掉显示为 sleep 的线程,可能是有损的:



在上面的流程中,如果断开 session A 的连接,由于事务还未提交,MySQL 只能按照回滚事务来处理;如果断开 session B 的连接,就没有大影响。因此按照优先级来说,应该先断开像 session B 这样的事务外的空闲连接。

如何判断哪些是事务外空闲的呢?session C 执行 show processlist 看到的结果为:


id=4,id=5两个会话都是 sleep 状态,而要看事务具体状态的话,可以查 information_schema 库的 innodb_trx 表:


trx_mysql_thread_id=4,表示id=4的线程还处在事务中。


因此,如果连接数过多,可以优先断开事务外空闲久的连接;如果还是太多,再考虑断开事务内空闲久的连接。


服务端断开连接用的是 kill connection+id 的命令,处于 sleep 状态的客户端连接被服务端主动断开后,客户端并不会马上知道,直到客户端在发起下一个请求时,才会收到报错“Lost connection to MySQL server during query"。


方法二:减少连接过程的消耗


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

但该方法风险极高,并不建议使用。在 MySQL 8.0 版本中,如果启用该参数,MySQL 会默认把--skip-networking 参数打开,表示这时候数据库只能被本地客户端连接,不可外网访问。


慢查询性能问题


在 MySQL 中,会引发性能问题的慢查询大体分为三种可能:

  • 索引没有设计好;

  • SQL 语句没写好;

  • MySQL 选错索引。

接下来就具体分析这三种可能。


索引没有设计好


这种场景一般就是通过紧急创建索引来解决。在 MySQL 5.6 版本之后,创建索引已支持 Online DDL,对于高峰期数据库被语句打挂了的情况,最高效的做法就是直接执行 alter table 语句。

理想情况是能在备库先执行。假设现在有主库 A 和备库 B,该方案的大致流程为:

  • 在备考 B 上执行set sql_log_bin=off,即不写 binlog,然后执行 alter table 去加索引;

  • 执行主备切换;

  • 在 A 上执行set sql_log_bin=off,然后执行 alter table 加索引。

平时变更时,应该考虑 gh-ost 这样的方案更为稳妥,但紧急处理时上面的方案效率最高。


SQL 语句没写好


有时候语句没写好,会导致语句没有使用上索引。这时可以通过改写 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()的存储过程是让插入的新规则生效,可以用下面的方法来确认改写规则是否生效:



MySQL 选错索引


这时应急方案就是给语句加上 force index。

上面的三种情况,实际上出现最多的是前两种。为了避免这两种情况,可以通过下面的过程预先发现问题:

  • 上线前,在测试环境打开慢查询日志,且把 long_query_time 设为 0,确保每个语句都会被记录入慢查询日志;

  • 在测试表里插入模拟线上的数据,做一遍回归测试;

  • 观察日志里 每类语句的输出,特别留意 Rows_examined 字段是否与预期一致。

如果新增 SQL 语句不多,手动跑一下就可以。如果是新项目或修改了原项目的表结构设计,全量回归测试都是必要的,这是可以借助开源工具 pt-query-digest。


QPS 突增问题


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

对于功能 bug,最理想的情况是让业务把这个功能下掉,下掉一个功能,从数据库端处理的话,对于不同的背景,有不同的方法可用:

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

  • 如果功能使用的是单独的数据库用户,可以删除这个用户然后断开现有连接;

  • 如果新增功能跟主体功能部署在一起,只能通过处理语句来限制,如把压力最大的 SQL 语句直接重写成 select 1。这个操作风险很高,可能存在两个副作用:

    如果别的功能也用到了这个 SQL 语句模板,会有误伤;

    很多业务不是只靠一个语句完成,如果单独把这个语句重写,可能导致后面的业务逻辑一起失败。


文章转载自:san-mu

原文链接:https://www.cnblogs.com/san-mu/p/19001717

体验地址:http://www.jnpfsoft.com/?from=001YH

用户头像

还未添加个人签名 2025-04-01 加入

还未添加个人简介

评论

发布
暂无评论
MySQL有哪些“饮鸩止渴”提高性能的方法?_MySQL_量贩潮汐·WholesaleTide_InfoQ写作社区