MySQL训练营-binlog和性能

基础知识回顾

binlog存储与格式

SHOW MASTER STATUS 是 MySQL 中用于显示主服务器（Master）二进制日志状态信息的命令。执行该命令后，通常会返回类似以下的结果：

+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      785 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

各字段含义

File - 当前正在使用的二进制日志文件名
- 格式通常为 mysql-bin.xxxxxx（数字递增）
- 这是主服务器记录所有数据更改的文件
Position - 当前二进制日志文件中的位置
- 表示下一个事件将被写入的位置
- 从服务器复制时需要指定这个位置
Binlog_Do_DB - 指定只记录哪些数据库的二进制日志
- 如果为空，表示记录所有数据库的更改
Binlog_Ignore_DB - 指定不记录哪些数据库的二进制日志
- 如果为空，表示没有数据库被排除
Executed_Gtid_Set - 已执行的全局事务标识符集合（GTID）
- 仅在使用GTID复制时显示
- 格式为 source_id:transaction_id 的集合

GTID（Global Transaction Identifier，全局事务标识符）是 MySQL 5.6 版本引入的一种复制机制，它为每个事务分配一个全局唯一的标识符，极大地简化了主从复制的配置和管理。

GTID 的组成格式

GTID 的标准格式为：

source_id:transaction_id

source_id：产生事务的服务器唯一标识（通常是服务器的 server_uuid）
transaction_id：事务序列号，是一个递增的数字

示例：3E11FA47-71CA-11E1-9E33-C80AA9429562:23

GTID 的工作原理

主库执行事务时，会为每个事务分配一个 GTID
GTID 会写入二进制日志（binlog）
从库读取主库的 binlog 时，会记录已执行的 GTID
从库重新连接时，会根据已执行的 GTID 自动定位复制位置

如何查看 GTID 相关信息

查看服务器 UUID（source_id 部分）：
```
SHOW VARIABLES LIKE 'server_uuid';
```

查看已执行的 GTID 集合：

SHOW MASTER STATUS;
SHOW SLAVE STATUS\G

查看所有已执行的 GTID：
```
SELECT * FROM mysql.gtid_executed;
```

`binlog_format` 参数详解

binlog_format 是 MySQL 中控制二进制日志(binlog)记录格式的重要参数，它决定了数据库如何将数据更改事件写入二进制日志。

参数可选值

binlog_format 有三种可选格式：

STATEMENT（语句模式）
- 记录实际执行的 SQL 语句
- 日志量较小
- 可能出现主从不一致（如使用了不确定函数：NOW(), RAND()等）
ROW（行模式，MySQL 5.7+ 默认值）
- 记录每行数据的变化（修改前和修改后的值）
- 日志量较大
- 主从数据一致性最高
- 可以配合 binlog_row_image 参数进一步控制
MIXED（混合模式）
- 默认使用 STATEMENT 模式
- 对可能引起不一致的语句自动切换为 ROW 模式
- 平衡了日志量和一致性需求

查看当前设置

SHOW VARIABLES LIKE 'binlog_format';
-- 或
SELECT @@global.binlog_format;

修改设置方法

临时修改（重启后失效）：
```
SET GLOBAL binlog_format = 'ROW';
```
永久修改（需在配置文件中修改）：

在 my.cnf/my.ini 的 [mysqld] 段添加：
```
[mysqld]
binlog_format = ROW
```
然后重启 MySQL 服务

正确设置 binlog_format 对数据库复制、备份恢复和性能都有重要影响，应根据实际业务需求选择最合适的格式。

使用 mysqlbinlog 查看binlog

使用docker启动的mysqlbinlog没有该工具。

可以在宿主机中安装：sudo apt install mysql-server-core-8.0。在docker启动时，将log文件映射到宿主机上：

在宿主机上使用mysqlbinlog工具查看文件内容：

ROW 格式是 MySQL binlog 的三种格式之一（其他两种是 STATEMENT 和 MIXED），它记录的是表中行的实际更改内容，而不是执行的 SQL 语句。

基本查看命令：
```
mysqlbinlog mysql-bin.000014
```

