查看支持的引擎
SHOW ENGINES;
SHOW VARIABLES LIKE 'have_%';
三大引擎核心對比
| 維度 | InnoDB | MyISAM | Memory (HEAP) |
|---|
| 事務支援 | ✓ ACID | ✗ | ✗ |
| 行級鎖 | ✓ | ✗ (表鎖) | ✗ (表鎖) |
| 外鍵約束 | ✓ | ✗ | ✗ |
| 崩潰恢復 | ✓ Redo/Undo Log | ✗ (需修復) | ✗ (重啟丟失) |
| MVCC 多版本並發 | ✓ | ✗ | ✗ |
| 全文索引 | 5.6+ ✓ | ✓ (傳統優勢) | ✗ |
| 空間索引 | 5.7+ ✓ | ✓ | ✗ |
| 數據存儲 | 共享表空間/獨立 .ibd | .MYD (數據) + .MYI (索引) | 僅內存，.frm 結構文件落盤 |
| 索引結構 | B+ Tree (聚簇主鍵) | B+ Tree (非聚簇) | Hash (默認) / B-Tree |
| 壓縮 | 頁壓縮/表壓縮 | myisampack 僅讀壓縮 | 不支援 |
| 讀性能 | 高 (換頁優化) | 極高 (無事務開銷) | 最高 (純內存) |
| 寫性能 | 高 (行鎖+換頁) | 中 (表鎖爭用) | 高 (但持久化風險) |
| 磁碟佔用 | 較大 (系統表空間+Undo+Redo) | 較小 | 極小 (僅結構) |
| 適用場景 | 核心業務、高併發寫入、事務必須、高可用 | 只讀/少寫、全文搜尋、日誌分析、非關鍵數據 | 臨時表、緩存表、查找表、會話存儲、可丟失數據 |
InnoDB 關鍵特性深度
自增列
- 必須為主鍵或唯一鍵首列。
- 插入 0/NULL/缺省 → 自動分配；指定合法值 → 後續從該值+1 開始。
外鍵
- 子表引用父表主鍵/唯一鍵。
ON UPDATE/DELETE CASCADE|SET NULL|RESTRICT|NO ACTION。
文件結構
.frm 表結構ibdata1 系統表空間 (共享模式) / table_name.ibd (獨立表空間 innodb_file_per_table=1)- Redo Log (
ib_logfile0/1)、Undo Log (系統表空間/獨立 Undo 表空間 5.6+)
MyISAM 存儲格式與優化
三種存儲格式
- 靜態 (Fixed)： 所有列定長 (CHAR, INT 等)。極快，易修復，但浪費空間。
- 動態 (Dynamic)： 含變長列 (VARCHAR, TEXT, BLOB)。節省空間，但 UPDATE 易產生碎片，需定期
OPTIMIZE TABLE。
- 壓縮 (Compressed)：
myisampack 生成唯讀壓縮表，極省空間，適合歸檔數據。
Memory 引擎限制與用法
- 不支援
TEXT/BLOB、VARCHAR (內部轉定長 CHAR)，5.6 前不支援 AUTO_INCREMENT。
- 默認 Hash 索引，適合等值查詢；範圍/排序需顯式指定
USING BTREE。
- 數據僅駐留內存，**重啟即丟失**，適合「可重建、可丟失、極速讀取」場景。
- 表大小受
max_heap_table_size 限制 (默認 16MB)。
選型決策矩陣
- 需要事務/行鎖/外鍵/崩潰安全？→ InnoDB (95% 核心業務首選)。
2. 純只讀/極少寫、需要全文索引、表級鎖可接受？→ MyISAM (日誌、報表、歸檔)。
3. 臨時中間結果、查找表、Session 存儲、數據可丟失？→ Memory (或 Redis 替代)。
4. 混合場景：單庫多表可混用引擎，CREATE TABLE ... ENGINE=InnoDB 顯式指定。
遷移建議
- MyISAM → InnoDB：
ALTER TABLE t ENGINE=InnoDB; (鎖表重建，大表用 pt-online-schema-change 或 gh-ost)。
- 啟用
innodb_file_per_table=1 避免系統表空間無限增長。
- 調整
innodb_buffer_pool_size = 物理內存 60~80%，InnoDB 性能核心。
