一、场景简介

对于同一个业务场景，执行同一条SQL语句，但是后一条的速度相对于前一条显著降低，甚至引起阻塞超时的情况，下面记录关于类似场景的分析流程，便于自己深入理解MySQL。

二、数据存储简易模型

.png

当更新一个数据时，首先会更改内存中的数据，然后将相关的操作写入redolog日志，至此更新数据结束。后续获取数据都会首先在内存中查找，没有找到才会从磁盘读入。注意此时数据并没有立刻写入磁盘，因此内存中和磁盘中不相同的数据页被称之为“脏页”。刷新“脏页”的数据到磁盘中的操作被称为“flush”。如果一个操作触发了flush，那么必然会消耗相当多的性能，因此下面具体分析flush的时间点。

三、flush时间点

从上面的简易模型可简单推断出，影响flush的因素有以下2个。

redolog日志文件的大小
内存的大小

以下是4种flush的情况

1.redolog日志文件写满

当触发一个更新操作，会写入redolog，但redolog并不是无限增量续写的，它是被设计来可以重复写入的文件系统。因此当redolog被写满时，必须要将其中的一部分内容“擦除”，此时就需要触发相关“脏页”的“flush”操作。

2.内存被写满

当获取数据或者写入数据时，都是首先操作内存中的数据，这些数据可以理解成一个持久化的缓存。这是避免每次操作都会触发磁盘I/O，因此内存中会存在许多数据页。当将分配的内存占满时，此时也必须要触发flush操作，这样可以释放掉一些内存中的“脏页”。

3.系统闲暇时

MySQL也不是一直“繁忙”的，因此在负载不大时，也会触发flush操作。

4.正常关闭时

MySQL在正常关闭时，会触发flush操作。

四、优化思想

从上面的“flush”时间点，可以很明显看出，一旦触发了第一二种情况，则会导致当条SQL速度变慢。而且MySQL的flush是针对数据页的，一旦相邻数据页也有改动，也会连带写入磁盘，极端情况可能导致“flush”操作很慢，系统阻塞。因此我们必须针对内存和磁盘I/O做优化。

针对磁盘I/O速度做优化

MySQL有个参数innodb_io_capacity，它是用于告诉MySQL当前主机的磁盘I/O能力，因此系统才能正确知道当所有的磁盘I/O能力都被用于“flush”时，能有多快。当然系统不可能将所有的磁盘I/O能力都分配给MySQL，MySQL内部会根据这个值计算出一个刷新速度。
针对内存做优化

如果使用SSD固态硬盘时，建议关闭flush的“连带刷新”机制，也就是flush数据页A时，假如数据页B是相邻页且是脏页，也一起flush。因为固态硬盘的磁盘I/O速度是很快的，即便多几次I/O也不影响，但减少了单次数据吞吐量，会减少极端情况。第二尽量控制脏页的比例上限，这个值也会用于计算“flush“的刷新速度。
- innodb_max_dirty_pages_pct
  
  控制脏页比例上限
- innodb_flush_neighbors
  
  取消连带刷新机制

同样的SQL语句，为什么后一条比前一条执行速度慢很多