【MySQL实战】笔记_mysql实战笔记-CSDN博客

本文链接：https://blog.csdn.net/weixin_48912068/article/details/131382831

1、MySQL的基本架构组成

MySQL的基本架构组成包括以下几个部分：

连接器（Connection Manager）：负责与客户端建立连接，并处理客户端发送的请求。连接器会验证客户端的身份、分配线程和缓存等资源，并将请求转发给下一个组件处理。
查询缓存（Query Cache）：用于缓存查询语句及其结果，以提高查询性能。但是，由于查询缓存的限制较多，因此在实际应用中使用较少。
分析器（Parser）：用于对SQL语句进行解析，并生成相应的解析树。解析器会检查SQL语句的语法是否正确，并将其转换成内部的数据结构。
优化器（Optimizer）：用于对查询语句进行优化，以提高查询性能。优化器会根据查询的复杂度、表的大小、索引的使用情况等因素，选择最优的查询执行计划。
执行器（Executor）：负责执行查询语句，并将结果返回给客户端。执行器会根据优化器生成的查询执行计划，访问存储引擎，获取数据并进行相应的操作。
存储引擎（Storage Engine）：负责数据的存储和查询操作。MySQL支持多种存储引擎，如InnoDB、MyISAM、Memory等，每种存储引擎都有其优缺点和适用场景。
以上组件共同协作，构成了MySQL的基本架构。

2、redo log 和 binlog

redo log和binlog都是MySQL中的日志文件，用于记录数据库的变更操作，但它们的作用和使用方式略有不同。

redo log（重做日志）是InnoDB存储引擎特有的日志文件，用于记录事务中所做的修改操作。在事务提交前，InnoDB将重做日志记录到磁盘上的redo log文件中，以保证在发生故障时能够恢复事务。当MySQL重启时，它会将redo log文件中未提交的事务重新执行，以确保数据的一致性。

binlog（二进制日志）是MySQL的通用日志文件，用于记录所有数据库的修改操作。在执行每个语句或事务时，MySQL将binlog记录到磁盘上的binlog文件中。binlog文件中的内容可以被MySQL复制到其他服务器上，以实现数据备份、复制和恢复等功能。binlog文件还可以用于数据恢复和故障排除。

简单来说，redo log是InnoDB存储引擎的事务日志，用于保证数据的一致性和可靠性；而binlog是MySQL的通用日志文件，用于记录所有数据库的修改操作，以实现数据备份、复制和恢复等功能。

redo log 用于保证 crash-safe 能力。innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候，表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1，这样可以保证 MySQL 异常重启之后数据不丢失。

sync_binlog 这个参数设置成 1 的时候，表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1，这样可以保证 MySQL 异常重启之后 binlog 不丢失。

3、执行一条update的流程

mysql> update T set c=c+1 where ID=2;

执行器先找引擎取 ID=2 这一行。ID 是主键，引擎直接用树搜索找到这一行。如果 ID=2 这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。
执行器拿到引擎给的行数据，把这个值加上 1，比如原来是 N，现在就是 N+1，得到新的一行数据，再调用引擎接口写入这行新数据。
引擎将这行新数据更新到内存中，同时将这个更新操作记录到 redo log 里面，此时 redo log 处于 prepare 状态。然后告知执行器执行完成了，随时可以提交事务。
执行器生成这个操作的 binlog，并把 binlog 写入磁盘。
执行器调用引擎的提交事务接口，引擎把刚刚写入的 redo log 改成提交（commit）状态，更新完成。

4、怎样让数据库恢复到半个月内任意一秒的状态？

当需要恢复到指定的某一秒时，比如某天下午两点发现中午十二点有一次误删表，需要找回数据，那你可以这么做：

首先，找到最近的一次全量备份，如果你运气好，可能就是昨天晚上的一个备份，从这个备份恢复到临时库；

然后，从备份的时间点开始，将备份的 binlog 依次取出来，重放到中午误删表之前的那个时刻。

这样你的临时库就跟误删之前的线上库一样了，然后你可以把表数据从临时库取出来，按需要恢复到线上库去。

5、为什么日志需要“两阶段提交”

假设当前 ID=2 的行，字段 c 的值是 0，再假设执行 update 语句过程中在写完第一个日志后，第二个日志还没有写完期间发生了 crash，会出现什么情况呢？

