存儲引擎決定了表的鎖粒度、事務支援、崩潰恢復、索引結構、備份方式。MyISAM 與 InnoDB 是歷史上最主流的兩大引擎，本文從架構原理到生產選型全維度對比。
| 維度 | MyISAM | InnoDB |
|---|---|---|
| 事務支援 | ❌ 不支援 | ✅ ACID、行級鎖、MVCC、崩潰恢復 |
| 鎖粒度 | 表級鎖 | 行級鎖(意向鎖+記錄鎖+間隙鎖+Next-Key鎖) |
| 全文索引 | ✅ 5.6+ 原生支援 | ✅ 5.6+ 原生支援 |
| 外鍵約束 | ❌ | ✅ |
| 索引結構 | B+Tree(非聚簇，葉子存行指針) | B+Tree(聚簇索引，葉子存完整行數據) |
| 主鍵要求 | 可無主鍵 | 強制有主鍵(無則隱式 6 字節 row_id) |
| 存儲文件 | .frm/.MYD/.MYI 分離 | 共享表空間 ibdata1 / 獨立表空間 .ibd |
| COUNT(*) | O(1) 直接讀元數據 | O(N) 全表掃描(無 WHERE) |
| 壓縮表 | myisampack 唯讀壓縮 | ROW_FORMAT=COMPRESSED 透明壓縮 |
| 崩潰恢復 | ❌ 易損壞，需 myisamchk 修復 | ✅ Redo/Undo Log 自動恢復 |
| 熱備份 | 需鎖表/文件複製 | XtraBackup 無鎖熱備份 |
需要事務/行級鎖/外鍵/崩潰安全 → InnoDB (95%+ 場景)
↓ 否
純讀/極少寫/全文搜索/日志/歸檔表 → MyISAM (可考慮)
↓ 否
需壓縮唯讀/嵌入式/極簡場景 → MyISAM / CSV / Memory
現代最佳實踐：**默認 InnoDB**。MyISAM 僅用於：只讀歸檔表、全文搜索(若不引入 ES)、極簡嵌入式場景。5.7+ 默認引擎已為 InnoDB。
| 操作 | 命令 |
|---|---|
| 查看表引擎 | SHOW CREATE TABLE t\G / SHOW TABLE STATUS LIKE 't'\G / mysqlshow -u root -p --status db t |
| 修改引擎 | ALTER TABLE t ENGINE=InnoDB; (會重建表，大表走 pt-osc/gh-ost) |
| 啟動默認引擎 | mysqld --default-storage-engine=InnoDB 或 my.cnf: default-storage-engine=InnoDB |
| 會話臨時改引擎 | SET SESSION default_storage_engine=InnoDB; / SHOW VARIABLES LIKE 'default_storage_engine'; |
[mysqld]
# 核心緩存
innodb_buffer_pool_size = 10G # 物理內存 60-75%
innodb_buffer_pool_instances = 8 # 1GB/實例，減少鎖爭用
innodb_buffer_pool_chunk_size = 128M # 擴容粒度
# Redo Log
innodb_log_file_size = 1G # 大日志減少 checkpoint 刷盤
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M # 大事務緩衝
# 刷盤策略
innodb_flush_log_at_trx_commit = 1 # 強一致，金融必設
innodb_flush_method = O_DIRECT # 繞過 OS Cache
innodb_flush_neighbors = 0 # SSD 關閉鄰近頁刷盤
# 並發控制
innodb_thread_concurrency = 0 # 0=無限制，由 OS 調度
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
# 索引/統計
innodb_stats_persistent = ON
innodb_stats_auto_recalc = ON
innodb_stats_sample_pages = 32
# 死鎖/鎖等待
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON
InnoDB 以「聚簇索引 + MVCC + Redo/Undo Log」三大核心機制，奠定了 MySQL 關係型數據庫的工業級地位。深入理解其內核，是高級 DBA 的必修課。
