MySQL深入——17（主备延迟）

备库为什么延迟好几个小时

之前说的延迟为分钟级的，备库稳定之后都能追上来，但若备库的执行日志速度持续低于主库的生成日志速度，延迟就有可能变为小时级的。这就设计到了备库的并行复制能力。

InnoDB支持行锁，除了并发事务都更新同一行的情况下，对于业务的友好度还是很好的。

日志在备库上执行，备库上sql_thread更新数据的逻辑，若是用单线程的话就会导致备库应用日志不够快，造成主备延迟。

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

原来的的主库通过io_thread给到relay log，然后relay log再给到sql_thread再传给DATA。

现在的为：relay log——>coordinator分给多个work线程，形成多线程复制。

sql_thread变为coordinator，现只负责读取中转日志和分发事务，更新工作有work线程完成。

work线程的个数由参数salve_parallel_workers决定，在32核下，设置为8-16个最好，防止耗完CPU资源。

那么事务能否通过轮询的方式分给工作线程呢？

答案是不可以的，因为在CPU的调度策略下，假如事务按照先后分给work_1和work_2。CPU可能会让work_2先执行，跳过work_1，要是碰巧执行同一行，就会造成主备不统一。

那要是我将一个事务中的不同更新语句分出呢？

这样也是不行的，你这样想，我现在刚将work_1执行完，还没执行work_2就直接查询了，这样查到了就是更新到一半的数据，破坏了事务逻辑的隔离性。通过这两个问题我们可以得出两个结论

1 .更新同一条语句的两个事务，必须按照顺序分到一个work当中

2 .同一个事务不能被拆开，必须被放到一个work当中。  

我们接下来看看历代版本的复制策略

MySQL5.5版本不支持并行复制，该如何实现？

第一个策略：按照表分发策略

两个事务若是更新不同的表，就可以并行，因为数据按表分发故可保证两个work不会更新同一张表。还存在一种跨表的情况，就将两种表放在一起考虑。

每一个work线程对应一个hash表，其中保存work线程执行队列事务所设计的表。key代表库名.表名。 value为数字，代表有多少个事务在修改这个表。

当事务被分配到work线程的时候，涉及的表被加入到对应的hash表当中，执行完成后再从hash表当中删除

流程分析：

1.事务T进入线程分配，若与work_1冲突就去work_2进行判断，如果也冲突coordinator就进入等待。

2.哪个线程先完，就分配给冲突的哪一方。

3.无冲突产生就分配给空闲线程。

第二个策略：按照行分发策略

如果两个事务没有更新的行，可以在备库上并行执行，binlog为row形式

key为库+表+唯一键的值，这样还是不够的因为知道了主键id还存在一个唯一索引的情况，比如说我们创建了一个表，主键id，还有一个唯一索引a，将（1,1）（2,2）……（5,5）插入到该表当中。在session A当中将id=1的a改为6，在到了session B将id =2的a改为1。

若是先执行B的话，就会报唯一键冲突的错误，所以我们还要考虑唯一键。

针对这个表来讲，我们就得将表的key设置为库+表+索引a的名字+a的值，可以通过这种手段来阻止唯一键冲突的情况。级联更新在binlog当中无记录，冲突检测不准确。

按照这种策略来讲是不能有外键的。

MySQL 5.6 并行复制策略

按库并行 key为数据库名

并行效果取决于压力模型

主库上若有多个DB（database），并且每个DB的压力均衡，效果很好。

优势：构建hash值很快，因为只有库名，binlog格式不设限，因为statement也容易得到库名。

MariaDB的并行复制策略

和redo log组提交相同

特性：1.能够在同一个组中提交的事务，一定不会修改同一行

           2.主库上可以并行执行的事务，备库上也一样

实现上：1.在一组里面一起提交的事务，有一个相同的commit_id下一组就是commit_id+1

               2.commit_id直接写入binlog当中

               3.传入备库的时候，相同1commit_id给多个worker执行

               4.这一组完了之后再取下一组。

值得注意的是大事务拖后腿！

因为一组一组来的话，系统的吞吐量是不够的。

MySQL5.7策略

在MariaDB并行复制实现后，MySQL5.7也提供了相应的策略

1.配置为DATABASE时，MySQL5.6执行

2.配置为LOGICAL_CLOCK，就是类似于MariaDB的策略，并且对其进行了优化。

那么同时处于“执行状态”的所有事务，是否可以并行？

不可以，可能会出现锁冲突，处于锁等待的事务在备库上被分配到不同的worker当中，就会出现主库不一致的情况。

MariaDB核心为commit状态可并行，无需考虑锁冲突，但是其实到了redo log prepare状态就可以了。

思想：1.处于prepare状态事务可并行

           2.处于prepare状态和出于commit状态直接的事务可并行

可通过设置 binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数来积累prepare状态的事务量，来提高并行度。

MySQL 5.7.22

基于WRITESET的并行复制

新增一个参数 binlog_transaction_dependeney_tracking用于控制

1.COMMIT_ORDER为5.7的策略

2.WRITESET表示对事务涉及更新的每一行，计算出hash值，组成集合writeset，若两个事务的集合无交集，在备库上也得一样

hash值通过库+表+索引+值 计算的

若对于唯一索引和外键约束的场景，这个策略就无法执行就会退化为单线程。