MySQL redo log和binlog刷盘时机的个人理解

在MySQL中redo log和binlog在数据保护方面有着至关重要的作用，是有需要花点时间去研究一下这两个玩意儿。以下结论是个人一家之见，看官们请酌情观看，有异议之处欢迎进行交流，万分感谢！

1.正常情况下两阶段提交配合组提交的流程如下：

我个人理解是在双一模式下的流程，每个事务提交时都需要进行fsync刷盘，其执行过程才如上图所示。
然而在配合两个参数sync_binlog和innodb_flush_log_at_trx_commit，上图会有所调整。

一.innodb_flush_log_at_trx_commit在非1的情况下，步骤三可以忽略。
a.innodb_flush_log_at_trx_commit=0，redo log日志条目写入到redo log buffer中，MySQL即认为redo log已完成写入，即redo log prepare状态，可以进行下一步动作。b.innodb_flush_log_at_trx_commit=2，redo log日志条目写入到文件系统缓存page cache，MySQL即认为已完成redo log完成写入，即redo log prepare状态，可以进行下一步动作。此时的刷盘由MySQL的后台主线程和操作系统层进行完成。

二.sync_binlog在非1的情况下，步骤四可以进行忽略。
a.sync_binlog=0，binlog只需要写入到binlog cache即可进行下一步，此刻MySQL会认为binlog已完成写入，redo log和binlog达成一致，redo log可以commit。
b.sync_binlog=N（N>1）,每个事务的binlog都写入到binlog cache，攒够N个事务之后，集中fsync刷盘（由后台主线程处理），此刻MySQL会认为binlog已完成写入，redo log和binlog达成一致，redo log可以commit。

此时我们需要借助binlog组提交的延迟来验证我们的结论：

####双一模式下，每个事务均需要刷盘
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.01 sec)

mysql> 
mysql> show variables like '%binlog_group%';
+-----------------------------------------+---------+
| Variable_name                           | Value   |
+-----------------------------------------+---------+
| binlog_group_commit_sync_delay          | 1000000 |
| binlog_group_commit_sync_no_delay_count | 10      |
+-----------------------------------------+---------+
2 rows in set (0.00 sec)

mysql> select * from words_bak;
+----+--------+
| id | word   |
+----+--------+
|  1 | hello  |
|  2 | world  |
|  3 | python |
|  4 | c      |
|  5 | c++    |
|  6 | php    |
|  7 | B      |
|  8 | mysql  |
|  9 | pg     |
| 10 | oracle |
| 11 | go     |
+----+--------+
11 rows in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from words_bak where id = 11;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (1.00 sec)

此刻事务提交时需要花费1.00 sec，因为binlog_group_commit_sync_delay设置的是1s，binlog_group_commit_sync_no_delay_count和binlog_group_commit_sync_delay满足其中一条即可

###将sync_binlog参数设置为5
mysql> set global sync_binlog=5;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 5     |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like '%binlog_group%';
+-----------------------------------------+---------+
| Variable_name                           | Value   |
+-----------------------------------------+---------+
| binlog_group_commit_sync_delay          | 1000000 |
| binlog_group_commit_sync_no_delay_count | 10      |
+-----------------------------------------+---------+
2 rows in set (0.01 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from words_bak;
+----+--------+
| id | word   |
+----+--------+
|  1 | hello  |
|  2 | world  |
|  3 | python |
|  4 | c      |
|  5 | c++    |
|  6 | php    |
|  7 | B      |
|  8 | mysql  |
|  9 | pg     |
| 10 | oracle |
+----+--------+
10 rows in set (0.00 sec)

mysql> delete from words_bak where id = 10;
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

此时事务提交只需要0.00sec，可以初步判定提交的瞬间未刷盘，但是提交成功了。如果继续测试，你会发现因为sync_binlog=5，在后续的2-4的事务，commit都是很快，第5个事务commit所消耗的时间是1s左右。

sync_binlog和binlog_group_commit_sync_no_delay_count的关联和差异

sync_binlog表示累积N个事务后开始进行刷盘。在参数binlog_group_commit_sync_delay为非0的情况下，MySQL会认为刷盘的过程中需要按照某些条件进行延迟刷盘。
假设场景1：
binlog_group_commit_sync_delay=1000000(1 秒) ; —>1s后刷盘
binlog_group_commit_sync_no_delay_count = 2; —>并发同时提交的事务达到2个进行刷盘
假设当前只有一个事务，这个事务触发了MySQL刷盘动作，但是此时不满足binlog_group_commit_sync_no_delay_count=2，刷盘的时候会按照1s的延迟就行刷盘。

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 3     |
+---------------+-------+
1 row in set (0.02 sec)

mysql> show variables like '%binlog_group%';
+-----------------------------------------+---------+
| Variable_name                           | Value   |
+-----------------------------------------+---------+
| binlog_group_commit_sync_delay          | 1000000 |
| binlog_group_commit_sync_no_delay_count | 2       |
+-----------------------------------------+---------+
2 rows in set (0.00 sec)

mysql> delete from words where id = 112;
Query OK, 1 row affected (0.00 sec)

mysql> delete from words where id = 113;
Query OK, 1 row affected (0.00 sec)

此时下一个事务例如delete from words where id = 114 commit才会触发binlog刷盘，此时才会进入binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count的延迟刷盘的逻辑，这时候开启两个并行的窗口，这两个窗口同时commit提交
窗口1：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from words where id = 114;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

窗口2：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> delete from words where id = 115;
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.10 sec)

观察可以发现commit均使用了0.1sec，不是之前的1 sec了，此刻符合binlog_group_commit_sync_no_delay_count=2，无须延迟1sec进行刷盘，耗时0.1 sec。

假设场景2：
打开双一，即每个事务均会刷盘，此刻关闭binlog_group_commit_sync_no_delay_count即设置为0，binlog_group_commit_sync_delay=1000000，此刻每个事务commit时均会消耗1秒左右

mysql> show variables like '%binlog_group%';
+-----------------------------------------+---------+
| Variable_name                           | Value   |
+-----------------------------------------+---------+
| binlog_group_commit_sync_delay          | 1000000 |
| binlog_group_commit_sync_no_delay_count | 0       |
+-----------------------------------------+---------+
2 rows in set (0.01 sec)

mysql> show variables like '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 1     |
+---------------+-------+
1 row in set (0.01 sec)

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> delete from words where id = 137;
Query OK, 1 row affected (1.00 sec)

mysql> delete from words where id = 138;
Query OK, 1 row affected (1.00 sec)

mysql> delete from words where id = 139;
Query OK, 1 row affected (1.01 sec)

mysql> delete from words where id = 140;
Query OK, 1 row affected (1.00 sec)

mysql> delete from words where id = 141;
Query OK, 1 row affected (1.00 sec)

mysql> delete from words where id = 142;
Query OK, 1 row affected (1.00 sec)