MySQL 讀寫分流一致性：安全路由與寫後讀主庫

MySQL 讀寫分流不是把所有 SELECT 丟到副本就完成。真正困難的是辨識哪些查詢可以容忍延遲、哪些讀取會成為後續決策的權威值，以及路由失敗時能否回到保守且可觀察的主庫路徑。若只追求分散查詢流量，卻沒有明確的一致性邊界，系統可能回傳舊資料、把舊狀態寫入快取，甚至讓鎖定查詢失去原本語意。

本文聚焦 MySQL 讀寫分流 的正確性設計：預設保守、可一鍵關閉、只有可證明的純讀才進副本，寫入後的權威讀回固定走主庫。內容不假設特定公司架構，也不使用任何真實連線資訊；驗證採用隔離的 .NET 10 路由器模型，不連線至資料庫，不修改既有資料。

MySQL 讀寫分流的核心邊界是什麼？

安全的 MySQL 讀寫分流應把主庫視為權威來源，把副本視為可接受延遲的讀取選項；任何寫入、鎖定、判定、撤銷或寫後權威讀回，都應明確留在主庫。

MySQL 官方文件指出，傳統來源端到副本的複寫預設是非同步。即使使用半同步機制，來源端收到的確認也不代表交易已在副本套用完成；確認可能只代表事件已被接收並記錄。因此，「寫入剛成功」與「副本現在一定讀得到」是兩件不同的事。路由器不能把網路回應成功誤當成跨節點的立即可見性保證。

這個邊界首先影響登入狀態、一次性驗證資料、權限判定、庫存或配額扣減、狀態轉換結果，以及任何會觸發後續寫入的查詢。它們不是一般報表讀取，而是控制流程的一部分。若讀到舊值，系統可能做出與主庫現況相反的決策。此時即使查詢語句是 SELECT，也不代表它適合副本。

第二個影響是快取與事件。常見流程是「寫入主庫、讀回完整物件、更新快取、發出事件」。如果中間的讀回改走副本，舊物件可能被標示成最新版，並透過快取或訊息向外擴散。問題不再只是某個畫面短暫顯示舊資料，而是落後快照被升格為權威狀態。判斷標準應是資料用途，而不是 SQL 動詞本身。

第三個影響是資料庫鎖定語意。SELECT ... FOR UPDATE 雖以 SELECT 開頭，卻是鎖定讀取，目的通常是保護接續更新所依賴的資料列。MySQL 的 InnoDB 文件將它歸在 locking reads；把它導到副本會失去與主庫寫入相同的交易邊界。因此，路由規則必須辨識完整片語，而不能只檢查第一個單字。

如何建立 fail-safe 的查詢路由規則？

路由器應採白名單而非猜測：只有功能已啟用、原連線確定是主庫、語句以允許的唯讀動詞開始，且全文沒有寫入、鎖定或程序呼叫訊號時，才選擇副本。

範例限制：下方正規表示式只是教學模型，不是可投入正式環境的 SQL 安全分類器。 動詞白名單加上關鍵字排除，仍不能證明任意 SQL 沒有副作用；例如 FOR SHARE、多語句、函式呼叫、工作階段狀態與交易範圍都需要額外處理。正式路由應以經審核的查詢識別碼／固定模板及明示一致性需求為主要契約，必要時採用理解 MySQL 語法的解析器，未知情況留在原連線。交易內的命令不可任意切換連線。範例沒有實作這些正式環境控制。

第一層是總開關。Enabled=false 必須完全保留原行為，讓部署可以先加入程式碼而不改變資料路徑，也讓異常時能快速收斂回單一權威來源。這不是把錯誤藏起來，而是建立可預期的降級方式。若關閉後仍有部分 helper 偷走副本，開關便失去操作價值。

第二層是來源連線限制。只有原本指向指定主庫的連線，才允許被改寫成讀取端；其他資料庫、維運資料源或特殊連線應保持原狀。否則一個全域字串替換可能把完全不同用途的連線送往錯誤伺服器。路由函式最好接收結構化設定，而不是在任意文字上做模糊取代。

