普通索引和唯一索引的选择

普通索引和唯一索引的查询过程对比

例如：我们查询一个 where k=5 条件的SQL语句，这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，然后可以认为数据页内部通过二分法来定位记录。

对于普通索引来说，查找到满足条件的第一个记录 (5,500) 后，需要查找下一个记录，直到碰到第一个不满足 k=5 条件的记录。
对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索。

InnoDB 的数据是按数据页为单位来读写的。也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。在 InnoDB 中，每个数据页的大小默认是 16KB。
因为引擎是按页读写的，所以说，当找到 k=5 的记录的时候，它所在的数据页就都在内存里了。那么，对于普通索引来说，要多做的那一次“查找和判断下一条记录”的操作，就只需要一次指针寻找和一次计算。
当然，如果 k=5 这个记录刚好是这个数据页的最后一个记录，那么要取下一个记录，必须读取下一个数据页，这个操作会稍微复杂一些。但是，我们之前计算过，对于整型字段，一个数据页可以放近千个 key，因此出现这种情况的概率会很低。所以，我们计算平均性能差异时，仍可以认为这个操作成本对于现在的 CPU 来说可以忽略不计。

结论：这个索引不同查询带来的性能差距会有多少呢？答案是，微乎其微。

普通索引和唯一索引的更新过程对比

change buffer

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

需要说明的是，虽然名字叫作 change buffer，实际上它是可以持久化的数据。也就是说，change buffer 在内存中有拷贝，也会被写入到磁盘上。将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。显然，如果能够将更新操作先记录在 change buffer，减少读磁盘，语句的执行速度会得到明显的提升。而且，数据读入内存是需要占用 buffer pool 的，所以这种方式还能够避免占用内存，提高内存利用率。

唯一索引和普通索引对于change buffer的应用特点对比

对于唯一索引来说，所有的更新操作都要先判断这个操作是否违反唯一性约束。比如我们要插入一条数据，而这必须要将数据页读入内存才能判断。如果都已经读入到内存了，那直接更新内存会更快，就没必要使用 change buffer 了。

对于普通索引来说，则是将更新记录在 change buffer，语句执行就结束了。

因此，唯一索引的更新就不能使用 change buffer，实际上也只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

change buffer 的使用场景

因为 merge 的时候是真正进行数据更新的时刻，而 change buffer 的主要目的就是将记录的变更动作缓存下来，所以在一个数据页做 merge 之前，change buffer 记录的变更越多（也就是这个页面上要更新的次数越多），收益就越大。

因此，对于写多读少的业务来说，页面在写完以后马上被访问到的概率比较小，此时 change buffer 的使用效果最好。这种业务模型常见的就是账单类、日志类的系统。
对于写少读多的业务，将更新先记录在 change buffer，但之后由于马上要访问这个数据页，会立即触发 merge 过程。这样随机访问 IO 的次数不会减少，反而增加了 change buffer 的维护代价。

普通索引和唯一索引的选择结论：这两类索引在查询能力上是没差别的，主要考虑的是对更新性能的影响。所以，我建议你尽量选择普通索引。如果所有的更新后面，都马上伴随着对这个记录的查询，那么你应该关闭 change buffer。而在其他情况下，change buffer 都能提升更新性能。

change buffer 和 redo log

redo log 主要节省的是随机写磁盘的 IO 消耗（转成顺序写），而 change buffer 主要节省的则是随机读磁盘的 IO 消耗。(这块还是有点不太理解，个人理解就是：WAL是数据页已经在内存中了，这时我们修改或插入此数据页数据，直接更新内存中的数据，然后写入redo log，系统在空闲的时候进行将操作更新到磁盘。而 change buffer 主要解决的是数据页本身就不在内存中，这时候我们更新或者添加数据，就直接写入到change buffer中，等到需要读此数据页的数据时，才将此数据读入内存中，然后合并 change buffer中的修改)

MySQL为什么有时候会选错索引

1：MySQL选错索引，啥意思？

我们认为使用K索引检索的速度会更快的，但是MySQL没有使用，决定使用什么索引是由Server层的优化器来决定的，她也是想选择最佳的方案来检索数据的，不过他也是人写的程序也是存在bug的。

2：MySQL为啥会选错索引？

优化器选择索引的目的，是找到一个最优的执行方案，并用最小的代价去执行语句。在数据库里面，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。优化器认为使用那个索引检索数据的速度比较快是一个需要各种因素综合评估的事情，比如：是否使用临时表、是否排序、扫描的行数多少、回表的次数等。

索引的创建是非常的耗时的，因为需要真正的建索引的过程，但是删除索引却不需要耗费太多时间，因为是标记删除，这个是以空间换时间的思路。优化器采用采样评估出现误差的原因也在于，索引的标记删除影响的。

3：mysql如何判断一个查询的扫描行数?

MySQL 在真正开始执行语句之前，并不能精确地知道满足这个条件的记录有多少条，而只能根据统计信息来估算记录数。这个统计信息就是索引的“区分度”。显然，一个索引上不同的值越多，这个索引的区分度就越好。而一个索引上不同的值的个数，我们称之为“基数”（cardinality）。也就是说，这个基数越大，索引的区分度越好。我们可以使用 show index 方法，看到一个索引的基数。

4：索引基数如何计算?

为什么要采样统计呢？因为把整张表取出来一行行统计，虽然可以得到精确的结果，但是代价太高了，所以只能选择“采样统计”。采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

5：可以重新统计索引信息的命令是什么?

analyze table t 命令，可以用来重新统计索引信息

6：索引选择异常的问题可以有哪几种处理方式?

强制指定使用某个索引，不常用不推荐用
调整SQL语句，使优化器选择的和我们想的一样，不具有通用性
新建更合适的索引或者删除不合适的索引，是一个思路
使用analyze table可以解决索引统计信息不准确导致的索引选错的问题

怎么给字符串字段加索引

MySQL 是支持前缀索引的，也就是说，你可以定义字符串的一部分作为索引。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

前缀索引使用示例：

# 正常默认索引-包含了每个记录的整个字符串
alter table SUser add index index1(email);

# 前缀索引-对于每个记录都是只取前 6 个字节
alter table SUser add index index2(email(6));

两种索引的数据结构和存储区别

前缀索引因为只取整个字段的前几个字节，所以占用空间会更少，这就是使用前缀索引的优势，这同时带来的损失是，可能会增加额外的记录扫描次数，具体来说主要是使用索引检索的过程如下进行分析：

如果我们用的是整个字符串的索引，首先我们直接根据索引定位要查询的字符串，然后回表，取出数据，然后检索对比下一条，发现不满足条件，就查询结束。
如果使用前缀索引，因为前缀相同的记录可能会有多条，这样的情况也很正常，它就需要每一条都扫描，然后回表去查询具体的数据来进行对比，一直找到前缀不能匹配的记录，所以这就是前缀索引会增加额外的扫描次数的原因。

但是：如果我们通过分析业务，使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本（通过前缀就能很快的区分出大部分的数据）。

前缀索引对覆盖索引的影响

如下一条查询语句：

1	select id,email from SUser where email='zhangssxyz@xxx.com';

如果我们直接使用普通索引（email），我们因为有覆盖索引的机理，所以一次就能返回所有的数据而不需要进行回表，但是如果我们使用了覆盖索引，每一次都需要回表判断email字段是不是对应的具体记录，这样就增加了回表的逻辑，而事实上，即使我们的前缀索引长度设置的足够长，能包含所有的字段长度，它还是需要回表进行判断，因为系统并不知道前缀索引的定义是否截断了完整的信息。

综上所述：使用前缀索引就用不上覆盖索引对查询性能的优化了

前缀索引使用的其他方式

对于类似于邮箱这样的字段来说，使用前缀索引的效果可能还不错。但是，遇到前缀的区分度不够好的情况时，我们要怎么办呢？要知道，索引选取的越长，占用的磁盘空间就越大，相同的数据页能放下的索引值就越少，搜索的效率也就会越低。

方式一：倒序存储：倒序存储就是针对一些前缀区别度不大，但是后缀区别比较大的字段类型，我们可以使用倒序存储来创建前缀索引。
方式二：使用hash字段：你可以在表上再创建一个整数字段，来保存你所要的业务字段的校验码，同时在这个字段上创建校验码。

使用倒序存储和使用 hash 字段这两种方法的异同点:

相同点是，都不支持范围查询

不同点：

从占用的额外空间来看，倒序存储方式在主键索引上，不会消耗额外的存储空间，而 hash 字段方法需要增加一个字段。当然，倒序存储方式使用 4 个字节的前缀长度应该是不够的，如果再长一点，这个消耗跟额外这个 hash 字段也差不多抵消了。
在 CPU 消耗方面，倒序方式每次写和读的时候，都需要额外调用一次 reverse 函数，而 hash 字段的方式需要额外调用一次 crc32() 函数。如果只从这两个函数的计算复杂度来看的话，reverse 函数额外消耗的 CPU 资源会更小些。
从查询效率上看，使用 hash 字段方式的查询性能相对更稳定一些。因为 crc32 算出来的值虽然有冲突的概率，但是概率非常小，可以认为每次查询的平均扫描行数接近 1。而倒序存储方式毕竟还是用的前缀索引的方式，也就是说还是会增加扫描行数。

总结

直接创建完整索引，这样可能比较占用空间；
创建前缀索引，节省空间，但会增加查询扫描次数，并且不能使用覆盖索引；
倒序存储，再创建前缀索引，用于绕过字符串本身前缀的区分度不够的问题；
创建 hash 字段索引，查询性能稳定，有额外的存储和计算消耗，跟第三种方式一样，都不支持范围扫描。

为什么MySQL会“抖一下”

抖一下：一条SQL语句正常执行的时候非常的快，但有时突然就会变得特别慢，而且很难复现，它不止是随机的，而且持续时间很短。

脏页

当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。

InnoDB 在处理更新语句的时候，只做了写日志这一个磁盘操作。这个日志叫作 redo log（重做日志），在更新内存写完 redo log 后，就返回给客户端，本次更新成功（WAL）。

内存里的数据总是要找时间写入磁盘的，这个过程术语就是 flush。在这个 flush 操作执行之前，内存中的数据跟磁盘上的数据是不一致的，因为还没有将redo log中的记录刷新到磁盘上。

所以我们就能够想象，我们的MySQL突然抖一下，就是因为在刷脏页，平时执行很快的更新操作，其实就是写了内存和日志。

数据库flush过程的触发点

InnoDB 的 redo log 写满了。这时候系统会停止所有更新操作，把 checkpoint 往前推进，redo log 留出空间可以继续写（环形链表结构）。checkpoint 可不是随便往前修改一下位置就可以的。它需要将两个点之间的日志，对应的所有脏页都 flush 到磁盘上。
系统内存不足。当需要新的内存页，而内存不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要先将脏页写到磁盘。为什么刷脏页一定会写盘，这是为了保证每个数据页只有两种状态：

一种是内存里存在，内存里就肯定是正确的结果，直接返回；
另一种是内存里没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。这样的效率最高。

MySQL 认为系统“空闲”的时候。当然MySQL及时忙的时候，也会见缝插针的找时间，只要有机会就会刷一点脏页。
MySQL 正常关闭的情况。这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。

触发点的具体分析

