Mysql实战45讲

如上图所示MySQL整体来看，其实就有两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。

与查询流程不一样的是，更新流程还涉及两个重要的日志模块，它们正是我们今天要讨论的主 角：redo log（重做日志）和 binlog（归档日志）。

物理日志：redo log----InnoDB特有

作用：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。InnoDB特有的日志。

redo log：

• 内容：物理格式的日志，记录的是物理数据页面的修改的信息。
• 保存位置：他也是保存在磁盘当中的，但是他与更新mysql数据不同的是，更新过程是随机IO，而redo log是顺序IO，效率更高。

而粉板和账本配合的整个过程，其实就是MySQL里经常说到的WAL技术，WAL的全称是WriteAhead Logging，它的关键点就是先写日志，再写磁盘，也就是先写粉板，等不忙的时候再写账本。

注意：WAL不仅仅是因为提升IO性能，更重要的是要满足持久性和原子性。持久性好理解。原子性是指在事务写入时可能会崩溃，由于写入中间状态与崩溃都是无法避免的，为了保证原子性和持久性，只能在崩溃恢复后采取补救措施，这种能力就被称为“崩溃恢复”。 为了能够实现崩溃恢复，所以要通过写日志的方式，日志写成功了，再写入磁盘，从而实现原子性和持久性。

具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log（粉板）里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作 记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做，这就像打烊以后掌柜做的事。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个 能力称为crash-safe。

逻辑日志：binlog----用于主从复制

作用：用于复制，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 用于数据库的基于时间点的还原。Server层的日志系统binlog。

内容：逻辑格式的日志，可以简单认为就是执行过的事务中的sql语句。

这两种日志有以下三点不同。

1. redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
2. MySQL的binlog是逻辑日志，其记录是对应的SQL语句。而innodb存储引擎层面的redolog日志是物理日志，保存了数据库中的值”。
3. redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

这里我给出这个update语句的执行流程图，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

从图中可以看出，对于redolog，存在两个阶段：一个准备阶段，一个提交阶段（称两阶段提交）。为什么要有两阶段提交？

为了保证binlog和redo log的一致性问题。两阶段提交具体来说如下：

• 第一阶段： InnoDB Prepare阶段。此时SQL已经成功执行，并生成事务ID(xid)信息及redo和undo的内存日志。 此阶段InnoDB会写事务的redo log，但要注意的是，此时redo log只是记录了事务的所有操作日志，并没有记录提交（commit）日志，因此事务此时的状态为Prepare。 此阶段对binlog不会有任何操作。
• 第二阶段：commit 阶段，这个阶段又分成两个步骤。第一步写binlog（先调用write()将binlog内存日志数据写入文件系统缓存，再调用fsync()将binlog文件系统缓存日志数据永久写入磁盘）；第二步完成事务的提交（commit），此时在redo log中记录此事务的提交日志（增加commit 标签）。 还要注意的是，在这个过程中是以第二阶段中binlog的写入与否作为事务是否成功提交的标志。第一步写binlog完成 ，第二步redo log的commit未提交未完成，也算完成。

为什么要写两次redo log，写一次不行吗？

先不谈到底写几次redo log合适，如果只写一次redo log会有什么样的问题呢？

redo log与binlog都写一次的话，也就是存在以下两种情况：

先写binlog，再写redo log

当前事务提交后，写入binlog成功，之后主节点崩溃。在主节点重启后，由于没有写入redo log，因此不会恢复该条数据。

而从节点依据binlog在本地回放后，会相对于主节点多出来一条数据，从而产生主从不一致。

先写redo log，再写binlog

当前事务提交后，写入redo log成功，之后主节点崩溃。在主节点重启后，主节点利用redo log进行恢复，就会相对于从节点多出来一条数据，造成主从数据不一致。

因此，只写一次redo log与binlog，无法保证这两种日志在事务提交后的一致性。

也就是无法保证主节点崩溃恢复与从节点本地回放数据的一致性。

在两阶段提交的情况下，是怎么实现崩溃恢复的呢？

首先比较重要的一点是，在写入redo log时，会顺便记录XID，即当前事务id。在写入binlog时，也会写入XID。

如果在写入redo log之前崩溃，那么此时redo log与binlog中都没有，是一致的情况，崩溃也无所谓。

如果在写入redo log prepare阶段后立马崩溃，之后会在崩恢复时，由于redo log没有被标记为commit。于是拿着redo log中的XID去binlog中查找，此时肯定是找不到的，那么执行回滚操作。

如果在写入binlog后立马崩溃，在恢复时，由redo log中的XID可以找到对应的binlog，这个时候直接提交即可。

总的来说，在崩溃恢复后，只要redo log不是处于commit阶段，那么就拿着redo log中的XID去binlog中寻找，找得到就提交，否则就回滚。

在这样的机制下，两阶段提交能在崩溃恢复时，能够对提交中断的事务进行补偿，来确保redo log与binlog的数据一致性。

小结

今天，我介绍了MySQL里面最重要的两个日志，即物理日志redo log和逻辑日志binlog。

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

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

我还跟你介绍了与MySQL日志系统密切相关的“两阶段提交”。两阶段提交是跨系统维持数据逻辑 一致性时常用的一个方案，即使你不做数据库内核开发，日常开发中也有可能会用到。

简单来说，事务就是要保证一组数据库操作，要么全部成功，要么全部失败。在MySQL中，事务支持是在引擎层实现的。你现在知道，MySQL是一个支持多引擎的系统，但并不是所有的引 擎都支持事务。比如MySQL原生的MyISAM引擎就不支持事务，这也是MyISAM被InnoDB取代的重要原因之一。

隔离性与隔离级别

提到事务，你肯定会想到ACID（Atomicity、Consistency、Isolation、Durability，即原子性、一 致性、隔离性、持久性），今天我们就来说说其中I，也就是“隔离性”。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non- repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级别”的概念。

在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都要 在二者之间寻找一个平衡点。SQL标准的事务隔离级别包括：读未提交（read uncommitted）、 读提交（read committed）、可重复读（repeatable read）和串行化（serializable ）：

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

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”隔离级别下，这个视图是在事务启动时创建的（即执行这个启动命令start transaction with consistent snapshot；如果是begin启动，则是执行第一个快照读创建视图），整个事务存在期间都用这个视图。在“读提交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下直接用加锁的方式来避免并行访问。

多个并发事务会产生一系列问题，最常见的是如下三个：

• 脏读：一个事务读到另一个事务还没提交的数据
• 不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同
• 幻读：一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。幻读仅专指新插入的行，且在当前读的情况下。

一句话简单来说，索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。

哈希表只适合做等值搜索，对于范围搜索、模糊搜索都不行。

每一个索引在 InnoDB 里面对应一棵 B+ 树。

4.1 聚簇索引与二级索引

假设，我们有一个主键列为 ID 的表，表中有字段 k，并且在 k 上有索引。这个表的建表语句是：

mysql> create table T(id int primary key, k int not null, name varchar(16),index (k))engine=InnoDB;

表中 R1~R5 的 (ID,k) 值分别为 (100,1)、(200,2)、(300,3)、(500,5) 和 (600,6)，两棵树的示例示意图如下。

从图中不难看出，根据叶子节点的内容，索引类型分为主键索引和非主键索引。

主键索引的叶子节点存的是整行数据。在 InnoDB 里，主键索引也被称为聚簇索引（clustered index）。

非主键索引的叶子节点内容是主键的值。在 InnoDB 里，非主键索引也被称为二级索引（secondary index）。

而通过二级索引查询，可能会导致回表（多查询一棵索引树），所以要尽量使用主键查询，避免通过二级索引查询数据。

例如，造成回表的查询方式：

select * from T where ID=50//不会造成回表select * from T where k=5//会造成回表

4.2 索引维护

由于B+树可能会导致页（节点）分裂，或者页合并，导致时间复杂度剧增。所以一般建议，使用自增主键而不是业务字段作为主键索引（聚簇索引）。具体为什么如下：

自增主键是指自增列上定义的主键，在建表语句中一般是这么定义的： NOT NULL PRIMARY KEY AUTO_INCREMENT。

自增主键可以防止页分裂，逻辑删除并非物理删除防止页合并。

自增主键如何防止页分裂？

• 插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。也就是说，自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

不用业务字段做主键原因：

1. 业务字段不一定是递增的，有可能会造成主键索引的页分裂，导致性能不稳定。
2. 二级索引存储的值是主键，如果使用业务字段做主键，会导致占用大小不好控制，如果业务字段过长可能会导致二级索引占用空间过大，利用率不高。

所以，从上面分析得知，通过性能和存储空间方面考量，自增主键往往是更合理的选择。

---

但是！有没有什么场景适合用业务字段直接做主键的呢？还是有的。比如，有些业务的场景需求是这样的：

• 只有一个索引；
• 该索引必须是唯一索引。

你一定看出来了，这就是典型的 KV 场景。由于没有其他索引，所以也就不用考虑其他索引的叶子节点大小的问题。

这时候我们就要优先考虑上一段提到的“尽量使用主键查询”原则，直接将这个索引设置为主键，可以避免每次查询需要搜索两棵树。

此外：尽量避免对主键进行修改或者删除 。因为普通索引（二级索引）叶子节点存储的是主键值。修改/删除主键索引会影响普通索引。所以怎么修改普通索引和主键索引，主要要考虑的问题是减少修改主键索引对普通索引的影响！

为什么要重建索引：
当对InnoDB进行修改操作时，例如删除一些行，这些行只是被标记为“已删除”，而不是真的从索引中物理删除了，因而空间也没有真的被释放回收。 InnoDB的Purge线程会异步的来清理这些没用的索引键和行，但是依然没有把这些释放出来的空间还给操作系统重新使用，因而会导致页面中存在很多空洞。重建索引的过程会创建一个新的索引，把数据按顺序插入，提高空间利用率。

如何重建主键和二级索引

//重建主键alter table T engine=InnoDB; //重建二级索引alter table T drop index k;alter table T add index(k);

---

总结一下：

  其实理解下来就是：每一张表其实就是含有1个主键B+树，和多个二级索引的B+树。

• 对于主键B+树来说：树叶子结点的key值就是某一行的主键，value是该行的完整信息。
• 对于二级索引的B+树来说：树叶子结点的key值就是该二级索引的值，value就是该值对应的主键。

新建索引就是新增一个B+树，如果对二级索引进行查询操作，一般就是遍历二级索引B+树，获得主键值，再遍历主键B+树。

4.3 联合索引----注意最左前缀原则

覆盖索引如果执行的语句是 select ID from T where k between 3 and 5（k是二级索引，ID是主键），这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。也就是说，覆盖索引就是在这次的查询中，所要的数据已经在这棵索引树的叶子结点上了

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

覆盖索引也可以用在联合索引上，从而避免回表。

联合索引：根据创建联合索引的顺序，以最左原则进行where检索，比如（age，name）以age=1 或 age= 1 and name=‘张三’可以使用索引，单以name=‘张三’ 不会使用索引，考虑到存储空间的问题，还请根据业务需求，将查找频繁的数据进行靠左创建索引。

联合索引的最左前缀原则：联合索引在B+树结构中先根据第一个字段排序，如果第一个字段有相同的，就按照第二个字段排序，注意，这里仅仅有相同的第一个字段情况下，才会根据第二个字段排序。 所以，联合索引(A, B)意味着不需要建立A的索引了，因为这个联合索引意味着建立了(A,B)和(A)这两种索引。

那么，如果既有联合查询，又有基于 a、b 各自的查询呢？查询条件里面只有 b 的语句，是无法使用 (a,b) 这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护 (a,b)、(b) 这两个索引。

总结：你可以看到，在满足语句需求的情况下， 利用联合索引以及最左前缀原则来尽量少地访问资源是数据库设计的重要原则之一。我们在使用数据库的时候，尤其是在设计表结构时，也要以减少资源消耗作为目标。

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

5.1 全局锁（支持mvcc的可以不需要全局锁）

全局锁用于数据同步和备份。

顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock 和 unlock tables。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。所以其实有了 MVCC 的支持，就不需要全局锁了。

你一定在疑惑，有了这个功能，为什么还需要 FTWRL 呢？一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，我们就需要使用 FTWRL 命令了。

所以，single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

5.2 表级锁

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。

表锁：

