萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MyISAM 與 InnoDB 深度對比:選型指南與性能實測

核心架構差異

特性 MyISAM InnoDB
存儲引擎類型 非事務型 事務型 (ACID)
鎖粒度 表級鎖 行級鎖 (支持間隙鎖、Next-Key Lock)
外鍵約束 不支持 支持
全文索引 5.6+ 原生支持 5.6+ 原生支持
數據文件結構 .MYD (數據) + .MYI (索引) .ibd (數據+索引聚簇) 或共享表空間
崩潰恢復 需人工 repair,易損壞 Redo/Undo Log 自動恢復,極高可靠性
MVCC 支持 無 Read Committed / Repeatable Read 隔離級別
緩存機制 Key Buffer (只緩存索引) Buffer Pool (緩存數據+索引+鎖+插入緩衝)

性能實測數據(同配置環境:8C16G,SSD,MySQL 5.7)

場景 1:純寫入 (INSERT) - autocommit=1

+------------+----------+----------+----------+
| 記錄數     | MyISAM   | InnoDB   | 差異     |
+------------+----------+----------+----------+
| 1 萬       | 3 ms     | 219 ms   | MyISAM 快 73x |
| 10 萬      | 29 ms    | 2092 ms  | MyISAM 快 72x |
| 100 萬     | 287 ms   | 未測     | -        |
+------------+----------+----------+----------+
原因:InnoDB 默認每次提交刷盤 (innodb_flush_log_at_trx_commit=1),MyISAM 無事務開銷

場景 2:批量寫入 (autocommit=0 + 事務批量提交)

+------------+----------+----------+----------+
| 記錄數     | MyISAM   | InnoDB   | 差異     |
+------------+----------+----------+----------+
| 1 萬       | 3 ms     | 3 ms     | 持平     |
| 10 萬      | 30 ms    | 26 ms    | InnoDB 略快 |
| 100 萬     | 273 ms   | 379 ms   | MyISAM 快 28% |
+------------+----------+----------+----------+
結論:InnoDB 必須显式開啟事務批量提交,否則性能極差

場景 3:調優後 InnoDB (innodb_flush_log_at_trx_commit=2 + buffer_pool=2G)

+------------+----------+
| 記錄數     | InnoDB   |
+------------+----------+
| 1 萬       | 3 ms     |
| 10 萬      | 33 ms    |
| 100 萬     | 607 ms   |
+------------+----------+
適當放寬刷盤策略 + 足夠 Buffer Pool,InnoDB 寫入性能可超越 MyISAM

場景 4:高併發讀寫混合 (Sysbench OLTP, 64 線程)

+----------------+----------+----------+
| 指標           | MyISAM   | InnoDB   |
+----------------+----------+----------+
| TPS            | 1,200    | 4,800    |
| QPS            | 24,000   | 96,000   |
| 平均延遲 (ms)  | 53.2     | 13.1     |
| 95% 延遲 (ms)  | 180+     | 28       |
+----------------+----------+----------+
行級鎖 + MVCC 讓 InnoDB 在高併發下完勝

選型決策樹

  1. 需要事務 (轉帳、訂單、庫存)?→ InnoDB
  2. 需要外鍵約束?→ InnoDB
  3. 純只讀/插入為主、無事務、需全文搜索 (5.6 前)?→ MyISAM
  4. 日志類、統計類、數據倉庫大表、寫入極少?→ MyISAM 可考慮
  5. 其他所有 OLTP 場景?→ InnoDB (默認首選)

遷移 MyISAM → InnoDB 操作指南

# 1. 線上遷移 (pt-online-schema-change,零鎖表)
pt-online-schema-change --alter "ENGINE=InnoDB" \
  D=db_name,t=table_name --execute --user=root --password=xxx

# 2. 小表直接 ALTER (需鎖表)
ALTER TABLE table_name ENGINE=InnoDB;

# 3. 遷移後必須調整參數
# my.cnf
innodb_buffer_pool_size = 物理內存 70%
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

常見誤區澄清

❌ 誤區 1:「MyISAM 查詢比 InnoDB 快」

✅ 事實:只有「純全表掃描 COUNT(*) 無 WHERE」MyISAM 快 (元數據直接返回)。帶條件查詢、範圍查詢、聯表查詢,InnoDB 聚簇索引 + Buffer Pool 完勝。

❌ 誤區 2:「InnoDB 不支持全文索引」

✅ 事實:MySQL 5.6+ InnoDB 原生支持 Fulltext,且支持中文分詞插件 (ngram)。

❌ 誤區 3:「count(*) InnoDB 慢,要用 count(1) 或 count(主鍵)」

✅ 事實:MySQL 8.0+ 優化器會自動選擇最小索引掃描,三者性能一致。真正優化是避免全表掃描,用計數表或 Redis 緩存。

總結建議

現代 MySQL (5.7/8.0) 生產環境 99% 場景請直接使用 InnoDB。 MyISAM 僅保留給極少數唯讀、無事務、需特殊全文分詞的歷史表。新建表默認指定 ENGINE=InnoDB,並在 my.cnf 設 default-storage-engine=InnoDB。

copyright © 萬盛學電腦網 all rights reserved