MySQL 存儲引擎全解析：特性、選擇與最佳實踐

MySQL 存儲引擎全解析：特性、選擇與最佳實踐

存儲引擎是 MySQL 核心組件，負責處理不同表類型的 SQL 操作。MySQL 採用可插拔式存儲引擎架構，允許在運行時動態載入/卸載引擎。InnoDB 是 5.6 版起的預設引擎，也是最常用、 Oracle 官方推薦的選擇，除非有特殊場景需求。

主要存儲引擎特性對比

常見引擎適用場景

• InnoDB：預設首選，支援 ACID 交易、行級鎖、外鍵、崩潰恢復，適合高併發、資料完整性要求高的 OLTP 系統。
• MyISAM：表級鎖、不支援交易，適合唯讀或讀多寫少場景（如日誌、報表表），支援全文索引與壓縮表。
• Memory：資料全存記憶體，極速讀寫，適合暫存表、快取、查詢結果暫存；重啟後資料遺失。
• CSV：以逗號分隔文字檔儲存，適合資料匯入匯出交換。
• Archive：高壓縮、僅支援 INSERT/SELECT，適合海量歷史日誌、稽核資料存檔。
• Blackhole：不儲存資料，僅寫入 Binlog 傳遞給 Slave，適用於複寫過濾、效能測試。
• Merge：將多個同結構 MyISAM 表邏輯合併，適合分區日志表管理。
• Federated：透過連線字串存取遠端 MySQL 表，適合跨實例查詢，但效能較差、不支援事務。

引擎設定與切換

建表時指定

CREATE TABLE t1 (id INT) ENGINE = InnoDB;
CREATE TABLE t2 (id INT) ENGINE = CSV;

修改預設引擎

• 啟動參數：--default-storage-engine=InnoDB
• 設定檔：default-storage-engine=InnoDB
• 運行時：SET default_storage_engine=NDBCLUSTER;

轉換現有表引擎

ALTER TABLE t ENGINE = InnoDB;

若指定引擎不可用，MySQL 預設會替換為預設引擎並發出警告；啟用 NO_ENGINE_SUBSTITUTION SQL 模式可改為報錯。

MyISAM 進階特性

• 三檔案儲存：.frm（結構）、.MYD（資料）、.MYI（索引）
• 支援三種儲存格式：靜態（固定長度）、動態（變長欄位）、壓縮（myisampack 產生，唯讀）
• 並發插入：表無空洞時，SELECT 與 INSERT 可並行
• 索引特性：B-tree，支援前綴壓縮，PACK_KEYS 控制壓縮
• 損壞修復：CHECK TABLE / REPAIR TABLE 或離線 myisamchk

Memory 引擎關鍵限制

• 固定長度儲存：VARCHAR 亦按定義長度佔用記憶體
• 不支援 BLOB/TEXT、空間資料類型
• 表大小受 max_heap_table_size 限制（預設 16MB）
• 支援 HASH（預設）與 BTREE 索引，USING 子句指定
• 複寫注意：Master 重啟後 MEMORY 表清空，需用 --init-file 重載資料同步 Slave

CSV 引擎限制

• 不支援索引、分區
• 欄位必須 NOT NULL
• 支援 CHECK TABLE / REPAIR TABLE 自動修復

Archive 引擎特性

• zlib 無損壓縮，插入時壓縮，查詢時解壓
• 僅支援 AUTO_INCREMENT 索引，不支援其他索引
• 無 DELETE/UPDATE/REPLACE，適合唯讀歸檔
• OPTIMIZE TABLE 可進一步壓縮整理

Blackhole 引擎應用場景

• 複寫過濾：Master 寫入 Binlog → Blackhole 偽 Slave 依規則過濾 → 產生新 Binlog 給真實 Slave
• 驗證 Dump 檔案格式、測量 Binlog 負載、排除存儲引擎無關效能瓶頸
• 觸發器限制：僅 INSERT 觸發器生效

Merge 引擎優缺點

優點： 簡化日誌管理、跨磁盤分散大表提升速度、單表修復更快、突破單檔大小限制。
缺點： 僅支援 MyISAM 底表、無法強制唯一性約束、索引查詢較慢（需掃描所有底表）、檔案描述符消耗大、不支援分區與部份 DDL。

Federated 引擎注意事項

• 遠端表必須預先存在，本地僅存結構與連線字串
• 查詢會拉取全表資料再本地過濾，易造成網路與記憶體壓力
• 不支援事務、ALTER TABLE、分區、查詢快取
• 連線字串格式：mysql://user:pass@host:port/db/table 或透過 CREATE SERVER 共用連線定義

插件式架構：動態載入引擎

INSTALL PLUGIN example SONAME 'ha_example.so';
UNINSTALL PLUGIN example;

共享庫需置於 plugin_dir 目錄，操作需 mysql.plugin 表插入權限。卸載前確保無表使用該引擎，否則既有表將無法存取。

選擇建議： 除非有明確理由（如唯讀壓縮存檔用 Archive、跨實例查詢用 Federated），生產環境核心業務表請優先使用 InnoDB，並善用 SHOW ENGINES 確認引擎可用性。