表锁的语法是 lock tables 表名 read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

注意：有行锁用行锁！表锁一般是在数据库引擎不支持行锁的时候才会被用到的。

注意：
  表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查一下，比较可能的情况是：

• 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
• 要么是你的引擎升级了，但是代码还没升级。我见过这样的情况，最后业务开发就是把 lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

元数据锁（MDL）：
元数据锁（MDL）是server层的锁，表级锁，MDL作用是防止DDL（对表结构进行修改）和DML（对表中数据进行CRUD）并发的冲突。

每执行一条DML、DDL语句时都会申请MDL锁，DML操作需要MDL读锁，DDL操作需要MDL写锁（MDL加锁过程是系统自动控制，无法直接干预，读读共享，读写互斥，写写互斥）

当对一个表中的数据做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

• 读锁之间不互斥，因此你可以有多个线程同时对一张表的数据做增删改查。
• 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

所以需要考虑读的时候会一直占着MDL锁，导致没办法写操作而影响性能。

申请MDL锁的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到MDL锁后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放，例如id 44的语句改为，此时一旦alter语句执行完成会马上提交事务（autocommit=1），后面的select就在本次事务之外，其执行完成后不会持有读锁）

注意：MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。

5.3 行锁

5.3.1 行锁

概念：
  顾名思义，行锁就是针对数据表中行记录的锁。这很好理解，比如事务 A 更新了一行，而这时候事务 B 也要更新同一行，则必须等事务 A 的操作完成后才能进行更新（和MDL一样，无需显示添加）。

注意：事务在执行的时候，并不是一次性把所有行锁都持有，而是执行到哪一行就拿哪一行的锁。等到最后commit的时候，一起释放（两阶段锁----锁的添加与释放分到两个阶段进行）。

两阶段锁特性特性：
  在 InnoDB 事务中，行锁是在语句执行时才加上的，不是事务开始就加上，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。这个就是两阶段锁协议。根据这个特性，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，以减少锁等待的时间，提高并发性能

例如：

假设你负责实现一个电影票在线交易业务，顾客 A 要在影院 B 购买电影票。我们简化一点，这个业务需要涉及到以下操作：1. 从顾客 A 账户余额中扣除电影票价；2. 给影院 B 的账户余额增加这张电影票价；3. 记录一条交易日志。也就是说，要完成这个交易，我们需要 update 两条记录，并 insert 一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。那么，你会怎样安排这三个语句在事务中的顺序呢？试想如果同时有另外一个顾客 C 要在影院 B 买票，那么这两个事务冲突的部分就是语句 2 了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句 2 安排在最后，比如按照 3、1、2 这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。(尽量将并发度高的行,放在事务的最后进行执行)好了，现在由于你的正确设计，影院余额这一行的行锁在一个事务中不会停留很长时间。

5.3.2 死锁和死锁检测

例如：

这时候，事务 A 在等待事务 B 释放 id=2 的行锁，而事务 B 在等待事务 A 释放 id=1 的行锁。 事务 A 和事务 B 在互相等待对方的资源释放，就是进入了死锁状态。当出现死锁以后，mysql有两种策略解决死锁：

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

一般情况下，往往采用第二种方式，死锁检测。主动死锁检测在发生死锁的时候，是能够快速发现并进行处理的，但是它也是有额外负担的。

你可以想象一下这个过程：每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

每个新来的被堵住的线程，都要判断会不会由于自己的加入导致了死锁，这是一个时间复杂度是 O(n) 的操作。假设有 1000 个并发线程要同时更新同一行，那么死锁检测操作就是 100 万这个量级的。虽然最终检测的结果是没有死锁，但是这期间要消耗大量的 CPU 资源。因此，你就会看到 CPU 利用率很高，但是每秒却执行不了几个事务。

解决办法是控制访问相同资源的并发事务量。根据上面的分析，你会发现如果并发能够控制住，比如同一行同时最多只有 10 个线程在更新，那么死锁检测的成本很低，就不会出现这个问题。

注意：关于死锁检测，并不少每条事务在执行的时候都会进行死锁检测。而是他要加锁访问的行上有锁，他才要检测。

性能比较：

