安裝 MySQL 後必調整的 10 項關鍵配置參數詳解

安裝 MySQL 後必調整的 10 項關鍵配置參數詳解

源自 MySQL Performance Blog 實戰經驗，精簡數百參數為 10 項核心配置，涵蓋 InnoDB 核心、連線、快取、日誌、複制等關鍵面向，調整前請遵循「一次只改一項、運行時動態驗證、永久生效寫入配置檔」原則。

核心三項（必看必改）

1. innodb_buffer_pool_size —— InnoDB 核心快取

儲存資料與索引頁，越大越好，減少磁碟 IO。

• 專用 DB 伺服器：實體記憶體 70-80%
• 共用伺服器：實體記憶體 10-20%
• 典型值：8GB RAM → 5-6GB；32GB → 20-25GB；128GB → 100-120GB

innodb_buffer_pool_size=6G

2. innodb_log_file_size —— Redo Log 大小

影響寫入效能與崩潰恢復速度。MySQL 5.5+ 崩潰恢復大幅優化，可設較大值。

• 起步建議：512M（兩個檔案共 1GB）
• 寫入密集、MySQL 5.6+：可設 1G-4G
• 總大小 < 5.1 版 4GB 限制（5.6+ 已移除）

innodb_log_file_size=1G
innodb_log_files_in_group=2

修改步驟：停庫 → 移除舊 ib_logfile* → 修改配置 → 啟庫。

3. max_connections —— 最大連線數

預設 151 易觸發 "Too many connections"。

• 應用端未正確關閉連線 → 需較大值（如 1000-2000）
• 過大（>5000）配合高併發活躍事務可能導致伺服器無回應
• 根治：應用層使用連線池、MySQL 端啟用 thread_pool（Percona/MariaDB）

max_connections=2000

InnoDB 進階配置

4. innodb_file_per_table —— 獨立表空間

• ON（5.6+ 預設）：每表一個 .ibd，支援 DROP/TRUNCATE 回收空間、壓縮、可傳輸表空間。
• OFF：共享表空間 ibdata1，空間難回收。
• 極多表（>10k）考慮關閉避免檔案描述符耗盡。

innodb_file_per_table=1

5. innodb_flush_log_at_trx_commit —— 事務持久化策略

6. innodb_flush_method —— 磁碟寫入方式

• 有硬體 RAID + BBU (Write-Back)：O_DIRECT 避免雙重緩衝
• 其他：fdatasync（預設）
• 用 sysbench 實測決定

7. innodb_log_buffer_size —— 事務 Log 緩衝

• 預設 1MB，大事務（含 BLOB/TEXT）易填滿觸發額外 IO
• 監控 Innodb_log_waits ≠ 0 時調大（如 8MB-64MB）

其他關鍵配置

8. query_cache_size —— 查詢快取（建議關閉）

• 已知瓶頸，高併發下鎖競爭嚴重
• MySQL 5.6+ 預設關閉（0），5.7 棄用，8.0 移除
• 替代方案：應用層 Redis/Memcached、優化索引、讀寫分離

query_cache_size=0
query_cache_type=0

9. log_bin —— 二進制日誌

• 主從複制、PITR 必備
• 啟用後需設 server_id 唯一值
• 配合 expire_logs_days 自動清理、max_binlog_size 限制單檔大小
• 非複制節點可關閉減少 IO

log_bin=mysql-bin
binlog_format=ROW
server_id=1
expire_logs_days=7
max_binlog_size=100M

10. skip_name_resolve —— 關閉 DNS 反解

• 客戶端連線時 MySQL 預設做主機名反解，DNS 慢會導致連線建立延遲
• 開啟後 GRANT 僅能用 IP，不支援主機名
• 既有系統遷移需謹慎，確認所有帳號改用 IP

skip_name_resolve=1

調整檢查清單

1. 修改配置檔（/etc/my.cnf 或 /etc/mysql/my.cnf）[mysqld] 區段
2. 動態參數用 SET GLOBAL 即時生效驗證
3. 重啟 MySQL 確認永久生效
4. 監控關鍵指標：Innodb_buffer_pool_reads、Threads_connected、Slow_queries、Qcache_hits（如開啟）
5. 記錄變更原因與時間，納入版本控制