做网站和做公众号,北京云邦网站建设,godaddy网站建设,网站建设与网站管理正文
作为后端开发#xff0c;日常操作数据库最常用的是写操作和读操作。读操作我们下边会讲#xff0c;这个分类里我们主要来看看写操作时为什么会导致 SQL 变慢。
刷脏页
脏页的定义是这样的#xff1a;内存数据页和磁盘数据页不一致时#xff0c;那么称这个内存数据页…正文
作为后端开发日常操作数据库最常用的是写操作和读操作。读操作我们下边会讲这个分类里我们主要来看看写操作时为什么会导致 SQL 变慢。
刷脏页
脏页的定义是这样的内存数据页和磁盘数据页不一致时那么称这个内存数据页为脏页。
那为什么会出现脏页刷脏页又怎么会导致 SQL 变慢呢那就需要我们来看看写操作时的流程是什么样的。
对于一条写操作的 SQL 来说执行的过程中涉及到写日志内存及同步磁盘这几种情况。 这里要提到一个日志文件那就是 redo log位于存储引擎层用来存储物理日志。在写操作的时候存储引擎这里讨论的是 Innodb会将记录写入到 redo log 中并更新缓存这样更新操作就算完成了。后续操作存储引擎会在适当的时候把操作记录同步到磁盘里。
看到这里你可能会有个疑问redo log 不是日志文件吗日志文件就存储在磁盘上那写的时候岂不很慢吗
其实写redo log 的过程是顺序写磁盘的磁盘顺序写减少了寻道等时间速度比随机写要快很多 类似Kafka存储原理因此写 redo log 速度是很快的。
好了让我们回到开始时候的问题为什么会出现脏页并且脏页为什么会使 SQL 变慢。你想想redo log 大小是一定的且是循环写入的。在高并发场景下redo log 很快被写满了但是数据来不及同步到磁盘里这时候就会产生脏页并且还会阻塞后续的写入操作。SQL 执行自然会变慢。
锁
写操作时 SQL 慢的另一种情况是可能遇到了锁这个很容易理解。举个例子你和别人合租了一间屋子只有一个卫生间你们俩同时都想去但对方比你早了一丢丢。那么此时你只能等对方出来后才能进去。
对应到 Mysql 中当某一条 SQL 所要更改的行刚好被加了锁那么此时只有等锁释放了后才能进行后续操作。
但是还有一种极端情况你的室友一直占用着卫生间那么此时你该怎么整总不能尿裤子吧多丢人。对应到Mysql 里就是遇到了死锁或是锁等待的情况。这时候该如何处理呢
Mysql 中提供了查看当前锁情况的方式 通过在命令行执行图中的语句可以查看当前运行的事务情况这里介绍几个查询结果中重要的参数 当前事务如果等待时间过长或出现死锁的情况可以通过 「kill 线程ID」 的方式释放当前的锁。
这里的线程 ID 指表中 trx_mysql_thread_id 参数。
读操作
说完了写操作读操作大家可能相对来说更熟悉一些。SQL 慢导致读操作变慢的问题在工作中是经常会被涉及到的。
慢查询
在讲读操作变慢的原因之前我们先来看看是如何定位慢 SQL 的。Mysql 中有一个叫作慢查询日志的东西它是用来记录超过指定时间的 SQL 语句的。默认情况下是关闭的通过手动配置才能开启慢查询日志进行定位。
具体的配置方式是这样的
查看当前慢查询日志的开启情况 开启慢查询日志临时 注意这里只是临时开启了慢查询日志如果 mysql 重启后则会失效。可以 my.cnf 中进行配置使其永久生效。
存在原因
知道了如何查看执行慢的 SQL 了那么我们接着看读操作时为什么会导致慢查询。
1未命中索引
SQL 查询慢的原因之一是可能未命中索引关于使用索引为什么能使查询变快以及使用时的注意事项网上已经很多了这里就不多赘述了。
2脏页问题
另一种还是我们上边所提到的刷脏页情况只不过和写操作不同的是是在读时候进行刷脏页的。
是不是有点懵逼别急听我娓娓道来
为了避免每次在读写数据时访问磁盘增加 IO 开销Innodb 存储引擎通过把相应的数据页和索引页加载到内存的缓冲池buffer pool中来提高读写速度。然后按照最近最少使用原则来保留缓冲池中的缓存数据。
那么当要读入的数据页不在内存中时就需要到缓冲池中申请一个数据页但缓冲池中数据页是一定的当数据页达到上限时此时就需要把最久不使用的数据页从内存中淘汰掉。但如果淘汰的是脏页呢那么就需要把脏页刷到磁盘里才能进行复用。
你看又回到了刷脏页的情况读操作时变慢你也能理解了吧
防患于未然
知道了原因我们如何来避免或缓解这种情况呢
首先来看未命中索引的情况
不知道大家有没有使用 Mysql 中 explain 的习惯反正我是每次都会用它来查看下当前 SQL 命中索引的情况。避免其带来一些未知的隐患。
这里简单介绍下其使用方式通过在所执行的 SQL 前加上 explain 就可以来分析当前 SQL 的执行计划 执行后的结果对应的字段概要描述如下图所示 这里需要重点关注以下几个字段
1、type
表示 MySQL 在表中找到所需行的方式。其中常用的类型有ALL、index、range、 ref、eq_ref、const、system、NULL 这些类型从左到右性能逐渐变好。 ALLMysql 遍历全表来找到匹配的行 index与 ALL 区别为 index 类型只遍历索引树 range只检索给定范围的行使用一个索引来选择行 ref表示上述表的连接匹配条件哪些列或常量被用于查找索引列上的值 eq_ref类似ref区别在于使用的是否为唯一索引。对于每个索引键值表中只有一条记录匹配简单来说就是多表连接中使用 primary key 或者 unique key作为关联条件 const、system当 Mysql 对查询某部分进行优化并转换为一个常量时使用这些类型访问。如将主键置于 where 列表中Mysql 就能将该查询转换为一个常量system 是 const类型的特例当查询的表只有一行的情况下使用system NULLMysql 在优化过程中分解语句执行时甚至不用访问表或索引例如从一个索引列里选取最小值可以通过单独索引查找完成。
2、possible_keys
查询时可能使用到的索引但不一定会被使用没有任何索引时显示为 NULL。
3、key
实际使用到的索引。
4、rows
估算查找到对应的记录所需要的行数。
5、Extra
比较常见的是下面几种 Useing index表明使用了覆盖索引无需进行回表 Using where不用读取表中所有信息仅通过索引就可以获取所需数据这发生在对表的全部的请求列都是同一个索引的部分的时候表示mysql服务器将在存储引擎检索行后再进行过滤 Using temporary表示MySQL需要使用临时表来存储结果集常见于排序和分组查询常见 group byorder by Using filesort当Query中包含 order by 操作而且无法利用索引完成的排序操作称为“文件排序”。
对于刷脏页的情况我们需要控制脏页的比例不要让它经常接近 75%。同时还要控制 redo log 的写盘速度并且通过设置 innodb_io_capacity 参数告诉 InnoDB 你的磁盘能力。
面试资料整理汇总 这些面试题是我朋友进阿里前狂刷七遍以上的面试资料由于面试文档很多内容更多没有办法一一为大家展示出来所以只好为大家节选出来了一部分供大家参考需要全部文档的关注小编后点击这里即可免费领取。
面试的本质不是考试而是告诉面试官你会做什么所以这些面试资料中提到的技术也是要学会的不然稍微改动一下你就凉凉了
一一为大家展示出来所以只好为大家节选出来了一部分供大家参考需要全部文档的关注小编后点击这里即可免费领取。
面试的本质不是考试而是告诉面试官你会做什么所以这些面试资料中提到的技术也是要学会的不然稍微改动一下你就凉凉了
在这里祝大家能够拿到心仪的offer
