本文 AI 產出，尚未審核

MySQL 高可用架構：讀寫分離概念

簡介

在現代的 Web / 服務平台中，資料庫往往是系統的瓶頸。隨著使用者數量與交易量持續成長，單一 MySQL 節點難以同時滿足 高吞吐量 與 低延遲 的需求。此時，讀寫分離（Read‑Write Splitting）成為提升可用性與效能的關鍵技巧。

讀寫分離的核心理念是把 寫入（INSERT、UPDATE、DELETE） 的工作指派給一台（或多台）主節點（master），而 查詢（SELECT） 的工作則分散到多台從節點（slave）上。這樣不僅可以減少主節點的壓力，還能透過從節點的水平擴展（scale‑out）來應對大量的讀取需求。

本篇文章將以 淺顯易懂 的方式說明讀寫分離的概念、實作方式、常見陷阱與最佳實踐，並提供 實務範例，協助您從入門到在專案中落地。

核心概念

1. 什麼是讀寫分離

  • 寫入：所有會改變資料的 SQL（INSERT、UPDATE、DELETE、REPLACE）必須送到 主節點。
  • 讀取：大多數的 SELECT 除了需要即時一致性的查詢外，都可以由 從節點 處理。
  • 一致性模型：MySQL 複製採用 異步複製（Asynchronous Replication）為主，寫入後資料會在稍後同步到從節點，這會產生 讀取延遲（replication lag）。若業務需要即時一致性，需額外設計「讀寫路由」的例外處理。

重點：讀寫分離的目的不是「完全避免寫入到從節點」，而是 合理分配 工作負載，使系統在 吞吐量、可用性 與 成本 之間取得平衡。

2. 主從架構的運作原理

  1. 設定主節點：在 my.cnf 中開啟 binary log（log-bin）與 server‑id。
  2. 設定從節點：在 my.cnf 中設定唯一的 server-id、relay-log，並使用 CHANGE MASTER TO 指令告訴從節點要從哪個主節點拉取 binlog。
  3. 啟動複製：在從節點執行 START SLAVE;，MySQL 會自動把主節點的 binlog 讀取下來，寫入自己的 relay‑log，然後套用到本地資料庫。
-- 主節點 (master) my.cnf 片段
[mysqld]
log-bin=mysql-bin
server-id=1
binlog-format=row

-- 從節點 (slave) 設定與啟動
CHANGE MASTER TO
    MASTER_HOST='192.168.10.10',
    MASTER_USER='repl',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=  154;
START SLAVE;
SHOW SLAVE STATUS\G

3. 常見的實作方式

實作層級 方式說明 優點 缺點
應用程式層 在程式碼裡自行判斷 SQL 類型，分別連到 master / slave。 完全掌控路由邏輯，可加入自訂的 fallback。 需要在每個服務中重複實作，維護成本較高。
中介層（Proxy） 使用 ProxySQL、MaxScale、MySQL Router 等代理層自動分流。 集中管理、即時調整權重、支援故障轉移。 增加一層網路延遲，需額外部署與維護。
雲端服務 利用 RDS、Aurora、CloudSQL 等雲端提供的讀寫分離端點。 雲端自動管理、彈性伸縮。 受限於供應商功能，成本較高。

下面提供 5 個實用範例，示範在不同層級如何實作讀寫分離。

範例 1：PHP PDO 手寫路由

<?php
// config.php
$masterDsn = 'mysql:host=10.0.0.1;dbname=app';
$slaveDsn  = 'mysql:host=10.0.0.2;dbname=app';
$user = 'app_user';
$pass = 'secret';

// 取得 PDO 連線 (寫入使用 master，讀取使用 slave)
function getConnection($write = false) {
    global $masterDsn, $slaveDsn, $user, $pass;
    $dsn = $write ? $masterDsn : $slaveDsn;
    $pdo = new PDO($dsn, $user, $pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    ]);
    return $pdo;
}

