MySQL 存儲引擎全解析：InnoDB vs MyISAM
存儲引擎是 MySQL 核心服務層，負責數據存儲、處理與保護。MySQL 插件式架構允許編譯時選擇引擎，默認支持 ISAM、MyISAM、HEAP、InnoDB、BDB（BerkeleyDB）。
主流引擎特性對比
| 引擎 | 事務 | 鎖粒度 | 外鍵 | 全文索引 | 行數統計 | 適用場景 |
|---|
| MyISAM | ❌ | 表鎖 | ❌ | ✅ (5.6+) | ✅ 實時 O(1) | 讀多寫少、不需事務、全文搜索 |
| InnoDB | ✅ ACID | 行鎖 | ✅ | ✅ (5.6+) | ❌ 掃描表 | 高併發寫入、事務強一致、高可用 |
| Memory (HEAP) | ❌ | 表鎖 | ❌ | ❌ | ✅ | 臨時表、極速查找、數據可丟失 |
InnoDB 深度剖析
ACID 事務模型
- A (Atomicity)：原子性，全做或全不做（如取款劃卡+出鈔）。
- C (Consistency)：一致性，約束狀態轉換前後不變（如 A+B=10）。
- I (Isolation)：隔離性，並發事務互不干擾，4 級隔離級別。
- D (Durability)：持久性，提交後永久生效，不會回滾。
索引結構：聚集索引
- 數據文件即主鍵 B+ 樹，葉子節點 直接存放完整行數據。
- 輔助索引葉子節點存 主鍵值（非地址），查詢需「回表」：輔助索引→主鍵→聚集索引取數據。
- 啟示：主鍵宜短（推薦自增整型），過長主鍵會膨脹所有輔助索引；避免非單調主鍵（如 UUID），導致 B+ 樹頻繁分裂，插入性能劇降。
MyISAM 深度剖析
索引結構：非聚集索引
- 索引文件與數據文件分離，B+ 樹葉子節點存 數據文件物理地址。
- 主索引與輔助索引結構相同，唯一區別在主鍵要求唯一。
- 查詢流程：索引搜索→取地址→讀數據文件。
引擎切換方式
- 配置文件
my.ini：[mysqld] default-storage-engine=InnoDB 重啟生效。
- 建表指定：
CREATE TABLE t (...) ENGINE=MyISAM;
- 修改現有表：
ALTER TABLE t ENGINE=InnoDB;
驗證：SHOW TABLE STATUS FROM db; 或 SHOW CREATE TABLE t; 查看 Engine 字段。
選型決策指南
- 大數據量、高併發寫入、需事務/外鍵/故障恢復 → InnoDB（利用 Redo Log 秒級恢復）。
- 讀多寫少、無事務需求、依賴全文索引、需快速 COUNT(*) → MyISAM。
- 批量 INSERT MyISAM 略快；併發 UPDATE InnoDB 行鎖優勢巨大。
- 主鍵查詢 InnoDB 極快（聚集索引），但主鍵過長反成累贅。
理解引擎索引實現機制，是 SQL 優化與架構選型的基石。InnoDB 聚集索引設計決定了「主鍵短、自增、單調」三大鐵律；MyISAM 非聚集索引適合靜態數據高頻讀取。
