SQL慢的原因有哪些

发布时间:2021-10-22 10:40:57 作者:iii
来源:亿速云 阅读:231

本篇内容主要讲解“SQL慢的原因有哪些”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“SQL慢的原因有哪些”吧!

SQL慢的原因有哪些

- 思维导图 -

写操作

作为后端开发,日常操作数据库最常用的是写操作和读操作。读操作我们下边会讲,这个分类里我们主要来看看写操作时为什么会导致 SQL 变慢。

刷脏页

脏页的定义是这样的:内存数据页和磁盘数据页不一致时,那么称这个内存数据页为脏页。

那为什么会出现脏页,刷脏页又怎么会导致 SQL 变慢呢?那就需要我们来看看写操作时的流程是什么样的。

对于一条写操作的 SQL 来说,执行的过程中涉及到写日志,内存及同步磁盘这几种情况。

SQL慢的原因有哪些

- Mysql 架构图 -

这里要提到一个日志文件,那就是 redo log,位于存储引擎层,用来存储物理日志。在写操作的时候,存储引擎(这里讨论的是 Innodb)会将记录写入到  redo log 中,并更新缓存,这样更新操作就算完成了。后续操作存储引擎会在适当的时候把操作记录同步到磁盘里。

看到这里你可能会有个疑问,redo log 不是日志文件吗,日志文件就存储在磁盘上,那写的时候岂不很慢吗?

其实,写redo log 的过程是顺序写磁盘的,磁盘顺序写减少了寻道等时间,速度比随机写要快很多( 类似Kafka存储原理),因此写 redo log  速度是很快的。

好了,让我们回到开始时候的问题,为什么会出现脏页,并且脏页为什么会使 SQL 变慢。你想想,redo log  大小是一定的,且是循环写入的。在高并发场景下,redo log 很快被写满了,但是数据来不及同步到磁盘里,这时候就会产生脏页,并且还会阻塞后续的写入操作。SQL  执行自然会变慢。

写操作时 SQL  慢的另一种情况是可能遇到了锁,这个很容易理解。举个例子,你和别人合租了一间屋子,只有一个卫生间,你们俩同时都想去,但对方比你早了一丢丢。那么此时你只能等对方出来后才能进去。

对应到 Mysql 中,当某一条 SQL 所要更改的行刚好被加了锁,那么此时只有等锁释放了后才能进行后续操作。

但是还有一种极端情况,你的室友一直占用着卫生间,那么此时你该怎么整,总不能尿裤子吧,多丢人。对应到Mysql  里就是遇到了死锁或是锁等待的情况。这时候该如何处理呢?

Mysql 中提供了查看当前锁情况的方式:

SQL慢的原因有哪些

通过在命令行执行图中的语句,可以查看当前运行的事务情况,这里介绍几个查询结果中重要的参数:

SQL慢的原因有哪些

当前事务如果等待时间过长或出现死锁的情况,可以通过 「kill 线程ID」 的方式释放当前的锁。

这里的线程 ID 指表中 trx_mysql_thread_id 参数。

读操作

说完了写操作,读操作大家可能相对来说更熟悉一些。SQL 慢导致读操作变慢的问题在工作中是经常会被涉及到的。

慢查询

在讲读操作变慢的原因之前我们先来看看是如何定位慢 SQL 的。Mysql 中有一个叫作慢查询日志的东西,它是用来记录超过指定时间的 SQL  语句的。默认情况下是关闭的,通过手动配置才能开启慢查询日志进行定位。

具体的配置方式是这样的:

查看当前慢查询日志的开启情况:

SQL慢的原因有哪些
SQL慢的原因有哪些
SQL慢的原因有哪些

注意这里只是临时开启了慢查询日志,如果 mysql 重启后则会失效。可以 my.cnf 中进行配置使其永久生效。

存在原因

知道了如何查看执行慢的 SQL 了,那么我们接着看读操作时为什么会导致慢查询。

(1)未命中索引

SQL 查询慢的原因之一是可能未命中索引,关于使用索引为什么能使查询变快以及使用时的注意事项,网上已经很多了,这里就不多赘述了。

(2)脏页问题

另一种还是我们上边所提到的刷脏页情况,只不过和写操作不同的是,是在读时候进行刷脏页的。

是不是有点懵逼,别急,听我娓娓道来:

为了避免每次在读写数据时访问磁盘增加 IO 开销,Innodb 存储引擎通过把相应的数据页和索引页加载到内存的缓冲池(buffer  pool)中来提高读写速度。然后按照最近最少使用原则来保留缓冲池中的缓存数据。

那么当要读入的数据页不在内存中时,就需要到缓冲池中申请一个数据页,但缓冲池中数据页是一定的,当数据页达到上限时此时就需要把最久不使用的数据页从内存中淘汰掉。但如果淘汰的是脏页呢,那么就需要把脏页刷到磁盘里才能进行复用。

你看,又回到了刷脏页的情况,读操作时变慢你也能理解了吧?

防患于未然

知道了原因,我们如何来避免或缓解这种情况呢?

首先来看未命中索引的情况:

不知道大家有没有使用 Mysql 中 explain 的习惯,反正我是每次都会用它来查看下当前 SQL 命中索引的情况。避免其带来一些未知的隐患。

这里简单介绍下其使用方式,通过在所执行的 SQL 前加上 explain 就可以来分析当前 SQL 的执行计划:

SQL慢的原因有哪些

执行后的结果对应的字段概要描述如下图所示:

SQL慢的原因有哪些

这里需要重点关注以下几个字段:

1、type

表示 MySQL 在表中找到所需行的方式。其中常用的类型有:ALL、index、range、 ref、eq_ref、const、system、NULL  这些类型从左到右,性能逐渐变好。

2、possible_keys

查询时可能使用到的索引(但不一定会被使用,没有任何索引时显示为 NULL)。

3、key

实际使用到的索引。

4、rows

估算查找到对应的记录所需要的行数。

5、Extra

比较常见的是下面几种:

对于刷脏页的情况,我们需要控制脏页的比例,不要让它经常接近 75%。同时还要控制 redo log 的写盘速度,并且通过设置  innodb_io_capacity 参数告诉 InnoDB 你的磁盘能力。

总结

写操作

读操作

到此,相信大家对“SQL慢的原因有哪些”有了更深的了解,不妨来实际操作一番吧!这里是亿速云网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!

推荐阅读:
  1. MySQL慢SQL语句常见原因是什么
  2. Mysql开启慢SQL并分析原因

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

sql

上一篇:s5pv210开发板linux怎么查看磁盘空间剩余

下一篇:怎么使用Linux自带的gssftp服务

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》