MySQL 表類型核心對比：MyISAM 與 InnoDB 選型指南（含實測數據）

核心定位：兩大主流存儲引擎

MySQL 中最常用的兩種表類型：**MyISAM**（非事務、表鎖、高速讀）與 **InnoDB**（事務、行鎖、高並發寫）。選型本質是 **業務特徵與引擎特性的匹配**。

快速選型決策表

實測數據對比（環境：MySQL 5.0.22, Windows 2000）

測試場景：存儲過程循環 INSERT 10000 次，每次 COMMIT

CREATE TABLE AAA (
  a1 VARCHAR(64) NOT NULL,
  b2 VARCHAR(255),
  c3 INT,
  PRIMARY KEY (a1)
) ENGINE=InnoDB;

CREATE PROCEDURE sp_AAA()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE a1 CHAR(64);
  DECLARE b2 CHAR(255);
  WHILE i < 10000 DO
    SET a1 = CONCAT('aaa', i);
    SET b2 = CONCAT('bbbbbbbbbbbbbbbbbbbbbbb', i);
    INSERT INTO aaa VALUES (a1, b2, i);
    SET i = i + 1;
  END WHILE;
END;

CALL sp_AAA();  -- InnoDB 耗時 295 秒

同環境對比參考

• Sybase 11.9：6 秒
• Oracle 9i：2 秒
• MySQL InnoDB (未調優)：295 秒

深度解析：為什麼差距這麼大？

1. innodb_flush_log_at_trx_commit=1 (預設)：每次 COMMIT 強制 fsync 刷盤，機械硬盤隨機 I/O 成殺手
2. 無群組提交：5.0 版本尚未支援 Group Commit，無法合併多事務刷盤
3. Windows 下 I/O 效能差：無原生 AIO，同步 I/O 阻塞
4. 緩衝池未配置：預設極小，無法發揮內存加速

現代優化後預期 (MySQL 8.0 + 適當配置)

innodb_flush_log_at_trx_commit = 2  -- 秒級刷盤，犧牲 1 秒數據
innodb_buffer_pool_size = 4G        -- 核心緩衝區
innodb_log_file_size = 512M         -- 減少 checkpoint
innodb_flush_method = O_DIRECT      -- 繞過文件系統緩存
# 預期同測試：< 5 秒

細節差異深度解析

1. COUNT(*) 效能

• MyISAM：元數據存行數，SELECT COUNT(*) O(1) 返回
• InnoDB：MVCC 可見性檢查，需掃描聚簇索引或最小二級索引 O(N)
• 帶 WHERE 兩者同樣掃描

2. AUTO_INCREMENT 差異

• InnoDB：自增列必須為索引最左列（單列或複合索引首列）
• MyISAM：允許複合索引非首列自增

3. DELETE 行為

• MyISAM：重建表（快）
• InnoDB：逐行標記刪除（慢，但支援回滾、MVCC）
• TRUNCATE 兩者同為 DDL 快速操作

4. LOAD TABLE FROM MASTER

• MyISAM：支援
• InnoDB：不支援，需轉 MyISAM 導入再轉回

5. 行鎖升級表鎖條件

UPDATE table SET num=1 WHERE name LIKE '%aaa%';  -- 無法用索引，全表掃描 → 表鎖

MyISAM 獨特優勢（仍具不可替代性）

1. 純讀/歸檔/日志/統計表：壓縮表 (myisampack)、極速全掃描
2. 跨 OS 文件複製部署：無需 dump/restore
3. 5.6 前全文搜尋唯一選擇
4. 嵌入式/唯讀介質場景

InnoDB 現代優勢（95%+ 場景首選）

1. 完整 ACID 事務、外鍵、MVCC、行鎖
2. 聚簇索引：主鍵查找極快、範圍查詢有序
3. 自適應哈希索引、插入緩衝、預讀優化
4. 崩潰安全：Doublewrite Buffer + Redo Log
5. 線上 DDL (5.6+)、即時加列 (8.0+)
6. 原生分區表、空間索引、JSON 支援

作者疑問的現代視角解答

「MySQL InnoDB 性能差距這麼大麼？」

當年真相：5.0 時代 InnoDB 尚未成熟，缺乏核心優化（Group Commit、自適應哈希、多核擴展）。現代實況：MySQL 8.0 + NVMe + 適當調優，單機輕鬆 5 萬+ TPS，早已不是當年的性能。

選型總結口訣

「要事務、要並發、要外鍵 → InnoDB 無腦選」
「純讀、要全文、要拷貝部署 → MyISAM 還有用」
「不確定選 InnoDB，錯不了」

任何引擎都不是萬能藥。**認清業務讀寫比、事務需求、並發模式、部署約束**，匹配引擎特性，配合參數調優，才能榨乾硬體效能。