MySQL 效能調優的本質是「在有限資源下，透過參數配置最大化吞吐與穩定性」。本文系統歸納 15 個高影響力參數，分為連線請求類、全域緩衝區類、線程私有緩衝區類、InnoDB 專屬類四大維度，並提供 8GB 實體記憶體實戰分配模型，構建可落地的調優知識體系。
一、連線請求類參數
| 參數 | 預設 | 推薦 | 調優邏輯與監控指標 |
|---|
| max_connections | 151 | 200-500 (視記憶體) | 監控 Max_used_connections / max_connections ≈ 85%。過小報 1040 錯誤，過大致 OOM 風險 |
| back_log | 50 | 128-256 | TCP 監聽隊列長度。show processlist 出現大量 unauthenticated user 時調大。Linux 上限 512 |
| interactive_timeout | 28800 (8h) | 7200 (2h) | 互動式連線空閒斷開。降低減少殭屍連線佔用槽位 |
| wait_timeout | 28800 | 300-600 (5-10min) | 非互動連線空閒斷開。應用層連線池配合 validationQuery 設定更短 |
二、全域緩衝區參數
| 參數 | 預設 | 推薦 | 調優邏與監控 |
|---|
| key_buffer_size | 8 MB | MyISAM 索引總量 + 20% | 僅對 MyISAM 生效。監控 Key_reads / Key_read_requests < 0.1%。純 InnoDB 環境設 32-64 MB 即可 |
| query_cache_size | 0/16 MB | 0 (禁用) | MySQL 8.0 已移除。5.7 高寫入場景強制關閉。監控 Qcache_lowmem_prunes、Qcache_hits |
| table_open_cache | 2000 | 4000-8000 | 監控 Open_tables 接近上限且 Opened_tables 持續增長時擴容。配合 open_files_limit ≥ 2-3× |
| max_heap_table_size | 16 MB | 64-256 MB | 內部記憶體臨時表上限。與 tmp_table_size 取較小值生效 |
| tmp_table_size | 16 MB | 64-256 MB | 監控 Created_tmp_disk_tables / Created_tmp_tables < 25%。線程私有，謹慎調大防 OOM |
| thread_cache_size | 自動 | 50-100 | 監控命中率 (Connections - Threads_created) / Connections > 90% |
三、線程私有緩衝區參數（每連線獨佔，乘以 max_connections 計總量）
| 參數 | 預設 | 推薦 | 用途與風險 |
|---|
| sort_buffer_size | 2 MB | 2-8 MB | ORDER BY/GROUP BY 排序。超過則用臨時表+Filesort。過大 × 連線數 = OOM |
| join_buffer_size | 256 KB | 256 KB - 2 MB | 非索引 Join (Block Nested Loop)。治本加索引，非加 Buffer。過大極易 OOM |
| read_buffer_size | 128 KB | 1-2 MB | 全表掃描循序讀取。大表掃描場景適度調大 |
| read_rnd_buffer_size | 256 KB | 1-4 MB | 排序後隨機讀取 (MRR)。大排序場景調大 |
| thread_stack | 256 KB | 256-512 KB | 線程棧空間。複雜存儲過程/觸發器需調大。預設通常足夠 |
四、InnoDB 專屬關鍵參數
| 參數 | 預設 | 推薦 | 核心影響 |
|---|
| innodb_buffer_pool_size | 128 MB | 實體記憶體 60-80% | InnoDB 核心緩衝池：資料頁、索引頁、鎖、自適應雜湊、插入緩衝。單機單實例設 70% 起步 |
| innodb_flush_log_at_trx_commit | 1 | 2 (平衡) / 0 (高性能) | 1=每提交 fsync(最安全最慢)；2=提交 write+每秒 fsync(推薦)；0=每秒 write+fsync(最快最不安全) |
| innodb_log_buffer_size | 16 MB | 16-64 MB | 大交易場景調大，減少刷盤頻率 |
| innodb_log_file_size | 48 MB | 256 MB - 2 GB | Redo Log 單檔大小。較大減少 checkpoint 壓力，但崩潰恢復較慢。總量 = size × files |
| innodb_log_files_in_group | 2 | 2-4 | Redo Log 組檔案數，循環寫入 |
| innodb_flush_method | 無 | O_DIRECT | 避免雙重快取 (OS + InnoDB)。Linux 必設，Windows 通常無效 |
| innodb_file_per_table | ON (5.6+) | ON | 獨立 .ibd 檔案，支援表級備份、空間回收、Online DDL |
| innodb_thread_concurrency | 0 (無限) | 0 或 2×(CPU核心+磁碟數) | 0=無限制(現代版本推薦)。高併發鎖爭用時可限制 |
| innodb_read_io_threads | 4 | 8-16 | 背景讀取線程。SSD/NVMe 環境調大 |
| innodb_write_io_threads | 4 | 8-16 | 背景寫入/刷盤線程。高寫入場景調大 |
| innodb_io_capacity | 200 | SSD: 2000-5000 / NVMe: 10000+ | 磁碟 IOPS 能力告知 InnoDB，影響刷盤、清除、合併速率 |
| innodb_io_capacity_max | 2000 | 2×innodb_io_capacity | 突發 IOPS 上限 |
五、8GB 實體記憶體實戰分配模型（MyISAM + InnoDB 混合、500 連線）
總記憶體 = 8192 MB
├─ 作業系統保留: 1024 MB (12.5%)
├─ 線程私有緩衝 (max_connections=300):
│ sort_buffer_size: 2 MB
│ join_buffer_size: 512 KB
│ read_buffer_size: 1 MB
│ read_rnd_buffer_size: 1 MB
│ thread_stack: 256 KB
│ 小計/線程: ~4.75 MB
│ 總計: 300 × 4.75 MB = 1425 MB (17.4%)
├─ MyISAM Key Buffer: 128 MB (實際索引極小，預留)
├─ Query Cache: 0 MB (禁用)
├─ Table Open Cache: ~50 MB (表結構快取)
├─ Binlog Cache: 32 MB
├─ InnoDB Log Buffer: 64 MB
├─ InnoDB Buffer Pool: 8192 - 1024 - 1425 - 128 - 50 - 32 - 64 = 5469 MB ≈ 5.3 GB (67%)
└─ 其他/預留/碎片: ~500 MB
總計 ≈ 7.7 GB < 8 GB ✓ 安全邊界
關鍵調優決策
- 優先保障 Buffer Pool： 扣除系統、線程、其他固定開銷後，剩餘盡量給 Buffer Pool
- 線程私有緩衝謹慎設定： 乘以 max_connections 計算總量，單位 MB 級變化影響 GB 級總量
- 實測驗證： 監控
Innodb_buffer_pool_pages_free、Innodb_buffer_pool_wait_free、Created_tmp_disk_tables、OOM 風險
- 專用實例專用調優： 純 InnoDB 可將 Key Buffer 降至 32 MB，釋放更多給 Buffer Pool
六、參數修改生效方式對照
| 參數類型 | 生效方式 | 範例 |
|---|
| 動態全域 | SET GLOBAL param = value; 即時生效，重啟失效 | sort_buffer_size, join_buffer_size |
| 動態會話 | SET SESSION param = value; 僅當前連線 | sort_buffer_size (臨時調大排序) |
| 靜態 | 修改 my.cnf/my.ini + 重啟 | innodb_buffer_pool_size, innodb_log_file_size |
| 只讀 | 僅顯示，不可修改 | version, basedir, datadir |
💡 專家總結： 「參數調優無銀彈，只有權衡」。記住三個黃金法則：
1. Buffer Pool 為王 —— 盡可能大，承載熱點資料與索引
2. 線程私有乘以連線數 —— 每 MB 都是 GB 級承諾，極度克制
3. 治本在索引與 SQL —— 參數只能托底，無法彌補缺索引、全表掃描、惡意 SQL
建立參數基線、納入版控、變更留痕、效果驗證，讓調優從「玄學調參」進化為「工程化交付」。