查看 ROW 格式的详细内容：

mysqlbinlog -v mysql-bin.000014

或更详细的：

mysqlbinlog -vv mysql-bin.000014

如何理解 ROW 格式的输出

执行insert into users values(4,4);后产生的binlog：

BEGIN
/*!*/;
# at 17135
#250706 15:38:16 server id 1  end_log_pos 17188 CRC32 0x024c80eb        Table_map: `mysql`.`users` mapped to number 130
# has_generated_invisible_primary_key=0
# at 17188
#250706 15:38:16 server id 1  end_log_pos 17232 CRC32 0xc708d774        Write_rows: table id 130 flags: STMT_END_F

BINLOG '
6CdqaBMBAAAANQAAACRDAAAAAIIAAAAAAAEABW15c3FsAAV1c2VycwACAwMAAwEBAOuATAI=
6CdqaB4BAAAALAAAAFBDAAAAAIIAAAAAAAEAAgAC/wAEAAAABAAAAHTXCMc=
'/*!*/;
### INSERT INTO `mysql`.`users`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
# at 17232
#250706 15:38:16 server id 1  end_log_pos 17263 CRC32 0xe36ca2a4        Xid = 843
COMMIT/*!*/;

这段 binlog 记录了一个在 mysql.users 表上的 INSERT 操作，下面我将逐部分解释：

事务开始

BEGIN
/*!*/;

表示一个新事务的开始。

表映射信息

# at 17135
#250706 15:38:16 server id 1  end_log_pos 17188 CRC32 0x024c80eb        Table_map: `mysql`.`users` mapped to number 130
# has_generated_invisible_primary_key=0

位置信息：位于 binlog 的 17135 位置
时间戳：2025年7月6日 15:38:16
服务器ID：1
表映射：mysql.users 表被映射为内部编号 130
has_generated_invisible_primary_key=0 表示该表没有使用 MySQL 8.0 的隐式主键特性

写入行事件

# at 17188
#250706 15:38:16 server id 1  end_log_pos 17232 CRC32 0xc708d774        Write_rows: table id 130 flags: STMT_END_F

BINLOG '
6CdqaBMBAAAANQAAACRDAAAAAIIAAAAAAAEABW15c3FsAAV1c2VycwACAwMAAwEBAOuATAI=
6CdqaB4BAAAALAAAAFBDAAAAAIIAAAAAAAEAAgAC/wAEAAAABAAAAHTXCMc=
'/*!*/;

这是一个 Write_rows 事件，表示插入操作
表ID是130（对应前面映射的 mysql.users 表）
flags: STMT_END_F 表示这是语句的结束
二进制数据部分（BASE64编码）包含了实际插入的行数据

插入的具体数据

### INSERT INTO `mysql`.`users`
### SET
###   @1=4 /* INT meta=0 nullable=1 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */

向 mysql.users 表插入了一行数据
第一列(@1)的值是4，类型是INT，可为空但实际不为空
第二列(@2)的值也是4，类型是INT，可为空但实际不为空
注意：@1和@2表示列的位置，具体对应哪些列需要查看表结构

事务提交

# at 17232
#250706 15:38:16 server id 1  end_log_pos 17263 CRC32 0xe36ca2a4        Xid = 843
COMMIT/*!*/;

事务提交，Xid是843（事务ID）
所有更改被持久化

如何理解 STATEMENT 格式的输出

执行insert into users values(5,5);后产生的binlog：

BEGIN
/*!*/;
# at 603
#250706 17:31:51 server id 1  end_log_pos 711 CRC32 0x59a4fe78  Query   thread_id=16    exec_time=0     error_code=0
use `mysql`/*!*/;
SET TIMESTAMP=1751794311/*!*/;
insert into users values(7,7)
/*!*/;
# at 711
#250706 17:31:51 server id 1  end_log_pos 742 CRC32 0xae5b01bf  Xid = 920
COMMIT/*!*/;

解析这段 MySQL binlog (STATEMENT 格式)

