26. 备库为什么会延迟好几个小时？

上一节学习了几种可能导致备库延迟的原因。这些场景里，不论是偶发性的查询压力，还是备份，对备库延迟的影响一般是分钟级的，而且在备库恢复正常以后都能够追上来。

但是，如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远追不上主库的节奏。

这就涉及到了几天的话题：备库并行复制能力。

再回顾一下第24节中的主备流程图：
image.png
图1. 主备流程图

谈到主备的并行复制能力，我们要关注的是图中黑色的两个箭头。一个箭头代表了客户端写入主库，另一箭头代表的是备库上sql_thread执行中转日志（relay log）。如果用箭头的粗细来代表并行度的话，那么真实情况就如图1所示，第一个箭头要明显粗于第二个箭头。

在主库上，影响并发度的原因就是各种锁了。由于InnoDB引擎支持行锁，除了所有并发事务都在更新同一行（热点行）这种极端场景外，它对业务并发度的支持还是很友好的。所以，在性能测试的时候会发现，并发压测线程32就比单线程时，总体吞吐量高。

在官方的5.6版本之前，MySQL支持是单线程复制，由此在主库并发高、TPS高时就会出现严重的主备延迟问题。

从单线程复制到最新版本的多线程复制，中间的烟花经历了好几个版本。

其实说到底，所以的多线程复制机制，都是要把图1中只有一个线程的sql_thread，拆成多个线程，也就是都符合下面的这个模型：
image.png
图2. 多线程模型

图2中，coordinator就是原来的sql_thread，不过现在它不再直接更新数据了，只负责读取中转和分发事务。真正更新日志的，变成了worker线程。而work线程的个数，就是由参数slave_parallel_workers决定的。根据经验，把这个值设置为8~16之间最好（32核物理机的情况），毕竟备库还有可能要提供读查询，不能把CPU吃光了。

27. 主库出问题了从库怎么办？

28. 读写分离有哪些坑？

29. 如何判断一个数据库是不是出问题了？

30. 答疑文章二：用动态的观点看加锁

这一节针对20和21节两篇文章做一个问题总结。

再次复习一下加锁规则，包含两个“原则”、两个“优化”和一个“bug”：

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

接下来，我们的讨论还是基于下面这个表t：

  2. CREATE TABLE `t` (
  3.   `id` int(11) NOT NULL,
  4.   `c` int(11) DEFAULT NULL,
  5.   `d` int(11) DEFAULT NULL,
  6.   PRIMARY KEY (`id`),
  7.   KEY `c` (`c`)
  8. ) ENGINE=InnoDB;
  10. insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

不等号条件里的等值查询

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

id是主键索引，按照加锁规则：

  1. 向左会扫描到第一个不满足条件的行id=5，于是加上next-key lock(0, 5]。
  2. 中间会扫描到行id=10，于是加上next-key lock(5, 10]。
  3. 向右会扫描到第一个不满足条件的行id=15，于是加上next-key lock(10, 15]，但是id=15不满足条件，退化为间隙锁(10, 15)。

于是，这个语句的加锁范围是主键索引上的(0, 5]、(5,10]和(10, 15)。

但是，我们的查询语句中where条件是大于号和小于号，这里的“等值查询”又是从哪里来呢？
要知道，加锁动作是发生在语句执行过程中的，所以在分析加锁行为的时候，要从索引上的数据结构开始。这里，我再把这个过程拆解一下。
image.png

图1. 索引id示意图

  1. 首先这个查询语句的语义是order by id desc，要拿到满足条件的所有行，优化器必须先找到“第一个id<12的值”。
  2. 这个过程是通过索引树的搜索过程得到的，在引擎内部，其实是要找到id=12的这个值，只是最终没找到，但找到了(10, 15) 这个间隙。
  3. 然后向左遍历，在遍历过程中，就不是等值查询了，会扫描到id=5这一行，所以会加一个next-key lock(0, 5]。

也就是说，在执行过程中，通过树搜索的方式定位记录的时候，用的是“等值查询”的方法。

等值查询的过程

  1. begin;
  2. select id from t where c in(5,20,10) lock in share mode; /* 读锁 */

