提升 MySQL InnoDB 效能的 14 關鍵配置參數詳解

核心記憶體參數

1. innodb_buffer_pool_size ⭐ 最關鍵

緩存資料頁、索引頁、鎖資訊、插入緩衝等。建議設為實體記憶體 70-80% (專用 DB 伺服器)。

innodb_buffer_pool_size = 4G  # 例：32G 記憶體主機

2. innodb_buffer_pool_instances

將 Buffer Pool 分割為多個實例減少爭用，建議 1GB/實例，innodb_buffer_pool_size > 1G 時啟用。

innodb_buffer_pool_instances = 8

3. innodb_additional_mem_pool_size (5.6.3 棄用)

舊版本用於資料字典、內部結構。5.6+ 整合進 Buffer Pool，無需設定。

日誌與持久化參數

4. innodb_log_file_size

Redo Log 檔案大小。較大值減少 Checkpoint 壓力，提升寫入效能，但延長崩潰恢復時間。建議 256M-2G，總大小 innodb_log_files_in_group * innodb_log_file_size 不超過 Buffer Pool 25%。

innodb_log_file_size = 512M
innodb_log_files_in_group = 4

5. innodb_log_buffer_size

事務日誌寫入磁碟前的記憶體緩衝。大事務/Blob 較多時調大至 16-64M。

innodb_log_buffer_size = 32M

6. innodb_flush_log_at_trx_commit ⭐ 效能與安全平衡

建議：核心交易庫用 1；日誌、快取、可重算資料用 2。

並發與鎖參數

7. innodb_thread_concurrency

0 代表無限制 (現代版本預設)，高併發可設為 2 * CPU核心 + 磁碟數。

8. innodb_lock_wait_timeout

行鎖等待超時秒數，預設 50s。高併發死鎖頻發時可適當降低 (如 10-20s) 配合應用重試。

9. innodb_deadlock_detect (5.7+)

啟用死鎖偵測 (ON)。極高併發可關閉，改用 innodb_lock_wait_timeout 控制。

I/O 與檔案參數

10. innodb_file_per_table (預設 ON)

每表獨立 .ibd 檔案，支援 DROP TABLE 釋放空間、可攜式表空間匯入匯出。

11. innodb_flush_method

Linux 建議 O_DIRECT 避免雙重緩衝 (OS Cache + InnoDB Buffer Pool)。

innodb_flush_method = O_DIRECT

12. innodb_io_capacity / innodb_io_capacity_max

告知 InnoDB 底層儲存裝置 IOPS 能力，背景刷頁、合併插入緩衝自動調節。SSD 設 5000-20000，HDD 設 200-500。

其他實用參數

13. innodb_stats_persistent (5.6+)

持久化統計資訊避免重啟後計劃抖動。配合 innodb_stats_auto_recalc。

14. skip-innodb

完全不使用 InnoDB 時啟用，節省記憶體。現代版本預設需 InnoDB 為系統表引擎，不建議開啟。

調優順序建議

1. innodb_buffer_pool_size → 記憶體允許最大
2. innodb_log_file_size + innodb_log_files_in_group → 寫入穩定
3. innodb_flush_log_at_trx_commit → 業務容忍度決定
4. innodb_flush_method + innodb_io_capacity → 儲存硬體匹配
5. 觀測 Innodb_buffer_pool_reads、Innodb_log_waits、Innodb_row_lock_waits 指標微調