高手優化 MySQL 實戰配置範本解析：參數選擇邏輯與調優思路

配置範本來源

流傳較廣的「高手優化 my.cnf」，適用於雙核/四核、4-8GB 記憶體、中等負載的 MySQL 5.x 專用資料庫伺服器。

完整配置內容

[mysqld]
basedir = D:/MySQL
datadir = D:/MySQL/data
# bind-address = 0.0.0.0
# port = 3306

# 核心連線與並發
max_connections = 1500
thread_cache_size = 8
thread_concurrency = 8        # 建議 CPU 核心數 * 2
back_log = 500                # 突發連線隊列

# 緩衝區與快取
key_buffer_size = 384M        # MyISAM 索引快取（視實際引擎調整）
table_cache = 512             # 表快取 (5.7+ 為 table_open_cache)
sort_buffer_size = 2M         # 排序快取
read_buffer_size = 2M         # 循序讀快取
read_rnd_buffer_size = 4M     # 隨機讀快取
myisam_sort_buffer_size = 64M # MyISAM 建索引快取

# 通訊封包
max_allowed_packet = 1M       # 較保守，大 BLOB 應調大

# 鎖定與並發
skip-locking                  # 5.5+ 已棄用，改用 skip-external-locking
# skip-networking             # 僅本機連線時開啟

# 慢查詢與日誌
# slow_query_log = 1
# long_query_time = 2
# log_error = error.log

# 複寫
server-id = 1
# log-bin = mysql-bin
# binlog_format = ROW

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[WinMySQLadmin]
Server = D:/MySQL/bin/mysqld-nt.exe
user = root
password = xxxxxxxx

參數選擇邏輯深度解析

遺漏的關鍵參數（必補）

# InnoDB 核心（若使用 InnoDB 必須顯式設定）
innodb_buffer_pool_size = 2G      # 物理記憶體 60-70%
innodb_log_file_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# 字元集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 慢查詢（生產必開）
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# 安全
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

調優思路總結

1. 基線配置：依硬體（CPU、記憶體、磁碟）給出合理初始值。
2. 啟用觀測：慢查詢日誌、Prometheus/Grafana 監控核心指標。
3. 瓶頸定位：CPU 高 → 查詢優化/索引；IO 高 → buffer_pool/磁碟；鎖等待 → 交易設計/隔離級別。
4. 單參數微調：一次改一個，觀察效果，留存基線對比。
5. 版本演進：5.7/8.0 參數預設值已大幅優化，避免沿用舊版經驗值。

配置範本是起點，監控驅動的迭代才是正道。