16.Update语句执行流程之ChangeBuffer

以一条更新语句: update t set c=c+1 where id=2 为例。

我们已经介绍了两阶段提交和组提交以及BufferPool。

并写入了一条undolog:update t set c=c-1 where id=2。

接下来我们继续按照Update语句执行流程继续往下讲。

下面的执行流程是将这一行的值+1并写入新行,将新行更新到内存。

此时是直接更新内存吗？还是将数据写入到bufferpool对应的缓存页呢？

当需要更新一个数据页时，如果数据页在BuffferPool内存中，则直接更新。

否则，在不影响数据一致性的前提下，InnoDB 将这些操作缓存在 change buffer 中，这样就不必从磁盘中读取数据，当下次查询需要访问这个数据页时，再将数据页读入内存，然后执行 change  buffer 中与这个页有关的操作，最后将查询结果返回。

ChangeBuffer简介

change buffer是InnoDB存储引擎的一个重要特性，但在InnoDB 1.0.x版本之前，只支持插入缓冲(insert buffer)，后来又对其进行升级，不仅支持insert buffer，还支持delete、update的缓冲，分别对应delete buffer、purge buffer，并统称为变更缓冲(change buffer)。

结构：change buffer数据结构是一颗B+树。

存储：位于缓冲池(buffer pool)中，存储在共享的表空间，默认是ibdata1系统表空间，既拷贝在内存，也存储在磁盘。

ChangeBuffer作用

当有数据变更操作时，如果数据页也不在内存中，在不影响数据一致性的前提下，InnoDB存储引擎会先将变更操 作写入change buffer中，当下一次查询需要访问这个数据页(原数据页)时，再执行change buffer中缓存的变更操作，得到变更后的数据页(通过执行change buffer中的缓存的变更操作，将原数据页进行变更，得到变更后的数据页，这个过程称为merge)。change buffer有效的减少了读磁盘的次数，也减少了数据页占用缓冲池(buffer pool)的内存，提高内存的利用率；

change buffer的三种操作

BTRINSERTOP：普通的 insert

BTRDELMARKOP：在用户线程执行 update 和 delete 中，如果发生数据删除行为，会将记录标记为 delete mark

BTRDELETEOP：purge 线程删除二级索引中 delete mark 的数据行

并不是数据库中的所有操作都会进入ChangeBuffer，满足以下条件的数据库语句，在执行阶段不会修改数据页，而是会进入ChangeBuffer。

1.SQL会修改数据库中的数据； 2.SQL语句不涉及唯一键的校验； 3.SQL语句不需要返回变更后的数据； 4.涉及的数据页不在缓存中；

change buffer流程

当数据发生变更时(update、insert、delete)，要更新对应的索引页，如果索引页在Buffer Pool里命中的话，就直接更新缓存页。

否则，InnoDB会将这些更新操作缓存在change buffer中，这样就无需从硬盘读入索引页。

下次查询时，会将磁盘页读入Buffer Pool对应的缓存页，然后将change buffer中的操作应用到对应的缓存页，得到最新结果，这个过程称为merge，通过这种方式就能保证数据逻辑的正确性。

不难看出，change buffer通过减少硬盘随机IO读与提高内存利用率，让数据库的并发能力更强。

ChangeBuffer之merge

当下一次需要加载这个页面的时候，也就是这个页面有需求的时候，会将Change Buffer内的更改合并到Buffer Pool，随后当服务器在空闲的时候，这个更改会刷到disk(磁盘)上, 或者在不繁忙的时候进行merge，这时候merger操作也是发生在buffer pool;

将  change  buffer 的操作应用到数据页的过程称为 merge。除了访问数据页会触发 merge 外；系统后台有线程会定期 merge；数据库正常关闭的过程中也会触发 merge 操作。 e操作呢？

1.访问变更操作对应的数据页； 2.InnoDB后台定期Merge； 3.数据库BufferPool空间不足； 4.数据库正常关闭时； 5.RedoLog写满时；

Change Buffer的使用限制

看到这里，相信大家对change buffer有了基本的认识。现在可以展开讲讲change buffer的使用限制。

是的，你没听错，change buffer不能随随便便用。

一般我们可以把常用索引分类为下面几种

其中聚簇索引和唯一索引是无法使用change buffer，因为它们具备唯一性。

当更新唯一索引字段的内容时，需要把相应的索引页加载进Buffer Pool，验证唯一性约束，此时都已经读入到Buffer Pool了，那直接更新会更快，没必要使用change buffer。

只有非唯一索引才能使用change buffer

*change buffer 只针对辅助索引而言,对辅助索引叶子节点的更改才可能借助change buffer。 1.对主键索引无效。 2.如果该列上没有索引,也不会借助change buffer! *

为什么必须是二级索引页，不能是主键索引页？

很简单，因为主键索引要保证唯一性的约束，如果把 insert id=1 缓存起来，再次有要 insert id=1 时再缓存起来，则等批量的 apply 时就会出错。

持久化

看到这里小伙伴有疑问了，change buffer在内存中，如果万一MySql实例挂了或宕机了，这次的更新操作不全丢了吗？

其实不用担心，InnoDB对这块有相应的持久化方案，会有后台线程定期把change buffer持久化到硬盘的系统[表空间](ibdata1)。

并且每次change buffer记录的内容，会写入到redo log buff中，由后台线程定期将redo log buff持久化到硬盘的redolog日志。

最后MySql重启，可以通过ibdata1或redolog恢复change buffer，恢复的过程，分为下面几种情况 最后MySql重启，可以通过ibdata1或redolog恢复change buffer，恢复的过程，分为下面几种情况

1. change buffer的数据刷盘到ibdata，直接根据ibdata恢复

2. change buffer的数据未刷盘，redolog里记录了change buffer的内容

   • change buffer写入redo log，redo log虽做了刷盘但未commit,binlog未刷盘,这部分数据丢失
   • change buffer写入redolog，redolog虽做了刷盘但未commit,binlog已刷盘,先从binlog恢复redolog,再从redolog恢复change buffe
   • change buffer写入redolog，redolog和binlog都已刷盘，直接从redolog里恢复。

ChangeBuffer适用场景

那现在有一个问题，使用change buffer一定可以起到加速作用吗？

相信大家都清楚merge的时候是将change buffer记录的操作应用到索引页。

所以索引页merge之前，change buffer记录的数据越多收益就越大。

因此对于写多读少的业务场景，索引页在写完以后马上被访问到的概率很小，此时change buffer的收益最高。

相反，读多写少的业务场景，更新完马上做查询，则会触发change buff立即merge, 不但硬盘随机IO次没有减少，还增加change buffer的维护成本。

• 读多写少的业务：当更新执行后，不会立马触发查询，即查询频率很低的情况下，触发merge的频率就会很低。
• 读少写多的业务：当更新执行后，就会立马触发查询，即立即触发merge，此时随机访问IO的次数不会减少(因为还是得访问磁盘页的数据)，还会增加change buffer的维护代价。

故change buffer使用于，写多读少的业务，如日志类系统。如果所有的更新后面，都马上伴随着对这个记录的查询，应该关闭change buffer，innodb_change_buffering设置为none表示关闭change buffer。

唯一索引和非唯一索引的选择

由于唯一索引用不上change buffer的优化机制，在业务可以接受的情况下，从性能角度出发建议考虑非唯一索引

设置ChangeBuffer大小

我们可以通过innodb_change_buffer_max_size来动态设置change buffer占用的内存大小，默认25。

假设参数设置为50的时候，表示change buffer的大小最多只能占用buffer pool的 50%。

思考

change buffer与WAL分别提升性能的侧重点是什么？

changebuffer加速读:减少随机IO访问

假设需要向表T插入数据:insert into T (user_id,name) values(23,"张三");

则过程如下：

情景一：插入的数据在bufferPool的缓存页中

若userid为唯一索引：找到userid为22和24之间的位置，判断没有冲突，则插入数据；

若userid为普通索引：找到userid为22和24之间的位置，插入数据；

情景二：插入的数据不在bufferPool的缓存页中

若userid为唯一索引：将数据从磁盘读到内存，找到userid为22和24之间的位置，判断没有冲突，则插入数据；

若user_id为普通索引：将数据插入到change buffer；

所以在普通索引场景下，change buffer可以减少磁盘读入内存随机IO的访问，对更新性能有明显的提升。

redolog加速写:将随机写磁盘变成了顺序写磁盘。

灵魂拷问

如果你能回答上这个问题，说明你真的理解了change buffer！

问：

我开启change buffer 之后，现在要删除一个非唯一的二级辅助索引数据行，比如就删除name=Tom的行，并且这个索引页不在内存中……接下来会发生什么？

按照change buffer的作用来说，是不是当索引页不在内存中时，不去读盘，而是会把这个删除操作写到change buffer 中？

那问题又来了，既然你是把这个操作写到了change buffer中，那你返回给客户端的影响行数怎么算出来的呢？你都没有读读磁盘，万一磁盘上都没你要删除的数据呢…… 你告诉客户端，删除成功了，影响行数为1？

答：其实客户端每次都能得到正确的影响行数！不错，change buffer中是把缓存了你的delete操作，但是buffer pool是没有被影响的呀，如果buffer pool中没有这个name=Tom的行，它依然会去读磁盘的！你品一品，buffer pool和change buffer是两块缓存哦～

参考：https://blog.csdn.net/qq_44837912/article/details/121556401

参考：https://blog.csdn.net/qwer123451234123/article/details/124719607