MySQL 服務器調優核心：my.cnf 配置文件選型、關鍵變數調優與作用域解析

MySQL 效能 30% 看硬體，70% 看配置。my.cnf (Linux) / my.ini (Windows) 是調優的核心入口。官方提供四套樣本配置對應不同規模實例，理解變數作用域與關鍵參數含義，是 DBA 必修課。

官方樣本配置文件選型指南

配置文件	適用場景	記憶體需求	核心特徵
my-small.cnf	嵌入式、開發機、< 256MB RAM	< 256 MB	最小資源佔用，關閉多數快取
my-medium.cnf	中小型應用、共享主機	256 MB - 512 MB	平衡快取與記憶體
my-large.cnf	專用資料庫伺服器	512 MB - 2 GB	大快取、多執行緒並發
my-huge.cnf	大型電商/核心業務	> 2 GB (如 4-16 GB)	最大快取、高並發、大排序緩衝

位置：/usr/local/mysql/share/mysql/my-*.cnf (源碼安裝) 或 /usr/share/mysql/ (套件安裝)

配置文件三種作用域與優先級

作用域	放置路徑	生效範圍	優先級
全域	/etc/my.cnf 或 /etc/mysql/my.cnf	該主機所有 MySQL 實例	低 (被實例/用戶覆蓋)
實例級	[mysql-install-dir]/my.cnf 或 /var/lib/mysql/my.cnf	特定實例 (data 目錄下)	中
用戶級	~/.my.cnf (用戶家目錄)	該用戶啟動的客戶端/實例	高 (最高)

讀取順序：全域 → 實例級 → 用戶級 → 命令列參數 (最高)。後讀取者覆蓋先讀取者。

核心調優變數深度解析

1. 連線層

max_connections = 500              # 最大並發連線 (視記憶體與檔案描述符限制)
max_connect_errors = 100000        # 防止主機被封鎖
wait_timeout = 28800               # 非互動連線超時 (8小時)
interactive_timeout = 28800        # 互動連線超時
thread_cache_size = 100            # 執行緒快取，減少建立/銷毀開銷

2. InnoDB 核心 (最關鍵)

innodb_buffer_pool_size = 4G       # **核心**：專用機設為物理記憶體 70-80%
innodb_log_file_size = 512M        # Redo Log 大小，大 = 寫入快、恢復慢
innodb_log_buffer_size = 64M       # 事務日誌緩衝
innodb_flush_log_at_trx_commit = 1 # 1=ACID最嚴格，2=高效能(秒級丟失), 0=最高效能
innodb_file_per_table = ON         # 獨立表空間，支援單表優化/遷移
innodb_read_io_threads = 8         # 讀 IO 執行緒
innodb_write_io_threads = 8        # 寫 IO 執行緒
innodb_io_capacity = 2000          # SSD 建議 2000-5000，HDD 200

3. 查詢快取 (MySQL 5.7 已棄用，8.0 移除)

query_cache_type = 0               # 關閉 (高並發下鎖爭用嚴重)
query_cache_size = 0

4. 臨時表與排序

tmp_table_size = 64M               # 記憶體臨時表上限
max_heap_table_size = 64M          # MEMORY 引擎表上限
sort_buffer_size = 2M              # 每連線排序緩衝 (謹慎調大，乘以連線數)
read_rnd_buffer_size = 1M          # 隨機讀緩衝
join_buffer_size = 256K            # Join 無索引時緩衝

5. Binlog 與複製

log_bin = mysql-bin
binlog_format = ROW                # ROW 格式最安全，支援混合模式
expire_logs_days = 7               # 自動清理 7 天前 Binlog
max_binlog_size = 1G               # 單檔上限
sync_binlog = 1                    # 1=最安全(每提交同步)，0=由OS決定，N=每N次同步

查看當前實例所有可調變數

# 命令列
mysqld --help --verbose | grep -A1 '^possible variables'

# 或登入後
SHOW VARIABLES;
SHOW GLOBAL VARIABLES LIKE 'innodb_%';

配置變更生效方式

變數類型	生效方式	範例
動態 (Dynamic)	SET GLOBAL var = value 立即生效，配置檔同步修改持久化	SET GLOBAL innodb_buffer_pool_size = 6G; (5.7.5+ 支援線上調整)
靜態 (Static)	修改配置檔 + **重啟服務**	innodb_log_file_size, innodb_buffer_pool_instances
只讀	不可修改，僅供參考	version, protocol_version

調優驗證腳本

-- 關鍵健康度一鍵檢查
SELECT 
    'Buffer Pool Hit Ratio' AS Metric,
    (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads') / 
         NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_read_requests'),0)) * 100 AS Value
UNION ALL
SELECT 'Page Life Expectancy (est)', 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_pages_data') * 16 / 1024 / 1024 / 
    NULLIF((SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Innodb_buffer_pool_reads'),0) * 3600
UNION ALL
SELECT 'Max Connections Used %',
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME='Max_used_connections') / 
    (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME='max_connections') * 100;

避坑指南

• ❌ **盲目套用 my-huge.cnf** 記憶體不足 → OOM Killer 殺進程
• ❌ **sort_buffer_size/join_buffer_size 設太大** → 乘以 max_connections 爆記憶體
• ❌ **修改靜態參數未重啟** → 以為生效實際無效
• ❌ **忽略 innodb_flush_log_at_trx_commit=1 的 IOPS 壓力** → SSD 必須、HDD 需權衡
• ❌ **查詢快取開啟** (5.7-) → 高並發下全局鎖瓶頸

總結

MySQL 調優鐵律：
1. **選對樣本配置** 作為基線 (small/medium/large/huge)
2. **核心三參數** 定乾坤：innodb_buffer_pool_size (70-80% RAM)、innodb_log_file_size (512M-2G)、max_connections (配合 thread_cache_size)
3. **作用域正確**：生產環境改實例級 /var/lib/mysql/my.cnf，重啟生效
4. **動態調整驗證**：SET GLOBAL 後 SHOW VARIABLES 確認
5. **基線監控**：建立關鍵指標儀表板，變更前後對比