这段 binlog 记录了一个在 mysql.users 表上的 INSERT 操作，使用的是 STATEMENT 格式（与 ROW 格式不同，它记录的是实际执行的 SQL 语句）。下面是详细解析：

事务开始

BEGIN
/*!*/;

表示一个新事务的开始。

查询事件头信息

# at 603
#250706 17:31:51 server id 1  end_log_pos 711 CRC32 0x59a4fe78  Query   thread_id=16    exec_time=0     error_code=0

位置信息：位于 binlog 的 603 位置
时间戳：2025年7月6日 17:31:51
服务器ID：1
事件类型：Query（SQL语句执行事件）
线程ID：16（执行该操作的客户端连接线程）
执行时间：0秒
错误代码：0（表示成功执行）

执行的SQL语句

use `mysql`/*!*/;
SET TIMESTAMP=1751794311/*!*/;
insert into users values(7,7)
/*!*/;

这部分包含实际执行的SQL语句序列：

use mysql`` - 切换到 mysql 数据库
SET TIMESTAMP=1751794311 - 设置时间戳（Unix时间戳1751794311对应2025-07-06 17:31:51）
insert into users values(7,7) - 向 users 表插入一行数据，两列的值都是7
事务提交

# at 711
#250706 17:31:51 server id 1  end_log_pos 742 CRC32 0xae5b01bf  Xid = 920
COMMIT/*!*/;

Xid = 920：事务ID为920
COMMIT：提交事务，使更改永久生效

binlog.999999 之后再 flush binary logs 会怎么样?

创建binlog.1000000，6位数字不是真实大小。

MySQL 会在以下几种情况下清理二进制日志(binlog)文件：

自动清理机制

基于过期时间

通过 expire_logs_days 或 binlog_expire_logs_seconds 参数控制
默认情况下，MySQL 会定期检查并删除超过指定时间的 binlog
清理时机：
- 服务器启动时
- 执行 FLUSH LOGS 时
- binlog 文件轮转时

基于空间限制 (MySQL 8.0+)

通过 binlog_expire_logs_auto_purge 和 binlog_space_limit 控制
当日志总大小超过限制时自动清理最旧的 binlog

手动清理命令

PURGE BINARY LOGS

-- 清理指定时间前的日志
PURGE BINARY LOGS BEFORE '2023-10-01 00:00:00';

-- 清理到指定日志文件之前的所有日志
PURGE BINARY LOGS TO 'binlog.000123';

RESET MASTER (谨慎使用)

RESET MASTER;  -- 删除所有binlog文件并重置编号

复制相关清理

当配置了主从复制时，MySQL 会保留所有尚未被所有从库应用的 binlog
通过 SHOW SLAVE STATUS 查看从库读取的 binlog 位置
可以使用 PURGE BINARY LOGS 安全地清理已被所有从库应用的日志

服务一直开着，binlog 会不会把磁盘打爆？

binlog_expire_logs_auto_purge ｜  ON
binlog_expire_logs_seconds     | 2592000 |

MySQL提供了以下机制来防止这种情况：

binlog_expire_logs_seconds (MySQL 8.0+)

作用：设置 binlog 文件的过期时间（秒）
默认值：30天（2592000秒）
效果：超过这个时间的 binlog 文件会被自动删除
优先级：高于 expire_logs_days（如果同时设置）

binlog_expire_logs_auto_purge (MySQL 8.0.29+)

作用：控制是否启用自动清理过期 binlog 的功能
默认值：ON（启用）
效果：当设置为 OFF 时，即使 binlog 过期也不会自动删除

expire_logs_days (较旧版本)

作用：设置 binlog 文件的过期天数
替代方案：在 MySQL 8.0 中建议使用 binlog_expire_logs_seconds

工作机制

触发时机：
- 当 binlog 轮换（达到 max_binlog_size）时
- 服务器启动时
- 手动执行 FLUSH LOGS 时
所以到达2592000秒不会进行切换，只有上诉三个时机。
清理过程：
- MySQL 会检查 binlog 文件的最后修改时间
- 删除所有早于过期阈值的 binlog 文件
- 保留至少一个 binlog 文件（即使已过期）

