MySQL 實戰踩坑記：幻讀、更新丟失與大偏移分頁優化

前言

在日常開發與運維中，MySQL 的事務隔離、併發更新、大數據分頁等問題屢見不鮮。本文結合實際場景，剖析三個典型坑點的成因與解法，助你寫出更健壯的 SQL。

一、幻讀：Repeatable Read 並非萬能盾

1.1 什麼是幻讀？

幻讀指：事務 A 多次執行相同範圍查詢，事務 B 在中間插入/刪除滿足條件的行並提交，導致事務 A 兩次結果集行數不一致。MySQL 預設隔離級別 REPEATABLE READ（RR）雖通過 Next-Key Lock（記錄鎖+間隙鎖）在加鎖讀（SELECT ... FOR UPDATE / LOCK IN SHARE MODE）下防止幻讀，但快照讀（普通 SELECT）無法阻擋幻讀。

1.2 實測復現

-- 事務 1
BEGIN;
SELECT * FROM t;  -- 返回 10 行

-- 事務 2
INSERT INTO t SELECT ...; COMMIT;  -- 新增 5 行

-- 事務 1 再次查詢
SELECT * FROM t;  -- 仍返回 10 行（MVCC 快照）
-- 但若嘗試插入相同主鍵
INSERT INTO t SELECT ...;  -- Duplicate key error！這就是幻讀的體現

1.3 避坑指南

• 需強一致性場景：使用 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 加鎖讀；
• 可接受最終一致性：業務層面容忍幻讀，或採用樂觀鎖（version 字段）控制；
• 理解 RR ≠ Serializable，別對快照讀抱有幻想。

二、更新丟失：先 SELECT 再 UPDATE 的隱患

2.1 錯誤模式

-- 線程 A
SELECT balance FROM account WHERE id=1;  -- 讀到 100
-- 線程 B
SELECT balance FROM account WHERE id=1;  -- 讀到 100
-- 線程 A
UPDATE account SET balance=100+50 WHERE id=1;  -- 寫入 150
-- 線程 B
UPDATE account SET balance=100+30 WHERE id=1;  -- 覆蓋寫入 130，線程 A 的 +50 丟失

2.2 正確姿勢：單條 SQL 原子更新

UPDATE account SET balance = balance + 50 WHERE id = 1;
-- InnoDB 自動加行鎖（X 鎖），併發安全，無需應用層加鎖

原則：能用單條 UPDATE 解決的，絕不拆成 SELECT + UPDATE。

三、大偏移分頁：LIMIT 100000, 10 的性能陷阱

3.1 為什麼慢？

MySQL 必須掃描前 100,000 行並丟棄，再取 10 行。偏移量越大，掃描成本線性增長，本質是全表掃描。

3.2 三種優化方案

3.3 實戰建議

• 禁止大偏移 LIMIT，強制業務改用游標或搜索條件縮小範圍；
• 若必須跳頁，評估延遲關聯方案，確保子查詢走索引；
• 分庫分表後，分頁更複雜，建議引入 ES 或專用搜索服務。

總結

幻讀源於快照讀無鎖，更新丟失源於非原子讀改寫，大偏移分頁源於全掃機制。理解底層原理，才能在業務代碼中寫出經得起併發考驗的 SQL。