SQl语句为什么变“慢”（mysql45讲）

Mysql的刷脏页机制

问题
小结

问题

一条SQl语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，而且这样的场景很难复现，它不只随机，而且持续时间还很短，看上去就像Mysql抖了一下，这是什么原因？

mysql“抖”的原因

InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作，这个日志叫Redo log(重做日志)
把内存里的数据写入磁盘的过程，术语就是Flush
当内存数据页和磁盘数据页内容不一致时，我们称这个内存页为脏页，内存数据写入到磁盘后（Flush），内存和磁盘上的数据页内容就一致了，称为干净页

平时执行很快的更新操作，其实就是在写内存和日志，而Mysql偶尔“抖”一下的那个瞬间，可能就是刷脏页

什么情况会引发数据库的flush过程呢?

InnoDB的RedoLog写满了，这时候系统会停止所有更新操作，把Check Point(两个指针：一个Write pos，一个Check point) 往前推，Redo log留出空间可以继续写，check Point往前推的同时会刷掉对应位置的脏页
系统内存不足，当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用，如果淘汰的是脏页，就要先将脏页写到磁盘

问题：难道不能直接把内存页给淘汰，下次把数据从磁盘读出来，然后取出RedoLog应用不就行了吗?
```
这里是从性能上考虑，如果刷脏页一定会写盘，就保证了数据页有两种状态
		1.一种是内存里存在，内存里肯定是正确的结果，直接返回
		2.一种是内存里没有数据，就可以肯定数据文件上是正确的的结果，读入内存后返回，这样的效率最高
```
Mysql认为系统空闲的时候，当然刷一刷
Mysql正常关闭的情况，这时候Mysql会把内存里的脏页都Flush到磁盘上，这样下次Mysql启动时，就可以直接从磁盘上读数据，而且启动速度还很快

思考

第一种是Redo log写满了，要Flush脏页，这种情况是InnoDB尽量要避免的，因为出现这种情况的时候，整个系统就不能在接受更新了，所有的更新都必须要堵住，如果你从监控上看，这时候更新数会跌为0
第二种是内存不够用了，要先将脏页刷到磁盘，这种情况是常态，InnoDB使用缓冲池(buffer pool)管理内存，缓冲池中的内存页有三种状态
```
1.还没有使用的
2.使用了并且是干净的
3.使用了并且是脏页
```

注意点： InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少

刷盘策略：

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

所以：一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长
所以：InnoDB需要有控制脏页比例的机制！！！

InnoDB刷脏页的控制策略

首先要正确的告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快?

这里就要用到innodb_io_capacity这个参数，它会告诉InnoDB你的磁盘能力，这个值建议设置成磁盘的IOPS,磁盘的IOPS是可以通过fio这个工具(fio是一个测试磁盘IO的工具，需要下载安装)来测试，下面的语句是用来测试磁盘随机读写的命令：

mysql> fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest;

InnoDB的刷盘速度就是要参考这两个因素，一个是脏页比例，一个是redo log写盘速度

参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%,InnoDB会根据当前的脏页比例（假设为M）,算出一个范围在0到100之间的数字，计算这个数字的伪代码类似如下：

mysql> show variables like 'innodb_max_dirty_pages_pct';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| innodb_max_dirty_pages_pct | 75.000000 |
+----------------------------+-----------+
1 row in set (0.00 sec)

其中，脏页比例是通过Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total


mysql> select variable_value into @a from gloable_status where Variable_name = 'Innodb_buffer_pool_pages_dirty';

mysql> select variable_value into @b from global_status where variable_name = 'Innodb_buffer_pool_pages_total';

select @a/@b;

一个有趣的策略：

一旦一个请求需要在执行过程中先Flush掉一个脏页时，这个查询就可能比平时慢了，而Mysql中的一个机制，可能会让你的查询会更慢，在准备刷一个脏页时，如果这个数据页旁边刚好是脏页，就会把这个旁边的脏页一起刷掉，并且这个逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟他相邻的数据页也还是脏页的话，也会被放到一起刷
在InnoDB中。InnoDB_flush_neighbors参数就是用来控制这个行为的，值为1的时候会有上述的连坐机制，值为0时表示不找旁边的脏页刷，只是自己刷自己的，Mysql8之后就把这个值设置成0了

小结

主要讨论了导致Mysql突然变慢的两种情况，一个是redolog不够用，一个是内存不够用，然后又讨论了InnoDB刷脏页的一些策略，先告诉Mysql自己的磁盘IO能力，然后设置对应的参数，设置的参数主要有两个因素影响，一个是Redo log写的速度，另一个是脏页比例，我们能做的是控制好脏页比例，然后告知mysqlIO自己的IO能力即可

文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/202544.html