MySQL 5.5.x my.cnf 核心參數深度解析與優化模板
針對雙路 E5620、16GB 內存、主流 Web 應用（日 IP 50-100萬、PV 1-3億）、InnoDB 為主的典型配置，本文逐項解析核心參數含義、調優邏輯與推薦值，並提供可直接落地的模板。
一、 客戶端與連接層
[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8
[mysql]
no-auto-rehash # 禁用自動補全，加快啟動
[mysqld]
port = 3306
socket = /tmp/mysql.sock
bind-address = 0.0.0.0 # 監聽所有網卡，內網環境建議限定具體 IP
skip-name-resolve # 禁用 DNS 反解，避免連接延遲
skip-external-locking # 禁用外部鎖，提升性能
二、 連接與緩衝區
| 參數 | 推薦值 | 說明 |
|---|
| max_connections | 3000 | 最大連接數，配合應用連接池上限設定，預留 10% 給管理連接 |
| max_connect_errors | 100000 | 防止誤封禁，配合定期 FLUSH HOSTS |
| back_log | 300 | TCP 監聽隊列長度，高並發突發時緩衝 |
| thread_cache_size | 100 | 線程緩存，減少創建/銷毀開銷，監控 Threads_created 指標調整 |
| thread_concurrency | 8 | 僅 Solaris 生效，Linux 忽略 |
| sort_buffer_size | 2M | 會話級排序緩衝，過大易 OOM，複雜排序臨時調大 |
| join_buffer_size | 2M | 無索引 JOIN 緩衝，同 sort_buffer 原則 |
| read_buffer_size | 2M | 全表掃描順序讀緩衝 |
| read_rnd_buffer_size | 8M | 隨機讀（如排序後）緩衝 |
三、 查詢緩存（5.5 仍可用，8.0 移除）
query_cache_type = 1 # ON
query_cache_size = 128M # 總大小，建議 ≤ 256M，過大鎖競爭劇增
query_cache_limit = 4M # 單查詢結果上限
query_cache_min_res_unit = 2K # 減少碎片
# 寫入頻繁的表建議 SQL_NO_CACHE 或關閉 QC
四、 InnoDB 核心參數（重中之重）
| 參數 | 推薦值（16GB 內存） | 調優邏輯 |
|---|
| innodb_buffer_pool_size | 10G (≈ 70% 物理內存) | 數據+索引熱點全內存，監控 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率 > 99% |
| innodb_buffer_pool_instances | 8 | 減少鎖競爭，每實例 ≥ 1GB，5.5 需手動設定 |
| innodb_log_file_size | 512M | 單文件大小，總大小 = size × files_in_group ≈ Buffer Pool 25%，大文件減少 checkpoint 壓力但延長恢復時間 |
| innodb_log_files_in_group | 2 | 循環寫入組數 |
| innodb_log_buffer_size | 16M | 事務日誌緩衝，大事務可適當調大 |
| innodb_flush_log_at_trx_commit | 2 | 1=最安全（每提交 fsync），2=每秒 fsync（推薦，性能提升 10 倍，最多丟 1 秒），0=最高性能風險最大 |
| innodb_file_per_table | ON | 獨立表空間，便於回收空間、單表備份、攜帶統計信息 |
| innodb_flush_method | O_DIRECT | 繞過 OS 緩存，減少雙重緩存與 fsync 開銷，需文件系統支援 |
| innodb_thread_concurrency | 0 (無限) | 5.5+ 建議 0 讓引擎自適應，舊版設為 2×CPU 核心 |
| innodb_read_io_threads | 8 | 讀線程數，SSD 可調大 |
| innodb_write_io_threads | 8 | 寫線程數 |
| innodb_lock_wait_timeout | 50 | 鎖等待超時，避免長事務阻塞 |
| innodb_deadlock_detect | ON | 死鎖自動檢測回滾 |
五、 日誌與復制
# 二進制日誌（主從、PITR 必備）
log-bin = mysql-bin
binlog_format = ROW # ROW 格式最安全，MIXED 兼容性好
expire_logs_days = 7 # 自動清理 7 天前日誌
max_binlog_size = 512M # 單文件上限
sync_binlog = 1 # 每提交同步到磁碟，配合 RAID BBU 可設 0 提升性能
# 慢查詢日誌
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1 # 秒，核心業務建議 0.5
log_queries_not_using_indexes = 1 # 記錄無索引查詢
# 錯誤日誌
log-error = /data/mysql/logs/error.log
log-warnings = 2 # 記錄更多警告（如中斷連接）
六、 MyISAM 兼容參數（僅用於系統表/臨時表）
key_buffer_size = 64M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover = FORCE,BACKUP
七、 臨時表與內存表
tmp_table_size = 256M
max_heap_table_size = 256M
# 超過此大小自動轉磁碟 MyISAM 表，監控 Created_tmp_disk_tables 指標
八、 完整生產模板（精簡版）
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql
port = 3306
socket = /tmp/mysql.sock
server-id = 1
character-set-server = utf8
collation-server = utf8_general_ci
skip-name-resolve
skip-external-locking
max_connections = 3000
max_connect_errors = 100000
back_log = 300
thread_cache_size = 100
table_open_cache = 4096
table_definition_cache = 2048
open_files_limit = 65535
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
# InnoDB
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_lock_wait_timeout = 50
# Binlog
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 512M
sync_binlog = 1
# Slow log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
[mysqldump]
quick
max_allowed_packet = 64M
[mysql]
no-auto-rehash
[mysqld_safe]
open-files-limit = 65535
log-error = /data/mysql/logs/error.log
九、 參數調優避坑指南
- 切勿盲目複製大內存模板：小內存機器設大 Buffer Pool 會導致 Swap、OOM Killer。
- 修改 innodb_log_file_size 需停機操作：停服 → 刪除舊 ib_logfile* → 改配置 → 啟動。
- query_cache_size > 256M 通常有害：全局鎖競爭抵消命中收益，寫入密集場景建議關閉。
- max_connections 過大需配合 open_files_limit、ulimit -n：系統級文件描述符限制必須同步調大。
- 定期用 pt-variable-advisor、MySQLTuner 檢查配置：自動化發現失誤。
