【MySQL】变更缓冲区：作用、主要配置以及如何查看

📢博客主页：https://blog.csdn.net/2301_779549673
📢博客仓库：https://gitee.com/JohnKingW/linux_test/tree/master/lesson
📢欢迎点赞 👍 收藏 ⭐留言 📝 如有错误敬请指正！
📢本文由 JohnKi 原创，首发于 CSDN🙉
📢未来很长，值得我们全力奔赴更美好的生活✨

在这里插入图片描述

文章目录

🏳️‍🌈一、变更缓冲区的作用?
- 1.1 为什么是二级索引?
- 1.2 Merge的触发时机有哪些?
🏳️‍🌈二、变更缓冲区的主要配置项都有哪些?
- 2.1 缓冲类型
- 2.2 更改缓冲区的最大大小
🏳️‍🌈三、怎么查看当前变更缓冲区的信息?
👥总结

前些天发现了一个巨牛的 人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站。

在这里插入图片描述

🏳️‍🌈一、变更缓冲区的作用?

变更缓冲区用来缓存对 二级索引数据 的修改，当数据页没有被回载到内存中时，先把修改缓存起来，等到其他查询操作发生时，数据页被加载到内存后，再直接修改内存中的数据页，从而达到减少磁盘I/0的目的。

变更缓冲区占用 Buffer Pool 的一部分空间，具体如图所示
在这里插入图片描述
场景：如果要修改一条数据，内存与磁盘之间的交互过程是怎么样的

在磁盘中找到对应的数据行
数据行所在的数据页加载到内存中
在内存中完成对数据行的修改
把修改过后的数据页在写回到磁盘中

即，需要两次磁盘IO才能完成一次数据修改操作

变更缓冲区用来缓存对 二级索引数据 的修改，是一个特殊的数据结构

当使用 INSERT 、UPDATE 或 DELETE 语句修改二级索引对应的数据时，如果对应的数据页在缓冲池中则直接更新，如果不在缓冲池中，那么就把修改操作缓存到变更缓冲区，这样就不用立即从磁盘读取对应的数据页了，当之后的读操作将对应的数据页从磁盘加载到缓冲池中时，变更缓冲区中缓存的修改操作再批量合并到缓冲池，从而达到减少磁盘I/0的目的。执行流程如图所示:

在这里插入图片描述

1.1 为什么是二级索引?

关于索引在数据库初阶已经做了介绍，我们知道索引分为 聚集索引(主键) 和 二级索引(自定义)
由于聚集索引具有唯一性，我们分析一下聚集索引为什么不能被放入变更缓存，假设表中有一个主键(ID)，现在有两条 INSER 语句，都在插入数据时ID的值相同(id=1)，那么在变更缓冲区中就存在两个修改操作，如果以后要合并到缓冲池中，这时就会出现重复的主键值，所以聚集索引的修改不能被加入到变更缓冲区;
与聚集索引不同，二级索引通常是不唯一的，并且向二级索引中插入数据时由于数据列不同，所以位置相对随机，同样对于删除和更新操作可能会影响不相邻的二级索引页，如果每次都从磁盘读取数据就会发生大量的随机I/0，以变更缓冲区的方式先将修改缓存起来，当真正的读取数据时再把修改合并到缓冲池中可以提升效率。

1.2 Merge的触发时机有哪些?

读取对应的数据页时;
当系统空闲或者 Slow Shutdown 时，主线程发起 merge;
Change buffer 的内存空间即将耗尽时;
Redo Log 写满时。

🏳️‍🌈二、变更缓冲区的主要配置项都有哪些?

主要的配置项有 缓冲类型 和 更改缓冲区的最大大小

2.1 缓冲类型

在修改二级索引数据时变更缓冲区可以减少磁盘I/0从而提高效率，但是变更缓冲区占用了缓冲池的一部分空间，从而减少了可用于缓存数据页的内存，如果业务场景读多写少，或者表中的二级索引相对较少，那么可以考虑禁用更改缓冲从而提高缓冲池空间。

可以通过选项文件或 SET GLOBAL 语句对系统变量 innodb_change_buffering 进行设置，来控制变更缓冲区对于插入、删除操作(索引记录被标记为删除) 和清除操作(当索引记录被物理删除时)的开启或禁用:

删除操作: 索引记录被标记为删除
清除操作: 索引记录被物理删除时
更新操作: 是插入和删除操作的组合

all: 默认值，缓存插入、删除标记操作和清除
none:不缓存任何操作
inserts: 只缓存插入操作
deletes: 只缓存删除标记操作
changes: 缓存插入和删除标记操作
purges: 缓存发生在后台的物理删除操作

2.2 更改缓冲区的最大大小

通过 innodb_change_buffer_max_size 系统变量可以设置更改缓冲区的最大大小，默认为25，最大为50，表示更改缓冲区占缓冲池内存总大小的百分比。
在有大量插入、更新和删除的业务场景中，可以考虑增加 innodb_change_buffer_max_size 的值，在大部分是读多写少，比如用于报表的静态数据场景中考虑减小 innodb_change_buffer_max_size 的值
需要注意的是，如果更改缓冲区占了缓冲池太多的内存空间，会导致缓冲池中的数据页更快地淘汰。

🏳️‍🌈三、怎么查看当前变更缓冲区的信息?

通过使用 SHOW ENGINE InnODB STATUS 访问 InnoDB 标准监视器输出中 INSERTBUFFER AND ADAPTIVE HASH INDEX 部分查看有关更改缓冲区状态的信息。

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
... # 省略
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s
... # 省略