189 8069 5689
本篇文章为大家展示了MySQL性能下降的原因有哪些，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。
SQL 执行突然变慢的原因
在之前讲解 MySQL Redo log 时，说到了 WAL 机制，为了保证 MySQL 更新的速度，在进行更新操作时，先将更新内容写入 redo log，后续系统空闲时，再将 redo log 的内容应用到磁盘。
当内存数据页（redo log）和磁盘数据页内容不一致时，将该内存也称为 “脏页”。将内存数据写入到磁盘后，数据一致，内存页称为 "干净页"。
在内存数据写入磁盘时，这个过程称为 flush 过程。SQL 突然执行变得很慢，性能下降。原因就可能和 flush 操作有关。
因为在进行 flush 操作时，更新操作会等待 redo log 的写入。
引起 flush 操作的原因
场景一：redo log 日志已经记满。这时系统会停止更新操作，将 check point 向前推进，让 redo log 留出空间可以继续写。
这里假设 CP 到 CP‘ 间隙已经写入到磁盘，这部分就变成了干净页，此时 write pos 就可以写入这部分区域了。
场景二：系统内存不足，需要新的内存页时，发现内存不够用了，就需要淘汰一些数据页。如果淘汰时，这时数据页时脏页，就要将脏页写到磁盘。
这时有个问题是，命名 redo log 中的内容已经被记录到日志中了，假如内存满了，直接删除不就可以吗？下次读入时，再把 redo log 日志中的内容应用到磁盘。
没有选择直接清空内存，是从性能考虑的，因为在查询数据时，有两种情况：
所以这样效率比较高。
场景三：MySQL 会在系统空闲时，进入 flush 操作。
场景四：在 MySQL 正常关闭时，会把内存脏页 flush 到磁盘上。
引起 flush 对性能的影响
对于第三，四场景来说，是比较正常的情况，不需要考虑性能问题。
对于第一种场景，InnoDB 会尽量避免，因为在这种情况下，整个系统不再接受更新。
但有时出现人为的配置错误，比如内存为 128 GB，innodb_io_capacity 设置为 20000 的实例。通常建议将 redo log 设置成 4 个 1GB 的文件。但由于配置错误，设置成 100M 的文件。
这里由于 redo log 设置的太小，很快就会被写满。write pos 一直追着 check point. 这时，系统只能停止所有更新，推进 checkpoint.
表现就是，磁盘 IO 很小，但是出现间歇性的性能下降。
对于第二种场景，内存不够用的情况，InnoDB 会用缓冲池（buffer pool）管理内存
内存页在缓冲池中会有三种状态：
每个数据页头部有LSN，8字节，每次修改都会变大。
对比这个 LSN 跟 checkpoint 的 LSN，比checkpoint小的一定是干净页
由于 InnoDB 的策略是尽可能使用内存，所以对于长时间运行的库来说，未被使用的页面很少。
当发现想读入的数据页没有在内存中时，必须到缓冲池申请数据页。并会把最久不用得数据页从内存中淘汰
如果是干净页，直接释放使用
如果是脏页，必须先刷盘，变成干净页才能复用
当时，如果在下面的情况进行刷脏页，会明显影响性能：
要淘汰的脏页太多，导致查询响应时间较长。
日志写满，更新被阻塞。
为了解决这个问题，InnoDB 使用控制脏页比例的机制，来避免上面的情况。
InooDB 控制刷脏页的策略
在 InnoDB 中，通过 innodb_io_capacity 参数，来告诉 InnoDB 目前主机的磁盘能力是多少，这个值建议设置成磁盘的 IOPS.
可以通过 fio 这个工具来测试：
fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest