核心架構對比
| 維度 |
MyISAM |
InnoDB |
| 事務支持 |
❌ 無 |
✅ ACID 完整支持 |
| 鎖粒度 |
表級鎖 (共享讀/排他寫) |
行級鎖 + 間隙鎖 + Next-Key Lock |
| MVCC |
❌ 無 |
✅ Read Committed / Repeatable Read |
| 外鍵 |
❌ 不支持 |
✅ 支持 CASCADE/SET NULL/RESTRICT |
| 全文索引 |
5.6+ 原生 |
5.6+ 原生 (ngram/MeCab) |
| 崩潰恢復 |
需 REPAIR TABLE,易數據損壞 |
Redo/Undo Log 自動恢復,極高可靠 |
| 聚簇索引 |
❌ 堆表,主鍵非聚簇 |
✅ 主鍵即聚簇索引 (數據按主鍵順序存儲) |
| 緩存池 |
Key Buffer (僅索引) |
Buffer Pool (數據+索引+鎖+插入緩衝+自適應哈希) |
| 壓縮表 |
✅ myisampack (只讀) |
✅ 透明頁壓縮 / 行壓縮 |
硬件環境與測試表結構
CPU: AMD 2500+ (1.8GHz) | 內存: 1GB | 硬盤: 80GB IDE
OS: Windows XP SP2 | MySQL 5.0.37
CREATE TABLE `myisam_test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) DEFAULT NULL,
`content` TEXT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;
CREATE TABLE `innodb_test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(100) DEFAULT NULL,
`content` TEXT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;
插入性能實測數據 (單位:秒/萬行)
場景 1:默認配置 (innodb_flush_log_at_trx_commit=1, autocommit=1)
+----------+---------+----------+-----------+
| 數據量 | MyISAM | InnoDB | 比率 |
+----------+---------+----------+-----------+
| 1 萬 | 3 | 219 | 1 : 73 |
| 10 萬 | 29 | 2092 | 1 : 72 |
| 100 萬 | 287 | 未測 | - |
+----------+---------+----------+-----------+
InnoDB 每次提交強制 fsync,性能極差
場景 2:innodb_flush_log_at_trx_commit=0 (秒級刷盤,可能丟 1 秒數據)
+----------+---------+----------+-----------+
| 數據量 | MyISAM | InnoDB | 比率 |
+----------+---------+----------+-----------+
| 1 萬 | 3 | 3 | 1 : 1 |
| 10 萬 | 30 | 29 | 持平 |
| 100 萬 | 273 | 423 | InnoDB 慢 55% |
+----------+---------+----------+-----------+
放寬刷盤策略後,中小數據量持平,大數據量 InnoDB 因雙寫緩衝/檢查點開銷略慢
場景 3:大 Buffer Pool (innodb_buffer_pool_size=1024M)
+----------+----------+
| 數據量 | InnoDB |
+----------+----------+
| 1 萬 | 3 |
| 10 萬 | 33 |
| 100 萬 | 607 |
+----------+----------+
足夠內存下,InnoDB 寫入性能可接受
場景 4:批量事務提交 (autocommit=0 + 手動 COMMIT)
SET autocommit=0;
INSERT ...; INSERT ...; ... (批量 1000 行) COMMIT;
+----------+----------+
| 數據量 | InnoDB |
+----------+----------+
| 1 萬 | 3 |
| 10 萬 | 26 |
| 100 萬 | 379 |
+----------+----------+
批量提交是 InnoDB 高性能寫入關鍵,減少 fsync 次數
關鍵配置參數解析 (my.cnf)
[mysqld]
# InnoDB 核心配置
innodb_buffer_pool_size = 159M # 生產建議: 物理內存 60-70%
innodb_log_file_size = 80M # 生產建議: 1-4GB,減少檢查點壓力
innodb_log_buffer_size = 2M
innodb_flush_log_at_trx_commit = 1 # 1=最安全(雙1), 2=高性能(秒刷盤), 0=最高性能(風險丟1秒)
innodb_flush_method = O_DIRECT # 繞過 OS Cache,避免雙重緩存
innodb_file_per_table = ON # 獨立表空間,便於管理
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_thread_concurrency = 0 # 0=無限制,由 OS 調度
# MyISAM 核心配置 (如仍在使用)
key_buffer_size = 82M # 僅緩存索引,建議 ≤ 4GB
myisam_sort_buffer_size = 100M # 修復/建索引排序緩存
read_buffer_size = 64K
read_rnd_buffer_size = 256K
# 通用
max_connections = 500
table_cache = 256
query_cache_size = 0 # 8.0 已移除,5.7 建議關閉
tmp_table_size = 50M
max_heap_table_size = 50M
生產選型決策矩陣
| 業務場景 |
推薦引擎 |
核心理由 |
| 電商訂單、支付、庫存、賬戶 |
InnoDB |
事務 ACID、行鎖高併發、外鍵約束、崩潰安全 |
| 日志表、審計表、點擊流、IoT 數據 |
InnoDB / MyISAM |
僅追加寫入,MyISAM 壓縮存儲省空間;InnoDB 更安全 |
| 數據倉庫大寬表、OLAP 掃描 |
MyISAM / ColumnStore |
全表掃描無鎖競爭,MyISAM 存儲緊湊;或用列式引擎 |
| 只讀字典表、配置表、郵編表 |
MyISAM (可壓縮) |
myisampack 壓縮 70% 空間,只讀無鎖問題 |
| 全文搜索 (中文) |
InnoDB + ngram/MeCab |
5.7+ 原生支持,事務一致性 |
| 臨時表、中間結果表 |
MEMORY / InnoDB |
MEMORY 極快但重啟丟失;InnoDB 持久化 |
遷移檢查清單
# 1. 線上無鎖遷移 (推薦)
pt-online-schema-change --alter "ENGINE=InnoDB" \
D=db_name,t=table_name --execute --max-load Threads_running=50 --critical-load Threads_running=100
# 2. 遷移後必調參數
innodb_buffer_pool_size = 物理內存 70%
innodb_log_file_size = 2G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
# 3. 驗證
SHOW TABLE STATUS LIKE 'table_name'\G
-- Engine: InnoDB
-- Row_format: Dynamic/Compressed
-- Data_free: 0 (無碎片)
總結
MyISAM 是歷史產物,僅在「純只讀、無事務、極度壓縮存儲」極窄場景有殘留價值。
InnoDB 是當代唯一選擇:行級鎖支撐高併發、MVCC 實現無鎖讀、聚簇索引優化主鍵訪問、Redo/Undo 保證零數據丟失、成熟的生態工具鏈。
只要配置得當 (innodb_buffer_pool_size、innodb_flush_log_at_trx_commit、innodb_log_file_size),InnoDB 性能全面超越 MyISAM。