这条查询语句里用的是in，我们先来看这条语句的explain结果。
image.png
图2. in语句的explain结果

可以看到，这条语句使用了索引c并且rows=3，说明这三个值都是通过B+树搜索定位的。
在查找c=5的时候，先锁住了(0, 5]。但是由于c不是唯一索引，为了确认海域没有别的记录c=5，就要向右遍历，找到c=10才确认没有了，这个过程满足优化2，所以加了间隙锁(5, 10)。
同样地，执行c=10这个逻辑的时候，加锁的范围是(5, 10]和(10, 15)；执行c=20这个逻辑的时候，加锁的范围是(15, 20]和(20, 25)。
通过这个分析，我们可以知道，这条语句在索引c上加的三个记录锁的顺序是：先加c=5的记录锁，再加c=10的记录锁，最后加c=20的记录锁。
综合起来，这个加锁范围就是(5, 25)中去掉c=15的行锁，结果如此，但是加锁过程需要动态分析。

理解了这个加锁过程之后，我们就可以来分析下面例子中的死锁问题了。
如果同时有另外一个语句，是这么写的：

  1. select id from t where c in(5,20,10) order by c desc for update; /* 写锁 */

此时的加锁范围，又是什么呢？
我们现在都知道间隙锁是不互锁的，但是这两条语句都会在索引c上的c=5、10、20这三行记录上加记录锁。
这里需要注意一下，由于语句里面是order by c desc，这三个记录锁的加锁顺序，是先锁c=20，然后c=10，最后是c=5。
也就是说，这两天语句要加锁相同的资源，但是加锁顺序相反。当这两条语句并发执行的时候，就可能出现死锁。
关于死锁的信息，MySQL只保留了最后一个死锁的现场，但这个现场还是不完备的。

如何看死锁
下图是出现死锁后，执行show engine innodb status命令得到的部分输。这个命令会输出很多信息，有一节LATESTDETECTED DEADLOCK，就是记录的最后一次死锁信息。
image.png
图3. 死锁现场

我们先看看这图中的几个关键信息。

  1. 这个结果分为三部分：
    1. （1）TRANSACTION，是第一个事务的信息；
    2. （2）TRANSACTION，是第二个事务的信息；
    3. WE ROLL BACK TRANSACTION(1)，是最终的处理结果，表示回滚了第一个事务。
  2. 第一个事务的信息中：
    1. WAITINGFOR THIS LOCKTOBEGRANTED，表示的是这个事务在等待的锁信息；
    2. index c of table test.t，说明在等的是表t的索引c上面的锁；
    3. lock mode S waiting，表示这个语句要自己加一个读锁，当前的状态是等待中；
    4. Record lock，说明这是一个记录锁；
    5. n_fields，表示这个记录是两列，也就是字段c和主键字段id；
    6. 0: len 4; hex0000000a; asc ;;是第一个字段，也就是c。值是十六进制a，也就是1；
    7. 1: len 4; hex0000000a; asc ;;是第二个字段，也就是注解id。值也是10；
    8. 这两行里面的asc表示的是，接下来要打印出值里面的“可打印字符”，但10不是可打印字符，因此就显示空格。
    9. 第一个事务信息就只显示出了等锁的状态，在等待(c=10, id=10)这一行的锁。
    10. 既然出现死锁了，就表示这个事务也占有别的锁，但是没有显示出来。别着急，我们从第二个事务的信息中推导出来。
  3. 第二个事务显示的信息要多一些：
    1. “HOLDS THE LOCK(S)”，显示这个事务持有哪些锁；
    2. index c of table test.t，表示锁是在表t的索引c上；
    3. hex0000000a和hex00000014，表示这个事务持有c=10和c=20这两个记录锁；
    4. WAITINGFOR THIS LOCKTOBEGRANTED，表示在等(c=5, id=5)这个记录锁。

从上面这些信息中，我们就知道：

  1. “lock in share mode”的这条语句，持有c=5的记录锁，在等c=10的锁；
  2. “for update”这个语句，持有c=20和c=10的记录锁，在等c=5的记录锁。

