MySQL 效能調優：my.cnf 關鍵參數設定全攻略

配置檔案機制

MySQL 透過 my.cnf 參數調優，啟動時自動套用。官方提供四種預設範本：

三種生效範圍

1. 全域：/etc/my.cnf — 影響該主機所有 MySQL 實例
2. 實例級：[MYSQL-INSTALL-DIR]/var/my.cnf — 僅影響特定實例
3. 使用者級：~/.my.cnf — 僅影響該使用者啟動的實例

優先順序：使用者級 > 實例級 > 全域

如何發現可調參數

/usr/local/mysql/libexec/mysqld --help
# 找到 "Possible variables for option --set-variable (-O) are" 後的列表

參數設定語法

[mysqld]
set-variable = max_connections=100
# 或新版語法：
max_connections = 100

核心效能參數分類

連接與執行緒

• max_connections：最大同時連接數（預設 151）
• thread_cache_size：執行緒快取，減少建立/銷毀開銷
• thread_concurrency：建議 2 x CPU 核心數

緩衝區與快取

• key_buffer_size：MyISAM 索引快取（關鍵，建議 實體記憶體 25-30%）
• innodb_buffer_pool_size：InnoDB 資料+索引快取（關鍵，建議 實體記憶體 70-80%）
• query_cache_size：查詢結果快取（高讀少寫場景有效）
• query_cache_type：0=關閉, 1=開啟, 2=按需
• sort_buffer_size：排序緩衝（每連接）
• read_buffer_size：循序掃描緩衝
• read_rnd_buffer_size：隨機讀取緩衝

日誌與恢復

• innodb_log_file_size：交易日誌大小（大 = 恢復慢，寫入快）
• innodb_flush_log_at_trx_commit：1=最安全, 2=平衡, 0=最快
• sync_binlog：二進位日誌同步頻率

表與檔案

• table_open_cache：表快取數量
• max_heap_table_size / tmp_table_size：記憶體臨時表上限

調優原則

1. 一次只調一個參數，觀察效果
2. 以實際負載測試為準，非理論值
3. 監控關鍵指標：Threads_connected、Key_reads/Key_read_requests、Innodb_buffer_pool_reads、Qcache_hits
4. 修改後必須重啟 MySQL 生效（部分參數可 SET GLOBAL 熱調整）