第三層是語句分類。可考慮的開頭通常包含 SELECT、SHOW、DESC、DESCRIBE、EXPLAIN，但仍需排除 INSERT、UPDATE、DELETE、REPLACE、CALL、LOCK、UNLOCK、LAST_INSERT_ID 與 FOR UPDATE 等訊號。比對必須使用單字或片語邊界。若用簡單的 Contains("UPDATE")，欄位名稱 updatetime 會被誤判；反過來，只看開頭則會錯放 SELECT ... FOR UPDATE。

第四層是預存程序。程序名稱本身通常不能證明只讀，因為內部可能包含寫入、動態 SQL、交易控制或鎖定。保守做法是預設主庫，只有經過來源碼盤點與測試的程序才加入唯讀白名單。若無法取得可靠定義，寧可犧牲部分分流比例，也不要把未知副作用導到副本。

第五層是取消與錯誤分類。查詢被取消不等於副本故障，應保留取消語意；可重試的連線或讀取失敗，才考慮在明確範圍內改走主庫一次。若任何例外都重試，使用者取消、逾時政策與系統關閉訊號可能被吞掉，導致請求執行時間反而加倍。

為什麼 read_only 不能單獨證明副本安全？

read_only 與 super_read_only 是重要的防護設定，但它們不是拓撲、資料新鮮度或路由正確性的完整證明；部署前仍要分別驗證角色、限制值與複寫狀態。

MySQL 文件說明，super_read_only 啟用時會一併啟用 read_only，停用 read_only 也會停用 super_read_only。文件同時描述部分具高權限的工作仍可能不受一般唯讀限制。這表示應用程式不能只看到主機被稱為 replica，就推論所有寫入必然失敗；也不能只看到某個唯讀變數，就推論該節點資料一定新鮮。

安全驗收至少要把三個問題拆開。第一，節點在拓撲上是否真的接收來源端事件。第二，唯讀限制是否符合預期，應用程式使用的帳號是否可能繞過。第三，已接收的交易是否已套用到查詢可見的位置。三者答案可能不同，任何一個布林值都不足以取代完整判斷。

這也說明為什麼測試環境不能用「看起來像副本」的共用資料庫做破壞性探針。若設定與預期不一致，測試寫入可能真的落地並參與複寫。本文的重現刻意只測純函式路由決策，不向任何 MySQL 節點送出語句。實際環境驗收應由授權人員在隔離 sandbox 使用合成資料完成，並先建立可回復邊界。

監控上也不應只收集路由命中率。較有意義的指標包括：副本讀取失敗後回主庫的次數、被判定為權威讀而留主庫的比例、未知程序被保守攔截的數量、複寫套用延遲，以及寫後讀不一致的告警。分流率高不是成功定義；一致性錯誤為零且降級可控，才是底線。

寫後讀與快取更新應如何設計？

只要讀回結果會更新快取、發出事件、決定下一次寫入或作為 API 的權威回應，寫入後的那次讀取就應固定在主庫，並與一般讀取路徑清楚分離。

最直接的設計是提供顯式範圍，例如 UsePrimaryDb()、ReadConsistency.Authoritative 或獨立的 GetPrimaryDb()。它的價值不是縮短程式碼，而是讓 code review 能一眼看見一致性選擇。若路由完全隱藏在底層連線字串替換中，上層很難表達「這次 SELECT 不能延遲」。

寫入方法也不應在結尾偷偷呼叫一般 GetById()，因為一般方法可能預設走副本。較安全的做法是把權威讀回做成具名方法，或讓查詢接收一致性參數。名稱要描述資料承諾，而不只是資料來源，例如 LoadAuthoritativeSnapshotAsync 比 LoadAgainAsync 更能提醒維護者它不能被任意改路由。

