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

一条SQL语句，正常执行时候特别快，但有时会变得特别慢，且这种情况很难复现，随机且持续时间很短，看上去像是“抖”了一下。
你的SQL语句为什么变“慢”了

在MySQL 02中，介绍了WAL机制，InnoDB在处理更新语句时，更新内存写完redo log后，就返回给客户端，本次更新成功。
而内存里的数据最后要写入磁盘，这个操作称为flush。当内存数据页跟磁盘数据页内容不一致时，称这个内存页为脏页，内存数据写入磁盘后两者上的数据页内容一致，称为干净页。不论脏页还是干净页，都是指的内存中的数据页。
对于开头提出的场景，平时执行很快的更新操作，其实就是在写内存和日志，而“抖”的瞬间，很有可能就是在flush脏页。
有几种情况会引发数据库的flush过程：

• InnoDB的redo log写满，这时候系统会停止所有的更新操作，通过flush把redo log中的checkpoint往前推进，使得redo log能有空间可以继续写；
  
• 系统内存不足。当需要新的内存页，而内存不够用，就需要淘汰一些数据页，空出内存给别的数据页。如果要淘汰的是脏页，就需要先将脏页刷到磁盘。可能会问，这种情况为什么不能直接淘汰内存，下次请求时从磁盘读入数据页，然后应用redo log？这里是从性能考虑的，对于刷脏页写盘的方法，保证每个数据页有两种状态：
  
  
  
  • 内存里存在，肯定是正确结果，能直接返回；
    
  • 内存里不存在，磁盘中肯定是正确结果，读入内存后返回。
    
  
  
• MySQL认为系统“空闲”，有机会就会进行flush；
  
• MySQL正常关闭时，会把内存的脏页都flush到磁盘，这样下次MySQL启动时，直接从磁盘读数据即可，启动速度很快。
  

分析上述四种情况对性能的影响：

• 第一种情况要尽量避免，因为这种情况下整个系统不能再更新，从监控上看更新数会跌为0；
  
• 第二种情况较常见。在InnoDB中，用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：
  
  
  
  • 还没有使用；
    
  • 使用了且是干净页；
    
  • 使用了且是脏页；
    
  由于InnoDB策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。如果要读入的数据页不在内存中，就需要在缓冲池申请一个数据页，把内存中最久未使用的数据页淘汰。如果淘汰的是脏页，就需要先flush。
  而如果一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长，会明显影响性能。
  
  
• 第三种情况，系统没什么压力；
  
• 第四种情况，数据库本身就要关闭了，不需要太关注性能问题。
  

InnoDB刷脏页的控制策略

接下来讲讲控制策略，以及和策略相关的参数。
首先，需要正确告诉InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候可以刷多快。这需要用到innodb_io_capacity参数，它会告诉InnoDB你的系统的磁盘能力，其值建议设置成磁盘的IOPS，而磁盘的IOPS可以通过fio工具进行测试。
知道了“全力刷脏页”的能力，但使用时不可能一直全力刷，毕竟磁盘能力不能只用来刷脏页，还需要服务用户请求。因此，需要让InnoDB控制引擎按照“全力”的一定百分比来刷页。
那么，如果设计策略控制刷脏页的速度，你会考虑哪些因素呢？考虑到如果刷太慢，内存中脏页数会太多，且redo log可能写满，因此主要参考因素就是：

• 脏页比例；
  
• redo log写盘速度。
  

InnoDB会根据这两个因素单独算出来两个数字：

• 参数innodb_max_dirty_pages_pct是脏页比例上限，默认为75%。InnoDB会根据当前脏页比例\(M\)，算出一个范围在0-100间的数字，计算方法为：
  1. F1(M)
  2. {
  3.   if M>=innodb_max_dirty_pages_pct then
  4.       return 100;
  5.   return 100*M/innodb_max_dirty_pages_pct;
  6. }

  复制代码
• InnoDB每次写入的日志都有一个序号，假设当前写入的序号跟checkpoint对应的序号之间的差值为\(N\)。InnoDB会根据\(N\)算出一个范围在0-100之间的数字，\(N\)越大算出来的值\(F2(N)\)越大。
  

根据两个数字\(F1(M)\)和\(F2(N)\)，取其中较大的值记为\(R\)，之后引擎可以按照innodb_io_capacity定义的能力乘以\(R\)%来控制刷脏页的速度。
上述过程的流程如图：

讲到这里你应该知道，无论是查询语句在需要内存的时候可能要求淘汰一个脏页，还是由于刷脏页会占用IO资源影响更新语句，都可能感知到MySQL“抖了一下。要尽量避免这种情况，就需要合理设置innodb_io_capacity的值，并且平时要多关注脏页比例，不要让它经常接近75%。
其中，脏页比例计算方法是Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total。

接下来，再看一个策略。
一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。MySQL中有个机制，可能让查询更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页也是脏页，就会把这个“邻居”也带着一起刷掉，那么可能不断往后顺延。
在InnoDB中，用innodb_flush_neighbors参数控制该行为，值为1会有上述机制。
这类优化在机械磁盘时代比较有意义，可以减少很多随机IO。而对IOPS比较高的设备比如SSD，建议把值设为0，因为这时候IOPS往往不是瓶颈，而只刷自己能更快执行完必要的刷脏页操作，减少SQL语句响应时间。
在MySQL 8.0中，该参数默认值已经为0了。

来源：豆瓜网用户自行投稿发布，如果侵权，请联系站长删除