《MySQL 实战45讲》笔记——实战篇（9~21课）

鉴于45讲的内容很多，所以选择将笔记拆为一部分一部分的，这是第二部分。

第一部分内容：《MySQL实战45讲》笔记 ——基础篇 - coderZoe的博客

1. 普通索引与唯一索引的选择

项目开发中对于一个不重复的字段应该选择普通索引还是唯一索引？这主要需要考虑索引的查询和更改效率，我们得分别分析普通索引和唯一索引的查询和更新流程。

1.1 一条查询的执行

我们以一条查询语句 select id from T where k = 5;，其实id是主键，k不重复。

• 对于普通索引，查询到满足条件的第一条数据后还需要查找下一条记录直到不满足查询条件为止。上述SQL语句会先查到第一条k = 5的记录，然后接着往下查，直到k≠5。
• 对于唯一索引，因为索引定义了唯一性，因此查到第一条满足需求的记录后就会停止检索。

上面两种情况其实性能差距是微乎其微的，因为普通索引虽然继续查询了，但我们的k是不重复的，因此也只会多查询一次而已，而且MySQL中数据是以页的形式读取到内存，一页大概有上千条记录，因此也只不过是在内存中多向下找了一条数据（除非k = 5这个记录刚好是数据页的最后一条记录，那这个时候就还需要再加载下一个页到内存，但这种情况概率很小，平均下来两种情况依然差异很小）。

因此对于查询，普通索引和唯一索引的性能差异是很小的。那我们再来看更新。

1.2 一条更新的执行

1.2.1 change buffer

在讲更新流程前我们需要先讲个东西change buffer。

首先如果我们想更新一条数据会存在两种情况：1. 这条数据在内存中 2. 这条数据不在内存中。

对于第一种情况，因为数据在内存中那么直接更新内存中的这条数据即可。而对于第二种情况，数据不在内存中要怎么办呢？要从磁盘加载数据到内存吗，如果将数据从磁盘加载到内存那可就是一条随机读IO，这个成本是很大的。这个时候change buffer就要登场了。

change buffer作用场景为：对于一条更改语句，这条要改的数据不在内存里，同时又不想从磁盘查出来加载到内存耗时间，那就可以先将这个改动先缓存到change buffer中。后续当有查询语句要查这条数据的时候，这条数据所在的数据页会从磁盘加载到内存，这时数据还是之前未修改的样子，我们叫做原数据页，此时再将之前change buffer中的改动生效到这个原始数据页中。将change buffer中的操作应用到原始数据页，得到最新结果的过程叫做merge。

很显然，通过change buffer减少了随机读磁盘的次数。因为对于未在内存中的数据页的修改会先记录下来而不是立马从磁盘中加载到内存，这样语句的执行速度得到了提升。另外，如果将修改的数据页的数据读入内存，由于一页(16 kb)要比一条change buffer大很多，因此我们使用change buffer代替页的加载，也减少了内存的消耗。

既然change buffer那么好，那么什么条件下可以用change buffer呢？

事实上只有普通索引可以使用change buffer，唯一索引不可以使用。这很好理解，对于唯一索引，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如对于上面的例子，我插入了一条(4,100)数据，就需要判断表中是否存在k = 4这条记录。既然要判断是否存在，自然就需要将相关数据的数据页读入内存才能判断，而既然数据都读入内存了，我直接更改内存就可以了，没必要使用change buffer了。

1.2.2 普通索引与唯一索引的更改

假设我们现在要插入一条(4,400)的数据，对于InnoDB其处理流程为：

1. 当前要插入的目标页在内存中：

   • 唯一索引：找到3和5之间的位置，判断是否有冲突，如果没冲突就插入，语句结束。
   • 普通索引：找到3和5之间的位置，插入数据。

2. 当前要插入的目标页不在内存中：

   • 唯一索引：将数据页读取到内存，判断是否有冲突，如果没冲突就插入，语句结束。
   • 普通索引：将更新记录到change buffer中，语句执行结束。

