MySQL存儲引擎深度解析：MyISAM、InnoDB、MEMORY、MERGE特性全覽

MySQL存儲引擎深度解析：MyISAM、InnoDB、MEMORY、MERGE特性全覽

MySQL 5.5+ 默認採用 InnoDB 作為默認存儲引擎。存儲引擎決定了表的存儲方式、索引結構、鎖機制、事務支持等核心行為。本文系統梳理四大主流引擎的特性、適用場景及關鍵參數。

查看與設置引擎

• 查看默認引擎：SHOW VARIABLES LIKE 'default_storage_engine';
• 查看支持引擎：SHOW ENGINES; 或 SELECT * FROM INFORMATION_SCHEMA.ENGINES;
• 創建/修改表指定引擎：CREATE TABLE t (id INT) ENGINE=InnoDB; / ALTER TABLE t ENGINE=InnoDB;
• 查看表引擎：SHOW TABLE STATUS FROM db_name;

一、MyISAM —— 傳統高性能非事務引擎

核心特性

• 不支持事務、外鍵、行級鎖（表級鎖）。
• 讀取速度極快，適合以 SELECT、INSERT 為主、對事務完整性無要求的場景。
• 磁盤存儲三文件：.frm（定義）、.MYD（數據）、.MYI（索引）。
• 支持數據/索引文件分離存放（DATA DIRECTORY/INDEX DIRECTORY），均衡 I/O。
• 表狀態標誌：標記是否正常關閉，崩潰後可自動檢查修復（啟動參數 --myisam-recover）。

三種存儲格式

二、InnoDB —— 事務安全首選引擎

核心優勢

• 完整 ACID 事務支持：提交、回滾、崩潰恢復。
• 行級鎖 + MVCC，高併發性能優異。
• 外鍵約束支持（唯一支持外鍵的內建引擎）。
• 聚簇索引：數據按主鍵順序存儲，主鍵查詢極快。

關鍵特性詳解

1. 自增列： 插入 0/NULL 觸發自增；ALTER TABLE ... AUTO_INCREMENT=n 設置起始值（重啟失效，保存在內存）；LAST_INSERT_ID() 獲取最後自增值。注意： 自增列必須為索引（或組合索引首列）。
2. 外鍵約束： 父表需有索引，子表自動創建索引。支持 ON DELETE/UPDATE {RESTRICT|CASCADE|SET NULL|NO ACTION}。可用 SET foreign_key_checks=0/1 臨時關閉/開啟檢查。
3. 寫前日志 (WAL) / Redo Log / Undo Log： 保證持久性與回滾。

三、MEMORY (HEAP) —— 內存級極速引擎

特性

• 數據駐留內存，.frm 文件存元數據。服務重啟數據丟失，結構保留。
• 默認 HASH 索引：等值查詢 (=, <=>) 極快，不支持範圍查詢 (<, >, BETWEEN, ORDER BY)。可指定 USING BTREE。
• 固定行長格式：不支持 BLOB/TEXT；VARCHAR 內部按 CHAR 處理。
• 表大小受 max_heap_table_size (默認 16MB) 限制，可用 MAX_ROWS 指定行數上限。

典型用法

-- 創建內存表並從現有表導入
CREATE TABLE tmp_mem ENGINE=MEMORY SELECT id, name FROM users;

-- 指定 BTREE 索引支持範圍查詢
CREATE INDEX idx_city ON tmp_mem (city_id) USING BTREE;

-- 服務啟動自動預熱數據（配置 --init-file）

四、MERGE (MRG_MyISAM) —— MyISAM 表聚合視圖

• 將多個結構完全相同的 MyISAM 表聚合為一個邏輯表。
• 無自身數據，操作轉發給底層 MyISAM 表。
• INSERT_METHOD={FIRST|LAST|NO} 控制插入目標表。
• DROP TABLE 僅刪除定義，不影響底表。
• 磁盤文件：.frm (定義)、.MRG (底表列表及插入策略)。可直接編輯 .MRG 後 FLUSH TABLES 生效。

CREATE TABLE man_all (id INT, name VARCHAR(20)) ENGINE=MERGE UNION=(man1,man2) INSERT_METHOD=LAST;

選型建議

理解各引擎特性，結合業務場景選型，是 MySQL 架構設計的關鍵一環。