MySQL深入23-备库延迟好几个小时
# MySQL深入23-备库延迟好几个小时
备库延迟的原因有：备库性能差、备库压力大、大事务。但是这些无论是偶发性的查询压力，还是备份，对备库延迟的影响一般是分钟级的，而且在备库恢复恢复正常以后能够追上来。
但是如果备库执行日志的速度持续低于主库生成日志的速度，那这个延迟就有可能成了小时级别。而且对于一个压力持续比较高的主库来说，备库很可能永远都追不上主库的节奏。
# 备库并行复制能力
主备的并行复制能力，需要关注两个点：
- 客户端写入主库。
- 备库上sql_thread执行中转日志。
对于主库而言，影响并发度的原因就是各种锁，除去所有并发事务更新同一行（热点行）这种场景外，InnoDB对业务并发度的支持还是很友好的，所以客户端写入主库这个过程，总体吞吐量高。
对于备库而言，如果sql_thread执行中转日志是用单线程的话，就会导致备库应用日志不够快，造成主备延迟。所以MySQL要从单线程复制变更到最新版本的多线程复制。
从本质来讲，我们需要将一个线程的sql_thread拆成多个线程：
上图中coordinator就是原来的sql_thread，不过现在它不再是直接更新数据了，而是只负责读取中转日志和分发事务。真正更新日志的，变成了worker线程。而work线程的个数，就是由参数slave_parallel_workers 决定的。
32核物理机的情况，这个数值设置为8-16最合适，需要把一部分CPU资源留给备库的读查询。
能否轮询分发事务
coordinator能否按照轮训的方式，把第一个事务分给worker_1，第二个事务分给worker_2呢？
其实是不行的。因为，事务被分发给 worker 以后，不同的 worker 就独立执行了。但是，由于 CPU 的调度策略，很可能第二个事务最终比第一个事务先执行。而如果这时候刚好这两个事务更新的是同一行，也就意味着，同一行上的两个事务，在主库和备库上的执行顺序相反，会导致主备不一致的问题。
能否将同一个事务的多个更新语句，分给不同的worker执行
也是不行的，一个事务更新了表 t1 和表 t2 中的各一行，如果这两条更新语句被分到不同 worker 的话，虽然最终的结果是主备一致的，但如果表 t1 执行完成的瞬间，备库上有一个查询，就会看到这个事务“更新了一半的结果”，破坏了事务逻辑的隔离性。
针对上面的两个问题，coordinator 在分发的时候，需要满足以下这两个基本要求:
- 不能造成更新覆盖。这就要求更新同一行的两个事务，必须被分发到同一个 worker 中。
- 同一个事务不能被拆开，必须放到同一个 worker 中。
# 按表分发策略
按表分发事务的基本思想： 如果两个事务更新不同的表，它们就可以并行。因为数据是存储在表里的，所以按表分发，可以保证两个worker不会更新同一行。对于跨表的事务，还是要把两张表放在一起考虑。
可以看到，每个worker线程对应一个hash表，用于保存当前正在这个worker的“执行队列”里的事务所涉及的表。hash表的key是“库名.表名”，value 是一个数字，表示队列中有多少个事务修改这个表。
在有事务分配给 worker 时，事务里面涉及的表会被加到对应的 hash 表中。worker 执行完成后，这个表会被从 hash 表中去掉。
假设在上图情况下，中转日志中读入一个新事务T，这个事务修改的行涉及到表t1和t3，下面来分析一下分配规则：
由于事务T中涉及修改表t1，而 worker_1 队列中有事务在修改表 t1，事务 T 和队列中的某个事务要修改同一个表的数据，这种情况我们说事务 T 和 worker_1 是冲突的。
按照这个逻辑，顺序判断事务 T 和每个 worker 队列的冲突关系，会发现事务 T 跟 worker_2 也冲突。
事务 T 跟多于一个 worker 冲突，coordinator 线程就进入等待。
每个 worker 继续执行，同时修改 hash_table。假设 hash_table_2 里面涉及到修改表 t3 的事务先执行完成，就会从 hash_table_2 中把 db1.t3 这一项去掉。
这样 coordinator 会发现跟事务 T 冲突的 worker 只有 worker_1 了，因此就把它分配给 worker_1。(因为更新同一个表的事务必须放在同一个worker中)
coordinator 继续读下一个中转日志，继续分配事务。
根据上面的分析，每个事务在分发的时候，跟所有worker的冲突关系如下：
- 如果跟所有 worker 都不冲突，coordinator 线程就会把这个事务分配给最空闲的 woker;
- 如果跟多于一个 worker 冲突，coordinator 线程就进入等待状态，直到和这个事务存在冲突关系的 worker 只剩下 1 个；
- 如果只跟一个 worker 冲突，coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。
按表分发的方案，在碰到热点表，比如所有的更新事务都会涉及到某一个表的时候，所有事务都会被分配到同一个 worker 中，就变成单线程复制了。
# 按行分发策略
为了解决热点表的并行复制问题，就需要一个按行复制的方案。该方案的核心思想：如果两个事务没有更新相同的行，它们在备库上可以并行执行。显然，这个模式要求 binlog 格式必须是 row。
这时候我们判断事务和worker冲突的规则就是：修改同一行而不是修改同一个表。
按行复制的数据结构和按表复制的数据结构差不多，为每个worker，分配一个 hash 表。只是要实现按行分发，这时候的 key，就必须是“库名 + 表名 + 唯一键的值”。
但是这个“唯一主键”是有可能会和“普通索引”冲突的：
CREATE TABLE `t1` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5);
如果两个session被分到不同的worker中，假设session B先执行，这时候 id=1 的行的 a 的值还是 1，就会报唯一键冲突。
因此，基于行的策略，事务 hash 表中还需要考虑唯一键，即 key 应该是“库名 + 表名 + 索引 a 的名字 +a 的值”。
比如在表t1中执行update t1 set a=1 where id=2语句，binlog里面就记录了整行的数据修改前后各个字段的值：
- key=hash_func(db1+t1+“PRIMARY”+2), value=2; 这里 value=2 是因为修改前后的行 id 值不变，出现了两次。
- key=hash_func(db1+t1+“a”+2), value=1，表示会影响到这个表 a=2 的行，修改前的数值。
- key=hash_func(db1+t1+“a”+1), value=1，表示会影响到这个表 a=1 的行，修改后的数值。
相比于按表并行分发策略，按行并行策略在决定线程分发的时候，需要消耗更多的计算资源：
- 耗费内存。比如一个语句要删除 100 万行数据，这时候 hash 表就要记录 100 万个项。
- 耗费 CPU。解析 binlog，然后计算 hash 值，对于大事务，这个成本还是很高的。
同时无论是按表分发还是按行分发都存在约束条件：
- 要能够从 binlog 里面解析出表名、主键值和唯一索引的值。也就是说，主库的 binlog 格式必须是 row；
- 表必须有主键；
- 不能有外键。表上如果有外键，级联更新的行不会记录在 binlog 中，这样冲突检测就不准确。
# 5.6版本的并行复制策略
该版本支持的粒度是按库并行，上述的按表分发和按库分发的key里面都会有库名。
相比于按表和按行分发，这个策略有两个优势：
- 构造 hash 值的时候很快，只需要库名；而且一个实例上 DB 数也不会很多，不会出现需要构造 100 万个项这种情况。
- 不要求 binlog 的格式。因为 statement 格式的 binlog 也可以很容易拿到库名。
# MariaDB的并行复制策略
MariaDB 的并行复制策略利用的就是这个特性：
能够在同一组里提交的事务，一定不会修改同一行；
主库上可以并行执行的事务，备库上也一定是可以并行执行的。
在实现上，MariaDB是这么做的：
- 在一组里面一起提交的事务，有一个相同的 commit_id，下一组就是 commit_id+1；
- commit_id 直接写到 binlog 里面；
- 传到备库应用的时候，相同 commit_id 的事务分发到多个 worker 执行；
- 这一组全部执行完成后，coordinator 再去取下一批。