MySQL 复制延迟原因总结（持续更新）

一、前言

• 互联网场景数据库读多写少，如果读的业务确实远大于写可以使用读写分离来优化。如果使用读写分离，那么主从的延迟就会变的非常敏感，总结下一些见到的导致主从延迟的情况。

二、主从延迟

1. 网络层面：主从复制的原理就主库 BinlogDump 线程通过网络将 binlog 发送给从库 IO Thread 那么如果网络出现问题，从库拿不到 binlog 也会出现延迟。目前数据库分为云端数据库和自建数据库，如果是云端那么网络出现问题可能性非常小，如果是自建数据库，那么第一时间需要排查网络情况。
2. 从库配置：从库配置远低于主库，数据量比较大的情况下，如果出现 DDL 操作从库 CPU/磁盘没有主库强劲，某些 DDL 操作没有执行完此时查询就会出现堵塞从而导致延迟。
3. 负载：大多数企业都会有自己的 BI/大数据系统，会有很多复杂的 SQL 扫描行数很大 Slave SQL Thread 回放 SQL 非常缓慢，产生复制延迟此时的 BI/大数据系统属于准实时。往往跑数分库的配置也会比较高。
4. 锁冲突：数据库在备份时为获取一致性位点信息会执行 FTWRL (Flush Tables With Read Lock) 生成一个全局读锁。如果 FTWRL 被阻塞，后续涉及到该表的会话都会被阻塞，包括 SQL 回放线程。还有一种情况就是从库 DDL 被大事务扫描行数很大的 SQL 堵塞无法获取 MDL 锁🔒 如果需要执行 DDL 需要先关注数据库的会话状态。
5. 大表 DDL：直接在主库上对大表进行 DDL 操作往往需要很长时间，例如 ALTER 语句添加一个索引主库运行需要两分钟，从库也就需要执行两分钟，ALTER 锁表的时间会很长，此时非常容易导致延迟堵塞。简单介绍一下 innodb 引擎在执行 DDL 操作时的过程：
   • 按照原始表 (original_table) 的表结构和 DDL 语句，新建一个不可见的临时表 (temporary_table)；
   • 在原表上面加上 WRITE LOCK 阻塞所有的 DML 操作；
   • 执行 insert into tmp_table select * from original_table；
   • rename original_table 和 tmp_table 最后 drop original_table；
   • 最后释放掉 write lock；
   • 我们发现在 DDL 期间会堵塞所有的 DML 只能查询无法写入，针对此情况 Percona 公司推出 pt-online-schema-change 可以在线执行 DDL 操作。
6. 大事务：大查询长时间执行无法释放 DML 读锁，后续同步主库的 DDL 操作获取 DML 写锁资源被阻塞等待，导致后续同步主库的操作堆积，主从延迟增长严重。一般遇到这种情况建议业务 Kill 掉一大查询操作。总结一下：常见情况就是大事务堵塞 DDL 操作 MDL 会出现锁等待导致延迟。