我们着重分析第一种和第二种情况，因为第三种和第四种都是正常的情况，我们不需要关注它的性能问题。

第一种是“redo log 写满了，要 flush 脏页”，这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候，整个系统就不能再接受更新了，所有的更新都必须堵住。如果你从监控上看，这时候更新数会跌为 0。

第二种是“内存不够用了，要先将脏页写到磁盘”，这种情况其实是常态。InnoDB 用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

第一种是，还没有使用的；
第二种是，使用了并且是干净页；
第三种是，使用了并且是脏页。

InnoDB 的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。而当要读入的数据页没有在内存的时候，就必须到缓冲池中申请一个数据页。这时候只能把最久不使用的数据页从内存中淘汰掉：如果要淘汰的是一个干净页，就直接释放出来复用；但如果是脏页呢，就必须将脏页先刷到磁盘，变成干净页后才能复用。

所以，刷脏页虽然是常态，但是出现以下这两种情况，都是会明显影响性能的：

一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长；
日志写满，更新全部堵住，写性能跌为 0，这种情况对敏感业务来说，是不能接受的。

InnoDB 刷脏页的控制策略

首先，你要正确地告诉 InnoDB 所在主机的 IO 能力，这样 InnoDB 才能知道需要全力刷脏页的时候，可以刷多快。这就要用到 innodb_io_capacity 这个参数了，它会告诉 InnoDB 你的磁盘能力。这个值我建议你设置成磁盘的 IOPS。因为没能正确地设置 innodb_io_capacity 参数，而导致的性能问题也比比皆是。
innodb_flush_neighbors：MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。innodb_flush_neighbors 参数就是用来控制这个行为的，值为 1 的时候会有上述的“连坐”机制，值为 0 时表示不找邻居，自己刷自己的。SSD的时代，建议设置为0，MySQL8 已经默认设置为0了。

总结

WAL这个机制后续需要的刷脏页操作和执行时机。利用 WAL 技术，数据库将随机写转换成了顺序写，大大提升了数据库的性能。但是，由此也带来了内存脏页的问题。脏页会被后台线程自动 flush，也会由于数据页淘汰而触发 flush，而刷脏页的过程由于会占用资源，可能会让你的更新和查询语句的响应时间长一些。

重点的重点

淘汰的时候，刷脏页过程不用动redo log文件的，直接将脏页数据刷到磁盘中就完成了。而在redo log重放的过程中，有个额外的保证，如果一个数据页已经是刷过的，会识别出来并跳过。这个保证的具体实现机制是：innodb的每个数据页头部有LSN，8字节，每次修改都会变大，对比这个LSN跟checkpoint 的LSN，比checkpoint小的一定是干净页。
这样解决的问题是：当内存不够用了，要将脏页写到磁盘，会有一个数据页淘汰机制（最久不使用），假设淘汰的是脏页，则此时脏页所对应的redo log的位置是随机的，当有多个不同的脏页需要刷，则对应的redo log可能在不同的位置，这样就需要把redo log的多个不同位置刷掉，这样对于redo log的处理不是就会很麻烦（合并间隙，移动位置）

现在我们对 WAL、change buffer、脏页、flush串起来一下，首先我们来了一条更新数据的语句，如果此数据所在页已经在内存中，那么MySQL直接更新内存数据，然后写redo log，如果此数据页没有再内存中，那么MySQL会写change buffer（前提不是添加数据，或者修改的列没有建立唯一索引，如果建立了，就需要将数据页加载到内存中进行判断唯一，这样就直接更新内存了），然后写redo log，这样我们的更新就完成了。下面我们说flush的过程，1. 在我们内存满了后需要淘汰脏页，这是就会将脏页的数据在内存中直接刷到磁盘上，WAL进行redo log重放的时候，会判断此数据是否被之前已有脏页刷过盘了，如果刷过了就跳过，没有刷过就重放更新磁盘数据。2. change buffer中的数据加载是看数据有没有被读取过，如果数据被读取，那么数据就会从磁盘读取到内存中，然后从change buffer中取出更改更新到内存中，那么此时内存中就是脏页，脏页在淘汰或者刷盘中就走了正常逻辑，如果没有被读取，change buffer 的后台也会有自己的合并行为，在一定的时机就会将change buffer中的数据刷新到磁盘中（这里重点注意：如果change buffer中的数据被丢了，那么它还是可以通过 redo log 进行重放进行恢复）。3. redo log 的重放，重放会将我们所有的更改持久化到磁盘数据中，这里重放的时候会判断当前数据有没有被脏页刷盘过，有没有被change buffer的后台自动持久化过，如果有，就跳过，如果没有，就通过redo log进行重放（LSN）。

redo log 的重放：redo log 并没有记录数据页的完整数据，所以它并没有能力自己去更新磁盘数据页，也就不存在“数据最终落盘，是由 redo log 更新过去”的情况。

如果是正常运行的实例的话，数据页被修改以后，跟磁盘的数据页不一致，称为脏页。最终数据落盘，就是把内存中的数据页写盘。这个过程，甚至与 redo log 毫无关系。
在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能在崩溃恢复的时候丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，就回到了第一种情况的状态。

数据库表的空间回收

一个 InnoDB 表包含两部分，即：表结构定义和数据。在 MySQL 8.0 版本以前，表结构是存在以.frm 为后缀的文件里。而 MySQL 8.0 版本，则已经允许把表结构定义放在系统数据表中了。因为表结构定义占用的空间很小。

参数 innodb_file_per_table

表数据既可以存在共享表空间里，也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的：

这个参数设置为 OFF 表示的是，表的数据放在系统共享表空间，也就是跟数据字典放在一起；
这个参数设置为 ON 表示的是，每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。从 MySQL 5.6.6 版本开始，它的默认值就是 ON 了。

建议你不论使用 MySQL 的哪个版本，都将这个值设置为 ON。因为，一个表单独存储为一个文件更容易管理，而且在你不需要这个表的时候，通过 drop table 命令，系统就会直接删除这个文件。而如果是放在共享表空间中，即使表删掉了，空间也是不会回收的。

数据删除流程

InnoDB 里的数据都是用 B+ 树的结构组织的，假设我们有一个数据页PageA，数据页中存的数据ID有（3、5、6），现在我们把 ID为5 的这条记录删除，InnoDB 引擎只会把 5 这个记录标记为删除，如果之后要再插入一个 ID 在 3 和 6 之间的记录时，可能会复用这个位置。但是，磁盘文件的大小并不会缩小。如果我们删掉了一个数据页上的所有记录，那么整个数据页就可以被复用了，但是，数据页的复用跟记录的复用是不同的。记录的复用，只限于符合范围条件的数据，比如上面这个例子，我们删除 5 记录之后，如果插入一个 ID 为8 的记录，就不能复用这个位置了。而当整个页从 B+ 树里面摘掉以后，可以复用到任何位置。如果将数据页 page A 上的所有记录删除以后，page A 会被标记为可复用。这时候如果要插入一条 ID=50 的记录需要使用新页的时候，page A 是可以被复用的。

如果相邻的两个数据页利用率都很小，系统就会把这两个页上的数据合到其中一个页上，另外一个数据页就被标记为可复用。

进一步地，如果我们用 delete 命令把整个表的数据删除，所有的数据页都会被标记为可复用。但是磁盘上，文件不会变小。

空洞

上述我们了解到，delete 命令其实只是把记录的位置，或者数据页标记为了“可复用”，但磁盘文件的大小是不会变的。也就是说，通过 delete 命令是不能回收表空间的。这些可以复用，而没有被使用的空间，看起来就像是“空洞”。

插入数据也可以产生空洞：如果数据是按照索引递增顺序插入的，那么索引是紧凑的。但如果数据是随机插入的，就可能造成索引的数据页分裂。如果我们某个Page页满了，向此页中插入数据就不得不再申请一个新的数据页，来保存数据，页分裂完成后，旧页的末尾就留下了空洞。

更新索引上的值也可以产生空洞：更新索引上的值，可以理解为删除一个旧的值，再插入一个新值。不难理解，这也是会造成空洞的（比如将一个索引的值 10 改为 1000）。

重建表

经过大量增删改的表，都是可能是存在空洞的。所以，如果能够把这些空洞去掉，就能达到收缩表空间的目的。而重建表，就可以达到这样的目的。

可以使用 alter table A engine=InnoDB 命令来重建表。在 MySQL 5.5 版本之前，这个命令的执行流程大致是：

1	新建一个与表 A 结构相同的表 B，然后按照主键 ID 递增的顺序，把数据一行一行地从表 A 里读出来再插入到表 B 中。

由于表 B 是新建的表，所以表 A 主键索引上的空洞，在表 B 中就都不存在了。用表 B 替换 A，从效果上看，就起到了收缩表 A 空间的作用。

MySQL 会自动完成转存数据、交换表名、删除旧表的操作。

Online DDL

上述流程中，显然，花时间最多的步骤是往临时表插入数据的过程，如果在这个过程中，有新的数据要写入到表 A 的话，就会造成数据丢失。因此，在整个 DDL 过程中，表 A 中不能有更新。也就是说，这个 DDL 不是 Online 的。在 MySQL 5.6 版本开始引入的 Online DDL，对这个操作流程做了优化。下面描述一下引入了 Online DDL 之后，重建表的流程：

建立一个临时文件，扫描表 A 主键的所有数据页；
用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
用临时文件替换表 A 的数据文件。

此过程与上述重建表过程不同的是，由于日志文件记录和重放操作这个功能的存在，这个方案在重建表的过程中，允许对表 A 做增删改操作。这也就是 Online DDL 名字的来源。alter 语句在启动的时候需要获取 MDL 写锁，但是这个写锁在真正拷贝数据之前就退化成读锁了。对于一个大表来说，Online DDL 最耗时的过程就是拷贝数据到临时表的过程，这个步骤的执行期间可以接受增删改操作。所以，相对于整个 DDL 过程来说，锁的时间非常短。对业务来说，就可以认为是 Online 的。

Online 和 inplace

inplace：上述我们在讲MySQL 5.5 以前的版本重建表的流程，我们把表 A 中的数据导出来的存放位置叫作 tmp_table。这是一个临时表，是在 server 层创建的，而5.5之后，根据表 A 重建出来的数据是放在“tmp_file”里的，这个临时文件是 InnoDB 在内部创建出来的。整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个“原地”操作，这就是“inplace”名称的来源。

inplace重建表的语句：

1	alter table t engine=innodb,ALGORITHM=inplace; (ALGORITHM=inplace 可以不加，因为它是默认的)

copy重建表的语句：

1	alter table t engine=innodb,ALGORITHM=copy;

当你使用 ALGORITHM=copy 的时候，表示的是强制拷贝表，对应的就是类似上述5.5版本重建表的逻辑流程。

inplace(rebuild)的整体执行过程

准备阶段：

对表加元数据共享升级锁，并升级为排他锁。（此时DML不能并行）
在原表所在的路径下创建.frm和.ibd临时中转文件（no-rebuild除创建二级索引外只创建.frm文件，其中添加二级索引操作最为特殊，该操作属于no-rebuild不会生成.ibd，但实际上对.ibd文件却做了修改，该操作会在参数tmpdir指定路径下生成临时文件，用于存储索引排序结果，然后再合并到.ibd文件中）
申请row log空间，用于存放DDL执行阶段产生的DML操作。（no-rebuild不需要）

执行阶段：

释放排他锁，保留元数据共享升级锁（此时DML可以并行）。
扫描原表主键以及二级索引的所有数据页，生成 B+ 树，存储到临时文件中；
将所有对原表的DML操作记录在日志文件row log中

注：如果只修改元数据部分（no-rebuild）该阶段只是修改.frm文件，不需要其他操作，也不需要申请row log

提交阶段：

升级元数据共享升级锁，产生排他锁锁表（此时DML不能并行）。
重做row log中的内容。（no-rebuild不需要）
重命名原表文件，将临时文件改名为原表文件名，删除原表文件
提交事务，变更完成。

说明：在DDL期间产生的数据，会按照正常操作一样，写入原表，记redolog、undolog、binlog，并同步到从库去执行，只是额外会记录在row log中，并且写入row log的操作本身也会记录redolog，而在提交阶段才进行row log重做，此阶段会锁表，此时主库（新表空间+row log）和从库（表空间）数据是一致的，在主库DDL操作执行完成并提交，这个DDL才会写入binlog传到从库执行，在从库执行该DDL时，这个DDL对于从库本地来讲仍然是online的，也就是在从库本地直接写入数据是不会阻塞的，也会像主库一样产生row log。但是对于主库同步过来DML，此时会被阻塞，是offline的，DDL是排他锁的在复制线程中也是一样，所以不只会阻塞该表，而是后续所有从主库同步过来的操作（主要是在复制线程并行时会排他，同一时间只有他自己在执行）。所以大表的DDL操作，会造成同步延迟。

Online 和 inplace的区别

DDL 过程如果是 Online 的，就一定是 inplace 的；
反过来未必，也就是说 inplace 的 DDL，有可能不是 Online 的。截止到 MySQL 8.0，添加全文索引（FULLTEXT index）和空间索引 (SPATIAL index) 就属于这种情况。

optimize table、analyze table 和 alter table 这三种方式重建表的区别

从 MySQL 5.6 版本开始，alter table t engine = InnoDB（也就是 recreate）默认的就是上面 Online重建表的流程了；
analyze table t 其实不是重建表，只是对表的索引信息做重新统计，没有修改数据，这个过程中加了 MDL 读锁；
optimize table t 等于 recreate+analyze。

count函数应用剖析

count(*) 的实现方式

在不同的 MySQL 引擎中，count(*) 有不同的实现方式：

MyISAM 引擎把一个表的总行数存在了磁盘上，因此执行 count(*) 的时候会直接返回这个数，效率很高；
而 InnoDB 引擎就麻烦了，它执行 count(*) 的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数。

这里需要注意的是，我们在这篇文章里讨论的是没有过滤条件的 count(*)，如果加了 where 条件的话，MyISAM 表也是不能返回得这么快的。

为什么 InnoDB 不跟 MyISAM 一样，也把数字存起来呢？这是因为即使是在同一个时刻的多个查询，由于多版本并发控制（MVCC）的原因，InnoDB 表“应该返回多少行”也是不确定的。

InnoDB 是索引组织表，主键索引树的叶子节点是数据，而普通索引树的叶子节点是主键值。所以，普通索引树比主键索引树小很多。对于 count(*) 这样的操作，遍历哪个索引树得到的结果逻辑上都是一样的。因此，MySQL 优化器会找到最小的那棵树来遍历。在保证逻辑正确的前提下，尽量减少扫描的数据量，是数据库系统设计的通用法则之一。

小结

MyISAM 表虽然 count(*) 很快，但是不支持事务；
show table status 命令虽然返回很快，但是不准确；
InnoDB 表直接 count(*) 会遍历全表，虽然结果准确，但会导致性能问题。

不同的 count 用法

