为什么我的MySQL会抖一下？

1.你的SQL语句为什么变慢了？

可能你会发现，有时候执行SQL语句会比平时要慢，这个是为什么呢？

我们回顾下《日志系统：一条SQL更新语句是如何执行的？》中讲到的故事，孔乙己经常来赊账，掌柜为了提高记账速度，所以使用粉板来先记账，等有空的时候才记到账本上面。

做下类比的话，掌柜记账的账本是数据文件，记账用的粉板是日志文件（redo log），掌柜的记忆就是内存。

掌柜更新账本的时候，也就相当于把内存里的数据写入磁盘的过程，术语称为flush。在flush之前，孔乙己新的赊账和账本上的赊账是不一致的。

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页内容就一致了，称为“干净页”。

接下来，用图示来展示下孔乙己赊账的整个过程。假设原来孔乙己已经赊账了10文，这次又要赊9文。

其实很容易想到，MySQL抖的时候，就是就是在刷脏页。那什么情况会出现刷脏页的过程呢？

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，使得redo log留出空间可以继续写。（粉板满了，记不下了）。
   

2. 系统内存不足，当需要内存页时，内存不够用了，就要淘汰数据页，空出内存给别的数据用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。（生意太好，掌柜记不住了）

3. MySQL认为系统空闲的时候，就会刷一下脏页。（生意不忙的时候）

4. MySQK正常关闭的时候，MySQL会把内存的脏页都flush到磁盘上，这样下次MySQL启动时，可以直接从磁盘读数据。（酒店歇业休息的时候）

接下来，我们分析四种情况对性能的影响：

1. 对应第一种情况，整个系统无法更新，更新性能为0
2. 对应第二种情况，需要将脏页刷到磁盘上，耗费性能
3. 对应第三种情况，空闲时不占用性能
4. 对应第四种情况，关闭时不占用性能

InnoDB是使用缓存池（buffer pool）管理内存，缓存池中的内存页有三种状态：

1. 还没有使用的
2. 使用了并且是干净页
3. 使用了并且是脏页

当内存不够的时候，就需要从缓存池中淘汰数据页。如果淘汰的是干净页，就可以直接释放出来使用。如果淘汰的是脏页，那就需要刷到磁盘。

2.InnoDB刷脏页的控制策略

因为刷脏页十分影响性能，所以下面讨论下控制刷脏页的速度。

如果出现刷脏页的情况，主要原因是内存脏页太多，其次是redo log写满。因此，我们主要考虑两个因素：脏页比例，redo log写盘速度。

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。参数innodb_io_capacity是innodb刷磁盘的速度。我们会将innodb_io_capacity转换成0-100的值，然后会选择两者较大来控制刷脏页的速度，如下图：

最后是一个有趣的策略，MySQL中会存在一个机制：在准备刷一个脏页的时候，如果这个数据页旁边也是脏页，那么就会被一起刷掉。并且这种方式会出现无限连带的情况，导致一次刷很多脏页。当我们拥有高性能的磁盘时，可以通过设置innodb_flush_neighbors为0，来避免这种行为。

来源：自己整理的MySQL实战45讲笔记