监控方法：

SHOW VARIABLES LIKE 'binlog_expire%';
SHOW VARIABLES LIKE 'expire_logs%';
SHOW BINARY LOGS;  -- 查看现有binlog文件

statement格式要被官方弃用了，本单元只讨论 binlog_format=row 的情况。

主要问题：

安全性问题：非确定性函数可能导致主从不一致（如UUID(), NOW()）
- 依赖问题：表结构必须完全一致
- 功能限制：不支持某些DDL操作的无损复制

MySQL主备同步

MySQL 主备同步（主从复制）是通过二进制日志(binlog)实现的异步数据复制过程，以下是完整的工作流程：

主库(Master)操作流程

事务执行：
- 应用程序向主库写入数据
- 主库执行事务并更新存储引擎数据
写入binlog：
- 事务提交时，主库将更改记录到二进制日志(binlog)
- 记录格式取决于binlog_format设置(ROW/STATEMENT/MIXED)
binlog同步：
- 主库的binlog dump线程检测到新binlog事件
- 根据从库请求的位置发送相应binlog内容

从库(Slave)操作流程

I/O线程工作：
- 从库I/O线程连接主库，请求指定位置的binlog
- 接收主库发送的binlog事件
- 将事件写入从库的relay log(中继日志)
SQL线程工作：
- 从库SQL线程读取relay log中的事件
- 解析并执行这些事件，重放主库的操作
- 更新从库数据使其与主库保持一致

影响主库性能的`binlog`参数

binlog_cache_size

binlog_checksum

binlog_group_commit_sync_delay

binlog_group_commit_sync_no_delay_count

binlog_order_commits

binlog_row_image

semi-sync...

binlog_cache_size

binlog_cache_size太小

当全局 binlog 缓存不足时，MySQL 会使用临时文件来存储 binlog 事件，具体机制如下：

缓存层级与溢出处理

缓存层级结构：
- 第一层：线程专属缓存（binlog_cache_size控制）
- 第二层：全局共享缓存（binlog_stmt_cache_size控制）
- 第三层：磁盘临时文件（当内存缓存不足时）
溢出到临时文件的触发条件：
- 当事务产生的 binlog 事件超过线程缓存大小
- 并且全局缓存也无法满足需求时
- 系统会自动创建临时文件存储超出的部分

相关状态变量

可以通过以下命令查看缓存使用情况：

SHOW GLOBAL STATUS LIKE 'Binlog_cache%';

关键指标：

Binlog_cache_disk_use：使用临时文件的次数
Binlog_cache_use：成功使用内存缓存的次数

性能影响

临时文件的影响：

显著增加I/O操作
降低事务执行速度
可能成为系统瓶颈

binlog_cache_size太大

binlog_cache_size 设置过大会带来多方面的负面影响，需要根据实际业务场景谨慎配置：

内存资源浪费

每个连接预分配：每个客户端连接都会预先分配指定大小的缓存内存，即使实际不使用

内存占用公式：

总内存占用 ≈ 连接数 × binlog_cache_size

示例：1000连接 × 1MB设置 = 1GB内存常驻占用

系统稳定性风险

OOM风险：高并发连接时可能导致内存耗尽
交换内存使用：可能触发系统使用swap，严重降低性能
影响其他组件：挤占InnoDB缓冲池等关键组件内存

性能反优化

问题类型	影响表现
CPU缓存失效	过大的缓存降低CPU缓存命中率
TLB压力	内存页表项增加，导致TLB抖动
NUMA问题	跨NUMA节点访问内存延迟增加

binlog_checksum

binlog_checksum 是 MySQL 中控制二进制日志校验和的参数，它会在 binlog 事件写入时计算并存储校验和，在读取时进行验证，这对系统性能有一定影响。

可选值：
- NONE：不计算校验和（MySQL 5.6.6 之前默认）
- CRC32：使用 CRC32 算法计算校验和（5.6.6+ 默认）
- SHA1/SHA256：使用更安全的哈希算法（较少使用）

写入性能影响