若系統採用快取，更新順序必須保持清楚：主庫寫入成功、從主庫取得權威快照、驗證版本或併發條件、再更新快取。不要先提高版本戳，再把可能落後的副本物件寫入。版本號只能證明這次發布較新，不能自動證明承載內容也是最新。

主庫路由仍不是所有併發問題的解法。已存在的交易快照不會因選擇主庫就自動更新；需要讀到已提交狀態時，應明確安排提交與後續讀取的交易邊界。鎖定讀需要適當交易範圍；在主庫取得快照後，較慢的發布者仍可能覆蓋較新的快取值，因此版本檢查與快取寫入必須具備相應的原子條件更新或排序機制，而不是先檢查、再無條件寫入。

回退策略同樣要有限制。一般可容忍延遲的讀取若在副本失敗，可以記錄原因後改走主庫一次；兩邊都失敗時應回傳明確錯誤，而不是把空集合偽裝成「查無資料」。空資料與查詢失敗在商業語意上完全不同，混在一起會讓監控失去作用，也可能觸發錯誤的清理或初始化流程。

若查詢成本很高，副本失敗後立刻讓大量請求同時回主庫，可能形成第二次壓力尖峰。可以搭配有界併發、短時間熔斷或單航班機制，避免所有請求同時重查；但這些控制不能改變資料正確性原則。它們只管理退路的流量，不應把失敗資料改造成成功回應。

驗證與重現：用 .NET 10 測試路由邊界

這個隔離測試固定使用 .NET SDK 10.0.400，只驗證 SQL 分類與權威讀規則；排程環境已實際執行，沒有連線資料庫，也沒有建立、更新或刪除任何資料。

1. 建立空白目錄，加入 global.json，把 SDK 固定為 10.0.400，並建立目標框架為 net10.0 的主控台專案。
2. 將下列程式存成 Program.cs；它只處理合成 SQL 字串，沒有連線字串與資料庫套件。
3. 執行 dotnet --version，確認輸出為 10.0.400，再執行 dotnet run -c Release --nologo。
4. 只有四個 PASS 與最後的 ALL PASS 全部出現、程序結束碼為零才算通過；任何例外、缺少標記或不同 SDK 都算失敗。

global.json

{
  "sdk": {
    "version": "10.0.400",
    "rollForward": "disable"
  }
}

ReadRoutingLab.csproj

  
    Exe
    net10.0
    enable
    enable
    true
  

Program.cs

using System.Text.RegularExpressions;

enum DatabaseTarget { Primary, Replica }

static class ReadWriteRouter
{
    private static readonly Regex LeadingReadVerb = new(
        @"^\s*(SELECT|SHOW|DESC|DESCRIBE|EXPLAIN)\b",
        RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);

    private static readonly Regex UnsafeToken = new(
        @"\b(INSERT|UPDATE|DELETE|REPLACE|CALL|LOCK|UNLOCK|LAST_INSERT_ID)\b|\bFOR\s+UPDATE\b",
        RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);

    public static DatabaseTarget Route(string sql, bool enabled, bool sourceIsPrimary)
    {
        if (!enabled || !sourceIsPrimary) return DatabaseTarget.Primary;
        return LeadingReadVerb.IsMatch(sql) && !UnsafeToken.IsMatch(sql)
            ? DatabaseTarget.Replica
            : DatabaseTarget.Primary;
    }

    public static DatabaseTarget AuthoritativeReadAfterWrite() => DatabaseTarget.Primary;
}

static class Check
{
    public static void Equal(T expected, T actual, string name) where T : notnull
    {
        if (!EqualityComparer.Default.Equals(expected, actual))
            throw new InvalidOperationException($"FAIL: {name}; expected={expected}; actual={actual}");
        Console.WriteLine($"PASS: {name}");
    }
}

static class Program
{
    public static void Main()
    {
        Check.Equal(DatabaseTarget.Replica,
            ReadWriteRouter.Route("SELECT updatetime FROM sample WHERE id = 7", enabled: true, sourceIsPrimary: true),
            "identifier containing update is not mistaken for UPDATE");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.Route("SELECT id FROM sample WHERE id = 7 FOR UPDATE", enabled: true, sourceIsPrimary: true),
            "locking read stays on primary");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.Route("SELECT id FROM sample", enabled: false, sourceIsPrimary: true),
            "disabled routing preserves primary behavior");

        Check.Equal(DatabaseTarget.Primary,
            ReadWriteRouter.AuthoritativeReadAfterWrite(),
            "authoritative read after write uses primary");

        Console.WriteLine("ALL PASS");
    }
}

預期輸出（本次 Editor 重跑亦符合）：

PASS: identifier containing update is not mistaken for UPDATE
PASS: locking read stays on primary
PASS: disabled routing preserves primary behavior
PASS: authoritative read after write uses primary
ALL PASS

這是刻意縮小範圍的單元模型。sourceIsPrimary=false 回傳 Primary 並不代表可以把其他連線改指主庫：呼叫端必須先排除非主庫來源並保留原連線，本範例沒有該整合層。AuthoritativeReadAfterWrite() 只是固定選擇主庫的政策示範；測試沒有真的寫入、讀回或檢查資料新鮮度。四項通過不能證明所有鎖定語法都被攔截，也不能證明完整應用程式的一致性。

本次真實執行結果為 SDK 10.0.400，並依序通過「欄位名稱含 update 不誤判」、「FOR UPDATE 留主庫」、「關閉分流維持主庫」與「寫後權威讀使用主庫」四項檢查，最後輸出 ALL PASS。這是路由分類的單元層證據，不是 MySQL 複寫延遲、吞吐量或正式流量安全性的證明。

完整資料庫驗收仍需在隔離環境增加整合測試：建立合成資料、刻意暫停套用或製造可控延遲、確認一般讀可觀察舊值、確認權威讀固定取得主庫值，再清除 sandbox。由於排程環境沒有 Docker，此資料庫層測試屬讀者可重現步驟，排程環境未執行；本文不捏造延遲數字或效能結果。

上線前檢查清單與結論

可維護的 MySQL 讀寫分流不是追求最高副本命中率，而是讓每一種讀取都有明確的一致性承諾、可關閉的路由開關、可觀察的回退結果與不會誤導下游的失敗語意。

上線前應確認：總開關關閉時所有請求維持原路徑；只有指定主庫連線可改寫；SQL 分類使用單字與片語邊界；鎖定讀、程序、判定與寫後讀留在主庫；副本失敗只重試一次且不吞取消；空結果不冒充故障；快取只接收主庫權威快照；監控能分辨副本命中、主庫保留、回退與雙端失敗。

還要確認文件與程式碼一致。若設定變更需要完整重啟，就要把它寫進發布手冊；若程序白名單需要審查，就要記錄審查依據；若某些查詢接受最終一致性，也要說明可接受的使用情境。沒有文件的一致性例外，通常會在下一次重構中被當成多餘程式碼刪除。

最後，不要把「副本可查詢」當成「副本可承擔所有讀取」。正確作法是先分類資料用途，再選擇資料來源：報表、列表與可容忍短暫延遲的查詢可以評估副本；登入、授權、鎖定、撤銷、寫後讀、快取發布與狀態決策則回到主庫。這條邊界能把效能優化限制在不破壞正確性的範圍內。

官方文件與查核範圍

半同步確認的界線依據複寫文件；鎖定與交易需求依據 InnoDB 文件。唯讀變數的權限例外與連動關係應查閱 Server System Variables，而非只依賴副本啟動選項頁。read_only 對具 CONNECTION_ADMIN 或舊式 SUPER 權限的用戶有例外；super_read_only 進一步限制此類客戶端更新，但複寫執行緒仍可套用資料。

• MySQL 8.4: Semisynchronous Replication
• MySQL 8.4: InnoDB Locking Reads
• MySQL 8.4: read_only
• MySQL 8.4: super_read_only