MySQL 架構深度解析：邏輯架構、並發控制、事務與存儲引擎

MySQL 架構深度解析：邏輯架構、並發控制、事務與存儲引擎

基於《MySQL 權威指南》與《高性能 MySQL》整理的系統筆記，涵蓋邏輯分層、鎖機制、事務 ACID、隔離級別、MVCC、主流存儲引擎對比及引擎轉換實操。

一、邏輯架構三層模型

1. 連接層：處理客戶端連接、認證、安全、線程池化——非 MySQL 獨有。
2. 核心服務層：查詢解析、優化、快取、內建函數、存儲過程、觸發器、視圖——所有跨引擎功能在此。
3. 存儲引擎層：負責數據存取、索引維護、鎖實現——插件式架構，支援 InnoDB、MyISAM、Memory 等。

二、並發控制

1. 鎖類型

• 共享鎖（S 鎖/讀鎖）：兼容，多事務可同時讀同一資源。
• 排他鎖（X 鎖/寫鎖）：互斥，阻塞其他鎖請求。

2. 鎖粒度與策略

鎖策略是在鎖開銷與數據安全性間尋找平衡。InnoDB 預設行鎖，僅對索引記錄加鎖；無索引條件會退化為表鎖。

三、事務與 ACID

隔離級別

死鎖與檢測

• 原因：不同順序鎖定資源、並發鎖定同一資源。
• InnoDB 策略：自動檢測循環等待，回滾持有較少行鎖的事務；或等待 innodb_lock_wait_timeout 超時回滾。

事務日誌（WAL）

修改先寫 Redo Log（循環寫入）、再刷盤，崩潰恢復時重放 Redo Log 保證持久性。

四、MVCC 多版本並發控制

InnoDB 每行隱藏兩列：trx_id（建立版本）、roll_pointer（刪除版本/Undo Log 指針）。

• SELECT：只讀 trx_id <= 當前事務版本 且 刪除版本未定義 或 > 當前版本 的行。
• INSERT：新行 trx_id = 當前版本。
• DELETE：原行標記 刪除版本 = 當前版本。
• UPDATE：新行 trx_id = 當前版本，舊行標記刪除版本。

優點：讀不加鎖、寫僅鎖必要行；缺點：額外存儲、版本鏈遍歷開銷。

僅在 REPEATABLE READ、READ COMMITTED 生效。

五、主流存儲引擎對比

六、引擎轉換三種方法

1. ALTER TABLE：ALTER TABLE t ENGINE=InnoDB; 鎖表、耗時、丟失原引擎特有屬性（如外鍵）。
2. mysqldump 導出導入：修改建表語句引擎選項，需改表名避免衝突。
3. 創建新表 + INSERT SELECT（推薦）：

   CREATE TABLE t_new LIKE t_old;
   ALTER TABLE t_new ENGINE=InnoDB;
   INSERT INTO t_new SELECT * FROM t_old;

   大表分批事務提交：

   START TRANSACTION;
   INSERT INTO t_new SELECT * FROM t_old WHERE id BETWEEN 1 AND 10000;
   COMMIT;

InnoDB 主鍵宜短（如自增整型），因二級索引葉子節點存主鍵值，主鍵過大會膨脹所有索引。