先写 redo log 后写 binlog：
假设在 redo log 写完，binlog 还没有写完的时候，MySQL 进程异常重启。由于我们前面说过的，redo log 写完之后，系统即使崩溃，仍然能够把数据恢复回来，所以恢复后这一行 c 的值是 1。但是由于 binlog 没写完就 crash 了，这时候 binlog 里面就没有记录这个语句。因此，之后备份日志的时候，存起来的 binlog 里面就没有这条语句。然后你会发现，如果需要用这个 binlog 来恢复临时库的话，由于这个语句的 binlog 丢失，这个临时库就会少了这一次更新，恢复出来的这一行 c 的值就是 0，与原库的值不同。

先写 binlog 后写 redo log：
如果在 binlog 写完之后 crash，由于 redo log 还没写，崩溃恢复以后这个事务无效，所以这一行 c 的值是 0。但是 binlog 里面已经记录了“把 c 从 0 改成 1”这个日志。所以，在之后用 binlog 来恢复的时候就多了一个事务出来，恢复出来的这一行 c 的值就是 1，与原库的值不同。

可以看到，如果不使用“两阶段提交”，那么数据库的状态就有可能和用它的日志恢复出来的库的状态不一致。

6、事务的隔离级别

SQL 标准的事务隔离级别包括：读未提交（read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。
读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。
可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。
串行化，顾名思义是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

MySQL 默认的事务隔离级别是 REPEATABLE READ ，而 Oracle 默认的事务隔离级别是 READ COMMITTED 。这意味着在 MySQL 中，在同一事务中多次读取相同的数据时，将始终看到相同的结果，而在 Oracle 中，每次读取都会看到最新的数据。需要注意的是，这些默认设置可以通过修改数据库配置进行更改。

Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，一定要记得将 MySQL 的隔离级别设置为“读提交”。配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。可以用 show variables 来查看当前的值。

7、多版本并发控制（MVCC）

多版本并发控制（MVCC）是一种数据库管理系统中的技术，它允许多个事务同时访问同一个数据源，而不会发生数据冲突或丢失。MVCC 通过在每个事务中创建数据的多个版本来实现这一点。每个版本都包含数据的先前状态，以便在事务之间进行区分。这种技术可以提高数据库的性能和可靠性，因为它允许多个事务同时进行，而不会产生竞争条件或死锁。

8、MySQL的事物启动方式

显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select
语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

9、索引的常见模型

每个索引在innodb中对应的都是一棵B+树。

哈希表：是一种以键 - 值（key-value）存储数据的结构，只要输入待查找的键即 key，就可以找到其对应的值即Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value放在数组的这个位置。不可避免地，多个 key值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。哈希表这种结构适用于只有等值查询的场景。
有序索引：有序数组在等值查询和范围查询场景中的性能非常优秀。仅仅看查询效率，有序数组就是最好的数据结构了。但是，在需要更新数据的时候就麻烦了，往中间插入一个记录就必须得挪动后面所有的记录，成本太高。有序数组索引只适用于静态存储引擎，比如要保存的是2017 年某个城市的所有人口信息，这类不会再修改的数据。
二叉搜索树的特点是：父节点左子树所有结点的值小于父节点的值，右子树所有结点的值大于父节点的值。这样如果你要查 ID_card_n2的话，按照图中的搜索顺序就是按照 UserA -> UserC -> UserF -> User2 这个路径得到。这个时间复杂度是O(log(N))。

哈希表
有序索引
二叉搜索树
10、聚簇索引和非聚簇索引

聚簇索引和非聚簇索引是关系型数据库中的两种常见的索引类型。
聚簇索引是按照表中的主键来组织数据的一种索引方式。在聚簇索引中，数据行按照主键的值进行排序，并且每张表只能有一个聚簇索引。由于数据行的物理存储顺序与主键的值的顺序相同，因此聚簇索引能够提高主键查询的性能，但是对于非主键列的查询性能则较差。
非聚簇索引是按照非主键列的值来组织数据的一种索引方式。在非聚簇索引中，索引文件中存储的是非主键列的值和对应数据行的指针，而不是数据行本身。非聚簇索引可以有多个，用于提高非主键列的查询性能。
在使用非聚簇索引进行查询时，需要根据叶子节点中的主键值回到聚簇索引中查找对应的数据行，这个过程被称为回表操作。聚簇索引的优点是能够提高主键查询的性能，非聚簇索引的优点是能够提高非主键列的查询性能。