场景	影响程度	说明
高并发写入环境	中等	每个事务需要额外计算校验和，增加CPU开销
批量插入/大事务	较小	校验和计算是流式的，对大事务影响相对小
低负载系统	几乎无感	额外开销占比很小

典型性能下降：在极端高并发写入场景下，可能造成 2-5% 的吞吐量下降

读取/复制性能影响

场景	影响程度	说明
主从复制	中等	从库需要验证每个事件的校验和
基于binlog的恢复操作	较小	校验时间远小于实际应用事件时间
日常查询	无影响	不影响普通查询性能

不同算法的比较

算法	安全性	CPU开销	推荐场景
NONE	无保护	零开销	仅测试环境
CRC32	中等	低开销	生产环境默认
SHA1	高	较高开销	高安全性要求

最佳实践建议

生产环境建议保持默认CRC32：
```
SET GLOBAL binlog_checksum='CRC32';
```
在数据安全性和性能之间取得良好平衡
性能敏感场景的优化：
- 如果确实需要极致性能且能接受潜在数据风险，可设为NONE
- 配合定期校验工具替代实时校验
监控指标：
- 观察Com_binlog_*状态变量
- 监控主从延迟情况
与其他参数的协同：
- 与sync_binlog配合使用（建议sync_binlog=1或100）
- 与binlog_group_commit_sync_delay协同优化

在我们刚刚看到的binlog中就存在CRC32校验值：

#250706 15:38:16 server id 1  end_log_pos 17188 CRC32 0x024c80eb        Table_map: `mysql`.`users` mapped to number 130
# has_generated_invisible_primary_key=0
# at 17188
#250706 15:38:16 server id 1  end_log_pos 17232 CRC32 0xc708d774        Write_rows: table id 130 flags: STMT_END_F

sync_binlog

sync_binlog 参数决定了 MySQL 服务器在多少次二进制日志写入后将日志同步到磁盘。这个参数直接影响数据安全性和系统性能。

0：由操作系统决定何时同步到磁盘，性能最好但安全性最低。只write，不fsync。
1：每次事务提交时都同步到磁盘，最安全但性能开销最大。每次都fsync。
N（大于1的数）：每N次事务提交后同步一次，平衡安全性和性能。累计N个事务后才fsync。

在sync_binlog非1时，OMM不会丢数据。因为已经提交给操作系统，操作系统不崩溃就能安全写入磁盘。

group_commit相关

在 MySQL 中，当使用 Group Commit（组提交）机制时，多个事务的提交操作可以被合并，从而减少实际的磁盘同步（sync）次数。

binlog_group_commit_sync_delay

1. 参数基本概念

binlog_group_commit_sync_delay 是 MySQL 5.7 及更高版本引入的一个二进制日志(binlog)组提交(group commit)优化参数，用于控制事务提交时二进制日志的同步延迟时间。

2. 参数定义

作用：指定在同步二进制日志到磁盘前等待的微秒数，以便将更多事务分组提交
默认值：0（无延迟）
取值范围：0~1000000（即最大可设置1秒延迟）
动态修改：支持运行时修改（SET GLOBAL）
适用版本：MySQL 5.7+

3. 工作机制

3.1 基本工作流程

事务进入提交阶段
检查当前是否有其他事务正在提交
如果有，则等待加入组提交
等待 binlog_group_commit_sync_delay 指定的微秒数
将组内所有事务的二进制日志一次性写入并同步到磁盘
通知所有事务提交完成

3.2 详细机制

当设置 binlog_group_commit_sync_delay=N（N>0）时：

事务收集阶段：
- 第一个到达提交阶段的事务成为"领导者"(leader)
- 后续到达的事务成为"跟随者"(follower)，加入当前组
- 系统启动一个计时器，等待N微秒
等待策略：
- 在等待期间，新到达的事务可以不断加入当前组
- 如果等待期间组内事务数达到 binlog_group_commit_sync_no_delay_count 设置的值，则立即提交，不等待完整延迟时间
- 如果没有任何其他事务加入，单个事务也会在延迟结束后提交
磁盘同步：
- 等待结束后，整个组的事务二进制日志被一次性写入并同步到磁盘
- 然后所有事务被标记为提交完成

