MySQL主從延遲現象及原理分析詳解

更新時間：2019年02月12日 09:31:15 作者：wlmmmm

今天小編就為大家分享一篇關于MySQL主從延遲現象及原理分析詳解，小編覺得內容挺不錯的，現在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧

一、現象

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

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

二、原理

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

master 服務器和 slave 服務器連接時，創(chuàng)建Binlog dump thread以發(fā)送bin log數據：

一個Binlog dump thread對應一個 slave 服務器；
Binlog dump thread從bin log獲取數據時會加鎖，獲取到數據后，立即釋放鎖。

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

I/O thread以拉的方式，從 master 讀取事件，并存儲到 slave 服務器的relay log中；
SQL thread從relay log中讀取事件并執(zhí)行；
slave可以按照自己的節(jié)奏讀取和更新數據，也可以隨意操作復制進程(啟動和停止)。

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

通過 SHOW PROCESSLIST 可查看線程狀態(tài):

Binlog dump thread:

mysql> SHOW PROCESSLIST\G
*************************** 1. row ***************************
  Id: 2
 User: root
 Host: localhost:32931
  db: NULL
Command: 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 PROCESSLIST\G
*************************** 1. row ***************************
  Id: 10
 User: system user
 Host:
  db: NULL
Command: Connect
 Time: 11
 State: Waiting for master to send event
 Info: NULL
 *************************** 2. row ***************************
  Id: 11
 User: system user
 Host:
  db: NULL
Command: 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讀取更新的頻率，就有可能出現主從延遲的情況，如: