本文为《MySQL归纳学习》专栏的第八篇文章，同时也是关于《MySQL缓存》知识点的第三篇文章。
相关文章：
InnoDB缓冲池揭秘：MySQL中的数据缓存利器
揭秘InnoDB插入缓冲：提升非唯一辅助索引插入性能的秘密武器
MySQL的Change Buffer（变更缓冲）是提升写入性能的秘密武器。本文将深入介绍Change Buffer的作用和重要性，揭示它在MySQL中隐藏的性能加速器。我们将详细讨论Change Buffer的merge操作和持久化操作，帮助你全面理解这项优化技术。同时，我们还将对比Change Buffer和Redo Log的区别，为你揭示它们在性能优化方面的差异。
在学习插入缓冲 insert buffer 时提到过 change buffer，现在我们就来深入学习一番。
介绍
InnoDB从1.0.x版本开始引入了change buffer，可以将其视为insert buffer的升级。从这个版本开始，InnoDB存储引擎可以对DML操作——insert、delete、update都进行缓冲，它们分别是：insert buffer、delete buffer、purge buffer。
和insert buffer一样，change buffer适用的对象依然是非唯一的辅助索引。
对一条记录进行update操作可以分为两个过程：
（1）将记录标记为删除；
（2）真正将记录删除。
因此delete buffer对应update操作的第一个过程，即将记录标记为删除。purge buffer对应update操作的第二个过程，即将记录真正的删除。同时InnoDB存储引擎提供了参数innodb_change_buffering，用来开启各种buffer选项。该参数的可选值为：inserts、deletes、purges、changes、all、none。inserts、deletes、purges就是前面讨论过的三种情况。changes表示启用inserts和deletes，all表示启用所有，none表示都不启用。
从 InnoDB 1.2.x版本开始，可以通过参数 innodb_change_buffer_max_size 来控制 Change Buffer 最大使用内存的数量：
mysql> show variables like 'innodb_change_buffer_max_size';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| innodb_change_buffer_max_size | 25 |
+-------------------------------+-------+
1 row in set (0.18 sec)
innodb_change_buffer_max_size 值默认为25，表示最多使用1/4 的缓冲池内存空间。而需要注意的是，该参数的最大有效值为50。
关于 change buffer 的状态信息，仍然通过下面命令查看：
show engine innodb status;
作用
当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作（即后文提到的merge操作）。通过这种方式就能保证这个数据逻辑的正确性。
buffer pool 的数据页是批量写入的，不是每次数据更新都把内存中的脏页写到磁盘中的，所以不需要 change buffer 来做这个事情。针对数据页的写操作，需要先读到内存中，再写回硬盘，如果想减少这次读操作，就可以直接把写的动作保存在内存（change buffer）中。
关于 change buffer 的位置描述，它作为 insert buffer 的升级，同样不仅仅只存在于缓冲池中，还处于系统表空间中，如下图所示：
从上图可以看出在右边的 System Tablespace 中可以看到持久化 Change Buffer 的空间。
merge与持久化
Change Buffer 中的数据最终还是会刷回到数据所在的原始数据页中，将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。出现 merge 操作有以下几种情况：
- 原始数据页加载到 Buffer Pool 时。
- 系统后台定时触发 merge 操作。
- MySQL 数据库正常关闭时。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。
Merge 操作为了保证数据的一致性和正确性，那么持久化操作就是为了数据的持久性。
比如说 merge 操作出现的第一种情况：当原始数据页加载到 buffer pool，接下来将 change buffer 中该数据页的变更进行同步操作，同步结束后，buffer pool 中的数据页就是最新的，后续查询操作可以在缓存中直接命中。但此时磁盘中的数据页还是旧的，一旦缓存内数据丢失，就意味着数据变更丢失，所以还需要持久化操作，将缓存中的数据页持久化到磁盘中。
触发写缓存（Change Buffer）持久化操作有以下几种情况：
-
数据库空闲时，后台有线程定时持久化
-
数据库缓冲池不够用时
-
数据库正常关闭时
-
redo log 写满时
更多详情讲解推荐阅读：写缓冲(change buffer)，这次彻底懂了！！！
MySQL 数据库的提速器-写缓存（Change Buffer）
change buffer 和 redo log
了解了 change buffer 的原理后，会发现 redo log 的功能很相似，都是尽量减少随机读写。
现在，我们要在表上执行这个插入语句：
mysql> insert into t(id,k) values(id1,k1),(id2,k2);
这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。下图所示是带 change buffer 的更新状态图。
分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。
关于数据表空间和系统表空间，区别如下：
- 数据表空间：就是一个个的表数据文件，对应的磁盘文件就是“表名.ibd”；
- 系统表空间：用来放系统信息，如数据字典等，对应的磁盘文件是“ibdata1”。
上述 SQL 语句的执行顺序如下：
做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。
同时，图中的两个虚线箭头，是后台操作，不影响更新的响应时间。
那在这之后的读请求，要怎么处理呢？比如，我们现在要执行 select * from t where k in (k1, k2)。
如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了。
从图中可以看到：
使用场景
学习普通索引和唯一索引时详细讲解过，这里不做重复讲解。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。
凡事并无绝对，普通索引和唯一索引的选择还是要结合具体的业务场景。比如说写多读少使用 changebuffer 可以加快执行速度(减少数据页磁盘 io)；但是，如果业务模型是写后立马会做查询，则会触发 changebuff 立即 merge 到磁盘, 这样 的场景磁盘 io 次数不会减少，反而会增加 changebuffer 的维护代价。
扩展
1、MySQL执行update、delete操作会返回影响行数，行数信息是如何得到的
affected-rows：受影响的行数。这适用于 UPDATE 、 INSERT 或 DELETE 等语句。
对于 UPDATE 语句，affected-rows 值默认是实际更改的行数。如果在连接到 mysqld 时将 CLIENT_FOUND_ROWS 标志指定为 mysql_real_connect() ，则affected-rows值是“找到”的行数；即由 WHERE 子句匹配。
在 MySQL 连接时，CLIENT_FOUND_ROWS 是一个连接选项，用于设置返回的受影响行数的行为。默认情况下，CLIENT_FOUND_ROWS 的默认值是禁用的，也就是说，返回的受影响行数是实际修改的行数，而不是找到的行数。
要查看当前连接的 CLIENT_FOUND_ROWS 设置，可以使用以下命令：
SHOW VARIABLES LIKE 'client_found_rows';
这将返回一个结果集，其中包含名为 client_found_rows 的变量以及其当前的值。
我们再来看看 change buffer 的介绍：当辅助索引页不在缓冲池中时，它会缓存这些页的更改。缓冲的更改可能由 INSERT 、 UPDATE 或 DELETE 操作 (DML) 产生，稍后当其他读取操作将页面加载到缓冲池中时，这些更改将被合并。
那么如果 update、delete 操作缓存在change buffer中，那么如何返回affected-rows的？
这个问题其实很简单，change buffer 存在与 buffer pool 中，还记得 buffer pool 中存放了哪些内容吗？来看下面这张图：
既然有索引页，那返回 affected-rows 也就没啥问题了。
感兴趣的朋友可以阅读这篇文章，作者进行了详细的测试。
2、change buffer不支持降序索引
什么是降序索引？
MySQL支持降序索引：索引定义中的 DESC 不再被忽略，而是导致键值按降序存储。以前，可以按相反的顺序扫描索引，但会降低性能。降序索引可以按正序扫描，效率更高。当最有效的扫描顺序混合某些列的升序和其他列的降序时，降序索引还使优化器可以使用多列索引。
CREATE TABLE t (
c1 INT, c2 INT,
INDEX idx1 (c1 ASC, c2 ASC),
INDEX idx2 (c1 ASC, c2 DESC),
INDEX idx3 (c1 DESC, c2 ASC),
INDEX idx4 (c1 DESC, c2 DESC)
);
官网是这样描述的，“如果索引包含降序索引键列或主键包含降序索引列，则辅助索引不支持更改缓冲。”
由于降序索引的特殊性，Change buffering 机制不能应用于包含降序索引键列的辅助索引或主键索引。这是因为降序索引的插入顺序和索引结构与升序索引不同，导致更改操作的处理方式变得复杂。
3、change buffer 是否会出现一致性问题？
答案是不会的。原因有以下三点：
（1）数据库异常崩溃，能够从 redo log 中恢复数据；
（2）写缓冲不只是一个内存结构，它也会被定期刷盘到写缓冲系统表空间；
（3）数据读取时，有另外的流程，将数据合并到缓冲池；
