MySQL 行級鎖與頁級鎖優缺點深度解析：InnoDB vs BDB、死鎖機制、表鎖優勢場景

前言

InnoDB 採行級鎖，BDB 採頁級鎖。本文深度解析兩者優缺點、死鎖成因、表鎖優勢場景、版本控制（MVCC）與並發插入機制。

鎖粒度對比

兩者皆可能產生死鎖，因鎖獲取非在事務開始時一次性完成。

行級鎖優點

• 多執行緒存取不同列時減少衝突。
• 事務回滾時變更資料較少。
• 支援長時間鎖定單列（如複雜計算）。

行級鎖缺點

• 比頁級/表鎖消耗更多記憶體（鎖結構開銷）。
• 大量表操作時較慢（需請求大量鎖資源）。
• 頻繁 GROUP BY/全表掃描場景明顯劣於粗粒度鎖。

表鎖優勢場景（MyISAM/MEMORY）

1. 絕大多數操作為讀取。
2. 嚴格索引條件下的讀取/更新/刪除（單一索引鍵值定位）。
3. SELECT 與 INSERT 並發（MyISAM 並發插入）。
4. 大量全表掃描/GROUP BY 無寫入操作。

大量 INSERT + SELECT 優化模式

若並發插入不可行，採「暫存表批次合併」：

LOCK TABLES real_table WRITE, insert_table WRITE;
INSERT INTO real_table SELECT * FROM insert_table;
TRUNCATE TABLE insert_table;
UNLOCK TABLES;

MVCC 與並發插入（InnoDB 特有）

• 版本控制： 依存取時間點提供一致性視圖（Time Travel / Copy-on-write）。
• 並發插入： 讀取不阻塞寫入，寫入不阻塞讀取，極大提升並發吞吐。

相關延伸閱讀

• 隨機獲取 MySQL 資料方法
• 依據 MySQL 表備份
• MySQL 複製表結構問題解決方法
• MySQL 清空表實現方法
• MySQL 效能測試工具