11、聚簇索引等于主键索引吗

聚簇索引不一定等同于主键索引，但是通常情况下主键索引就是聚簇索引。
聚簇索引是将表的数据按照索引的顺序存储的一种索引类型，它的叶子节点存储了整张表的行数据。在一张表中，只能有一个聚簇索引，通常情况下是将表的主键作为聚簇索引。
主键索引是一种特殊的唯一索引，不允许有空值。在一张表中，每个表只能有一个主键索引，通常情况下主键索引就是聚簇索引。
但是，如果表中没有主键，那么聚簇索引就会选择一个唯一非空索引作为聚簇索引。如果没有唯一非空索引，则InnoDB内部会生成一个隐藏的聚簇索引。

12、什么是回表操作

回表操作是指当使用非聚簇索引进行查询时，需要根据叶子节点中的主键值回到聚簇索引中查找对应的数据行的操作。因为非聚簇索引中只存储了主键的值和对应的行指针，而不包含其他列的值，聚簇索引的叶子节点记录了整行内容，因此需要回到聚簇索引中查找其他列的值。这个过程就被称为回表操作。回表操作可能会增加额外的IO操作，影响查询性能。

13、覆盖索引

覆盖索引（Covering Index）是指一个索引包含了查询所需的所有列，即可以通过索引本身就能够获取到查询所需的数据，而无需再次访问数据表。当查询需要返回的列都包含在索引中时，可以利用覆盖索引来避免访问磁盘上的实际数据行，从而提高查询性能。

通常情况下，当我们执行一个查询语句时，数据库引擎需要根据查询条件在索引中找到匹配的行，并根据索引中的指针再次访问数据表来获取完整的数据行。这个过程涉及到磁盘的读取操作，而磁盘读取是相对较慢的操作。

而覆盖索引的优势在于，它将查询所需的列都包含在索引中，因此数据库引擎可以直接从索引中获取到所需的数据，而无需再次访问数据表。这样就避免了额外的磁盘读取操作，大大提高了查询的性能。

使用覆盖索引可以减少磁盘的IO操作次数，减少了查询的响应时间，特别适用于一些需要查询大量数据的场景。但需要注意的是，覆盖索引也会增加索引的大小，因此在选择创建覆盖索引时需要权衡索引的大小和查询性能的提升。

14、最左前缀原则

最左前缀原则是指在创建复合索引时，索引的最左边的列将被用于最优化索引的查询。只有当查询中包含了索引的最左边的列，索引才能被有效地使用。
举一个例子来说明最左前缀原则。假设有一个包含以下列的表：
表名：学生
列名：学号（student_id）、姓名（name）、年龄（age）、性别（gender）
现在我们需要查询年龄在18到25岁之间的男性学生。如果我们创建了一个复合索引包含了（age, gender）这两列，那么这个索引就可以被称为满足最左前缀原则。
当我们执行以下查询时：
SELECT * FROM 学生 WHERE age BETWEEN 18 AND 25 AND gender = ‘男’
由于查询中包含了索引的最左边的列（age），数据库引擎可以使用索引来定位到满足条件的数据行。然后再判断性别是否符合条件。这样可以大大提高查询性能，因为数据库引擎只需要读取索引的数据页，而不需要读取和解析实际的数据行。
相比之下，如果我们没有创建满足最左前缀原则的索引，而只创建了（gender, age）的索引，那么在执行上述查询时，数据库引擎将无法使用索引来定位到满足条件的数据行，而需要扫描整个表进行筛选。这样会增加额外的IO操作和数据传输，降低查询性能。
因此，在设计复合索引时，需要根据查询的特点和最常见的查询条件来确定最左前缀，以最大程度地提高查询性能。

15、索引下推

有一个需求：检索出表中“名字第一个字是张，而且年龄是 10 岁的所有男孩”。那么，SQL 语句是这么写的：

mysql> select * from tuser where name like '张%' and age=10 and ismale=1;

已经知道了前缀索引规则，所以这个语句在搜索索引树的时候，只能用 “张”，找到第一个满足条件的记录 ID3。