// 寫入範例
$pdo = getConnection(true);               // 取得 master 連線
$pdo->exec("INSERT INTO orders (user_id, amount) VALUES (123, 99.9)");

// 讀取範例
$pdo = getConnection();                   // 取得 slave 連線
$stmt = $pdo->query("SELECT * FROM orders WHERE user_id = 123");
$order = $stmt->fetch();
print_r($order);
?>

說明：getConnection(true) 代表必須走寫入路徑，否則預設走讀取（slave）。若在同一請求內同時需要寫入與讀取，可自行在寫入後切換至 master，以避免 replication lag 帶來的讀取不一致。

範例 2：ProxySQL 設定

-- 1. 新增 backend 伺服器 (master)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (10, '10.0.0.1', 3306, 1000);

-- 2. 新增 backend 伺服器 (slave)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight)
VALUES (20, '10.0.0.2', 3306, 1000);

-- 3. 設定 query_rules，將寫入指向 hostgroup 10，讀取指向 hostgroup 20
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
(1, 1, '^SELECT', 20, 1),    -- 所有 SELECT 走 slave
(2, 1, '^(INSERT|UPDATE|DELETE|REPLACE)', 10, 1); -- 寫入走 master

-- 4. 載入設定
LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

說明：ProxySQL 透過 hostgroup 把不同類型的查詢路由到對應的資料庫。只要改變 weight 或 max_connections，即可在不中斷服務的情況下調整負載。

範例 3：MySQL Router (MySQL 8.0)

# 建立一個讀寫分離的路由器
mysqlrouter --bootstrap root@master-host:3306 \
    --directory /opt/mysqlrouter \
    --conf-use-env \
    --force

# 產生的 router.conf 會包含以下設定
# [routing:read_write_split]
# bind_address = 0.0.0.0:7001
# mode = read-write-splitting
# destinations = master=master-host:3306, slaves=slave1:3306,slave2:3306

說明：MySQL Router 內建 read-write-splitting 模式，只要在 destinations 中列出主從節點，即可自動完成路由。適合在 Kubernetes 或 Docker 環境中以 side‑car 方式部署。

範例 4：Python SQLAlchemy + read‑write 分離

from sqlalchemy import create_engine, event
from sqlalchemy.orm import sessionmaker

# Master 與 Slave 的 DSN
MASTER_DSN = "mysql+pymysql://app_user:secret@10.0.0.1/app"
SLAVE_DSN  = "mysql+pymysql://app_user:secret@10.0.0.2/app"

# 建立兩個 Engine
master_engine = create_engine(MASTER_DSN, pool_pre_ping=True)
slave_engine  = create_engine(SLAVE_DSN,  pool_pre_ping=True)

# 自訂路由規則
def get_engine(conn):
    stmt = conn.statement
    if stmt.strip().upper().startswith("SELECT"):
        return slave_engine
    return master_engine

# 事件：在每次執行前切換 engine
@event.listens_for(master_engine, "before_cursor_execute")
def receive_before_cursor_execute(conn, cursor, statement, parameters, context, executemany):
    conn.engine = get_engine(conn)

Session = sessionmaker(bind=master_engine)   # 預設使用 master，事件會自動切換

# 使用範例
session = Session()
session.execute("INSERT INTO products(name) VALUES ('Widget')")
result = session.execute("SELECT * FROM products")
for row in result:
    print(row)

說明：利用 SQLAlchemy 的事件機制，根據 SQL 字串自動切換到 master 或 slave。這樣的寫法適合 大型 Python 專案，且保留了 ORM 的便利性。

範例 5：檢測 Replication Lag（避免讀舊）

-- 在從節點執行，取得與主節點的時間差
SELECT 
    UNIX_TIMESTAMP(NOW()) - UNIX_TIMESTAMP( 
        (SELECT FROM_UNIXTIME(MAX(timestamp)) FROM mysql.slave_master_info) 
    ) AS replication_lag_seconds;

