开发者社区> 欢少的成长之路> 正文

MySQL有哪些提升性能的方法呢?

简介: 大家好,前面几章我们介绍了关于锁的规则优化问题。今天我们介绍一下MySQL的那些提升性能的方法?
+关注继续查看

案例


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

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


max_connections

这个参数是控制最大连接数的,一旦数据库处理得慢一些,连接数就会暴涨。超过这个值,系统就会拒绝接下来的连接请求,并报错提示“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 的线程,可能是有损的。我们来看下面这个例子。

image.png

sessionA

  • 开启一个事务并且插入一个1的记录

sessionB

  • 查询id为1的数据

sessionC

  • 查询状态

如果我们把sessionA关掉,那么MySQL事务就需要回滚sessionA执行的事务,

如果我们把sessionB关掉,好像对MySQL的确没有太大的影响

结论: 应该先关掉在事务外空闲的连接,然后再关掉事务内的空闲连接,最终关掉那些无关紧要的查询语句。

那么我们怎么判断是不俗事务外的空间连接呢?

通过执行 show processlist 根据输出结果进行讨论分析。image.png图中 id=4 和 id=5 的两个会话都是 Sleep 状态,显然单凭这一条方式是万万不够的。那么我们就需要查询每一个ID对应的事务结果。看看事务内的执行逻辑。

看事务具体状态的话,你可以查 information_schema 库的 innodb_trx 表。image.png

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

这个这两种方式就可以断定,当前这个链接还在事务内正在执行。那么根据我们上文总结的优先级。就可以先排除这条ID连接了。通过这种方式可以查到没有在事务内的正在执行的ID

然后再通过kill connections id 即可。

服务端控制客户端强行断开之后,客户端并没有立马提示,而是等下次执行当前连接查询数据的时候才会提示 ERROR 2013 (HY000): Lost connection to MySQL server during query

从数据库端主动断开连接可能是有损的,尤其是有的应用端收到这个错误后,不重新连接,而是直接用这个已经不能用的句柄重试查询。这会导致从应用端看上去,“MySQL 一直没恢复”。


减少连接过程的消耗。

下面我们介绍一下 –skip-grant-tables 这个参数的用法。

有些业务为了提升并发性能,考虑先从数据库中提前申请连接。这样就可以省去连接,校验等时间的限制。

那么想执行当前方法就需要重启数据库,并使用–skip-grant-tables 参数启动。这样整个MySQL就会跳过所有的权限验证(包括语句执行)。

利弊

这样的确可以解决性能问题,但是这样做是非常危险的。如果暴露在外网的话,就更不能这么做了。

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


慢查询

先说一下影响性能的几大因素

  • 因为索引问题,导致回表过多,扫描过多
  • SQL问题,导致慢
  • MySQL引擎选错了索引。这里不懂的话去我MySQL学习专栏查找引导那一篇

下面我们一个一个分析


索引问题

如果是索引问题的话,那就必须重新设计索引了。因为是在生产库动数据结构,所以玩玩小心。肯定是不能随随便便就动的了。

单库

MySQL 5.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问题

这种情况还是比较好解决的。一般不会出现在生产库发生。不过也有,下面我们介绍一下吧。

新手问题的SQL的话没啥好说的,多跑跑explain吧。一些上点技术含量的话。那就是前几篇介绍的索引莫名失效问题。主要从三个方面阐述了。隐式转换,隐式编码,函数操作等失效原因。

我们可以通过改写 SQL 语句来处理。MySQL 5.7 提供了 query_rewrite 功能,可以把输入的一种语句改写成另外一种模式

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

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

query_rewrite.flush_rewrite_rules 这个是一个存储过程,是为了让上面的insert生效。先验证一下是否生效image.png

改写成功!


选错索引

选错索引这里我们也介绍过了。来自《优化器选错索引,导致线上瘫痪》,可以先去复习一下。这里便于理解。

回到主题。

如果索引没有按照我们的思路选择索引的话,我们常用的写法就是 加一个 force index。引导优化器选择索引。

同样的,我们这个地方解决这个选错索引的话也可以通过这种方式。

开发场景中常见的还是索引问题和SQL问题。一般MySQL不会选错的。这两种往往是比较好预防的。

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

不要吝啬这段花在上线前的“额外”时间,因为这会帮你省下很多故障复盘的时间。


QPS

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

我之前碰到过一类情况,是由一个新功能的 bug 导致的。当然,最理想的情况是让业务把这个功能下掉,服务自然就会恢复。

而下掉一个功能,如果从数据库端处理的话,对应于不同的背景,有不同的方法可用。我这里再和你展开说明一下。

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

当然,这个操作的风险很高,需要你特别细致。它可能存在两个副作用:

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

所以,方案 3 是用于止血的,跟前面提到的去掉权限验证一样,应该是你所有选项里优先级最低的一个方案。

同时你会发现,其实方案 1 和 2 都要依赖于规范的运维体系:虚拟化、白名单机制、业务账号分离。由此可见,更多的准备,往往意味着更稳定的系统。


总结


今天介绍了优化使用MySQL的几种方式。以及常见的问题解决访问方案。都是线上的问题。


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
23629 0
阿里云服务器ECS远程登录用户名密码查询方法
阿里云服务器ECS远程连接登录输入用户名和密码,阿里云没有默认密码,如果购买时没设置需要先重置实例密码,Windows用户名是administrator,Linux账号是root,阿小云来详细说下阿里云服务器远程登录连接用户名和密码查询方法
22473 0
如何设置阿里云服务器安全组?阿里云安全组规则详细解说
阿里云安全组设置详细图文教程(收藏起来) 阿里云服务器安全组设置规则分享,阿里云服务器安全组如何放行端口设置教程。阿里云会要求客户设置安全组,如果不设置,阿里云会指定默认的安全组。那么,这个安全组是什么呢?顾名思义,就是为了服务器安全设置的。安全组其实就是一个虚拟的防火墙,可以让用户从端口、IP的维度来筛选对应服务器的访问者,从而形成一个云上的安全域。
20708 0
windows server 2008阿里云ECS服务器安全设置
最近我们Sinesafe安全公司在为客户使用阿里云ecs服务器做安全的过程中,发现服务器基础安全性都没有做。为了为站长们提供更加有效的安全基础解决方案,我们Sinesafe将对阿里云服务器win2008 系统进行基础安全部署实战过程! 比较重要的几部分 1.
12011 0
阿里云服务器安全组设置内网互通的方法
虽然0.0.0.0/0使用非常方便,但是发现很多同学使用它来做内网互通,这是有安全风险的,实例有可能会在经典网络被内网IP访问到。下面介绍一下四种安全的内网互联设置方法。 购买前请先:领取阿里云幸运券,有很多优惠,可到下文中领取。
23007 0
使用SSH远程登录阿里云ECS服务器
远程连接服务器以及配置环境
14844 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,云吞铺子总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系统盘、创建快照、配置安全组等操作如何登录ECS云服务器控制台? 1、先登录到阿里云ECS服务器控制台 2、点击顶部的“控制台” 3、通过左侧栏,切换到“云服务器ECS”即可,如下图所示 通过ECS控制台的远程连接来登录到云服务器 阿里云ECS云服务器自带远程连接功能,使用该功能可以登录到云服务器,简单且方便,如下图:点击“远程连接”,第一次连接会自动生成6位数字密码,输入密码即可登录到云服务器上。
36544 0
+关注
欢少的成长之路
有物流,电商经验,RocketMQ领域专家,csdn/掘金等平台优质作者,就职于物流企业Java开发岗位
98
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载