4. 实测

mysql> set global binlog_group_commit_sync_delay=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t12(id int);
Query OK, 0 rows affected (1.03 sec)

mysql> insert into t12 values(1);
Query OK, 1 row affected (1.01 sec)

由于 binlog_group_commit_sync_delay=1000000（1秒），且没有并发事务，每个操作都等待了完整的 1 秒才提交。

binlog_group_commit_sync_no_delay_count

binlog_group_commit_sync_no_delay_count 是 MySQL 5.7+ 引入的二进制日志组提交(group commit)优化参数，与 binlog_group_commit_sync_delay 协同工作，共同控制事务提交的合并行为。

特性	`binlog_group_commit_sync_delay=10000` + `no_delay_count=10`	`sync_binlog=10`
控制维度	基于时间+事务数的动态组提交	基于固定事务数的同步
同步触发条件	(1)等待10ms 或 (2)积累10个事务	每10个事务强制同步
延迟机制	主动等待更多事务合并（最多10ms）	无主动等待
事务合并能力	更强（可跨多个"逻辑组"合并）	较弱（严格按N事务分组）
数据安全性	较高（可配置较短等待时间）	较低（可能丢失最后N-1个事务）
适用版本	MySQL 5.7+	所有版本

binlog_order_commits

binlog_order_commits 是 MySQL 中控制事务提交顺序的重要参数，它影响事务的提交顺序保证。

1. 参数基本概念

作用：控制是否按照二进制日志(binlog)中的顺序提交存储引擎层的事务
默认值：ON (MySQL 5.7+), OFF (MySQL 5.6)
动态修改：支持运行时修改（SET GLOBAL）
适用版本：MySQL 5.6+

2. 工作机制

2.1 参数开启（ON）时

严格顺序提交：
- 事务在存储引擎(InnoDB)中的提交顺序
- 严格与binlog中的写入顺序保持一致
- 通过内部队列机制保证顺序
工作流程：
1. 事务写入binlog
2. 进入提交队列（按binlog顺序）
3. 依次在InnoDB层提交

2.2 参数关闭（OFF）时

并行提交：
- 存储引擎层的事务提交可以并行执行
- 可能快于binlog的写入顺序
- 仅保证单个事务的原子性

3. 性能影响

场景	binlog_order_commits=ON	binlog_order_commits=OFF
高并发写入	吞吐量较低（顺序限制）	吞吐量更高（并行提交）
低并发	影响不明显	影响不明显
从库应用	更有序，减少冲突	可能增加并行度
崩溃恢复	更易确定恢复点	恢复顺序可能不一致

binlog_row_image

binlog_row_image 是 MySQL 中控制行格式二进制日志(binlog)记录内容的参数，它决定了在行格式复制(Row-Based Replication)时，binlog 中记录的行数据包含哪些列信息。

1. 参数基本概念

作用：控制行格式二进制日志记录中包含的列信息
默认值：FULL (MySQL 5.6+), 早期版本为FULL或NOBLOB
可选值：
- FULL：记录所有列的值
- MINIMAL：仅记录被修改的列和唯一索引列
- NOBLOB：记录所有非BLOB列和修改的BLOB列
动态修改：支持运行时修改（SET GLOBAL）
适用版本：MySQL 5.6+

2. 不同模式详解

2.1 FULL（完全模式）

记录内容：所有列的值（无论是否被修改）
优点：
- 数据最完整
- 从库应用更安全
- 便于数据恢复
缺点：
- binlog体积最大
- 网络传输开销大

2.2 MINIMAL（最小模式）

记录内容：
- 被修改的列
- 所有唯一索引/主键列（用于行定位）
优点：
- binlog体积最小
- 网络传输效率高
缺点：
- 从库应用时可能出现问题（如缺少非索引列）
- 数据恢复可能不完整

2.3 NOBLOB（非BLOB模式）

记录内容：
- 所有非BLOB列
- 被修改的BLOB列
优点：
- 平衡了体积和安全性
- 减少大字段传输
缺点：
- 未修改的BLOB列不会记录

