作者：沈光宇，爱可生南区 DBA 团队成员，主要负责 MySQL 故障处理和性能优化MySQL 。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源MySQL 。
本文约 1200 字，预计阅读需要 3 分钟MySQL 。
背景介绍
MySQL 主从复制是确保数据库高可用性和扩展性的常用架构MySQL 。当主从复制存在延迟时，可能导致主从查询数据不一致，影响用户体验和数据库高可用切换。
主从延迟的一个常见原因是 执行大事务，尤其是在涉及多表写入的复杂操作时MySQL 。
本文基于一个 真实生产案例，分析了业务端因分表操作引发的主从延迟问题，并分享了排查过程及优化建议，为数据库管理员和开发人员提供实用的参考MySQL 。
问题描述
业务开发团队在程序中实现了一个分表操作：从 1 张大表读取数据，经过计算后写入 100 个分表MySQL 。
这一过程产生了涉及多表写入的大事务，导致主从复制延迟显著增加MySQL 。初步观察发现，从库的 relay log大小为 1.6GB（超过默认值 1.1GB），且从库的 INFORMATION_SCHEMA.INNODB_TRX表显示存在插入行数较多的大事务，插入行数量动态变化，表明从库正面临较大的复制压力。
主从延迟状态
从库落后于主库 37325 秒，主从相差 4 个 binlogMySQL 。
排查过程步骤 1：检查从库事务
首先查询了从库的 INFORMATION_SCHEMA.INNODB_TRX表，以查看当前活跃事务的状态MySQL 。结果显示存在插入行数较多的大事务，且事务数量不断变化，初步确认大事务是导致延迟的潜在原因。
步骤 2：检查从库 Relay Log 大小
检查从库的 relay log，发现其大小为 1.6GB，大于默认的 1.1GBMySQL 。
步骤 3：解析 Binlog 以查看数据更改行数shell>mysqlbinlog --base64-output=decode-rows -vv mysql-bin.003731 | awk \
'BEGIN {s_type=""; s_count=0;count=0;insert_count=0;update_count=0;delete_count=0;flag=0;} \
分析输出文件 003731.txt，发现事务大多数都是插入、更新或删除行均为 1 行、2 行、3 行、4 行MySQL 。
这并非是一次性插入、更新或删除几十上百万行的大事务MySQL ，统计修改行数的结果如下：
25647 4
3902
可以看到 binlog 中有 2 个大事务MySQL ，每个大事务 700 多 MB，与先前的 relay log大小 1.6G 相吻合
shell>head -n 5 003731_top_20.txt
2789
2783
2779
#解析事务起止位置
shell>less 003731_trx_start_stop_pos.txt
步骤 5：使用 my2sql 分析大事务
为了更直观地分析大事务的细节，我们使用了 my2sql[1]工具对 binlog 进行解析MySQL 。
shell>./my2sql -user repuser -password repuserpassword -host 10.235.98.18 -port 3306 -work-type stats -start-file mysql-bin.003731 -stop-file mysql-bin.003735 -big-trx-row-limit 5000 -output-dir /tmp/log/my2sql_output
从输出文件 biglong_trx.txt中，我们发现 8 个典型的大事务从 2024-01-22 11:00持续到 2024-01-22 18:48，最长的大事务执行了约 7 小时 48 分钟，每个大事务涉及 400 万行数据，分别写入 100 个分表MySQL 。事务详情显示了对多个 db.t_sharding_XX表的插入操作,如下图：
解决方案 123
拆分大事务：将多表写入的大事务拆分为多个小事务，分批执行，减少单次事务操作行数和执行时间MySQL 。
优化分表逻辑：在程序中优化数据处理流程，减少不必要的多表操作MySQL 。
监控和报警：建立针对大事务和主从延迟的监控机制，及时发现和处理异常MySQL 。
参考资料
[1]
my2sql: