MySQL InnoDB 與 MyISAM 深度對比與優化實戰
InnoDB 與 MyISAM 是 MySQL 最常用的兩大儲存引擎，架構差異巨大，選型直接決定系統效能與可靠性。本文從核心架構、功能差異、鎖機制、優化參數四個維度深度對比，並給出生產環境調優建議。
一、核心架構差異
| 維度 | MyISAM | InnoDB |
|---|
| 資料檔案 | 每表三檔：.frm(結構) .MYD(資料) .MYI(索引) | 共享表空間或獨立表空間，資料與索引混存 |
| 索引結構 | 非聚簇索引，葉節點存行號指向資料檔 | 聚簇索引，主鍵葉節點直接存完整行資料 |
| 緩衝池 | 僅緩存索引，資料依賴 OS 檔案系統快取 | 統一緩存資料+索引，自主管理 |
| 事務支援 | 無 | 完整 ACID，MVCC、行鎖、死鎖偵測 |
| 外鍵約束 | 無 | 支援 |
| 全文索引 | 支援（5.6+ InnoDB 也支援） | 5.6+ 支援 |
| 崩潰恢復 | 需 myisamchk 修復，耗時不可控 | Redo/Undo Log 自動恢復，時間可預期 |
| 並發效能 | 表鎖，讀寫互斥，寫入序列化 | 行鎖，高並發讀寫共存 |
二、關鍵行為差異細節
- COUNT(*)：MyISAM 直接讀取表元資料（極快）；InnoDB 需全表掃描（含 MVCC 可見性檢查）。帶 WHERE 條件時兩者同樣掃描。
- AUTO_INCREMENT：InnoDB 要求自增欄位必須是索引首欄（或單欄索引）；MyISAM 允許聯合索引非首欄。
- DELETE FROM table：MyISAM 重建表（極快）；InnoDB 逐行刪除（產生 Undo Log，較慢但可回滾）。
- LOAD TABLE FROM MASTER：僅支援 MyISAM；InnoDB 需先轉 MyISAM 導入再轉回，但外鍵表不可用。
- 行鎖升級為表鎖：InnoDB 無法確定掃描範圍時（如
WHERE name LIKE '%aaa%'）會鎖全表。
三、選型決策指南
| 場景 | 推薦引擎 | 理由 |
|---|
| 高並發寫入、需事務、外鍵 | InnoDB | 行鎖+MVCC+崩潰安全 |
| 純讀取、報表、日誌、歸檔 | MyISAM | 全表掃描快、壓縮支援、COUNT(*) 秒級 |
| 全文搜尋（5.6 前） | MyISAM | InnoDB 5.6 前不支援 |
| 混合負載、無法明確區分 | InnoDB | 通用性最強、預設引擎、生態最完善 |
四、MyISAM 核心優化參數
| 參數 | 建議值 | 說明 |
|---|
key_buffer_size | 30-40% 可用記憶體（純 MyISAM） | 索引緩衝區核心。留 60%+ 給 OS 快取資料。索引總量 1GB 時設 4GB 屬浪費 |
myisam_sort_buffer_size | 64M-128M | 建索引/修復排序緩衝 |
read_buffer_size | 2M-4M | 全表掃描循序讀取緩衝 |
read_rnd_buffer_size | 8M-16M | 排序後隨機讀取緩衝 |
bulk_insert_buffer_size | 64M | 批次插入緩衝 |
concurrent_insert | 2 (ALWAYS) | 允許尾部並發插入（無空洞時） |
delay_key_write | ON (ALL) | 延遲索引寫入，表關閉才刷盤。風險：崩潰需修復 |
五、InnoDB 核心優化參數（生產級）
| 參數 | 建議值 | 說明 |
|---|
innodb_buffer_pool_size | 70-80% 物理記憶體 | 最關鍵參數。16GB 機器設 12GB。資料+索引全緩存 |
innodb_log_file_size | 256M-512M | 大 = 寫入快、恢復慢。平衡點 256M |
innodb_log_buffer_size | 8M-16M | 寫入峰值大則調大。每秒刷盤，無需超過 1 秒產生量 |
innodb_flush_log_at_trx_commit | 2 | 1=最安全最慢；2=刷 OS 快取（預設每秒刷盤），丟 1-2 秒事務；0=最快最不安全 |
innodb_thread_concurrency | 0 (不限制) 或 2×邏輯CPU | 高並發時 0 讓 InnoDB 自適應 |
innodb_flush_method | O_DIRECT | 避免雙重緩衝、降低 swap。RAID 快取不足慎用 |
innodb_file_per_table | ON | 獨立表空間，避免 ibdata 無限增長、利於單表備份/遷移 |
innodb_read_io_threads | 4-16 | 讀取 I/O 執行緒，SSD 可調大 |
innodb_write_io_threads | 4-16 | 寫入 I/O 執行緒 |
innodb_io_capacity | 2000+ (SSD) / 200 (HDD) | 背景任務 I/O 能力上限，影響刷髒頁、合併插入緩衝速度 |
innodb_max_dirty_pages_pct | 75-90 | 髒頁比例上限，觸發積極刷盤。寫入重可調高 |
innodb_lock_wait_timeout | 50-120 | 鎖等待超時秒數 |
transaction_isolation | READ-COMMITTED | 若應用允許，比 REPEATABLE-READ 效能更好、Binlog 格式 ROW 時支援行級復制 |
六、通用優化參數（雙引擎通用）
| 參數 | 建議值 | 說明 |
|---|
table_open_cache | 2048-10000 | 開啟表快取。Open_tables 接近則調大。大量表必調 |
thread_cache_size | 64-256 | 執行緒快取。Threads_Created 持續增長則調大 |
query_cache_size | 0 (關閉) 或 32-512M | MySQL 5.7+ 建議關閉（鎖爭用大）。純讀、無應用層快取可開 |
sort_buffer_size | 2M-8M | 每連線獨佔。簡單查詢無需大。過大反傷效能 |
join_buffer_size | 2M-8M | 無索引聯結時使用。每連線獨佔 |
max_connections | 500-1000 | 視應用連線池設定。過大浪費記憶體 |
wait_timeout | 10-60 | 釋放空閒連線 |
skip-name-resolve | ON | 禁止 DNS 解析，授權表用 IP |
七、完美時空遊戲伺服器實戰配置（8GB 記憶體，InnoDB 主力）
[mysqld]
port = 3306
socket = /home/mysql/mysql.sock
datadir = /home/mysql/var
server-id = 1
log-bin = /data/mysqllog/binlog/mysql-bin
skip-name-resolve
default-character-set = utf8
back_log = 50
max_connections = 1000
max_connect_errors = 32
max_allowed_packet = 16M
table_cache = 2048
binlog_cache_size = 1M
max_heap_table_size = 64M
tmp_table_size = 64M
key_buffer_size = 32M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
thread_stack = 192K
query_cache_size = 64M
query_cache_limit = 2M
ft_min_word_len = 2
default_table_type = INNODB
transaction_isolation = REPEATABLE-READ
log_slow_queries
long_query_time = 2
log_long_format
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:2048M;ibdata2:2048M;ibdata3:2048M;ibdata4:2048M;ibdata5:2048M;ibdata6:1024M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqld_safe]
open-files-limit = 8192
八、監控關鍵指標
Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads → 命中率應 > 99%Innodb_row_lock_waits、Innodb_row_lock_time_avg → 鎖爭用情況Key_read_requests / Key_reads → MyISAM 索引命中率Table_open_cache_misses / Table_open_cache_hits → 表快取命中率Slow_queries、Questions → 慢查詢佔比
