12为什么我的MySQL会“抖”一下

为什么有时候SQL会忽然变慢

创建时间：2021年11月15日18:38:21

编辑时间：2021年12月7日15:58:15

————————————————————————————————

数据脏页、干净页

问：什么是脏页？什么是干净页？

答：内存中与磁盘数据不一致的数据页为脏页，一致的则为干净页。

问：redo log上的数据更新记录什么时候同步到磁盘？会出现什么情况？

答：内存中数据页的淘汰伴随着将redo log中的对应的记录标记为清理（本质是通过版本号实现的，并不会真的去动log文件）。

redo log满时，check point继续向前推进，同时将对应的脏页flush到磁盘中，此时停止所有的更新操作；

redo log快满时，先把内存中的一些脏页淘汰掉。淘汰策略是LRU，

redo log平时见缝插针地刷脏页。

数据库正常关闭时，刷完内存中所有脏页。

问：redo log的ck推进过程中，将推进过程中所涉略的脏页flush到磁盘，是否会flush到不在推进段中的更新？如果是，那之后再推进到这些redo log更新语句时，如何处理？

答：知识点包含了lsn。

首先，脏页的flush是以数据页为单位的，所以会flush到上述问题所提的不在ck推进段中的数据页变动记录。

我把下面的回答放到专栏笔记里了：

每个redo log记录上，都会以事务为单位记录对应的非连贯但单调递增的lsn号，等对应的数据页在内存中有应用到redo log的变更时，会将对应数据页标记上最新的lsn号。
当redo log快满时，要推进ck point，推进时，涉及到的数据页脏页，会被全部flush到磁盘，这时候可能flush的更新内容，远远比ck推进部分记录的redo log更新语句要多得多，那后面ck再推进时候，原本已经被flush的更新还会要这些数据页还要再刷一遍吗？
不需要了，那时候会通过数据页的lsn号比对，直接判定该数据页应用过对应的更新，那时候，也就是ck第二次推进时，就会直接淘汰掉已经应用过的redo log记录。

问：内存快满时，为什么是提前淘汰内存中的“脏页”的同时要进行flush，而不是直接丢弃，等下次读入内存时再更新呢？

答：不论是前后，都必然需要对磁盘进行更新操作，但后者下次读干净的数据页进入内存时，还要额外判断是否要应用redo log，开销会更大。

问：数据页的淘汰策略是什么？

答：LRU。淘汰最久不使用的数据页，如果是干净页，直接释放，如果是脏页，先将脏页刷进磁盘。

问：什么场景会让sql语句忽然变慢？

答：

一个查询语句恰好要淘汰的脏页数据过多。

redo-log日志被迅速写满。

问：在redo log、change buffer之间流动的更新数据最终是如何落地到磁盘的？

答：若一个更新语句中带普通二级索引，

更新时，如果目标数据页不在内存当中，则首先在change buffer中记录更新内容，redo log中记录change buffer的变动内容，

change buffer通过后台线程将自身的变动数据持久化到磁盘的系统表空间，

等客户端要访问到对应的数据时，加载数据进入内存，此时change buffer中有对应内容，则优先进行merge，

redo log会再记录merge的内容（等于内存中数据页的变动）和change buffer的变动内容，change buffer本身会再次持久化自己的新变动记录（merge时的过程记录）到磁盘

最后，某个时候，内存中的数据页会被flush到磁盘。

刷脏页控制策略

问：什么参数影响平时数据库刷脏页的速度？怎么查看设置？

答：数据库根据innodb_io_capacity参数值来判断磁盘能力，越小则认为能力越差。

show variables like '%innodb_io_capacity%'

一般默认IO为200，可以设置为磁盘的IOPS（如果该服务仅做为数据库支持的话）

linux命令

yum install fio -y

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

问：根据什么来设置这个刷脏页的速度？

答：脏页比例和redo log的写盘速度。

脏页比例越大或redo log中当前记录到ck之间的长度越大，刷脏页的速度越快，峰值受innodb_io_capacity影响。

-- 查询脏页比例上限
show  variables like '%innodb_max_dirty_pages_pct%'

F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

上面是伪代码，计算脏页比例关联的一个0~100的数字R1

F2()为当前redo log写入的序号到ck之间的长度为参数的一个函数，长度越长，函数值0~100得到的值R2就越大

最后根据两者间最大值Rx% * innodb_io_capacity决定刷脏页的速度。

问：怎么查询当前的数据库脏页比例？

答：

select VARIABLE_VALUE into @a from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty'; select VARIABLE_VALUE into @b from performance_schema.global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'; select @a/@b;

问：刷脏页时有什么逻辑需要额外注意？

答：如果被刷掉的脏页旁边也有脏页，则会“火烧燎原之势蔓延下去”。

-- 查询该参数设置值 为0则关闭该功能 
show variables like '%innodb_flush_neighbors%'

问：淘汰内存时要清理redo log，那这不就成了随机读写了吗？

答：直接刷脏页是不会动redolog的，等后续应用redolog的时候，会根据LSN 的大小来判断这个页有没有应用到这条log，有则跳过。

问：淘汰内存时要清理redo log，那这不就成了随机读写了吗？

答：直接刷脏页是不会动redolog的，等后续应用redolog的时候，会根据LSN 的大小来判断这个页有没有应用到这条log，有则跳过。