MySQL 生產環境必調 10 項核心配置參數（附避坑指南）

前言

MySQL 有數百個配置參數，但**真正決定效能與穩定的不到 10 個**。本文提煉生產環境必調的 10 項核心配置，說明調優邏輯、推薦值範圍、常見誤區，助你快速獲得穩健的基線配置。

調優鐵律（動手前必讀）

1. 一次只改一個參數，觀測效果再決定下一步。
2. 多數參數支援 `SET GLOBAL` 運行時生效，便於快速回滾；持久化需寫入 `my.cnf`。
3. 重啟不生效？查驗：配置檔路徑、 `[mysqld]` 區塊、單位（MB/GB/無單位）。
4. 嚴禁配置檔重複參數：最後一個生效，極難排查。用版本控制管理變更歷史。
5. 拒絕「記憶體翻倍→參數翻倍」朴素算法：參數間有非線性關係、硬體瓶頸不同。

三大基石配置（裝完即調，否則出事）

1. `innodb_buffer_pool_size` —— 效能核心

• 作用：緩存數據頁、索引頁、鎖資訊、自適應雜湊索引。
• 推薦：獨立 DB 主機 **60~80% 實體記憶體**。
  • 8GB RAM → 5~6 GB
  • 32GB RAM → 20~25 GB
  • 128GB RAM → 100~120 GB
• 監控：`Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1%`。
• 避坑：32 位元 OS 進程限制 2~3.5GB，勿設過大導致 OOM。

2. `innodb_log_file_size` —— 寫入與恢復平衡

• 作用：Redo Log 文件大小，決定 checkpoint 頻率與崩潰恢復時間。
• 推薦：起步 **512M**（總 1GB，預設 2 個文件）；寫入極重、MySQL 5.6+ 可設 **1~4GB**。
• 歷史包袱：5.1 時代總和限 4GB，恢復慢；5.5+ 恢復大幅優化，可放心調大。
• 修改步驟：停服務 → 刪除舊 `ib_logfile*` → 改配置 → 啟動（自動建立新文件）。

3. `max_connections` —— 併發上限

• 現狀：預設 151，極易觸發「Too many connections」。
• 推薦：應用連線池大小 × 實例數 × 1.5 安全係數（如 500~2000）。
• 副作用：過大（>5000）導致活躍事務爆發時 CPU/記憶體耗盡、服務假死。
• 根治：應用層**連線池化**（HikariCP/Druid）、ProxySQL 多路複用、MySQL 線程池（Percona/Enterprise）。

InnoDB 進階配置（5.5+ 預設引擎，佔 90%+ 工作負載）

4. `innodb_file_per_table = ON`

• 獨立表空間（`.ibd`），支援 `DROP`/`TRUNCATE`/`REBUILD` 回收磁碟、壓縮、可傳輸表空間。
• 5.6+ 預設 ON；舊版遷移需**導入數據前**設定，僅影響新建表。
• 反例：表數極多（>10k）導致文件描述符耗盡，可考慮關閉或分庫分表。

5. `innodb_flush_log_at_trx_commit`

生產主庫**堅持 1**；讀庫/日志庫可 2；極端寫入場景 0 需簽署風險確認書。

6. `innodb_flush_method`

• `O_DIRECT`：繞過 OS page cache，直寫磁碟。**有 RAID 卡 + BBWC/FBWC 時必選**。
• `fdatasync`（預設）：經 OS cache，無 RAID 卡或雲盤場景較安全。
• 用 `sysbench` 雙測對比決定。

7. `innodb_log_buffer_size`

• 預設 1~8MB，大事務含 `TEXT`/`BLOB` 易填滿觸發額外 I/O。
• 監控 `Innodb_log_waits > 0` 即調大（如 8~64MB）。

其他高影響配置

8. `query_cache_size = 0`（關閉查詢緩存）

• 已知瓶頸：全局鎖、失效掃描、高併發下拖垮效能。
• 5.7 預設關閉，8.0 移除。替代方案：Redis、應用層緩存、物化視圖。
• 若歷史業務強依賴且無問題，可保留並監控 `Qcache_lowmem_prunes`。

9. `log_bin` + `expire_logs_days`（啟用 Binlog 並自動清理）

• 主庫/需時間點恢復/主從同步：**必開**。
• `server_id` 唯一、`binlog_format=ROW`（5.7+ 預設）。
• `expire_logs_days=7~30` 防磁碟爆滿；或 `binlog_expire_logs_seconds`（8.0+）。
• 純讀庫/不需恢復可關閉，省 I/O。

10. `skip_name_resolve = ON`

• 禁用 DNS 反解，連線建立快、免「Host name could not be resolved」錯誤。
• 副作用：`GRANT`/`CREATE USER` 僅能用 IP，不可用主機名。遷移時需同步改授權表。

快速基線配置模板（my.cnf 片段）

[mysqld]
# 基石
innodb_buffer_pool_size = 24G        # 32G RAM 例
innodb_log_file_size = 1G
max_connections = 1000

# InnoDB 進階
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 16M

# 其他
query_cache_size = 0
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 14
skip_name_resolve = 1

# 連線池配合
thread_cache_size = 32
table_open_cache = 4000
tmp_table_size = 256M
max_heap_table_size = 256M

總結

MySQL 調優**不在參數多寡，在於精準聚焦核心 10 項**。「三大基石」決定下限，「InnoDB 進階」決定上限，「其他」解決特定痛點。遵循「一次一改、監控驅動、版本控制、拒絕盲目倍增」，即可構建穩健高效的生產基線。

1. 上一篇文章： MySQL 效能調優 101 條鐵律：硬體、配置、模式、查詢、備份全維度指南
2. 下一篇文章： MySQL 常用命令大全：從安裝部署到日常運維的完整速查手冊

MySQL 資料庫相關知識

MySQL GUI Tools 圖形化管理全攻略：Query Browser 與 Administrator 實戰指南

工具套裝組成 MySQL Query Browser：SQ

MySQL 中文亂碼、匯出 SQL/Excel、Java 寫入亂碼完整解決方案

環境 macOS 10.8.3 / MySQL 5.6.10

MySQL 半同步與異步複寫深度對比：原理、配置、效能與選型指南

前言 MySQL 複寫模式決定了資料一致性與可用性的權衡。異

MySQL 預防性維護完整方案：自動化表檢查、啟動驗證與日誌輪轉

維護三大支柱 定期備份 + 啟用更新日誌：災難恢復基石。