可以看到在目标页在内存中的情况，唯一索引和普通索引区别不大，虽然唯一索引有个唯一性校验，但因为数据在内存中，校验会很快。但是对于目标页不在内存中两者的性能差异就很大了，唯一索引需要先读入内存，这就需要一次随机读，而普通索引只做了一次内存的写。

因此很多时候更建议使用普通索引而非唯一索引。但这种情况也不绝对，比如你的应用场景是每次做完修改操作都紧跟一条查询，那虽然更改的时候使用了change buffer避免了随机读的出现，但后续的读操作依然要将数据从磁盘加载到内存，且由于之前用了change buffer还需要维护原始数据页与change buffer的merge。这时增加了change buffer的维护代价，change buffer反而成了副作用。

change buffer更适合的使用场景是一些归档数据或日志类数据等。

1.3 change buffer与redo log

很多同学会将change buffer与redo log的作用混淆。对于redo log我们知道它的核心是WAL，使用顺序IO替代随机IO，减少随机写，而change buffer则是为了避免更新时数据从磁盘加载到内存，使用内存暂时将更改记下来，减少了随机读。

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，将随机写磁盘，优化为了顺序写磁盘。

在我们mysql innodb中， change buffer机制不是一直会被应用到，仅当待操作的数据页当前不在内存中，需要先读磁盘加载数据页时，change buffer才有用武之地。 但redo log机制，为了保证crash-safe，一直都会用到。 而有无用到change buffer机制，对于redo log这步的区别在于： 用到了change buffer机制时，在redo log中记录的本次变更，是记录new change buffer item相关的信息，而不是直接的记录物理页的变更。

所以对于一个更新操作：

如果所在数据页在内存中，直接更新内存，然后记录redo log提交事务，而对于数据页不在内存中的，在change buffer区域记录更改操作，将这一信息记录到redo log然后提交事务。

可以看到一次更改，只涉及到一次写内存和一次顺序写磁盘，因此效率是比较高的。（需要注意的是redo log的写入是两阶段提交）

1.4 一个细节的补充

上面其实并没有完整的讲一条数据的更新过程，我们假设在使用change buffer的情况下做update操作，那么流程如下：

1. Server层向InnoDB申请更改一条数据
2. InnoDB查到数据不在内存，因此会生成一条更新语句记录到change buffer
3. InnoDB记录一条redo log，并将状态记录为prepare，然后返回给Server层成功
4. Server层记录binlog
5. InnoDB将redo log状态修改为commit

这样一条更新语句就执行完成了，如果后续这条被更新的语句被加载到内存，那么就需要在内存中做merge操作。merge的流程如下：

6. 数据被加载到内存，从change buffer中查找之前被更改的记录，将change buffer中的修改应用到内存的数据页中。
7. 记录一条redo log
8. 等后续某个时刻刷盘，将内存页的修改持久化到磁盘

很多同学可能会对上述流程有疑问，为什么会记录两次redo log？

这是因为change buffer其实也是数据页。第2步创建一条change buffer记录，然后第3步的redo log其实并非是对原数据页的修改记录，而是对change buffer的修改记录，只有在第6步，原数据页加载到内存，做完merge操作后，此时原数据页才算在内存中修改完成，但是这个修改并未持久化到磁盘，因此第7步记录一条redo log，而第7步的redo log其实才是对原数据页修改的记录。

我们都知道redo log是防止mysql宕机后crash safe的，现在我们假设mysql会在任何时候宕机：

如果mysql在第3步到第4步之间宕机，那么也就是记录了change buffer的redo log但没记录binlog，此时如果mysql恢复，由于未记录binlog，因此不恢复数据。

如果宕机发生在4和5或者5和6之间或者是7之前，那么记录了change buffer的redo log记录，binlog也有记录，因此会根据redo log恢复change buffer数据。这里需要注意的是，我们恢复的是change buffer数据，而非原数据页被修改的数据。change buffer数据恢复后，如果后续这条记录被加载到了内存，那么又可以根据恢复的change buffer做merge操作，数据的修改也不会丢失。

如果宕机发生在7和8之间，那么数据恢复的时候直接使用redo log对数据页修改的记录，直接恢复修改后的数据页的数据即可。

如果宕机发生在8之后，数据已经被持久化，因此无影响。

最后，贴一个极客时间对change buffer解释比较好的评论：

change Buffer和数据页一样，也是物理页的一个组成部分，数据结构也是一颗B+树，这棵B+树放在共享表空间中，默认ibdata1中。change buffer 写入系统表空间机制应该和普通表的脏页刷新到磁盘是相同的机制--Checkpoint机制； 之所以change buffer要写入系统表空间，是为了保证数据的一致性，change buffer做修改时需要写redo，在做恢复时需要根据redo来恢复change buffer，若是不进行change buffer写入系统表空间，也就是不进行持久化，那么在change buffer写入内存后掉电（也就是篇尾提出的问题），则无法进行数据恢复。这样也会导致索引中的数据和相应表的相应列中的数据不一致。 change buffer 写入到了系统表空间，merge的时候会先查询change buffer里对应的记录，然后进行merge，因为change buffer B+树的key是表空间ID，所以查询根据表空间ID 查询change buffer会很快。

2. InnoDB中的刷盘

有时一条 SQL 语句，正常执行的时候特别快，但是有时也不知道怎么回事，它就会变得特别慢，并且这样的场景很难复现，它不只随机，而且持续时间还很短，这种情况往往与InnoDB的刷盘有关。

在讲刷盘之前我们先讲一个概念脏页：

InnoDB IO操作的基本单位是页，默认情况一页是16kb。一张MySQL表会被记录为若干的页，MySQL在做查询或更新操作时都会在内存进行，这就代表一页的数据可能既存在于内存也存在于磁盘上。而对于修改操作我们前面已经知道，如果修改的数据页在内存，MySQL会直接修改内存，并记录一条redo log。这时就会存在一个现象：内存的页已经被修改了，但磁盘上的页数据还是以前的。也可以理解为内存修改的结果还未持久化到磁盘，这种情况下内存中的这个页就叫脏页。反之内存与磁盘数据一样的页叫干净页。

将脏页中的数据持久化到磁盘这一操作称为刷盘。

我们举个例子：

当前数据表t中有一条记录

此时执行一条更新语句：

update t set key = 20 where id = 1; 

根据我们前面的学习肯定知道，这条SQL语句执行主要是将内存中这条数据的值修改（假设数据已经在内存了），记录一个redo log然后就返回了。

此时我们磁盘的内容依然是(1,10)，但是内存的记录是(1,20)，然后redo log会记录一条某数据页某行将10改为20的日志操作。

但有时这样简单的一条更新语句可能会执行的比较久，这就代表有可能MySQL在执行这条语句的时候需要先刷盘。

我们想一下MySQL会在哪些情况下刷盘？

1. redo log记满了

   我们之前讲过redo log不是无限长的，当write pos追上checkpoint指针时就代表redo log写满了。由于redo log写满了，记不下了，就需要先进行刷盘操作，将redo log中记录的脏页数据持久化到磁盘，持久化后那些记录对应的redo log也就没了意义，因此checkpoint可以继续前移覆盖没用的记录，这样redo log可以继续写入。

2. 内存不够用，淘汰到了脏页

   InnoDB中存储页的内存叫Buffer Pool。Buffer Pool内会存储多个数据页，其中这些数据页分为三类：未使用的，已经使用的并且是干净页的数据页，已经使用的并且是脏页的数据页。

   在MySQL启动的时候，Buffer Pool内所有的页都是未使用的，一旦有修改或查询发生，MySQL就得从磁盘加载数据到内存，此时就需要向BufferPool申请一个数据页，一般情况下会优先使用未使用的数据页。但内存资源也是有限的，总有一天Buffer Pool内的数据页都被使用完，此时如果还想申请数据页，就得先淘汰一些内存中的数据页（淘汰算法是LRU，但会比较复杂，分为新生代和老年代等淘汰机制，等有时间我们会单独讲），如果恰巧淘汰的数据页是脏页，就得先把脏页持久化到磁盘，再释放内存。

3. MySQL空闲时会主动刷盘

   MySQL并不总是高负荷工作的，在空闲的时候，MySQL会合理安排时间，见缝插针的刷盘。

4. MySQL正常关闭时，在关闭前刷盘。

   在执行关闭指令时，MySQL会先将内存中脏页持久化到磁盘。

上面情况3和4都是MySQL空闲的情况下发生的，因此不会成为性能问题，主要的是问题主要在情况1和情况2。

上面1和2都有一个共同问题就是内存中脏页太多，由于脏页太多导致redo log记录的比较长，很可能用完。同样由于脏页比较多，淘汰页内存的时候很容器淘汰掉脏页数据。

针对这种问题，InnoDB主要从以下几种情况优化：

1. 设置合适的IO能力，你要告诉InnoDB你当前主机的IO能力，这个能力一般是磁盘的IOPS，而与之对应的InnoDB参数是innodb_io_capacity，一般情况下机械硬盘是300-900之间，而固态硬盘往往在20000左右。如果你的MySQL写入很慢，但数据库主机IO压力不大，往往是这个参数设置的较小。

2. 合理的刷盘速度。

   InnoDB刷盘速度与两个信息有关：当前脏页比（参数 innodb_max_dirty_pages_pct 是脏页比例上限，默认值是 75%。），和redo log长度（未同步的记录）。

   当前脏页比比较好理解，就是BufferPool中，脏页占所有数据页的比例，而redo log长度其实就是计算当前写入日志的序号与checkpoint对应的那条日志的差值。

3. 连坐

   一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。

   在 InnoDB 中，innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。

   找“邻居”这个优化在机械硬盘时代是很有意义的，可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百，相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。而如果使用的是 SSD 这类 IOPS 比较高的设备的话，建议把 innodb_flush_neighbors 的值设置成 0。因为这时候 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。在 MySQL 8.0 中，innodb_flush_neighbors 参数的默认值已经是 0 了

3. MySQL orderBy的执行原理

MySQL对于无序字段的排序是在Server层实现的，MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。我们以一个例子来看下MySQL的排序实现：

对于表t：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `city` varchar(16) NOT NULL,
  `name` varchar(16) NOT NULL,
  `age` int(11) NOT NULL,
  `addr` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `city` (`city`)
) ENGINE=InnoDB;

当我们执行查询语句：

select city,name,age from t where city='杭州' order by name limit 1000 ;

3.1 全字段排序

MySQL的执行顺序是这样的：

1. 初始化sort_buffer，确定要返回的数据是city、name、age这三个字段
2. 从二级索引city找到第一个满足city='杭州'的记录，返回它的主键id
3. 根据主键id回表，取出整行数据，然后拿出city、name、age这三个字段的值，存入sort_buffer中
4. 从二级索引city取出下一条符合记录的主键id
5. 重复3、4、5直到不再满足city='杭州'的查询条件
6. 对sort_buffer中的数据按照name字段做快排。
7. 将排序后结果的前1000行返回给客户端

因此整个排序过程可以简单总结为：

执行器向存储引擎索要满足的记录，每条满足的记录都放入sort_buffer中，拿到所有记录后，再对sort_buffer中的数据排序。 本例中，引擎层是通过二级索引+回表的形式获取一条完整数据返回给Server层

我们将上述排序成为全字段排序。所谓全字段排序，就是sort_buffer中存储的是返回需要的全部字段。它的流程大致如下：

很明显，数据是先放在sort_buffer中然后再排序的。这时我们不禁要问：如果满足条件的数据比较多，sort_buffer的大小不够呢？对于MySQL而言，如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

外部排序的算法往往采用归并排序，所谓归并排序大致流程如下：

1. 将要排序的数据分为N份存在N个文件中，保证每份文件中的数据都是有序的
2. 创建一个装结果的文件N+1
3. 同时读取N份文件的第一条数据
4. 比较N份数据，找出最小的那个数据，假设在第k份文件
5. 将最小的数据写入N+1文件中
6. 从第k份文件中读取下一条数据，重复4、5、6步骤，直到所有文件中的数据都读取完毕

总而言之就是分治的思想，将全部数据切割为多份，保证每份有序，再合并起来有序。

3.2 rowid 排序

我们在介绍全字段排序的时候讲过，全字段排序的sort_buffer中存储的是要返回需要的全部字段。假设一条查询要返回的字段比较多，那么也就代表sort_buffer中的一条记录很长，很占内存，这时候MySQL会采用第二种排序方式：rowid排序

rowid排序的思想也很简单，它的sort_buffer中只存放两个字段：主键id和要排序的字段。在排序完成后，需要对符合条件的结果再根据主键id做回表，查出整条记录取出所需的字段返回。

同样对于我们开头说的查询语句：

select city,name,age from t where city='杭州' order by name limit 1000 ;

使用rowid排序的时候整个流程就变为了

1. 初始化 sort_buffer，确定放入两个字段，即 name 和 id；
2. 从索引 city 找到第一个满足 city='杭州’条件的主键 id
3. 到主键 id 索引取出整行，取 name、id 这两个字段，存入 sort_buffer 中；
4. 从索引 city 取下一个记录的主键 id；
5. 重复步骤 3、4 直到不满足 city='杭州’条件为止；
6. 对 sort_buffer 中的数据按照字段 name 进行排序；
7. 遍历排序结果，取前 1000 行，并按照 id 的值回到原表中取出 city、name 和 age 三个字段返回给客户端。

3.3 全字段排序 VS rowid 排序

很明显，全字段排序优点是数据都在sort_buffer中，排序完不需要再做回表，但如果字段比较多或存在过长字段，会很容易导致sort_buffer不够用，而从内存的快排改为依赖磁盘的归并排序。rowid排序精简了sort_buffer的字段，使得sort_buffer可以装更多条数据，减少了使用归并排序的情况，即使使用归并排序，rowid由于数据比较小，因此数据拆分的文件块数也会比全字段排序少，归并排序也会更快些。但是rowid排序在排序完后需要做回表得从完整的行记录中取出需要的字段才能返回。

默认情况下，MySQL会优先选择全字段排序。因为MySQL会先假设我们的内存足够的大，在内存足够大的情况下，全字段排序是不需要回表的，性能更优。不过mysql会判断max_length_for_sort_data这一属性，如果要返回的字段的字节加起来大于max_length_for_sort_data，则会使用rowid排序。

比如我们执行：

SET max_length_for_sort_data = 16;

我们要返回的字段city varchar(16) 、name varchar(16) 、age int(11) ，加起来是16+16+4 = 36字节（假设全是英文，一个字符占一个字节）。此时就需要使用rowid排序了。

我们可以很明显的看到MySQL对排序的性能损耗是比较大的，实际上，上述查询语句可以使用索引覆盖来避免排序，我们建立city_name(city, name) 这个联合索引，这样查询一个city的时候，对于city一样的记录，name一定是有序的，这样就无需再做排序。

但是我们还是要回表，因为city_name(city, name) 联合索引上没有age字段，因此我们还可以再优化，创建索引(city,name,age)，既不需要排序，也不需要回表，查询更快。

4. 幻读与gap lock

本章是一个比较重要的章节，在本章我们将详细研究InnoDB的加锁，会将之前学习的行锁、MVCC、索引等知识都串起来，同时会引入新的概念间隙锁（gap lock）。

4.1 幻读

ISO/ANSI SQL92对事务的隔离定义了四个隔离级别：

可以看到，在RR隔离级别下会出现幻读的情况，那什么是幻读呢？

首先我们需要知道RR隔离级别有个很重要的特点：在一次事务中，同一条查询语句多次重复执行，得到的结果是一致的，也就是可以重复的读。所谓幻读其实就是即使你的隔离级别是RR，但仍然可能出现前后两次读取数据不一致的情况。更准确点讲，幻读是指由于数据插入的原因，在一个事务的前后两次以当前读去读取数据的时候会出现后一次查询看到了前一次查询没看到的行。

之前我们已经讲过MVCC了，你肯定知道，在快照读的情况下，所有数据都基于一致性视图建立那一瞬间的快照，因此无论读取多少次数据都是一致的。但我们之前也介绍过当前读，而幻读其实就是发生在当前读下。

我们以一个例子来说明：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

如上，我们建立了一个表t。

在讲幻读之前，我们先来思考下innoDB是如何加行锁的，我们分为走索引和不走索引的情况来分别讨论。另外如无特殊说明，下面例子都是RR隔离级别。

1. 走索引情况下的加行锁：

   begin;
   select * from t where id = 5 for update;
   commit;

   这种情况下，InnoDB只会扫描一行，因此只会在行(5,5,5)上加x锁（所谓x锁就是互斥锁，同样的s锁就是共享锁）。根据我们前面学的两阶段锁，行锁会在事务提交以后才被释放。

2. 不走索引情况下的加行锁：

   select * from t where d = 5 for update;

   这种情况由于没有字段d的索引，因此innoDB会进行全表扫描，会将所有扫描到的行加x锁。即使在扫描这些行的时候，这些行不符合条件，也不会立马释放行锁，而是在事务提交以后才释放所有的行锁。

补充说明一下，很多同学可能会问RR隔离级别下查询不应该是走MVCC查询吗，怎么还要加锁？不知大家注意到了没有我们的select语句最后都加了一句for update，这是当前读，而非基于MVCC的快照读， 且会在需要读的行上加上互斥锁（也叫排他锁或者独占锁或者x锁）。大家可以理解为按照update指令来加锁的select。

明白了如何加行锁后，我们再来看下什么是幻读

现在我们分析下T1、T3和T5时刻session A查询回来的数据都是什么：

1. T1时刻很明显，session A会查询回来(5,5,5)这一条记录。同时，由于session A查询的时候是当前读for update，因此会将所有扫描的行加x锁，由于字段d没有索引，因此走全表扫描，那么T1时刻，当前数据库所有的行都被加上了x锁。
2. T2时刻虽然session B要做更新语句，但由于T1时刻session A已经将所有行加了行锁，因此session B会被阻塞直到T6时刻session A提交事务释放锁才继续执行。T3时刻自然查询回来的依然是(5,5,5)一条记录。
3. 我们假设现在加的只有行锁（注意是假设，实际并非如此，实际还加了我们下面说的gap锁），那么T4时刻session C要插入一条数据，新插入的数据自然是没有上锁的，因此session C不会被session A阻塞，插入成功。那么T5时刻session A在执行查询的时候，由于是当前读，只看最新的结果，此时就会查询回来两条结果：(1,1,5)和(5,5,5)，而这就是幻读。

我们再来回顾下幻读是如何发生的：session A虽然给当前数据库的所有行都加了x锁，但它奈何不了新插入的数据。新插入的数据不需要申请行锁，可以直接插入，这样会使得session A在数据插入的前后以当前读看的结果不一样，一次事务的多次读取出现了不同的结果。

我们再来回顾下之前对幻读的定义：

幻读是指由于数据插入的原因，在一个事务的前后两次以当前读去读取数据的时候会出现后一次查询看到了前一次查询没看到的行。

4.2 数据不一致问题

假如上述情况还没让你觉得有什么大的问题，毕竟多数时候大家不用当前读来查询，而且即使多次查询到的结果不一致问题也不大。但现在我们要讨论一个会影响数据库数据一致性的问题。还拿上面创建的表t举个例子：

T2时刻将记录(5,5,5)更新为(5,5,100)，T3时刻由于行锁无法锁插入，因此session B插入新的记录(1,1,5)，同时session B提交事务。T4时刻session A提交事务。

因此我们现在数据库内应该有(5,5,100)和(1,1,5)两条数据。但我们现在的binlog呢？

由于session B事务先提交，sessionA的事务后提交，因此binlog的记录为：

insert into t values (1,1,5);
update t set d = 100 where d = 5;

如果你用这个binlog来恢复一个库或者做主从的复制，都会将记录变为(5,5,100)和(1,1,100)，这就造成数据的不一致。这个问题是十分大的。

另外，我们上面只说了插入会对数据不一致带来的影响，实际上更新也会有这个情况：

上述语句session A在T2时刻的更新由于走索引c，因此只会对(5,5,5)这一行加x锁，将(5,5,5)改为(5,5,100)；而session B更新的是(10,10,10)这条记录，不会被session B阻塞，因此session B将(10,10,10)这条记录改为(10,5,10)。总的结果是(5,5,100)和(10,5,10)

但binlog的记录中由于session B先提交而session A后提交，因此session B的事务会先记录，因此binlog为：

update t set c = 5 where c = 10;
update t set d = 100 where c = 5;

很明显，这两条语句执行下来的结果是(5,5,100)和(10,5,100)，这样数据又出现了不一致。

4.3 gap lock

无论是幻读还是数据不一致，很明显都是加行锁已经不够用的结果。因此为解决这一问题，InnoDB在RR级别下引入了gap lock（间隙锁）。还记得我们本章刚开始说的 ISO/ANSI SQL92定义的事务隔离的标准吗？在RR级别下是会出现幻读的，但InnoDB存储引擎做的比较历害，在RR级别下通过引入gap lock解决了这一问题，所以MySQL InnoDB存储引擎在RR级别下是不会出现幻读的。

那么什么是间隙锁呢？间隙锁顾名思义，就是对两个值间的空隙加锁。间隙锁不同于我们常规理解的锁，常规的无论是互斥锁还是共享锁都是将要锁的对象视为一种资源，而间隙锁更多的是一种禁止行为。禁止的是不允许向这个间隙插入数据。因此间隙锁之间不会冲突的。比如你创建了一个间隙锁(5,10)，那么就禁止了向(5,10)间插入数据；同样我也可以创建间隙锁(5,10)，我们俩都是保护，有着共同目标，因此对同一间隙的加锁不会冲突。

