怎么理解MySQL change buffer

共计 2624 个字符，预计需要花费 7 分钟才能阅读完成。

本篇内容介绍了“怎么理解 MySQL change buffer”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让丸趣 TV 小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

change buffer 是 MySQL5.5 加入的新特性，change buffer 是 insert buffer 的加强，insert buffer 只针对 insert 有效，change buffer 对 insert、delete、update(delete+insert)、purge 都有效。当修改一个索引块 (secondary index) 时的数据时，索引块在 buffter pool 中不存在，修改信息就会被 cache 在 change buffer 中，当通过索引扫描把需要的索引块读取到 buffer pool 时，会和 change buffer 中修改信息合并，再择机写回 disk。目的还是为了减少随机 IO 带来性能损耗，说明白了：把随机 IO 尽量变成顺序 IO。
Change buffer 的主要目的是将对二级索引的数据操作缓存下来，以此减少二级索引的随机 IO，并达到操作合并的效果。
change buffer 是存放二级索引的没有在 buffer pool 的变更页的缓存区，变更的 buffer 是由 insert，update,delete 等操作导致的。等页被加载进 buffer pool 中后会将 change buffer 中的页合并。 
二级索引通常是非唯一的，插入也是很随机的顺序，更新删除也都不是在邻近的位置，所以 change buffer 就避免了很多的随机 io 的产生。puge 操作会在系统空闲或慢关闭的时候定时将变更页写入到磁盘上去。 
change buffer 合并在有大量的二级索引页更新或有很多影响行的情况下会花费很长的时间。change buffer 会占用 innodb buffer pool 的部分空间，在磁盘上，change buffer 会占用系统表空间，所以在数据库重启后，索引变更仍然被缓存。在 change buffer 中被缓存的数据可以使用 innodb_change_buffering 控制，我们也可以调整 innodb_change_buffer_max_size 配置 change buffer 的大小。

索引对 insert 的影响

1、表 insert，对应表上的所有索引都需要 insert；

2、假设这些索引不常使用，容易产生物理读；

3、索引的顺序和表的顺序完全不一致；

原则：一个表上的索引最好不超过 6 个

将对索引的更新记录存入 insert buffer 中，而不是直接调入索引页进行更新；择机进行 merge insert buffer 的操作，将 insert buffer 中的记录合并 (merge) 到真正的辅助索引中。
解决了 insert 表数据产生过多物理读的问题。
Insert Buffer 何时 merge
有 2 种情况 innodb 会 merge Insert Buffer 到磁盘  
1)，master loop 线程主动 merge
若过去 1s 之内发生的 I /O，小于系统 I / O 能力的 5%，则主动进行一次 Insert buffer 的 merge 操作。Merge 的页面数为系统 I / O 能力的 5%，每 10s，必定触发一次 insert buffer merge 动作.
Merge 的页面数仍旧为系统 I / O 能力的 5% (系统能力指 innodb_io_capacity)

2), 读取数据发现该 page 在 Insert Buffer 还没有被 merge, 那么 innodb 首先会做一个 merge 操作，所以此时读取的速度会降低

在系统负载较高时，merge 操作会很少，而在系统比较空闲时，merge 操作会非常频繁，所以当你的系统突然负载变低时，DB 还有可能会进行很长一段时间的 IO 操作。

merge insert buffer 的操作可能发生在什么情况下：
在 merge insert buffer 之前，insert buffer 数据是存在内存中，为了防止数据库意外宕机导致数据丢失，系统会周期性将 insert buffer 数据写入共享表空间中。
1、辅助索引页被读取到 buffer pool 中
例如这在执行正常的 select 查询操作，索引页被调入内存，该索引页对应在 insert buffer 中的索引更改记录就会发生 merge 操作。
2、insert buffer bitmap 页追踪到该辅助索引页已无可用空间时
存于 ibd 文件中(表数据文件)
记录每一个索引页在 insert buffer 中对应的行数

如何看 insert buffer 的效果
1、insert buffer 所占空间，占比太高就影响缓冲性能
2、每次 merge 处理的数据量
1)、merges 如果很高，说明 insert buffer 调小了，也说明索引建多了；
2)、对表进行批量 IDU 的时候，可能会导致 insert buffer 迅速增加。
关注 change buffer 在 innodb buffer pool 中的占比
mysql show variables like %change_buffer%
+——————————-+——-+
| Variable_name                 | Value |
+——————————-+——-+
| innodb_change_buffer_max_size | 25    |
| innodb_change_buffering       | all   |
+——————————-+——-+
2 rows in set (0.01 sec)
1、innodb_change_buffer_max_size：表示 change buffer 在 buffer pool 中的最大占比，默认 25%，最大 50%
2、innodb_change_buffering：表示索引列 merge 对象，all 表示对 IDU 索引列都起作用，都进行 merge，如果只想对 insert 索引列进行 merge，就把 all 改为 inserts。

调整建议：
1、如果系统中有严重的 insert、update 并且还有活跃的 delete 时，就增大 max_size；
2、针对不更改数据的纯报表系统，可以减小该参数值。

“怎么理解 MySQL change buffer”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！