从头开始搞懂 MySQL(07)为什么同一条 SQL 时快时慢

1、为什么 SQL 会变慢

在我们平时工作的时候,有时候会发现,同一条 SQL 语句,在正常执行的时候特别快,但有时候不知道为什么,它就会变慢,并且这样的场景很难复现。是什么导致了 SQL 语句变慢了呢?

我们在 一条 SQL 更新语句是如何更新的 中,知道了 WAL 机制,InnoDB 在处理更新语句的时候,只是做了一个写日志的磁盘操作,这个日志叫做 redo log,更新内存写完 redo log 之后,就会返回给客户端,这次更新成功

什么是 flash

在 redo log 记录的差不多后,会把内存中的数据写入磁盘中,这个过程叫做 flush,在这个 flush 操作执行之前,内存数据页跟磁盘数据页内容不一致,我们称这个内存页为脏页,内存数据写入到磁盘中后,内存和磁盘上的数据页内容一致,称为干净页

我们知道了这些后,很容易能想到,平时执行很快的更新操作,其实就是在写内存和日志,而 SQL 突然变慢的时候,可能就是在(flush)刷脏页

2、什么情况下会触发 flash 过程
  • 第一种情况是如果 redo log 写满了,在这个时候,系统会停止所有更新操作,把脏页数据 flush 到磁盘上,然后 checkpoint 往前推进,redo log 留出空间可以继续写下去。
  • 第二种场景是,系统内存不足,当需要新的内存页,而内存不够的时候,就需要腾出来一些数据页,空出内存供其它数据页使用,如果是脏页的话,就要先将脏页写到磁盘
  • 第三种场景是,在系统比较空闲的时候,MySQL 会将一些脏页写到磁盘中去,系统也没什么压力
  • 第四种场景,如果 MySQL 正常关闭的时候,会将所有的脏页都刷到磁盘上

第一种情况是我们要尽量避免的,出现这种情况的话,整个系统就不能接收更新了

第二种情况其实比较常见,InnoDB 使用缓冲池(buffer pool)管理内存,缓存池中的内存有三种状态(还未使用的,使用了并且是干净页,使用了并且是脏页),InnoDB 的策略是尽量使用内存,对一个长时间运行的库来说,没有被使用的页会很少,但是出现以下两种情况,都会影响性能

  • 一个查询要淘汰的脏页个数太多,会导致查询的响应时间明显变长
  • 日志写满,更新全部堵住,写性能跌到 0
3、InnoDB 刷脏页的控制策略

innodb_io_capacity 这个参数控制的是 InnoDB 的磁盘能力,我们可以将这个值设置为磁盘的 IOPS,磁盘的 IOPS 可以通过 fio 这个工具来测试

innodb_max_dirty_pages_pct 是脏页比例上线,默认是 75%,我们要合理的设置 innodb_io_capacity 的值,并且多关注脏页比例,不要让它接近 75%

脏页比例是通过 Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total 得到的

一旦一个查询请求需要值执行过程中先 flush 掉一个脏页时,这个查询就可能比平时慢了,MySQL 中有一个机制,可能会使查询更慢:

在准备刷一个脏页的时候,如果这个数据页旁边的数据刚好是脏页,就会把相邻的脏页也一起刷掉,这个逻辑可以继续蔓延下去

在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述行为,值为 0 的话只会刷新自己,MySQL 8.0 中默认是 0

  • 3
    点赞
  • 9
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
如果你从头开始学习MySQL数据库,以下是一个简单的学习路径建议: 1. 学习基本概念:了解数据库的基本概念,如什么是数据库、表、字段、行、列、主键、外键等。可以参考MySQL官方文档或在线教程。 2. 安装和配置MySQL:根据你的操作系统,选择合适的MySQL版本并进行安装。学习如何配置和启动MySQL服务器。 3. 学习SQL语言:SQL是用于与数据库进行交互的语言,学习SQL语句的基本语法和常用操作,如创建表、插入数据、查询数据、更新数据和删除数据等。 4. 数据库设计与规范化:学习如何进行数据库设计,包括确定表结构、定义主键和外键、选择合适的数据类型等。了解数据库规范化的概念和规则,以提高数据库的性能和数据完整性。 5. 学习MySQL工具和命令行:掌握一些常用的MySQL工具,如MySQL Workbench、phpMyAdmin等,并熟悉使用命令行界面进行数据库操作。 6. 索引和查询优化:学习如何创建索引以提高查询性能,了解索引的类型和使用场景。学习如何优化查询语句,包括使用索引、避免全表扫描、合理使用JOIN等。 7. 数据备份和恢复:学习如何进行数据库备份和恢复操作,包括完全备份、增量备份、恢复到指定时间点等。 8. 安全和权限管理:了解如何设置数据库用户和权限,限制用户的访问权限,保护数据库的安全性。 9. 实践项目:通过实践项目来巩固所学知识,例如创建一个简单的数据库应用或进行数据分析。 在学习过程中,可以结合官方文档、在线教程、书籍和实践项目来深入理解和应用MySQL数据库。同时,多与社区和论坛互动,参与讨论和解决问题,以扩展你的知识和技能。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

一起来搬砖呀

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值