一文带你了解MySQL之Change Buffer

置顶 DG0913L

已于 2023-05-25 15:35:38 修改

阅读量1.3k

点赞数 3

CC 4.0 BY-SA版权

分类专栏： MySQL性能优化+原理+实战文章标签： mysql 数据库数据库架构大数据数据库开发

于 2023-05-05 11:29:00 首次发布

本文链接：https://blog.youkuaiyun.com/liang921119/article/details/130501944

MySQL性能优化+原理+实战专栏收录该内容

31 篇文章

订阅专栏

文章介绍了MySQLInnoDB存储引擎的ChangeBuffer机制，用于减少写入数据时的磁盘IO操作，特别是针对非唯一普通索引页的INSERT、UPDATE和DELETE操作。ChangeBuffer将变更暂存，待后续读取时合并，从而提高数据库性能。内容包括ChangeBuffer的工作原理、触发时机、适用场景以及相关参数监控。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前言

上一篇文章一文带你了解MySQL数据库InnoDB_Buffer_Pool（点击跳转）我们学习了InnoDB Buffer Pool的工作原理，其作用是减少MySQL读取数据时直接与磁盘打交道的次数。那么写入数据时MySQL是否做了减少IO的优化呢？答案是肯定的，就要我们深入的学习Change Buffer。先放一张官档中Change Buffer的架构图：

在这里插入图片描述

官档地址：https://dev.mysql.com/doc/refman/8.0/en/innodb-change-buffer.html
点击此处跳转

官档中对Change Buffer的概念是：更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它将更改缓存到二级索引页。缓冲的更改可能来自INSERT、UPDATE或DELETE操作(DML)，稍后当其他读操作将页面加载到缓冲池中时，这些更改将被合并。

一、如何减少因数据写入导致的IO？
二、Change Buffer工作原理
- 2.1 Change Buffer概念
- 2.2 Change Buffer工作原理
三、Change Buffer触发时机
四、Change Buffer适用场景
- 4.1 适合开启InnoDB的写缓冲机制
- 4.2 不适合开启InnoDB的写缓冲机制
五、Change Buffer的重要的参数
- 5.1 innodb_change_buffer_max_size
- 5.2 innodb_change_buffering
六、Change Buffer相关监控信息

一、如何减少因数据写入导致的IO？

对于读请求，缓冲池能够减少与磁盘打交道的次数，提升性能。但是，如果写数据请求也能减少和磁盘打交道的次数，那样性能不是更好吗？

这时也分为两种情况：

情况一：

假如要修改的页4正好在缓冲池内

在这里插入图片描述
处理过程为图中1、2：

直接修改缓冲池中的页，一次内存操作；
写入redo log，一次磁盘顺序写操作；

这样的效率是最高的（像写日志这种顺序写，每秒几万次没问题）

是否会出现一致性问题呢？

并不会。
1、读取，会命中缓冲池的页；
2、缓冲池LRU数据淘汰，会将“脏页”刷回磁盘；
3、数据库异常奔溃，能够从redo log中恢复数据；

什么时候缓冲池中的页，会刷到磁盘上呢？

定期刷磁盘，而不是每次刷磁盘，能够降低磁盘IO，提升MySQL的性能。

情况二：

假如要修改的这个页40正好不在缓冲池内。

在这里插入图片描述
此时麻烦一点，处理过程为1、2、3：

先把需要为40的索引页，从磁盘加载到缓冲池，一次磁盘随机读操作；
修改缓冲池中的页，一次内存操作；
写入redo log，一次磁盘顺序写操作；

没有命中缓冲池的时候，至少产生一次磁盘IO，对于写多读少的业务场景，是否还有优化的空间呢？

这即是InnoDB考虑的问题，写缓冲(change buffer)应用而生（从名字容易看出，写缓冲是降低磁盘IO，提升数据库写性能的一种机制）。

二、Change Buffer工作原理

2.1 Change Buffer概念

在MySQL5.5之前，叫插入缓冲（Insert Buffer），只针对INSERT做了优化；现在对DELETE和UPDATE也有效，叫做写缓冲（Change Buffer）。

它是一种应用在非唯一普通索引页（non-unique secondary index page）不在缓冲池中，对页进行了写操作，并不会立刻将磁盘页加载到缓冲池，而仅仅记录缓冲变更（Buffer Changes），等未来数据被读取时，再将数据合并（Merge）恢复到缓冲池中的技术。写缓冲的目的是降低写操作的磁盘IO，提升数据库性能。

