MySQL 新安裝必調 10 項核心配置：從緩衝池、Redo Log 到查詢快取與二進位日誌

MySQL 全新安裝必調 10 項關鍵配置：快速獲得穩健生產就緒參數

MySQL 可調參數超百個，但**改動 20% 參數解決 80% 問題**。本文精選新安裝必調的 10 項核心配置，涵蓋 InnoDB 核心、連線、查詢快取、二進位日誌、DNS 解析，附單位陷阱、修改驗證與漸進式調優原則。

鐵律：一次只改一項、運行時 SET GLOBAL 驗證、配置檔永久生效、重啟驗證、建立基準線對比。

一、InnoDB 核心三大件 (效能基石)

1. innodb_buffer_pool_size — 記憶體分配核心

• 作用：資料頁、索引頁、鎖資訊、自適應雜湊索引緩存
• 建議：實體記憶體 60-80% (專用 DB 伺服器)
• 範例：8GB RAM → 5-6GB；32GB → 20-25GB；128GB → 100-120GB
• 單位：位元組 (支援 G/M/K 後綴)，如 innodb_buffer_pool_size=6G

2. innodb_log_file_size — Redo Log 大小 (寫入效能與恢復平衡)

• 作用：循環寫入 Redo Log，保證交易持久化、崩潰恢復
• 5.5 前總上限 4GB (2 檔)；5.6+ 擴大上限
• 建議：512M - 2G (寫入密集可調大至 4G+)
• 修改需停服務、刪除舊 ib_logfile*、重啟

3. max_connections — 最大連線數

• 預設 151，易觸發 Too many connections
• 建議：500-2000 (視應用連線池大小、並發量)
• 搭配連線池 (HikariCP) 使用，避免連線洩漏導致耗盡
• 過大 (如 10000+) 可能導致記憶體壓力、鎖爭用劇增

二、InnoDB 進階四項 (穩定性與效能微調)

4. innodb_file_per_table — 獨立表空間 (預設 ON 5.6+)

• ON：每表獨立 .ibd，支援 DROP/TRUNCATE 回收空間、壓縮、可攜式表空間
• OFF：共享表空間 ibdata1，無法回收空間
• 極大表量 (10萬+) 考慮 OFF 減少檔案控制代

5. innodb_flush_log_at_trx_commit — 交易持久化策略

6. innodb_flush_method — I/O 系統呼叫方式

• 有硬體 RAID + BBWC (電池保護寫回快取) → O_DIRECT (繞過 OS Cache，減少雙重快取)
• 無 RAID 或不確定 → fsync (預設，安全)
• 用 sysbench 實測決定

7. innodb_log_buffer_size — 交易日誌緩衝

• 預設 1MB，大交易 (含 BLOB/TEXT) 易填滿觸發額外 I/O
• 觀察 Innodb_log_waits > 0 時調大至 8-64MB

三、系統級兩項 (查詢快取與二進位日誌)

8. query_query_cache_size — 查詢快取 (建議關閉)

• 已知瓶頸：高並發下全域鎖爭用劇烈、失效率高
• MySQL 5.6 預設 OFF (0)，5.7 移除
• 建議：query_cache_type=0、query_cache_size=0
• 替代：Redis、應用層快取、從庫分流讀取

9. log_bin — 二進位日誌 (主從、PITR 必備)

• log_bin=mysql-bin、server_id=1 (唯一)
• expire_logs_days=7 自動清理
• max_binlog_size=100M 控制單檔大小
• 非複寫節點可關閉減少 I/O

10. skip_name_resolve — 關閉 DNS 反向解析

• 啟用後：GRANT 僅能用 IP、SHOW PROCESSLIST Host 顯示 IP
• 解決連線緩慢 (DNS 卡頓)、安全審計便利
• 現有系統啟用需同步更新權限表 Host 值

配置檔範本 (my.cnf 核心段)

[mysqld]
# 基礎
basedir=/usr/local/mysql
datadir=/data/mysql
port=3306
server_id=1
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci

# InnoDB 核心
innodb_buffer_pool_size=6G
innodb_log_file_size=1G
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_file_per_table=ON

# 連線
max_connections=1000
skip_name_resolve=1

# Binlog
log_bin=mysql-bin
expire_logs_days=7
max_binlog_size=100M
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
log_slave_updates=ON

# 查詢快取關閉
query_cache_type=0
query_cache_size=0

# 其他
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp=1

驗證與漸進式調優

1. 修改配置 → systemctl restart mysqld
2. 檢查錯誤日誌：tail -f /var/log/mysqld.log
3. 核心參數生效確認：SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
4. 運行 sysbench / tpcc-mysql 基準測試，建立效能基線
5. 每次單一參數微調，對比 QPS、延遲、CPU、I/O

避坑提醒：單位錯誤 (如 innodb_buffer_pool_size=6 會被視為 6 位元組)、重複參數 (後蓋前)、區段錯誤 (寫在 [client] 區)、編碼非 UTF-8 導致解析失敗。