MySQL Replication 複寫優化全攻略：延遲類型、容量評估、單線程瓶頸與架構擴展

前言

MySQL Replication (主從複寫) 是高可用與讀寫分離的基石。但單線程複寫、大事務阻塞、從庫硬體不足等問題常導致延遲。本文系統分析延遲類型、複寫容量評估、優化策略、從庫硬體選型、主從架構擴展與應用層適配。

一、複寫延遲類型識別

二、MySQL 複寫架構限制

• 單線程複寫 (5.6 前)： 僅能利用 1 CPU 核心、1 磁碟，複雜查詢/大事務直接阻塞
• 5.6 GTID + 多線程複寫 (MTS)： 庫級並行 (slave_parallel_workers)，但跨庫事務仍串行
• 5.7 增強 MTS： 基於邏輯時鐘 (LOGICAL_CLOCK) 的並行，大幅提升並行度
• 8.0 Write-set 並行： 基於寫入集合衝突檢測，自動並行化無衝突事務

三、複寫容量評估與建議

測量方法

# 1. 暫停複寫 1 小時
STOP SLAVE;
sleep 3600;
START SLAVE;

# 2. 觀察追平時間 (Seconds_Behind_Master 歸零)
# 容量比 = 暫停時間 / 追平時間
# 建議維持 3 倍以上 (1 小時延遲，20 分鐘追平)

四、SQL 層面優化

1. 避免大事務與複雜查詢阻塞 (5.0/5.1)

-- 差：複雜查詢直接在 UPDATE/INSERT 中
INSERT ... SELECT ;
UPDATE ... WHERE ;

-- 優：分步驟，先寫入臨時表
CREATE TEMPORARY TABLE tmp AS SELECT ;
INSERT ... SELECT * FROM tmp;

2. 5.1+ 使用 Row-Based Replication (RBR)

• RBR 記錄實際行變更，而非 SQL 語句
• 從庫直接套用行變更，無需重新執行複雜查詢
• 設定：binlog_format = ROW

3. 拆分大事務

• 批次刪除/更新分批執行 (如每 1000 行 commit 一次)
• 避免長時間鎖定主庫資源

五、從庫硬體選型 (單線程複寫關鍵)

• CPU： 高主頻單核效能優於多核 (單線程複寫)
• 磁碟： 高轉速 SAS/SSD、大容量控制器快取、建議 RAID 10
• 記憶體： 足夠 innodb_buffer_pool_size、key_buffer_size
• 網路： 低延遲、高頻寬專線/內網

六、主從架構擴展策略

1. 降低寫入頻率擴散壓力

• 至少 1 台全量從庫，其餘可只同步核心表
• 應用層路由：不同查詢導向不同從庫
• 高頻計數器/Session 移至 Redis/Memcached
• 大量寫入表在從庫直接寫入 (不經複寫)，需業務容許
• 從庫使用 MyISAM (無事務開銷) 做純讀取報表

2. 提升從庫硬體利用率

• 分區表減少掃描範圍
• 選擇性同步核心表
• 從庫歸檔歷史資料 (分表/刪除)
• Session 持久化：寫入用戶導向主庫，其他導向從庫
• 按查詢類型路由：OLTP 走主庫，OLAP 走專用從庫

3. 應用層最大化利用從庫

• 非即時性查詢 (報表、列表) 走從庫
• 寫入後讀取：Session 綁定主庫，確保讀到自己寫入
• 版本號機制：Memcached 存資料版本，從庫版本落後則走主庫
• 查詢前檢查 SHOW SLAVE STATUS，延遲過大自動切主庫

七、監控關鍵指標

SHOW SLAVE STATUS\G
-- 關鍵欄位：
-- Seconds_Behind_Master: 延遲秒數 (NULL 表示斷開)
-- Slave_IO_Running / Slave_SQL_Running: Yes/No
-- Last_IO_Error / Last_SQL_Error: 錯誤詳情
-- Relay_Log_Space: Relay log 總大小

總結

優化複寫核心在於：主庫減輕寫入、從庫強化硬體、SQL 避免大事務、架構分流讀寫、應用層感知延遲。升級至 MySQL 8.0 並啟用 Write-set 並行複寫 (slave_parallel_type=LOGICAL_CLOCK、slave_parallel_workers=4) 是現代化解單線程瓶頸的最直接手段。