MySQL 存儲引擎深度解析：ISAM/MyISAM/HEAP/InnoDB/BDB 特性對比與選型指南

引擎架構：插件式存儲引擎

MySQL 通過插件式架構支持多種存儲引擎，CREATE TABLE ... ENGINE=xxx 表級指定。引擎決定：索引結構、鎖粒度、事務支持、崩潰恢復、壓縮、全文搜索等核心能力。

五大經典引擎特性對比

核心差異深度解析

1. ISAM：遠古產物

• 優點：讀快、佔用資源少
• 致命缺陷：無事務、無崩潰恢復 (硬盤壞 = 數據全丟)、表級鎖
• 現狀：**已從 MySQL 移除**，勿用

2. MyISAM：曾經的默認王者

• 表級鎖：讀鎖共享、寫鎖獨佔，**讀寫互斥、寫寫互斥**，高併發寫入性能極差
• OPTIMIZE TABLE 碎片整理、myisamchk 修復工具
• 支持壓縮表 (myisampack)、全文索引 (5.6 前)
• 非事務：INSERT 失敗中途已寫入數據無法回滾
• 現狀：非核心場景 (日志、報表、只讀歸檔) 仍可用，**核心業務禁用**

3. MEMORY (HEAP)：極速易失

• 數據純內存，Hash 索引 O(1) 查找，**極快**
• 重啟/崩潰 **數據全丟**，結構保留
• 表級鎖，不支持 BLOB/TEXT、AUTO_INCREMENT (早期版本)
• 用途：臨時中間結果、緩存表、字典查找、**數據來源可重建**

4. InnoDB：現代王者 (5.5+ 默認)

• **聚簇索引** : 主鍵即數據順序，主鍵查找極快 (無回表)
• **MVCC** : 多版本併發控制，讀不加鎖，**讀寫不阻塞**
• **行級鎖** : 高併發寫入吞吐強
• **崩潰恢復** : Redo Log (重做) + Undo Log (回滾) + Doublewrite Buffer，ACID 鐵三角
• 自適應哈希索引 (AHI)、Change Buffer、預讀優化
• 表空間管理：共享表空間 / 獨立表空間 (innodb_file_per_table=ON 推薦)

5. BDB：歷史過渡者

• Sleepycat 公司產品，頁級鎖、事務支持
• MySQL 5.1 移除，Oracle 收購 Sleepycat 後整合進 Oracle DB

引擎選型決策樹

1. 需事務/外鍵/行級鎖/崩潰恢復？ → InnoDB (95% 場景)
2. 純只讀、全文搜索(舊版)、歸檔不變數據？ → MyISAM
3. 臨時中間表、會話級緩存、數據可重建？ → MEMORY
4. 其他引擎：Archive (海量日志歸檔)、CSV (數據交換)、NDB Cluster (分佈式高可用)

引擎轉換操作

-- 單表轉換 (鎖表重建，大表慎用！生產用 pt-online-schema-change)
ALTER TABLE tbl_name ENGINE = InnoDB;

-- 批量轉換腳本
SELECT CONCAT('ALTER TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ' ENGINE=InnoDB;')
FROM information_schema.TABLES
WHERE ENGINE = 'MyISAM' AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys');

自定義引擎：MySQL++ API (了解即可)

MySQL 提供 C++ API 開發自定義存儲引擎，可實現 XML 存儲、HTTP 接口、加密存儲等特殊需求。但開發成本極高，維護負擔重，**極少見生產使用**。