3. 配置建议

场景	推荐设置	说明
生产环境（默认）	FULL	保证数据完整性和安全性
大表频繁更新	NOBLOB	减少BLOB字段记录
带宽受限环境	MINIMAL	最小化日志体积
无BLOB字段表	FULL或MINIMAL	根据安全性需求选择

semi-sync

半同步复制是介于异步复制和全同步复制之间的一种复制方式，它确保至少一个从库接收到事务事件后，主库才会向客户端返回事务提交成功的响应。

如上图所示，一般的主备集群主库对外提供读写能力，同时将修改同步到备机，如果主机挂了会切换备机为主机继续工作。semi-sync的设置可以保证主机的修改至少同步到了一个备机。

课堂练习

在主库单线程执行纯 insert 语句的情况下，开启下面这些参数，对性能(TPS)的影响，从高到低是?

binlog_checksum (on vs off)
sync binlog (1 vs 0)
binlog_group_commit_sync_delay (1000ms vs 0)
binlog_order_commits (on vs off)
binlog_row_image (full vs minimal)
semi-sync (on vs off)

影响程度排序（高 → 低）

binlog_group_commit_sync_delay (1000ms vs 0)
- 直接影响：=1000ms 强制每个事务等待1秒提交
- 性能差异：理论TPS上限从1000降至1（单线程无法利用组提交优势）
semi-sync (ON vs OFF)
- 网络开销：需等待从库ACK，增加至少1个RTT时间
- 性能差异：OFF 比 ON 快 30-50%（依赖网络延迟）
sync_binlog (1 vs 0)
- 最大影响：sync_binlog=1 每次提交都触发磁盘同步(fsync)，导致极高I/O延迟
- 性能差异：=0 比 =1 快 10-100倍（机械硬盘最明显）
binlog_checksum (ON vs OFF)
- CPU开销：ON 需计算CRC32校验和
- 性能差异：OFF 提升 2-5%（现代CPU影响较小）
binlog_order_commits (ON vs OFF)
- 并发限制：单线程场景下影响较小
binlog_row_image (FULL vs MINIMAL)
- 日志体积：单线程INSERT时两者记录内容相同

影响主备同步的`binlog`参数

replica_sql_verify_checksum：控制从库是否校验从主库接收到的binlog事件的校验和，默认开启（ON），确保数据传输完整性。
semi_sync）：主库提交事务时需等待至少一个从库接收并确认binlog后才返回成功，平衡数据安全性与性能，默认关闭（OFF）。

主备延迟常见原因:

机器负载
备库读压力
大事务
并发度

一般不用处理，正常现象

主备同步流程图

MySQL 主备同步流程详解

1. 主库（Master A）处理流程

事务启动：用户发起数据变更（start）
内存记录：
- 写入内存中的 undo log（undolog (mem)）用于回滚
- 更新内存数据页（data (mem)）
日志持久化：
- Prepare阶段：写入 redo log（redo log (prepare)），标记事务准备状态
- Binlog写入：同步记录二进制日志（binlog），用于主备同步
- Commit阶段：提交 redo log（redo log (commit)），事务正式生效
后台刷盘：
- bg-thread 将内存中的 undo log 和数据页异步刷盘（undolog (disk) / data (disk)）
日志传输：
- dump_thread 将 binlog 发送给从库

2. 从库（Slave B）同步流程

接收日志：
- io_thread 线程接收主库的 binlog，写入本地中转日志（relay log）
重放事务：
- sql_thread 线程读取 relay log 中的事件，在从库执行相同操作，更新数据（DATA）
状态反馈：
- 从库通过 ack 机制向主库确认日志接收位置（半同步复制场景）

早期版本 SQL 线程是单线程，可能成为复制瓶颈。MySQL 5.7+ 支持多线程回放（需配置 slave_parallel_workers）。

MySQL 并行复制策略详解

MySQL 的并行复制策略是提升主从复制性能的关键技术，不同版本逐步优化了并行复制的实现方式。以下是主要的并行复制策略及其演进过程：