在 MySQL 5.6 之前，只能从 ID3 开始一个个回表。到主键索引上找出数据行，再对比字段值。
而 MySQL 5.6 引入的索引下推优化（index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。
每一个虚线箭头表示回表一次。
图 1 中，在 (name,age) 索引里面特意去掉了 age 的值，这个过程 InnoDB 并不会去看 age 的值，只是按顺序把“name 第一个字是’张’”的记录一条条取出来回表。因此，需要回表 4 次。
图 2 跟图 1 的区别是，InnoDB 在 (name,age) 索引内部就判断了 age 是否等于 10，对于不等于 10 的记录，直接判断并跳过。在我们的这个例子中，只需要对 ID4、ID5 这两条记录回表取数据判断，就只需要回表 2 次。
无索引下推执行流程

16、MySQL的全局锁

MySQL的全局锁（Global Lock）是指在MySQL数据库中，对整个数据库实例进行加锁，使得其他会话无法修改数据库中的任何数据。全局锁通常用于执行一些需要对整个数据库进行操作的任务，例如备份数据库、导出数据等。
全局锁的作用是确保在执行特定任务期间，其他会话无法对数据库进行修改，以保证数据的一致性。当全局锁被获取时，其他会话的查询和修改操作会被阻塞，直到全局锁被释放。
MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。
释放全局锁命令是释放全局锁： UNLOCK TABLES。
当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
注意：
如果在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
如果在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟。

替代方法：引擎需要支持可重复读的隔离级别，在可重复读隔离级别下开启一个事务。官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。single-transaction 方法只适用于所有的表使用事务引擎的库。

16.1、找出表结构中可以删除的索引

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;
有两种使用场景：
select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;
可以删除的索引哪个？

对于InnoDB存储引擎，主键字段会被放在索引定义字段的后面，并且InnoDB会对主键进行去重。
所以，当主键是(a,b)的时候，
定义为c的索引，实际上是（c,a,b)；
定义为(c,a)的索引，实际上是(c,a,b)；
定义为(c,b）的索引，实际上是（c,b,a)；
所以可以删除ca的索引

17、既然要全库只读，为什么不使用 set global readonly=true 的方式进行数据库备份呢？

确实 readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，我不建议你使用。二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。
业务的更新不只是增删改数据（DML)，还有可能是加字段等修改表结构的操作（DDL）。不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都是会被锁住的。

18、表级锁

表级锁（Table-level lock）是MySQL中一种锁机制，用于控制对数据库表的并发访问。表级锁可以在整个表的级别上进行加锁，以保证数据的一致性和并发性。
在MySQL中，表级锁有两种类型：共享锁（Shared Lock）和排他锁（Exclusive Lock）。

共享锁（Shared Lock）：也称为读锁（Read Lock），多个会话可以同时获取共享锁，用于读取数据而不修改数据。共享锁之间是兼容的，即多个会话可以同时持有共享锁，但无法与排他锁同时存在。
排他锁（Exclusive Lock）：也称为写锁（Write Lock），只有一个会话可以获取排他锁，用于修改数据。排他锁与其他任何锁（包括共享锁和排他锁）都是不兼容的，即一个会话持有排他锁时，其他会话无法获取任何类型的锁。
在使用表级锁时，可以使用以下语句进行锁定和释放：
锁定表：
LOCK TABLES table_name [READ | WRITE];
释放表锁：
UNLOCK TABLES;
需要注意的是，表级锁是在整个表的级别上进行加锁，因此会对并发性能产生一定的影响。如果需要更细粒度的锁定，可以考虑使用行级锁（Row-level lock）或其他更高级别的锁机制。

19、安全的修改热点表的表结构

首先要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

DL NOWAIT和WAIT n是用于控制DDL（数据定义语言）操作的等待行为的选项。

DDL NOWAIT：使用DDL NOWAIT选项可以使DDL操作立即执行，而不会等待其他正在执行的事务或锁定操作完成。如果DDL操作无法立即执行，将会返回一个错误消息。
DDL WAIT n：使用DDL WAIT n选项可以指定DDL操作等待的时间，单位为秒。如果DDL操作无法立即执行，MySQL将等待指定的时间，如果在指定时间内DDL操作仍无法执行，则会返回一个错误消息。

20、备份一般都会在备库上执行，你在用–single-transaction 方法做逻辑备份的过程中，如果主库上的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？