說明：應用程式在需要即時一致性的情況下（例如下單成功後立即查詢訂單狀態），可以先檢查 replication_lag_seconds 是否在可接受範圍內，若超過門檻則 暫時切回 master 讀取。

常見陷阱與最佳實踐

陷阱 可能產生的問題 建議的最佳實踐
未考慮複製延遲 讀取到舊資料，導致使用者看到錯誤的交易狀態。 在關鍵查詢前檢查 Seconds_Behind_Master，或在寫入後強制回到 master。
寫入路由錯誤 SELECT ... FOR UPDATE、INSERT ... SELECT 等混合型語句被誤導向 slave，造成鎖定失效。 針對含有 FOR UPDATE、LOCK IN SHARE MODE、INSERT ... SELECT 的語句，明確標記為 寫入。
從節點不一致 主從結構不對稱（欄位、索引不同）導致查詢錯誤或效能下降。 保持 schema 同步：使用 pt-table-sync 或 gh-ost 等工具自動同步結構。
單點故障 主節點掛掉後，寫入全部失效，且從節點無法升為主節點。 部署 MHA、Orchestrator 或 Galera 等自動故障轉移機制。
過度分散 從節點過多導致管理成本升高，且每個節點的複製延遲累積。 依照 QPS 與 資料一致性需求 斟酌從節點數量，通常 2~4 台即可。

最佳實踐總結

  1. 先定義一致性需求：即時性 vs. 可接受的延遲。
  2. 在寫入後立即讀回 時，使用 master。
  3. 監控 replication lag（SHOW SLAVE STATUS、performance_schema.replication_applier_status_by_worker）。
  4. 使用 Proxy（如 ProxySQL）集中管理路由與故障切換，減少程式碼重複。
  5. 自動化測試：在 CI 中加入讀寫分離的測試腳本，確保升級不會破壞路由邏輯。

實際應用場景

場景 為何適合讀寫分離 常見的實作方式
電商網站的商品瀏覽 商品頁面流量極大，主要是 SELECT，寫入僅在下單或庫存變更時發生。 ProxySQL + 多台 slave，讀取走最近的地理位置的 slave。
即時分析儀表板 大量聚合查詢（GROUP BY、JOIN）對主庫壓力過大。 使用 MySQL Replication + MySQL Router，將分析報表指向專屬的只讀 replica。
微服務架構的共享資料庫 多個服務同時存取同一套資料，讀寫比例約 80/20。 在每個服務內部使用 ORM（如 SQLAlchemy）加上自訂路由，或在服務間共用 ProxySQL。
雲端 RDS / Aurora 雲端提供的只讀端點天然支援自動擴充。 直接使用雲端的 讀寫分離端點，不自行部署 Proxy。
高可用的金融交易系統 必須保證寫入成功後立即可查，且容忍極低的延遲。 主從雙活（雙主）或 Galera Cluster，配合 同步複製，讀寫分離僅限於非關鍵報表。

總結

讀寫分離是 提升 MySQL 可用性與效能 的核心技巧之一。透過將 寫入 集中於主節點、將 讀取 分散至多個從節點，我們可以：

  • 降低主庫的負載，避免因讀取請求衝擊寫入延遲。
  • 水平擴充：只要再加一台 slave，即可提升整體 QPS。
  • 提升容錯：單一 slave 故障不會影響整體服務，只要路由機制正確即可自動剔除。

實作上，應用層、代理層與雲端服務皆提供了不同的選擇。對於大多數自建環境，建議先以 ProxySQL 為中心，搭配 監控 replication lag 與 自動故障轉移（MHA / Orchestrator），再根據業務需求逐步調整。

最後，讀寫分離不是萬能解，在設計前務必明確了解資料一致性需求、系統的讀寫比例以及可接受的維運成本。只要遵循 最佳實踐、做好 監控與測試，就能讓 MySQL 在高流量與高可用的環境中發揮最大效能。

祝您在 MySQL 高可用之路上順利前行！