1. 基于数据库的并行复制 (5.6版本)

策略：按数据库名并行

实现方式：不同数据库的事务可以并行执行

配置参数：

slave_parallel_workers=4  # 设置并行工作线程数
slave_parallel_type=DATABASE

优点：实现简单，减少锁冲突
缺点：单库场景无效，不适用分库分表环境

2. 基于逻辑时钟的并行复制 (5.7版本)

策略：基于事务组并行

实现方式：利用 LOGICAL_CLOCK 机制，无冲突事务可并行

配置参数：

slave_parallel_workers=8
slave_parallel_type=LOGICAL_CLOCK

关键机制：
- 主库通过 binlog_group_commit_sync_delay 增加组提交机会
- 同一组提交的事务可以并行执行
优点：单库也能并行，提升30-50%性能
缺点：对DDL操作仍需串行

3. 基于WRITESET的并行复制 (8.0版本)

策略：基于行哈希值并行

实现方式：计算行数据的WRITESET哈希，无冲突事务可并行

配置参数：

slave_parallel_workers=16
slave_parallel_type=LOGICAL_CLOCK
binlog_transaction_dependency_tracking=WRITESET

关键优化：
- 自动识别无冲突事务（即使未在同一组提交）
- 支持 WRITESET_SESSION 模式保证会话顺序
优点：并行度更高，提升50-100%性能
缺点：需要计算行哈希，略微增加CPU开销

4. 增强型并行复制 (8.0.23+)

策略：事务依赖关系优化

新特性：
- 支持 COMMIT_ORDER 和 WRITESET 混合模式
- 自动适应不同负载特征

配置示例：

binlog_transaction_dependency_history_size=25000

性能对比测试数据

策略	TPS提升	适用场景
单线程复制	基准	兼容所有版本
DATABASE并行	20-30%	多库环境
LOGICAL_CLOCK	30-50%	5.7+单库高并发
WRITESET	50-100%	8.0+高并发OLTP
WRITESET_SESSION	40-80%	需保持会话顺序的场景

posted @ 2025-07-06 17:15 余为民同志阅读(156) 评论(0) 收藏举报

刷新页面返回顶部

cnyuyang

余为民同志

MySQL训练营-binlog和性能

基础知识回顾

binlog存储与格式

各字段含义

GTID 的组成格式

GTID 的工作原理

如何查看 GTID 相关信息

binlog_format 参数详解

参数可选值

查看当前设置

修改设置方法

使用 mysqlbinlog 查看binlog

如何理解 ROW 格式的输出

如何理解 STATEMENT 格式的输出

binlog.999999 之后再 flush binary logs 会怎么样?

服务一直开着，binlog 会不会把磁盘打爆？

MySQL主备同步

影响主库性能的binlog参数

binlog_cache_size

binlog_cache_size太小

binlog_cache_size太大

binlog_checksum

sync_binlog

group_commit相关

binlog_group_commit_sync_delay

1. 参数基本概念

2. 参数定义

3. 工作机制

4. 实测

binlog_group_commit_sync_no_delay_count

binlog_order_commits

1. 参数基本概念

2. 工作机制

2.1 参数开启（ON）时

2.2 参数关闭（OFF）时

3. 性能影响

binlog_row_image

1. 参数基本概念

2. 不同模式详解

2.1 FULL（完全模式）

2.2 MINIMAL（最小模式）

2.3 NOBLOB（非BLOB模式）

3. 配置建议

semi-sync

课堂练习

影响程度排序（高 → 低）

影响主备同步的binlog参数

主备同步流程图

1. 主库（Master A）处理流程

2. 从库（Slave B）同步流程

MySQL 并行复制策略详解

1. 基于数据库的并行复制 (5.6版本)

2. 基于逻辑时钟的并行复制 (5.7版本)

3. 基于WRITESET的并行复制 (8.0版本)

4. 增强型并行复制 (8.0.23+)

性能对比测试数据

公告

`binlog_format` 参数详解

影响主库性能的`binlog`参数

影响主备同步的`binlog`参数