SQL语句为什么有时候会执行的很慢
这种情况是随机的,有时候突然你执行一条语句就会变慢,这是为什么呢?
从以前的内容可以得知,InnoDB在执行更新语句的时候,有一个redo log,更新内存写完redo log之后就返回给客户端,代表着更新成功。
将内存中的数据写入磁盘的过程就叫做flush,当内存数据页跟磁盘数据页不一样的时候,称这个内存页为脏页,当吧内存数据写入磁盘后,内存和磁盘上的数据页就一致了,成为“干净页”
在执行Mysql语句的过程中,有可能因为flush过程使得执行过程变慢,那么哪些情况会导致flush呢?
1. InnoDB的redo log写满了。这个时候系统就会停止所有更新操作吧check point往前推进,使得redo log留出空间继续写
2. 系统内存不足,需要新的内存页,内存不够用的时候就要淘汰出一些数据页,空出内存页给别的数据页使用,如果淘汰的数据页是脏页,就得把脏页写入到磁盘中。
那么为什么不直接淘汰内存,从磁盘读入数据页,应用的时候直接把redo log拿出来就行了。
从性能来考虑,刷脏页会写盘,保证每个数据页有两种状态,1.内存中存在就会直接返回。2.内存中没有数据,将数据文件上的数据读入内存后返回
3.在系统空闲的时候刷脏页进行更新
4. Mysql正常关闭的时候
那么接下来分析上述四种场景刷脏页时对性能的影响
第三种情况的时候是系统空闲的时候去刷,很显然对性能没有什么影响,第四种是Mysql关闭的时候,也没有什么影响,接下来我们主要看一二种情况
第一种是redo log写满了,出现这种情况是任何时候都要去避免的,因为redo log写满后会,需要立即将redo log中的内容更新到内存当中,因为redo log已满,无法记录其他更新,这个时候就得堵住更新入口,这个时候更新数跌为0。
第二种情况是内存不够用,要现将脏页写入到内存当中,这种情况是常态,InnoDB用缓存池(buffer pool)管理内存,内存页有三种状态:1.未使用2.使用了为干净页3.使用了为脏页
InnoDB的策略是尽量使用内存,因此对于一个库来说,未被使用的页面很少,当读入的数据页没有内存的时候,就得去buffer pool中申请一个数据页,这个时候它会将很久不用的数据页给淘汰掉,当淘汰的数据页为干净页时,就直接释放使用,当为脏页的时候,就会刷入到磁盘变为干净页再使用。
刷脏页太多也会导致查询响应的时间变长
为了避免上述的两种情况,InooDB需要控制脏页比例的时机
innodb_io_capacity时告诉InnoDB你的磁盘能力的
因素有两种为脏页太多或者是redo log写满
故考虑脏页比例和redo log写盘速度这两种
innodb_max_dirty_pages_pct是脏页比例上限默认为百分之七十五
InnoDB会根据当前脏页比例,计算出一个数字:100*M/innodb_max_dirty_pages_pct得出F1(M)
InnoDB每次写入日志都有一个序号,写入序号与check point对应的序号之间的差值为N,他会赓续N拿出一个1-100的数字计算出F2(N),N越大,数值越大
上述算出F1(M),F2(N)两个值,取较大的为R,引擎就会按照innodb_io_capacity乘以R%来控制刷脏页的速度。
为了避免这种情况,要合理的设置innodb_io_capacity的值,并且时刻关注脏页比例,不让他超过百分之七十五。
脏页中的连坐机制
当一个查询请求在执行过程中会先flush一个脏页的时候,如果旁边的数据页也为脏页,就会一起flush掉,这个邻居的邻居如果也是脏页也会被flush掉。
innodb_flush_neighbors就是来控制这种情况的,当它等于1的时候执行,等于0的时候不执行
在Mysql8.0之后默认为0