MySQL 性能優化核心：my.cnf 關鍵參數深度解析與硬體配置對應指南

MySQL 性能優化核心：my.cnf 關鍵參數深度解析與硬體配置對應指南

在 LAMP 架構中，MySQL 是效能瓶頸的核心。優化分兩層：物理硬體（磁碟 I/O、CPU、記憶體）與配置參數。本文以 2×Xeon 2.4GHz、4GB 記憶體、SCSI 73GB 為基準環境，解析生產級 my.cnf 關鍵參數。

一、硬體層面優化方向

二、my.cnf 關鍵參數詳細解析（4GB 記憶體基準）

[mysqld]
port = 3306
server_id = 1
socket = /tmp/mysql.sock

# 核心優化參數
skip-locking                    # 關閉外部鎖定，提升穩定性
skip-name-resolve               # 禁用 DNS 解析，授權僅用 IP，極大降低連線延遲
back_log = 384                  # 連線等待隊列，Linux 建議 <512
key_buffer_size = 256M          # MyISAM 索引緩存，4GB 機器 256-384M，過大反拖慢
max_allowed_packet = 4M
thread_stack = 256K
table_cache = 512               # 表緩存，物理記憶體越大越調高
sort_buffer_size = 6M           # 排序緩存/連線，總量 = 連線數 × 6M，建議 6-8M
read_buffer_size = 4M           # 讀掃描緩存/連線
join_buffer_size = 8M           # 聯結緩存/連線
myisam_sort_buffer_size = 64M   # MyISAM 建索引排序緩存
thread_cache_size = 64          # 執行緒緩存，減少建立/銷毀開銷
query_cache_size = 64M          # 查詢緩存，觀察 Qcache_lowmem_prunes/Qcache_hits 調整
tmp_table_size = 256M           # 臨時表記憶體上限，超過轉磁碟 MyISAM
max_connections = 768           # 最大連線數，Too many connections 時調大
max_connect_errors = 10000000   # 防暴力破解
wait_timeout = 10               # 空閒連線回收，4GB 機器 5-10 秒
thread_concurrency = 8          # 邏輯 CPU 核心數 × 2（含超執行緒）

# InnoDB 專用
innodb_additional_mem_pool_size = 4M
innodb_flush_log_at_trx_commit = 1  # 1=最安全，0=高效能風險丟數據，2=折中
innodb_log_buffer_size = 2M
innodb_thread_concurrency = 8       # CPU 核心數

# 重複參數以最後生效者為準（下方覆蓋上方）
key_buffer_size = 256M
tmp_table_size = 64M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
sort_buffer_size = 32M
thread_cache_size = 120
query_cache_size = 32M

三、動態監控指標與調整依據

四、核心原則

1. 參數無萬能值，必須 基於實際負載監控數據調整；
2. 修改後必須 FLUSH PRIVILEGES 或重啟生效；
3. 大內存機器重點調大 innodb_buffer_pool_size（佔 70-80% 物理記憶體），而非 key_buffer；
4. 定期 SHOW GLOBAL STATUS 快照建立基準線，趨勢分析勝過單點數值。