我们还是以一个例子来说明：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` varchar(2) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,'a',0),(5,'e',5),
(10,'j',10),(15,'m',15),(20,'t',20),(25,'y',25);

4.3.1 非唯一索引

我们现在以非唯一索引c的加锁来说明情况，如果你熟悉InnoDB B+树的结构，那么你大概率知道我们当前索引c的B+树叶子节点大概是这样的：

由于是非聚簇索引，所以其每个节点既包含索引列信息也包含主键信息。

这样6个节点其实我们就生成了7个间隙：

这些间隙分别是-∞至(0,a)、(0,a)至(5,e)、(5,e)至(10,j)、(10,j)至(15,m)、(15,m)至(20,t)、(20,t)至+(25,y)、(25,y)至+supremum

假设我现在要执行一条语句：

select * from t where c = 'e' for update;

此时会对(5,e,5)这一行加行锁外，还会对间隙(0,a)至(5,e)与(5,e)至(10,j)这两个间隙加间隙锁。

因此对于如下例子：

session B想在T3时刻插入数据(6,'e',6)，但(6,'e',6)这条数据落在了(5,e)至(10,j)这个间隙之间，因此实际不会插入成功，session B会被阻塞。也因此T3时刻session A读取的数据不会包含session B插入的数据，也就避免了幻读出现的情况。

再举个例子：

T1时刻session A想查询数据c = f的记录，但表内并没有这个记录，因此不会上任何行锁，但c=f这个范围是落在(5,e)至(10,j)这个区间的，因此T1时刻session A虽然没有对任何行记录加锁，但是会对间隙(5,e)至(10,j)加间隙锁，阻止向这个间隙插入任何数据。session B想在T2时刻插入数据(7,'f',7)，很明显(7,'f')落在间隙(5,e)至(10,j)内，session B会在T2时刻阻塞，因此T3时刻session A的查询依然不会读到session B的插入结果，幻读依然不会发生。

通过上面的例子我们看到，间隙锁都是开区间。间隙锁与行锁合起来成为next-key lock，每个next-key lock都是前开后闭区间的。

4.3.2 唯一索引与不走索引

说完了非唯一索引下的间隙锁的加锁情况，我们再来说下不走索引与唯一索引的间隙锁加锁情况：

对于不走索引，会加所有行的行锁，同时会加全表间隙的gap lock。

而对于唯一索引：

• 精确等值检索，Next-Key Locks就退化为行锁，不会加gap锁
• 范围检索，会锁住where条件中相应的范围，范围中的记录以及间隙，换言之就是加上行锁和gap 锁。

可以看出，非唯一索引与唯一索引对于间隙加锁的唯一不同是非唯一索引即使是精确等值查询依然需要gap lock。因为非唯一索引同一值可以有多条记录，如：(0,'e',0)、(1,'e',1)、(2,'e',2)

执行select * from t where c = 'e' for update;会查到多条记录，同时你不保证现在某个session正在插入(3,'e',3)。因此非唯一索引即使是等值搜索仍需要gap lock。

4.4 一些补充

1. 不走索引的更新语句成本非常高

   通过上面的语句我们可以看到，如果不走索引更新的时候，不仅会锁住全表还会锁住全表的gap lock，而且这些锁是在事务结束后才释放，成本异常的高，因此千万要注意对于数据量较大的情况更新一定要走索引。

2. 间隙锁的死锁

   gap lock的引入带来了一个比较大的问题：死锁。死锁的产生也很简单：由于间隙锁谁都可以加，如果你和我都加了同一间隙的锁，那当我想改这个间隙的时候会被你锁住，你想改的时候也会被我锁住，因此就死锁了。

   比如对于如下例子：

   时间	session A	session B
   T1	begin;<br/>select * from t where id = 9 for update;
   T2		begin;<br/>select * from t where id = 9 for update;
   T3		insert into t values (9,'k',9) ;
   T4	insert into t values (9,'k',9) ;(ERROR 1213(40001):Deadlock found)

   这是由于session A在T1时刻对间隙(5,e)至(10,j)加间隙锁，而session B在T2时刻也对同一间隙加了间隙锁，同时T3时刻session B想插入数据(9,'k',9)的时候会被session A的间隙锁阻塞，而session A想在T4时刻插入数据(9,'k',9)会被session B的间隙锁阻塞，这样也就产生了死锁。

3. 只有在binlog为statement的时候才会导致数据不一致

   binlog有很多种格式，其中最早的一种格式是statement格式，statement这种格式非常简单，就是将执行的原生sql语句记录下来。我们之前说的数据不一致问题实际上只有在binlog是statement格式下才会出现。因此在RC隔离级别下MySQL禁止将binlog设为statement（因为RC隔离级别下没有gap lock）。同样，这也是为什么MySQL将RR设为默认隔离级别的根本原因。

   但是RR的隔离级别由于gap lock的引入会使得数据库性能降低，因此很多公司会选择隔离级别为RC，binlog为row格式这一组合。row格式不同于statement，row格式记录的是实际受影响的数据是真实删除行的主键id。比如对于sql语句 delete from t where age>10 and modified_time<='2020-03-04' limit 1，row的记录可能就是delete from t where id=3 and age=12 and modified_time='2020-03-05'。这样就不会存在数据不一致的问题。

4.5 参考文档

最后这一章其实我整理了很久，大概前后有一个多星期，因为对于幻读和gap lock我理解的太困难了。这期间除了《MySQL 45讲》还参考了很多好的文章，这里放一下，如果侵权还请联系我：

深入了解mysql--gap locks,Next-Key Locks_一撸向北的博客-CSDN博客

我的阿里二面，为什么MySQL选择REPEATABLE READ作为默认隔离级别？-HollisChuang's Blog