MySQL 主從延遲根因分析與解決方案全景圖

MySQL 主從延遲根因分析與解決方案全景圖

一、核心原因

1. 單線程復制瓶頸：主庫並發提交，從庫單 SQL 線程串行重放 (MySQL 5.6 前)。
2. 異步復制本質：主庫提交不等待從庫，天然存在延遲；半同步僅保證收到，不保證重放完成。
3. 硬件資源不對等：從庫常用低配機型、甚至跑多實例，IOPS/CPU 不足。

二、常見延遲場景與針對性解法

1. ROW 模式無主鍵/無索引/索引區分度低

現象：show slave status 位置不動；show open tables 某表 In_use=1 持續；表無主鍵或索引差。

解法：

# 從庫停止復制
STOP SLAVE;
# 關閉 binlog 記錄本次 DDL
SET SQL_LOG_BIN=0;
# 添加高區分度索引 (優先放最左)
ALTER TABLE tbl ADD KEY idx_col (col);
# 若舊版本只用第一個索引，需刪舊建新調整順序
START SLAVE;

觀察 show engine innodb status 中 rows_inserted/updated/deleted/selected 判斷追趕速度。

2. MIXED 模式下慢 SQL (如 INSERT...SELECT 帶 GROUP BY/多表關聯)

解法：主庫臨時改 SET GLOBAL binlog_format=ROW 繞過語句級重放慢 SQL；長期優化 SQL 或加索引。

3. 主庫大事務導致從庫延遲

特徵：binlog 中單事務巨大 (幾百 MB 甚至 GB)。

解法：業務端拆分大事務，引入緩存/異步寫入，減少長事務鎖持有時間。

4. 主庫寫入高頻，從庫單線程追不上

方案優先級排序：

1. 升級硬件：SSD/PCIe SSD (IOPS 提升百倍)、大內存 (增大 Buffer Pool)。
2. 文件系統：XFS/ReiserFS 優於 ext3/ext4 高併發穩定性。
3. RAID 級別：RAID 10 > RAID 5；全 SSD 可考慮 RAID 1/5/50 + 熱備盤。
4. RAID 寫緩存策略：WB (Write Back) / Force WB。
5. I/O 調度器：HDD 用 deadline，SSD 用 noop，禁用默認 cfq。

5. 大量 MyISAM 表導致備份鎖表阻塞 SQL 線程

根因：xtrabackup 末尾 FLUSH TABLES WITH READ LOCK 鎖住 MyISAM，阻塞 SQL 線程。

根治：將 MyISAM 表轉為 InnoDB (ALTER TABLE t ENGINE=InnoDB)。

三、遷移到 MariaDB 獲得真正並行復制

MariaDB 實現了基於組提交的並行復制，同一表不同行可並行重放，效果遠優於 Oracle MySQL 5.6 的庫級並行 (slave_parallel_workers)。實踐中 MariaDB 做從庫幾乎零延遲。

四、應用層優化 (治本)

• 前端緩存/隊列合併寫請求。
• 分庫分表降低單庫壓力。
• 業務設計規避跨庫事務與熱點行更新。