因此导致了死锁。这里，我们可以得到两个结论：

  1. 由于锁是一个个加的，要避免死锁，对同一组资源，要按照尽量相同的顺序访问；
  2. 在发生死锁的时刻，for update这条语句占有的资源更多，回滚成本更大，所以InnoDB选择了回滚成本更小的lock in share mode语句来回滚。

怎么看锁等待
下面看一个锁等待的例子。
场景的复现步骤如下图所示：
image.png
图4. delete导致间隙变化

可以看到，由于session A并没有锁住c=10这个记录，所以session B删除id=10这一行是可以的。但是之后，session B再想insert id=10这一行就不想了。
现在看一下此时show engine innodb status的结果。锁信息是在这个命令输出结果的TRANSACTIONS这一节。
image.png
图5. 锁等待信息

我们来看几个关键信息。

  1. index PRIMARY of table test.t，表示这个语句被锁住是因为表t主键上的某个锁。
  2. lock_mode X locks gap before rec insert intention waiting这里有几个信息：
    1. insert intention表示当前线程准备插入一个记录，这是一个插入意向锁。为了便于理解，你可以认为它就是插入动作本身。
    2. gap before rec表示这是一个间隙锁，而不是记录锁。
  3. 那么这个gap是在哪个记录之前的呢？接下来的0~4这5行内容就是这个记录的信息。
  4. n_fields也表示了，这一个记录有5列：
    1. 0: len 4; hex0000000f; asc ;;第一列是主键id字段，十六进制f就是id=15。所以，这时我们就知道了，这个间隙就是id=15之前的，因为id=10已经不存在了，它表示的就是(5, 15)。
    2. 1: len 6; hex000000000513; asc ;;第二列是长度为6字节的事务id，表示最后修改一行的是trx id为1299的事务；
    3. 2: len 7; hexb0000001250134; asc %4;;第三列长度为7字节的回滚段系信息。可以看到，这里的acs后面有显示内容(%和4)，这时因为刚好这个字节是可打印字符。
    4. 后面两列是c和d的值，都是15。

因此，我们就知道了，由于delete操作把id=10这一行删掉了，原来的两个间隙(5, 10)、(10, 15)变成了一个(5, 15)。

说到这里，可以联合起来再思考一下这两个现象之间的关联：

  1. session A执行完select语句后，什么都没做，但它加锁的范围突然变大了；
  2. 第21节的思考题，当我们执行select *fromt where c>=15 and c<=20 order by c desc lock in share mode;向左扫描到c=10的时候，要把(5, 10]锁起来。

也就是说， 所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。

update的例子
看了insert和delete的加锁例子，再来看一个update语句的案例：
image.png
图6. update的例子

session A的加锁范围是(5, 10]、(10,15]、(15, 20]、(20, 25]和(25, supermum]。

注意：根据c>5查到的第一个记录是c=10，因此不会加(0, 5]这个next-key lock。

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

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

按照我们上一节说的，索引c上(5, 10)间隙是由这个间隙右边的记录，也就是c=10定义的。所以通过这个操作，session A的加锁范围变成了下图所示的样子：
image.png
图7. session B修改后，session A的加锁范围

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

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

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

小结
这一节是对第20节和第21节内容的一个复盘和补充，注意在分析加锁范围时，一定要配合语句执行逻辑来进行。
每个想认真了解MySQL原理的同学，应该都要能够做到：通过explain的结果，就能够脑补出一个SQL语句的执行流程。达到这样的程度，才算是对索引组织表、索引、锁的概念有了比较清晰的认识。
另外，我们还学习了通过show engine innodb status输出结果中的事务信息和死锁信息。

小问题
这一节提到所谓“间隙”，其实根本就是由“这个间隙右边的那个记录”定义的。
那么，一个空表有空隙吗？这个间隙是由谁定义的？你怎么验证这个结论呢？
解析：
一个空表就只有一个间隙。比如，在空表上执行：

  1. begin;
  2. select * from t where id > 1 for update;

这个查询语句加锁的范围就是next-key lock (-∞, supremum]。
可以用下面的操作序列进行验证：
image.png
图8. 复现空表的next-key lock

结果如下：
image.png
图9. show engine innodb status部分结果