Mysql优化学习之MTS机制、GTID、二/三阶段提交、组提交汇总分析

MTS机制（针对的是relaylog与binlog的执行效率）

一、Mysql早在5.6.x版本之前在Server层存在两种线程I/O线程和SQL线程，其中I/O线程在主从数据库结构中负责读取主库的binlog中position位置之后的内容，写入从库的relaylog文件中，整个过程是顺序读取效率比较高。SQL线程将从库relaylog中读取SQL语句或行数据变化记录通过写回从库。

二、Mysql在5.6.x版本之后存在基于schema（库）级别的并行复制，此时SQL线程由coordinator和worker线程两部分组成，其中coordinator线程（协调器）判决redolog中的操作若为同一事务中的顺序操作要放入同一worker线程中处理（线程调度和抢占是由cpu决定的，带有随机性），同一行数据的不同事务操作也要放入同一worker线程中处理。若为事务跨库操作，则等所有worker线程执行完毕之后执行。

三、Mysql在5.7.x并行复制不在基于库，对二进制日志格式也不做限制且引入了事务分组提交也就是所谓的组提交，对于一组组提交可以进行并行回放（把relaylog中涉及修改行数据sql语句执行）。slave-parallel-type可选参数为DATABASE(默认值，基于库的并行复制方式)、LOGICAL_CLOCK(基于组提交的并行复制方式)。

组提交（针对的是事务提交的阶段，影响TPS指标）、三阶段提交

组提交使用的前提是双1设置也就是sync_binlog = 1 和 innodb_flush_log_at_trx_commit = 1

sync_binlog

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

innodb_flush_log_at_trx_commit

• 当设置为1的时候，事务每次提交都会将log buffer中的日志写入os buffer并调用fsync()刷到log file on disk中。这种方式即使系统崩溃也不会丢失任何数据，但是因为每次提交都写入磁盘，IO的性能较差。
• 当设置为0的时候，事务提交时不会将log buffer中日志写入到os buffer，而是每秒写入os buffer并调用fsync()写入到log file on disk中。也就是说设置为0时是(大约)每秒刷新写入到磁盘中的，当系统崩溃，会丢失1秒钟的数据。
• 当设置为2的时候，每次提交都仅写入到os buffer，然后是每秒调用fsync()将os buffer中的日志写入到log file on disk。
• ps：如果系统crash，最多丢失n-1个事务。设置为1最安全

Mysql普遍使用WAL机制（write ahead log），那么具体的应用就是redolog，可以在宕机的情况下保持一致性与原子性，在mysql中可以通过修改配置参数innodb_log_files_in_group和innodb_log_file_size配置日志文件数量和每个日志文件大小，redolog采用循环写的方式记录，当写到结尾时，会回到开头循环写日志。（在磁盘中ib_logfile_x为redolog），其中write pos表示当前日志记录的位置，check point表示将commit后的相关记录擦除掉，临时文件也会被删除。在此阶段会将redolog中的信息写入binlog。

如果仅一次执行一个事务对于磁盘IO性能会有影响，组提交发生在写入redolog之后的prepare阶段，在commit之前会有三个队列，分别代表flush，sync，commit三个阶段。队首的事务会得到prepare_commit_mutex锁，那么排在后面的事务相应的也同样如此，因为一次只有队首事务获得锁，从而避免了锁争，死锁等情况。

flush阶段：

• 首先获取队列中的事务组

• 将Redo log中prepare阶段的数据通过缓冲永久写入磁盘

• 将binlog数据写入文件系统的缓冲，并不能保证数据库崩溃时binlog不丢失

• Flush阶段队列的作用是提供了Redo log的组提交

• 如果在这一步完成后数据库崩溃，由于协调者binlog中不保证有该组事务的记录，所以MySQL可能会在重启后回滚该组事务

sync阶段：

• MySQL使用两个参数控制获取队列事务组的时机：binlog_group_commit_sync_delay=N：在等待N μs后，开始事务刷盘binlog_group_commit_sync_no_delay_count=N：如果队列中的事务数达到N个，就忽视binlog_group_commit_sync_delay的设置，直接开始刷盘

• Sync阶段队列的作用是支持binlog的组提交

• 如果在这一步完成后数据库崩溃，由于协调者binlog中已经有了事务记录，MySQL会在重启后通过Flush 阶段中Redo log刷盘的数据继续进行事务的提交

commit阶段：

• 首先获取队列中的事务组

• 依次将Redo log中已经prepare的事务在引擎层提交

• commit阶段队列的作用是承接Sync阶段的事务，完成最后的引擎提交，使得Sync可以尽早的处理下一组事务，最大化组提交的效率

• 释放prepare_commit_mutex锁让下一组事务获取

ps：二阶段提交是redolog到binlog分别提交的过程，三阶段提交为组提交的过程

GTID全局事务ID（单库也能使用并行复制）

gtid_mode = on

enforce_gtid_consistency = on

即可开启GTID模式，好处之一是不用在找binlog pos位置的而是通过CHANGE MASTER TO MASTER_HOST=‘xxx‘, MASTER_AUTO_POSITION=1，取代了之前的master_file，master_positon。在故障修复中也可以采用MASTER_AUTO_POSITION=‘X’的方式。在每次提交事务之前都会生成全局唯一的事务ID，每个binlog记录的生成会有一个递增的sequence_number标记事务，还有lastcommited标记上次的提交，那么一个组里的lastcommited的值必定相同，可以进行并行回放执行。最后有xid是标记xa事务即分布式事务。使用GTID复制模式时，不支持create temporary table 和 drop temporary table。但是在autocommit=1（开启自动事务，但仍需手动提交）的情况下可以创建临时表，Master端创建临时表不产生GTID信息，所以不会同步到slave，但是在删除临时表的时候会产生GTID会导致主从中断。

ps：

• Mysql5.6版本优化可以分库处理，而Mysql5.7版本优化可以使用GTID。
• 其中mysqlbinlog.xxxxx以及iblogfilex等文件在落盘前要通过操作系统内核os buffer，Linux使用fsync（磁盘同步函数）生成文件。
• fsync是根据文件描述符的变化永久写入磁盘。
• redolog的大小是固定的，日志上的记录修改落盘后，日志会被覆盖掉，无法用于数据回滚/数据恢复等操作。redolog是innodb引擎层实现的，并不是所有引擎都有。

redolog需要与binlog配合使用才能达到ACID的效果。

max_binlog_size控制每个binlog文件的大小

binlog_format有三种格式

• statement，每一条修改数据的SQL都会被记录（不包含DQL语句）。
• row，每一行数据修改的细节都会被记录（不会记录临时表操作）。
• mixed二者混合（推荐使用）

ps：binlog_max_flush_queue_time 在MySQL的5.7.9及之后版本不再生效，原为用于从flush队列中取事务的超时时间，这主要是防止并发事务过高，导致某些事务的响应时间上升。

（未完待更新）

原文：https://www.cnblogs.com/Qyehui/p/15212498.html