【MySql】 12 SQL 语句变“慢”的原因

一、脏页、干净页

1、脏页：当内存数据页跟磁盘数据页内容不一致

2、干净页：内存数据写 入到磁盘后，内存和磁盘上的数据页的内容就一致

3、flush 操作： 把内存里的数据写入磁盘的过程

二、InnoDB 引擎下 什么时候会触发flush 操作

1、 InnoDB的redo log写满: 系统会停止所有更新操作， 把 checkpoint 往前推进，redo log 留出空间可以继续写

2、淘汰“脏页”: 系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘 汰一些数据页，空出内存给别的数据页使用 => 要先将脏页写到磁盘

刷页可以保证数据的正确性(1)内存里存在，内存里就肯定是正确的结果，直接返回;(2)内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。 这样的效率最高。

3、MySQL认为系统“空闲”的时候刷新脏页

4、MySQL 正常关闭的情况,MySQL 会把内存的脏页都 flush 到磁盘上。

MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

三、四种刷盘性能影响

1、空闲、正常关闭这两种不会对我们的业务有影响。

2、“redo log 写满: 系统就不能再接受更新了,所有的更新都必须堵住,有监控显示的花更新数变为0。

3、内存不够用，要先将脏页写到磁盘

当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉:如果要淘汰的是一个干净页，就直接释放出来复用;但如果是脏页，就必须将脏页先刷到磁盘，变成干净页后才能复用。

4、InnoDB 用缓冲池(buffer pool)管理内存，缓冲池中的内存页有三种状态

1. 没有使用

2. 使用了并且是干净页

3. 使用了并且是脏页

5、InnoDB 的策略是尽量使用内存 => 未被使用的页面很少(长时间运行的库),因此内存不够用刷新脏页是正常操作。

6、什么时候刷脏页影响性能？

1. 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长。

2. 日志写满，更新全部堵住，写性能跌为0, 对业务影响比较大。

四、InnoDB 刷脏页的控制策略

1、使用 innodb_io_capacity: 告诉 InnoDB 你的磁盘能力。设置成磁盘的IOPS。磁盘的 IOPS 可以通过 fio 这个工具来测试

2、 控制刷脏页的速度 => InnoDB的刷盘速度参考因素: 脏页比例，redo log写盘速度。

a: innodb_max_dirty_pages_pct 是脏页比例上限,默认值是 75%。InnoDB 会根据当前的脏页比例算出。=> F1

b: InnoDB 每次写入的日志都有一个序号，根据当前写入的序号跟 checkpoint 对应的序号之间的差值算出 => F2(差值越大 F2 值越大)

c: 根据max(F1,F2) :取其中较大的值记为 R，之后引擎就可以 按照 innodb_io_capacity 定义的能力乘以 R% 来控制刷脏页的速度

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