为什么写缓冲优化，仅适用于非唯一普通索引页呢？
InnoDB里，聚集索引（Clustered Index）)和普通索引(Secondary Index)存在异同。如果索引设置了唯一（Unique）属性，在进行修改操作时，InnoDB必须进行唯一性检查。也就是说，索引页即使不在缓冲池，磁盘上的页读取无法避免（否则怎么校验是否唯一），此时就应该直接把相应的页放入缓冲池再进行修改

2.2 Change Buffer工作原理

假如要修改页号为40的索引页，而这个页正好不在缓冲池内

在这里插入图片描述
加入写缓冲优化后，流程优化为：

在写缓冲中记录这个操作，一次内存操作；
写入redo log，一次磁盘顺序写操作；

其性能与这个索引页在缓冲池中，相近（可以看到，40这一页，并没有加载到缓冲池中）。

是否会出现一致性问题呢？

也不会。
数据库异常奔溃，能够从redo log中恢复数据
写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；
数据读取时，有另外的流程，将数据合并到缓冲池；

稍后的一个时间，有请求查询索引页40的数据

在这里插入图片描述
此时的流程如序号1-3：

载入索引页，缓冲池未命中，这次磁盘IO不可避免；
从写缓冲读取相关信息；
恢复索引页，放到缓冲池LRU里；（可以看到，40这一页，在真正被读取时，才会被加载到缓冲池中）

三、Change Buffer触发时机

除了数据页被访问，还有这么几种情况，会刷写缓冲中的数据：

有一个后台线程，会认为数据库空闲时；
数据库缓冲池不够用时；数据库正常关闭时；
redo log写满时；（几乎不会出现redo log写满，此时整个数据库处于无法写入的不可用状态）

四、Change Buffer适用场景

4.1 适合开启InnoDB的写缓冲机制

数据库大部分是非唯一索引；
业务是写多读少，或者不是写后立刻读取；

可以使用写缓冲，将原本每次写入都需要进行磁盘IO的SQL，优化定期批量写磁盘。例如，账单流水业务

4.2 不适合开启InnoDB的写缓冲机制

数据库都是唯一索引；
或者，写入一个数据后，会立刻读取它；

这两类场景，在写操作进行时（进行后），本来就要进行进行页读取，本来相应页面就要入缓冲池，此时写缓存反倒成了负担，增加了复杂度。

五、Change Buffer的重要的参数

mysql> show variables like '%innodb_change_buffer%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+-------------------------------+-------+
2 rows in set (0.01 sec)

5.1 innodb_change_buffer_max_size

配置写缓冲的大小，占整个缓冲池的比例，默认25表示change buffer最大可以占用innodb buffer的25%，最大可设置的值为50%

 set persist innodb_change_buffer_max_size = 20

5.2 innodb_change_buffering

默认all表示所有的非唯一普通索引页写入都使用change buffer

all：所有的非唯一普通索引页写入
none：关闭change buffer
inserts：缓冲插入操作
deletes：缓冲删除操作
changes：缓冲插入和删除操作
purges：缓冲在后台发生的物理删除操作

set persist innodb_change_buffering=all;

六、Change Buffer相关监控信息

使用show engine innodb status \G命令查询

mysql> show engine innodb status \G;
**省略部分信息**
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
解读：
size 1：正在使用page
free list len：空闲的page
seg size：当前change buffer大小2*16K
merges：插入的条目

merged operations:
 insert 0, delete mark 0, delete 0
解读：
insert：通过change buffer插入的数目
delete mark：通过change buffer删除的数目
delete：通过change buffer purge的数目

discarded operations:
 insert 0, delete mark 0, delete 0
**省略部分信息**

change buffer的效果=merges/(insert+delete mark+delete)结果越小说明change buffer对性能提升越有利，也可以使用以下 select NAME,COUNT,MAX_COUNT,MIN_COUNT,AVG_COUNT,COMMENT from information_schema.innodb_metrics where name like '%ibuf%'语句进行监控

至此今天的学习就到此结束了，愿您成为坚不可摧的自己~~~

You can’t connect the dots looking forward; you can only connect them looking backwards. So you have to trust that the dots will somehow connect in your future.You have to trust in something - your gut, destiny, life, karma, whatever. This approach has never let me down, and it has made all the difference in my life

如果我的内容对你有帮助，请 点赞、评论、收藏，创作不易，大家的支持就是我坚持下去的动力

在这里插入图片描述