count() 的语义：count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加。最后返回累计值。所以，count(*)、count(主键 id) 和 count(1) 都表示返回满足条件的结果集的总行数；而 count(字段），则表示返回满足条件的数据行里面，参数“字段”不为 NULL 的总个数。

对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的（这里这个判断虽然是多余的，主键不可能为空，但是MySQL代码确实是这么做的），就按行累加。
对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，count(1) 执行得要比 count(主键 id) 快。因为从引擎返回 id 会涉及到解析数据行，以及拷贝字段值的操作。

对于 count(字段) 来说：

如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；
如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。

对于 count(*) 来说：并不会把全部字段取出来，而是专门做了优化，不取值。count(*) 肯定不是 null，按行累加。

结论：按照效率排序的话，count(字段) < count(主键 id) < count(1) ≈ count()，所以我建议你，尽量使用 count(\)。

order by 的工作原理

这里用一条SQL语句举例说明： select city,name,age from t where city=’杭州’ order by name limit 1000; （city字段建立了索引）

全字段排序

MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer。通常情况下，全字段排序上述语句执行流程如下：

初始化 sort_buffer，确定放入 name、city、age 这三个字段；
从索引 city 找到第一个满足 city=’杭州’ 条件的主键 id；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，存入 sort_buffer 中；
从索引 city 取下一个记录的主键 id；
重复步骤 3、4 直到 city 的值不满足查询条件为止；
对 sort_buffer 中的数据按照字段 name 做快速排序；
按照排序结果取前 1000 行返回给客户端。

排序这个动作，可能在内存中完成，也可能需要使用外部排序，这取决于排序所需的内存和参数 sort_buffer_size。sort_buffer_size，就是 MySQL 为排序开辟的内存（sort_buffer）的大小。如果要排序的数据量小于 sort_buffer_size，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序，外部排序一般使用归并排序算法。（一般是将数据分为多个小临时文件排好顺序，然后归并排序到一个大文件中）

rowid 排序

在上面这个算法过程里面，只对原表的数据读了一遍，剩下的操作都是在 sort_buffer 和临时文件中执行的。但这个算法有一个问题，就是如果查询要返回的字段很多的话，那么 sort_buffer 里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

我们可以通过配置参数max_length_for_sort_data，控制用于排序的行数据的长度，如果单行的长度超过这个值，MySQL 就认为单行太大，要换一个算法。使用如下：

1	SET max_length_for_sort_data = 16;

city、name、age 这三个字段的定义总长度是 36，我把 max_length_for_sort_data 设置为 16，新的算法放入 sort_buffer 的字段，只有要排序的列（即 name 字段）和主键 id，这时，排序的结果就因为少了 city 和 age 字段的值，不能直接返回，具体执行流程如下：

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

对比全字段排序，rowid 排序多访问了一次表 t 的主键索引，就是步骤 7。

全字段排序 VS rowid 排序

如果 MySQL 实在是担心排序内存太小，会影响排序效率，才会采用 rowid 排序算法，这样排序过程中一次可以排序更多行，但是需要再回到原表去取数据。如果 MySQL 认为内存足够大，会优先选择全字段排序，把需要的字段都放到 sort_buffer 中，这样排序后就会直接从内存里面返回查询结果了，不用再回到原表去取数据。如果内存够，就要多利用内存，尽量减少磁盘访问。

不用排序算法的 order by 场景

如果我们对上述查询语句的 city 和 name 建立联合索引，这时，对于 city 字段的值相同的行来说，还是按照 name 字段的值递增排序的，此时的查询语句也就不再需要排序了。这样整个查询语句的执行流程就变成了：

从索引 (city,name) 找到第一个满足 city=’杭州’条件的主键 id；
到主键 id 索引取出整行，取 name、city、age 三个字段的值，作为结果集的一部分直接返回；
从索引 (city,name) 取下一个记录主键 id；
重复步骤 2、3，直到查到第 1000 条记录，或者是不满足 city=’杭州’条件时循环结束。

在我们这个例子里，只需要扫描 1000 次，不需要经历排序算法。

我们还可以建立(city,name,age)的联合索引，这样上述步骤中的的回表操作就可以完全省略，因为在索引中已经包含了我们需要的所有字段（覆盖索引的原理），这样性能会更快。

当然，这里并不是说要为了每个查询能用上覆盖索引，就要把语句中涉及的字段都建上联合索引，毕竟索引还是有维护代价的。这是一个需要权衡的决定。

explain 中 Extra 字段返回结果

Using filesort：表示的就是需要排序

Using temporary表示的是需要使用临时表

order by内部会使用归并排序，根据sort buffer size决定是否需要使用外部（磁盘）排序，根据max_length_for_sort_data决定使用全字段排序还是rowid排序，不同点是rowid排序，只使用排序字段和主键，会在原有的基础上，多进行回表查询，多了磁盘操作，为此可以使用复合查询，这样从索引中查询出来的数据，就是有序的，可以直接进行回表，返回result，也可以考虑是否使用覆盖索引，直接返回值，如果order by后面加上limit num，num是小值，在5.6以上会使用优先队列进行排序。

rowid详解

如果把一个 InnoDB 表的主键删掉，是不是就没有主键，就没办法回表了？其实不是的。

如果你创建的表没有主键，或者把一个表的主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。这也就是排序模式里面，rowid 名字的来历。实际上它表示的是：

每个引擎用来唯一标识数据行的信息。对于有主键的 InnoDB 表来说，这个 rowid 就是主键 ID；
对于没有主键的 InnoDB 表来说，这个 rowid 就是由系统生成的；
MEMORY 引擎不是索引组织表，你可以认为它就是一个数组。因此，这个 rowid 其实就是数组的下标。

本章节注意：内存临时表、磁盘临时表的概念

SQL语句执行性能分析

条件字段函数操作

对索引字段做函数操作，可能会破坏索引值的有序性，因此优化器就决定放弃走树搜索功能。

例如，对于一个查询语句，t_modified字段类型为datetime类型：

1	select count(*) from tradelog where month(t_modified)=7;

上述语句就不会走索引，因为对索引字段做了month()函数操作

例如，对于 select * from tradelog where id + 1 = 10000 这个 SQL 语句，这个加 1 操作并不会改变有序性，但是 MySQL 优化器还是不能用 id 索引快速定位到 9999 这一行。所以，需要你在写 SQL 语句的时候，手动改写成 where id = 10000 -1 才可以。

隐式类型转换

例如如下查询语句，tradeid 这个字段上建立了索引，字段类型是 varchar(32)，但是此语句却没有走索引：

1	mysql> select * from tradelog where tradeid=110717;

这里的原因就是，tradeid 的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换，所以对于优化器来说，上述语句相当于：

1	select * from tradelog where CAST(tradid AS signed int) = 110717;

也就是说，这条语句触发了我们上面说到的规则：对索引字段做函数操作，优化器会放弃走树搜索功能。

注意重点：当字符串和数字比较时会把字符串转化为数字

隐式字符编码转换

例如我们有如下查询语句，此两个表的tradeid 这个字段上都建立了索引

1	select d.* from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;

在这条语句中，tradelog 为驱动表，trade_detail为被驱动表，具体的查询逻辑如下：

我们首先通过tradelog驱动表的主键索引找到id = 2的记录；
然后找出tradeid字段的值；
然后根据 tradeid 值到 trade_detail 表中查找条件匹配的行。

这里我们通过explain结果集发现，trade_detail 的 tradeid 值匹配并没有走索引，这是因为：这两个表的字符集不同，一个是 utf8mb4，一个是 utf8，所以做表连接查询的时候用不上关联字段的索引。所以我们在做上述第三步的时候，具体查询逻辑类似如下SQL：

1
2
3

select * from trade_detail where tradeid=$L2.tradeid.value;
也就是：
select * from trade_detail where CONVERT(traideid USING utf8mb4)=$L2.tradeid.value;

$L2.tradeid.value 的字符集是 utf8mb,。字符集 utf8mb4 是 utf8 的超集，所以当这两个类型的字符串在做比较的时候，MySQL 内部的操作是，先把 utf8 字符串转成 utf8mb4 字符集，再做比较。

这个设定很好理解，utf8mb4 是 utf8 的超集。类似地，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换过程中由于截断导致数据错误，也都是“按数据长度增加的方向”进行转换的。

所以优化此查询语句，就可以修改为：

1	select d.* from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8) and l.id=2;

这里，我主动把 l.tradeid 转成 utf8，就避免了被驱动表上的字符编码转换，从 explain 结果可以看到，这次索引走对了。

本节总结

索引字段不能进行函数操作，但是索引字段的参数可以玩函数，一言以蔽之。

SQL语句执行慢分析

查询长时间不返回

等 MDL 锁

例如我们执行一条很简单的语句：select * from t where id=1; 结果长时间不返回，一般出现这种情况大概率是表被锁住了，分析时我们一般执行 show processlist 命令，查看当前语句处于什么状态。

使用 show processlist 命令查看语句出现 Waiting for table metadata lock 的状态，说明现在有一个线程正在表 t 上请求或者持有 MDL 写锁，把 select 语句堵住了。场景是:session A 通过 lock table 命令持有表 t 的 MDL 写锁，而 session B 的查询需要获取 MDL 读锁。所以，session B 进入等待状态。这类问题的处理方式，就是找到谁持有 MDL 写锁，然后把它 kill 掉。(通过查询 sys.schema_table_lock_waits 这张表，我们就可以直接找出造成阻塞的 process id，把这个连接用 kill 命令断开即可。)

等 flush

我们使用select * from information_schema.processlist查询结果，发现状态有 Waiting for table flush，这个状态表示的是，现在有一个线程正要对表 t 做 flush 操作。MySQL 里面对表做 flush 操作的用法，一般有以下两个：

flush tables t with read lock;
flush tables with read lock;

这两个 flush 语句，如果指定表 t 的话，代表的是只关闭表 t；如果没有指定具体的表名，则表示关闭 MySQL 里所有打开的表。但是正常这两个语句执行起来都很快，除非它们也被别的线程堵住了。所以，出现 Waiting for table flush 状态的可能情况是：有一个 flush tables 命令被别的语句堵住了，然后它又堵住了我们的 select 语句，这个别的语句可能是一个耗时很长的查询操作，或者耗时很长的更新操作。

等行锁

例如：一条更新语句占用了写锁，并且事务一直没有提交，另一条查询语句如下 select * from t where id=1 lock in share mode; 或是使用了for update都表示需要当前读，这样就需要给当前数据加写锁，这时因为上一条执行事务没有提交，锁一直没有释放，就导致本条查询语句一直被阻塞。

我们可以通过SQL语句查看具体是被什么被锁住了：select * from t sys.innodb_lock_waits where locked_table=’[schema.tablename]’。查找到 blocking_pid，然后 kill [pid] 即可完成释放。

查询慢

扫描行数多

例如有如下语句：select * from t where c=50000 limit 1; 这条语句慢是因为字段c上没有索引，所以只能走主键ID顺序扫描，然后取出行记录进行对比，因此需要扫描50000行。扫描行数越多，执行越慢

一致性读 undo log 回放

比如我们会遇到一种现象，执行SQL语句 select * from t where id=1；这条SQL只扫描一行，但是执行时间确很长，如果我们执行语句是 select * from t where id=1 lock in share mode，执行时扫描行数也是 1 行，执行时间确非常短，看上去非常奇怪，按理说 lock in share mode 还要加锁，时间应该更长才对啊，具体说明如下，如下SQL流程：

session A	session B
start transaction with consistent snapshot;
	update t set c = c + 1 where id = 1;// 执行100万次
select * from t where id = 1;
select * from t where id = 1 lock in share mode;

你看到了，session A 先用 start transaction with consistent snapshot 命令启动了一个事务，之后 session B 才开始执行 update 语句，session B 更新完 100 万次，生成了 100 万个回滚日志 (undo log)。带 lock in share mode 的 SQL 语句，是当前读，因此会直接读到 1000001 这个结果，所以速度很快；而 select * from t where id=1 这个语句，是一致性读，因此需要从 1000001 开始，依次执行 undo log，执行了 100 万次以后，才将 1 这个结果返回。所以这就是上述现象的原因。

幻读和间隙锁

幻读

什么是幻读？

在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
上面 session B 的修改结果，被 session A 之后的 select 语句用“当前读”看到，不能称为幻读。幻读仅专指“新插入的行”。

幻读带来的问题？

对行锁语义的破坏
破坏了数据一致性

为啥会出现幻读？

行锁只能锁定存在的行，针对新插入的操作没有限定

间隙锁

产生幻读的原因是，行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读问题，InnoDB 只好引入新的锁，也就是间隙锁 (Gap Lock)。顾名思义，间隙锁，锁的就是两个值之间的空隙。

注意：间隙锁是在可重复读隔离级别（RR）下才会生效的。

数据行是可以加上锁的实体，数据行之间的间隙，也是可以加上锁的实体。但是间隙锁跟我们之前碰到过的锁都不太一样。跟间隙锁存在冲突关系的，是“往这个间隙中插入一个记录”这个操作。间隙锁之间都不存在冲突关系。

