MySQL MyISAM 與 InnoDB 效能實測：插入、配置參數影響與選型建議

前言

理論比較需實測驗證。本文記錄真實硬體環境下 MyISAM 與 InnoDB 插入效能對比，深度分析 innodb_flush_log_at_trx_commit、innodb_buffer_pool_size、autocommit 關鍵參數影響。

一、測試環境

二、表結構與測試資料

-- MyISAM 表
CREATE TABLE `myisam` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk;

-- InnoDB 表
CREATE TABLE `innodb` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

-- 測試資料
$name = "heiyeluren";
$content = "MySQL支援多種儲存引擎...(約 2KB 文本)";

三、測試結果總覽

場景 1：預設配置 (innodb_flush_log_at_trx_commit=1, autocommit=ON)

⚠️ 此結果**反常識** (InnoDB 遠快於 MyISAM)，原因：測試程式碼可能未提交交易、或 MyISAM 表鎖爭用嚴重。實際生產中 flush_log_at_trx_commit=1 通常導致 InnoDB 寫入較慢。

場景 2：innodb_flush_log_at_trx_commit = 0 (每秒刷盤)

關鍵發現：**innodb_flush_log_at_trx_commit=0 讓 InnoDB 效能追平甚至超越 MyISAM**，代價是崩潰可能丟失 1 秒交易。

場景 3：innodb_buffer_pool_size = 1024MB (超過實體記憶體，實際受限)

緩衝池過大無顯著提升，受限於實體記憶體與 32 位元限制。

場景 4：buffer_pool=256MB + flush_log=1 + autocommit=OFF (手動交易)

手動交易批次提交 (autocommit=0) 是 InnoDB 批次寫入的正確姿勢，效能接近 flush_log=0 但資料安全性高。

四、關鍵參數深度解析

1. innodb_flush_log_at_trx_commit

2. innodb_buffer_pool_size

• 核心參數：緩存資料頁、索引頁、插入緩衝、鎖資訊、自適應雜湊索引
• 建議：專用 DB 服務器 **70-80% 實體記憶體**
• 過大：OS Swap 導致效能崩塌
• 預熱：重啟後需時間載入熱點資料

3. autocommit / 手動交易

-- ❌ 預設 autocommit=ON：每 INSERT 一次 fsync (flush_log=1 時)
INSERT INTO t VALUES (...); -- 1 次 fsync
INSERT INTO t VALUES (...); -- 1 次 fsync

-- ✅ 手動交易批次提交
SET autocommit = 0;
START TRANSACTION;
INSERT INTO t VALUES (...); -- 1000 筆
INSERT INTO t VALUES (...);
COMMIT; -- 1 次 fsync (flush_log=1)
-- 效能提升 100-1000x

五、my.cnf 完整配置範本 (測試環境)

[mysqld]
port = 3306
basedir = "C:/mysql50/"
datadir = "C:/mysql50/Data/"
default-character-set = gbk
default-storage-engine = INNODB
sql_mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections = 100
query_cache_size = 0
table_cache = 256
tmp_table_size = 50M
thread_cache_size = 8
myisam_max_sort_file_size = 100G
myisam_sort_buffer_size = 100M
key_buffer_size = 82M
read_buffer_size = 64K
read_rnd_buffer_size = 256K
sort_buffer_size = 256K
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 2M
innodb_buffer_pool_size = 159M
innodb_log_file_size = 80M
innodb_thread_concurrency = 8

六、生產環境配置建議 (現代硬體)

[mysqld]
# 基礎
innodb_buffer_pool_size = 64G          # 128GB 記憶體機器
innodb_log_file_size = 4G
innodb_log_files_in_group = 4
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_io_capacity = 20000             # NVMe SSD
innodb_io_capacity_max = 40000
innodb_page_cleaners = 4
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_buffer_pool_instances = 8

# 連線
max_connections = 2000
thread_cache_size = 100

# 查詢快取 (5.7+ 建議關閉)
query_cache_type = OFF
query_cache_size = 0

# 臨時表
tmp_table_size = 256M
max_heap_table_size = 256M

# 日誌
slow_query_log = 1
long_query_time = 1
log_queries_not_using_indexes = 1

七、結論與選型建議

1. InnoDB 批次寫入必須用手動交易：SET autocommit=0; START TRANSACTION; ... COMMIT;，否則效能極差
2. innodb_flush_log_at_trx_commit=1 為安全基準，效能靠交易批次、緩衝池、IO 能力解決
3. 緩衝池大小是效能第一槓桿：熱點資料全在記憶體，IO 壓力降 90%+
4. MyISAM 仍有唯讀/歸檔價值，但核心業務**無理由不選 InnoDB**
5. 配置必須匹配硬體：SSD/NVMe 需調高 innodb_io_capacity，大記憶體需多實例

結語

實測數據證明：InnoDB 配合正確參數 (批次交易、足夠緩衝池、合理 flush 策略) 效能不遜於 MyISAM，且提供交易、行鎖、崩潰恢復等企業級特性。**不要用錯誤配置測出的數據否定 InnoDB**，要用正確配置釋放其真實實力。