• 插入时：唯一索引需要校验是否唯一，而普通索引不用。所以唯一索引无法使用change buffer（唯一索引需要把数据读入内存中进行判断是否唯一，所以无法使用change buffer），而普通索引可以用。所以如果需要更新的数据的目标页不在内存时，普通索引的插入速度远大于唯一索引。所以插入时普通索引性能>唯一索引
• 查询时：唯一索引命中一个就返回，普通索引命中后还要继续往下比较直到出现不满足的情况，但是，如果所查询的数据不是数据页的最后一个数据，那么其实往下比较也不需要耗费多大的时间，所以查询时普通索引性能=唯一索引

注：
  change buffer：当需要更新一个数据页时，如果数据页在内存中就直接更新，而如果这个数据页还没有在内存中的话，在不影响数据一致性的前提下，InnoDB 会将这些更新操作缓存在 change buffer 中，这样就不需要从磁盘中读入这个数据页了。在下次查询需要访问这个数据页的时候，将数据页读入内存，然后执行 change buffer 中与这个页有关的操作。

  显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

总结以上知识点

1. 普通索引与唯一索引，查询的性能上没差别，但是普通索引在更新时速度更快。所以在业务都满足的情况下尽量选普通索引。
2. change buffer只适合普通索引。
3. 如果更新之后马上就是查询时，不使用change buffer，innodb_change_buffer_max_size=0

1. change buffer 和 redo log的区别

redo log 和 change buffer这两个概念很容易混淆，现在先用一个例子来说明：

假设，我们要往表中插入数据：

mysql> insert into t(id,k) values(id1,k1),(id2,k2);

这里，我们假设当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。

这条更新语句做了如下的操作（按照图中的数字顺序）：

1. Page 1 在内存中，直接更新内存；
2. Page 2 没有在内存中，就在内存的 change buffer 区域，记录下“我要往 Page 2 插入一行”这个信息
3. 将上述两个动作记入 redo log 中（图中 3 和 4）。

做完上面这些，事务就可以完成了。所以，你会看到，执行这条更新语句的成本很低，就是写了两处内存，然后写了一处磁盘（两次操作合在一起写了一次磁盘），而且还是顺序写的。

比如，我们现在要执行 select * from t where k in (k1, k2)。

如果读语句发生在更新语句后不久，内存中的数据都还在.

1. 读 Page 1 的时候，直接从内存返回。有几位同学在前面文章的评论中问到，WAL 之后如果读数据，是不是一定要读盘，是不是一定要从 redo log 里面把数据更新以后才可以返回？其实是不用的。你可以看一下图 3 的这个状态，虽然磁盘上还是之前的数据，但是这里直接从内存返回结果，结果是正确的。
2. 要读 Page 2 的时候，需要把 Page 2 从磁盘读入内存中，然后应用 change buffer 里面的操作日志，生成一个正确的版本并返回结果。

可以看到，直到需要读 Page 2 的时候，这个数据页才会被读入内存。

所以，如果要简单地对比这两个机制在提升更新性能上的收益的话，redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。

总结：
redo log 与 change buffer(含磁盘持久化) 这2个机制，不同之处在于——优化了整个变更流程的不同阶段。 先不考虑redo log、change buffer机制，简化抽象一个变更(insert、update、delete)流程：

1. 从磁盘读取待变更的行所在的数据页，读取至内存页中。
2. 对内存页中的行，执行变更操作
3. 将变更后的数据页，写入至磁盘中。

步骤1，涉及 随机 读磁盘IO；
步骤3，涉及 随机 写磁盘IO；

• Change buffer机制，优化了步骤1——避免了随机读磁盘IO
• Redo log机制， 优化了步骤3——避免了随机写磁盘IO，将随机写磁盘，优化为了顺序写磁盘(写redo log，确保crash-safe)

在我们mysql innodb中， change buffer机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer才有用武之地。 redo log机制，为了保证crash-safe，一直都会用到。

有无用到change buffer机制，对于redo log这步的区别在于—— 用到了change buffer机制时，在redo log中记录的本次变更，是记录new change buffer item相关的信息，而不是直接的记录物理页的变更。

来源地址：https://blog.csdn.net/weixin_45045804/article/details/128527659