MySQL 表設計優化與索引（六）：MVCC 多版本並發控制核心機制、InnoDB 實現細節與隔離級別影響

MVCC：高並發的核心引擎

多版本並發控制（MVCC）是 InnoDB、Falcon、PBXT 等現代儲存引擎實現高並發的關鍵技術，同樣應用於 Oracle、PostgreSQL 等主流資料庫。MVCC 結合行鎖，透過版本鏈與可見性判斷，實現非阻塞讀取（讀不阻塞寫、寫不阻塞讀），僅在必要時對寫操作加鎖，大幅提升並發吞吐量。

InnoDB MVCC 核心資料結構

每行記錄隱藏兩個系統欄位（不佔用使用者定義欄位）：

• DB_TRX_ID (6 bytes)：建立/最後修改該行的事務 ID（遞增）
• DB_ROLL_PTR (7 bytes)：指向 Undo Log 中舊版本記錄的指標，形成版本鏈
• DB_ROW_ID (6 bytes)：若無主鍵/非空唯一鍵，InnoDB 自動生成的單調遞增行 ID

事務版本號與可見性判斷規則（REPEATABLE READ 隔離級別）

事務啟動時獲得當前系統版本號（事務 ID 快照）。查詢時對每行應用以下規則：

SELECT 可見性檢查

1. 版本足夠老：行的 DB_TRX_ID <= 事務版本號。確保行在事務啟動前已存在，或由本事務建立/修改
2. 未被刪除：行的刪除版本號（Undo Log 中標記）未定義 或 > 事務版本號。確保事務啟動前行未被刪除
3. 同時滿足 → 返回該行版本

INSERT 操作

• 新行 DB_TRX_ID = 當前系統版本號
• 無刪除版本號

DELETE 操作

• 在當前行寫入刪除標記，DB_ROLL_PTR 指向舊版本
• 刪除版本號 = 當前系統版本號
• 實際刪除由 Purge 執行緒異步清理

UPDATE 操作

• 視為 DELETE + INSERT 原子操作
• 複製行 → 新行 DB_TRX_ID = 系統版本號、舊行刪除版本號 = 系統版本號
• 版本鏈延伸

隔離級別對 MVCC 的影響

鎖策略與並發性對比

MVCC 代價與優化

• 儲存開銷：每行額外 13+ bytes 系統欄位 + Undo Log 空間
• CPU 開銷：每行可見性判斷需遍歷版本鏈（長鏈時開銷大）
• Purge 壓力：長事務阻礙舊版本清理，導致版本鏈膨脹、緩衝池污染、磁碟空間暴增
• 優化策略：
  • 避免長事務：業務邏輯拆分、批次提交、innodb_max_purge_lag 限流
  • 定期 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 整理碎片
  • 監控 SHOW ENGINE INNODB STATUS\G 中 History list length，超過 10 萬需關注
  • Read Committed + innodb_locks_unsafe_for_binlog=ON（舊版）減少間隙鎖，但可能導致幻讀

實戰驗證 MVCC 行為

-- 會話 1：開啟事務，查詢
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;  -- 余額 1000

-- 會話 2：修改並提交
UPDATE accounts SET balance = 2000 WHERE id = 1;
COMMIT;

-- 會話 1：再次查詢（RR 級別）
SELECT * FROM accounts WHERE id = 1;  -- 仍為 1000，可重複讀

-- 會話 1：提交後再查
COMMIT;
SELECT * FROM accounts WHERE id = 1;  -- 2000

進階：一致性讀 vs 當前讀

• 一致性讀（Consistent Read）：普通 SELECT，使用 MVCC 快照，不加鎖
• 當前讀（Current Read）：讀取最新版本，需加鎖：
  • SELECT ... LOCK IN SHARE MODE（共享鎖）
  • SELECT ... FOR UPDATE（排他鎖）
  • UPDATE、DELETE、INSERT
  • LOCK TABLES

深入理解 MVCC 版本鏈、可見性判斷、Undo Log 生命週期與 Purge 機制，是診斷 InnoDB 長事務問題、歷史列表膨脹、鎖等待風暴的核心鑰匙。將 MVCC 知識內化為設計原則：事務越短越好、讀寫分離減少當前讀、監控 History List Length，才能駕馭高並發 InnoDB 叢集。