萬盛學電腦網

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

MyISAM vs InnoDB:架構差異、性能實測與生產選型指南

核心架構對比

維度 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。

copyright © 萬盛學電腦網 all rights reserved