间隙锁和行锁合称 next-key lock，每个 next-key lock 是前开后闭区间。也就是说，我们有一个表 t 初始化以后，如果用 select * from t for update 要把整个表所有记录锁起来，就形成了 next-key lock，分别是 (-∞, id1]、(id2, id3]、… 、(idn, +supremum]。supremum从哪儿来的呢？这是因为 +∞是开区间。实现上，InnoDB 给每个索引加了一个不存在的最大值 supremum，这样才符合我们前面说的“都是前开后闭区间”。

注意：间隙锁本身是前开后开的区间锁，next-key lock 才是前开后闭区间。

间隙锁的引入，可能会导致同样的语句锁住更大的范围，这其实是影响了并发度的。

非索引字段update或者delete

对于非索引字段进行update或select .. for update操作，代价极高。所有记录上锁，以及所有间隔的锁。对于索引字段进行上述操作，代价一般。只有索引字段本身和附近的间隔会被加锁。
（个人理解：锁是加在主键索引上的）

MySQL加锁规则

MySQL加锁规则里面，包含了两个“原则”、两个“优化”和一个“bug”。

原则 1：加锁的基本单位是 next-key lock。希望你还记得，next-key lock 是前开后闭区间。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

注意：

只有访问到的对象才会加锁，比如我们访问一个普通索引的字段，使用覆盖索引就可以查询出来数据，这时我们是不会给主键索引来加锁的。（lock in share mode 只锁覆盖索引，但是如果是 for update 就不一样了。执行 for update 时，系统会认为你接下来要更新数据，因此会顺便给主键索引上满足条件的行加上行锁。如果你要用 lock in share mode 来给行加读锁避免数据被更新的话，就必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。）
申请锁的顺序是先申请间隙锁，然后再申请行锁。（有的时候我们一条更新语句加锁的时候我们先加了间隙锁，然后加行锁的时候发现行被占用，因为间隙锁之间不是互斥的，这是行锁申请失败被阻塞住等待其他事务释放行锁，但间隙锁已经加成功，也会导致一些其他的操作被此间隙锁所阻塞住）。
在删除数据的时候尽量加 limit。这样不仅可以控制删除数据的条数，让操作更安全，还可以减小加锁的范围。
“有行”才会加行锁。如果查询条件没有命中行，那就加 next-key lock。
范围查询：无论是否是唯一索引，范围查询都需要访问到不满足条件的第一个值为止。
例如有一个表有数据如下：

1	(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

对应字段为(id, c, d)，c 建立了普通索引

示例一

我们有如下查询语句：

1 2	begin select * from t where t.c >= 15 and c < 20 order by c desc lock in share mode;

这里的加锁逻辑如下:

由于是 order by c desc，第一个要定位的是索引 c 上“最右边的”c=20 的行，所以会加上间隙锁 (20,25) 和 next-key lock (15,20]。
在索引 c 上向左遍历，要扫描到 c=10 才停下来，所以 next-key lock 会加到 (5,10]。注意这里是重点：为什么会加(5,10]，因为我们的desc向左扫描的时候，我们需要扫描到10才能停下来，而我们的 next-key lock 是前开后闭区间，为了保证10这行本身也被锁住，所以我们需要正向的加 (5,10] 的 next-key lock，所以这就是为什么这个锁的范围这么广的原因。而如果我们是asc，这样就不会存在这种问题，就是正常的加锁逻辑，不会多出来一个锁区间。
在扫描过程中，c=20、c=15、c=10 这三行都存在值，由于是 select *，所以会在主键 id 上加三个行锁。

先定位索引c上最右边c=20的行，所以第一个等值查询会扫描到c=25，然后通过优化2，next-key lock退化为间隙锁，则会加上间隙锁（20，25），紧接着再向左遍历，会加 next-key lock (15, 20], (10, 15], 因为要扫描到c=10才停下来，所以也会加next-key lock (5,10]

示例二

如上数据库表结构还有经典加锁示例如下语句：

sessionA:

1 2	begin; select * from t where c > 5 lock in share mode;

sessionB:

update t set c = 1 where c = 5;
(Query OK)
update t set c = 5 where c = 1;
(blocked)

上述语句同时执行，sessionB的第二条语句会被阻塞住。原因是：首先sessionA的加锁范围是:

索引 c 上的 (5,10]、(10,15]、(15,20]、(20,25]和 (25,supremum]。注意：根据 c>5 查到的第一个记录是 c=10，因此不会加 (0,5]这个 next-key lock。

之后 session B 的第一个 update 语句，要把 c=5 改成 c=1，你可以理解为两步：

插入 (c=1, id=5) 这个记录；
删除 (c=5, id=5) 这个记录。

索引 c 上 (5,10) 间隙是由这个间隙右边的记录，也就是 c=10 定义的。所以通过这个操作，session A 的加锁范围变成了 (1,10]、(10,15] …

接下来 session B 要执行 update t set c = 5 where c = 1 这个语句了，一样地可以拆成两步：

插入 (c=5, id=5) 这个记录；
删除 (c=1, id=5) 这个记录。

第一步试图在已经加了间隙锁的 (1,10) 中插入数据，所以就被堵住了。

示例三

如上数据库表结构还有经典加锁示例如下语句：

1 2	begin; select * from t where id>9 and id<12 order by id desc for update;

这个语句的加锁范围是主键索引上的 (0,5]、(5,10]和 (10, 15)。也就是说，id=15 这一行，并没有被加上行锁。为什么呢？

当试图去找 “第一个id < 12的值”的时候，用的还是从左往右的遍历（因为用到了优化2），也就是说，当去找第一个等值的时候（通过树搜索去定位记录的时候），即使order by desc，但用的还是向右遍历，当找到了第一个等值的时候（例子中的id=15），然后根据order by desc，再向左遍历。