Linux 下 MySQL my.cnf 核心參數配置詳解與調優建議
MySQL 預設不建立 my.cnf，需從 /usr/local/share/mysql/ 複製範本（my-small/medium/large/huge/innodb-heavy-4G.cnf）至 /etc/my.cnf、資料目錄或 ~/.my.cnf。本文針對 4GB 記憶體伺服器，詳細解析 [mysqld] 區塊關鍵參數的含義、建議值與調優邏輯。
連線與網路層
| 參數 | 建議值 | 說明 |
|---|
port | 3306 | 預設埠號 |
socket | /tmp/mysql.sock | 本地 Unix socket 路徑 |
skip-locking | 啟用 | 避免外部鎖定，減少錯誤、增強穩定 |
skip-name-resolve | 啟用 | 禁止 DNS 解析，消除解析延遲。啟用後授權表 Host 必須用 IP |
back_log | 384 (<512) | 連接請求堆疊大小，爆發連線時緩衝。Linux 建議 <512 |
max_connections | 768 | 最大連線數。頻繁 Too many connections 時調大 |
max_connect_errors | 10000000 | 防止暴力破解導致 Host 被封鎖 |
wait_timeout | 10 (5-10) | 空閒連線最大存活秒數，釋放資源 |
skip-networking | 視情況 | 關閉 TCP/IP，僅允許本地 socket。Web 遠端連線勿開啟 |
緩衝區與快取層（每連線獨佔，乘以連線數 = 總佔用）
| 參數 | 建議值 | 說明 |
|---|
key_buffer_size | 256M-384M | MyISAM 索引緩衝區。過大反降效能，4GB 記憶體建議 256-384M |
sort_buffer_size | 6M-8M | 排序緩衝。100 連線 × 8M = 800MB，謹慎設定 |
read_buffer_size | 4M | 循序掃描讀取緩衝 |
join_buffer_size | 8M | 無索引聯結時的緩衝。每連線獨佔 |
read_rnd_buffer_size | 16M | 隨機讀取緩衝（如排序後讀取） |
InnoDB 專屬核心參數
| 參數 | 建議值 | 說明 |
|---|
innodb_buffer_pool_size | 2G (70-80% 記憶體) | InnoDB 最關鍵參數。緩存資料+索引。僅用 InnoDB 可設 70-80% |
innodb_additional_mem_pool_size | 16M-20M | 內部資料結構緩衝，現代 OS 影響較小 |
innodb_log_file_size | 256M | Redo log 檔案大小。大 = 寫入快、恢復慢。平衡點 64-512M |
innodb_log_buffer_size | 8M-16M | Log 寫入緩衝。寫入峰值大則調大，每秒刷盤一次 |
innodb_flush_log_at_trx_commit | 2 | 效能與安全平衡。1=每提交刷盤（最安全慢）；2=刷 OS 快取（每秒刷盤，丟 1-2 秒）；0=最快最不安全 |
innodb_thread_concurrency | 16 (邏輯 CPU × 2) | InnoDB 內部並發執行緒上限。0=不限制 |
innodb_flush_method | O_DIRECT | 避免雙重緩衝、降低 swap 壓力。RAID 快取不足時慎用 |
innodb_file_per_table | ON | 獨立表空間，避免共享表空間無限膨脹、利於管理 |
transaction_isolation | READ-COMMITTED | 若應用允許，比 REPEATABLE-READ 效能更好、支援行級復制 |
查詢快取與表快取
| 參數 | 建議值 | 說明 |
|---|
query_cache_size | 64M | 查詢快取總大小。過大維護開銷大。監控 Qcache_lowmem_prunes（大=不夠）、Qcache_hits（大=命中高）、Qcache_free_blocks（大=碎片多） |
query_cache_limit | 2M | 單一查詢結果最大快取值 |
table_cache | 512-2048 | 開啟表快取數。open_tables 接近此值則調大。大量表需調高 |
thread_cache_size | 64 | 執行緒快取。避免頻繁建立/銷毀。監控 Threads_Created 持續增長則調大 |
thread_concurrency | 8 (邏輯 CPU × 2) | Solaris/LinuxThreads 相關，新版 NPTL 影響較小 |
其他重要參數
| 參數 | 建議值 | 說明 |
|---|
max_allowed_packet | 16M | 單次通訊封包上限，大 BLOB/長 SQL 需調大 |
tmp_table_size | 64M-256M | 記憶體臨時表上限，超過轉磁碟 MyISAM 表 |
max_heap_table_size | 64M | MEMORY 表最大值，配合 tmp_table_size |
bulk_insert_buffer_size | 64M | MyISAM 批次插入緩衝 |
myisam_sort_buffer_size | 128M | MyISAM 建索引排序緩衝 |
log_slow_queries | 啟用 | 慢查詢日誌路徑 |
long_query_time | 2 | 超過秒數記錄慢查詢 |
配置檢查與生效
- 修改後重啟 MySQL：
systemctl restart mysqld
- 驗證參數生效：
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
- 觀察狀態變數：
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
- 根據實際負載持續微調，單次只改一個參數並觀察效果
完整範例配置（4GB 記憶體、InnoDB 主力）
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-name-resolve
back_log = 384
max_connections = 768
max_connect_errors = 10000000
wait_timeout = 10
key_buffer_size = 256M
sort_buffer_size = 6M
read_buffer_size = 4M
join_buffer_size = 8M
read_rnd_buffer_size = 16M
innodb_buffer_pool_size = 2G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_thread_concurrency = 16
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
transaction_isolation = READ-COMMITTED
query_cache_size = 64M
query_cache_limit = 2M
table_cache = 1024
thread_cache_size = 64
thread_concurrency = 8
tmp_table_size = 256M
max_heap_table_size = 64M
max_allowed_packet = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
log_slow_queries = /var/log/mysql/slow.log
long_query_time = 2
log_queries_not_using_indexes
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