在使用 --single-transaction 方法进行逻辑备份时，如果在主库上对一个小表执行了DDL操作（例如给表添加一列），那么从备库上会有以下现象：

如果备库上正在进行备份操作，那么备份操作会继续进行，并且不会受到DDL操作的影响。备份过程中的事务会在备库上以一个独立的事务运行，不会受到主库上的DDL操作的干扰。
如果备库上没有正在进行的备份操作，那么备库会根据主库上的DDL操作进行相应的表结构变更。这意味着备库上的相应表也会被添加新的列，以保持与主库的结构一致。
需要注意的是， --single-transaction 方法只适用于逻辑备份，而不适用于物理备份。在物理备份中，备份的是数据库文件本身，而不是逻辑数据。因此，在物理备份中，对主库上的DDL操作不会直接影响备库的结构。
总结来说，使用 --single-transaction 方法进行逻辑备份时，如果在主库上执行了DDL操作，备库会根据DDL操作进行相应的表结构变更，以保持与主库的结构一致。

21、MySQL的行锁

MySQL 的行锁是在引擎层由各个引擎自己实现的。但并不是所有的引擎都支持行锁，比如 MyISAM 引擎就不支持行锁。不支持行锁意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能有一个更新在执行，这就会影响到业务并发度。InnoDB 是支持行锁的，这也是 MyISAM 被 InnoDB 替代的重要原因之一。

22、MyISAM被InnoDB替换的原因

MyISAM被InnoDB替换的主要原因是因为InnoDB引擎提供了更好的事务支持和数据完整性，而MyISAM则不支持事务和行级锁定。以下是一些主要的原因：

事务支持：InnoDB引擎支持事务，可以将一系列操作作为一个原子性的单元进行提交或回滚。这对于需要保证数据的一致性和完整性的应用非常重要，例如银行交易或电子商务网站。
行级锁定：InnoDB引擎支持行级锁定，这意味着多个事务可以同时访问同一张表的不同行，而不会相互阻塞。这提高了并发性能，并减少了锁冲突和数据竞争的可能性。
外键约束：InnoDB引擎支持外键约束，可以保证数据的完整性和一致性。它可以自动处理关联表之间的引用完整性，并提供了CASCADE、SET NULL等选项来处理外键关联的数据更新和删除。
崩溃恢复：InnoDB引擎具有更好的崩溃恢复能力。它使用了事务日志（redo log）和回滚日志（undo log）来保证在数据库崩溃或断电后的数据一致性。
并发控制：InnoDB引擎使用多版本并发控制（MVCC）来实现高并发性能。它允许读取已提交的数据，而不会被其他事务的写操作所阻塞。
尽管MyISAM在某些特定场景下可能具有一些优势，如全文索引和更简单的表结构，但由于其不支持事务和行级锁定等重要特性，InnoDB引擎逐渐成为MySQL的首选存储引擎。

23、死锁和死锁检测

当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

24、解决死锁的策略

当出现死锁以后，有两种策略：
一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数 innodb_lock_wait_timeout 来设置。
另一种策略是，发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 innodb_deadlock_detect 设置为 on，表示开启这个逻辑。

25、change buffer是什么

当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。通过这种方式就能保证这个数据逻辑的正确性。
需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。
显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

26、什么情况下会使用change buffer

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如，要插入 (4,400) 这个记录，就要先判断现在表中是否已经存在 k=4 的记录，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

27、change buffer 的机制了解后，如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的？

第一种情况是，这个记录要更新的目标页在内存中。这时，InnoDB 的处理流程如下：对于唯一索引来说，找到 3 和 5 之间的位置，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，找到 3 和 5 之间的位置，插入这个值，语句执行结束。这样看来，普通索引和唯一索引对更新语句性能影响的差别，只是一个判断，只会耗费微小的 CPU 时间。但，这不是我们关注的重点。
第二种情况是，这个记录要更新的目标页不在内存中。这时，InnoDB 的处理流程如下：对于唯一索引来说，需要将数据页读入内存，判断到没有冲突，插入这个值，语句执行结束；对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。
将数据从磁盘读入内存涉及随机 IO 的访问，是数据库里面成本最高的操作之一。change buffer 因为减少了随机磁盘访问，所以对更新性能的提升是会很明显的。