一、現象

凌晨對線上一張表添加索引，表數據量太大(1億+數據，數據量50G以上)，造成主從延遲幾個小時，各個依賴從庫的系統無法查詢數據，最終影響業務。

現在就梳理下主從延遲的原理。

二、原理

根據 MySQL 官方文檔 MySQL Replication Implementation Details 中的描述，MySQL 主從復制依賴于三個線程：master一個線程(Binlog dump thread)，slave兩個線程(I/O thread和SQL thread)。主從復制流程如下圖：

master 服務器和 slave 服務器連接時，創建Binlog dump thread以發送bin log數據：

當 slave 服務器收到 START_SLAVE 命令時，會創建I/O thread和SQL thread：

注: START_SLAVE命令成功啟動線程后，如果后面I/O thread或SQL thread因為某些原因停止，則不會有任何的警告，業務方無法感知。可以通過查看 slave 的 error 日志，或者通過 SHOW SLAVE STATUS 查看 slave 上的線程狀態。

通過 SHOW PROCESSLIST 可查看線程狀態:

Binlog dump thread:

mysql> SHOW PROCESSLISTG*************************** 1. row ***************************  Id: 2 User: root Host: localhost:32931  db: NULLCommand: Binlog Dump Time: 94 State: Has sent all binlog to slave; waiting for binlog to   be updated Info: NULL

I/O thread 和 SQL thread:

mysql> SHOW PROCESSLISTG*************************** 1. row ***************************  Id: 10 User: system user Host:  db: NULLCommand: Connect Time: 11 State: Waiting for master to send event Info: NULL *************************** 2. row ***************************  Id: 11 User: system user Host:  db: NULLCommand: Connect Time: 11 State: Has read all relay log; waiting for the slave I/O   thread to update it Info: NULL

三、分析

根據上面的原理，由于slave是單線程(I/O thread)讀取數據，單線程(SQL thread)更新數據，而master是多線程寫入，那么只要master寫入的頻率大于slave讀取更新的頻率，就有可能出現主從延遲的情況，如: