一、前言

MySQL 高可用架構中最基礎、最為核心的內容：MySQL 復制（Replication），數據庫復制本質上就是數據同步。MySQL 數據庫是基于二進制日志（binary log）進行數據增量同步，而二進制日志記錄了所有對于MySQL 數據庫的修改操作。

很多時候我們會發現，MySQL 的主從復制會存在主從數據延遲的問題，甚至會導致讀寫分離架構設計在業務層出現較為嚴重的問題，比如遲遲無法讀取到主庫已經插入的數據。

所以本文，我們就如何從數據庫設計避免這個令人頭疼的問題。

二、延遲的原因

MySQL 復制基于的二進制日志是一種邏輯日志，其寫入的是每個事務中已變更的每條記錄的前項、后項。有了每條記錄的變化內容，用戶可以方便地通過分析 MySQL 的二進制日志內容。邏輯日志簡單易懂，方便數據之間的同步，但它的缺點是：事務不能太大，否則會導致二進制日志非常大，一個大事務的提交會非常慢。

假設有個 DELETE 刪除操作，刪除當月數據，由于數據量可能有 1 億條記錄，可能會產生 100G 的二進制日志，則這條 SQL 在提交時需要等待 100G 的二進制日志寫入磁盤，如果二進制日志磁盤每秒寫入速度為 100M/秒，至少要等待 1000 秒才能完成這個事務的提交。

三、大事務處理規范

核心原則：避免大事務（單次操作涉及大量數據或長時間持有鎖），以降低提交延遲、減少主從復制延遲風險，并提升系統整體并發能力。

3.1. 刪除類操作優化設計

（1）優先采用物理拆分替代邏輯刪除

• 適用場景：針對流水表、日志表等歷史數據定期清理需求。
• 規范要求：
  • 設計階段將此類表按時間維度分表（如按月分表）或分區（如按天/月分區），例如：

    -- 分表示例：按月分表（logs_202401、logs_202402...）CREATE TABLE logs_202401 (...); CREATE TABLE logs_202402 (...);-- 分區表示例：按月份范圍分區CREATE TABLE logs (id INT,log_time DATETIME,...) PARTITION BY RANGE (YEAR(log_time)*100 + MONTH(log_time)) (PARTITION p202401 VALUES LESS THAN (202402),PARTITION p202402 VALUES LESS THAN (202403),...);

• 刪除操作直接通過 DROP TABLE（分表）或 ALTER TABLE … DROP PARTITION（分區）實現，二進制日志（binlog）僅記錄一條元數據操作，寫入速度快且不占用大量日志空間。

    -- 分表刪除：直接刪除整月表（瞬時完成）DROP TABLE logs_202312;-- 分區刪除：移除指定月份分區（高效且可快速回收空間）ALTER TABLE logs DROP PARTITION p202312;
  

（2）未分表/分區時的拆分策略（應急方案）

• 若因歷史原因無法分表/分區，需通過小事務分批刪除替代單條大事務 DELETE：

  • 拆分方法：添加 LIMIT 子句限制單次刪除條數（如每次1000條），結合時間范圍條件逐步清理。

    -- 示例：每次刪除2024年1月內1000條記錄，循環執行直至完成DELETE FROM logs WHERE log_time BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59'LIMIT 1000;

• 優勢：

  • 單次事務量小，減少鎖持有時間（避免長時間阻塞其他會話）。
  • 降低二進制日志（binlog）體積，避免因大事務日志過大導致主從同步延遲。
  • 支持并發執行：可通過多線程/多連接分片刪除不同時間范圍或ID段的數據（需確保無重疊），提升清理效率。

3.2. 大事務通用拆分原則

• 核心要求：單次事務操作的數據量需控制在合理范圍內（建議單事務影響行數≤1萬條，具體根據業務負載調整）。

• 典型場景：

  • 批量數據插入/更新/刪除時，通過循環或分片拆分為多個小事務（如每次處理1000~5000條）。
  • 避免在事務中執行耗時操作（如網絡請求、復雜計算），減少鎖持有時間。

• 示例（批量更新拆分）：

  -- 原始大事務（風險高）：一次性更新10萬條記錄
  -- UPDATE orders SET status = 'completed' WHERE create_time < '2024-01-01';-- 拆分后小事務（推薦）：每次更新5000條
  UPDATE orders 
  SET status = 'completed' 
  WHERE create_time < '2024-01-01' 
  LIMIT 5000;-- 循環執行直至受影響行數為0（可通過程序控制）
  

四、數據一致性核對規范

4.1. 主從變更記錄識別方法

• 設計要求：所有業務表必須包含 last_modify_date 字段（或類似的時間戳字段，如 update_time），用于記錄每條數據的最后修改時間（建議默認值為 CURRENT_TIMESTAMP，并通過觸發器或應用邏輯保證更新時自動維護）。

  CREATE TABLE example (id INT PRIMARY KEY,data VARCHAR(255),last_modify_date DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

• 核對流程：

  1. 定位變更記錄：通過 last_modify_date 過濾出主庫上最近一段時間內（如最近1小時/1天）被修改的數據。

    -- 示例：查詢主庫上2024-06-01 00:00:00后更新的所有記錄SELECT * FROM example WHERE last_modify_date >= '2024-06-01 00:00:00';

2. 逐條比對：將主庫查詢結果與從庫對應表的數據進行字段級比對（可通過程序腳本實現），確認關鍵字段（如業務狀態、金額等）是否一致。
3. 異常處理：若發現不一致，需記錄差異詳情并觸發告警，由運維或開發人員排查原因（如主從延遲、業務邏輯錯誤等）。

五、小結

通過以上規范，可有效控制大事務風險，保障MySQL數據庫高可用架構的性能、可用性與數據一致性。