官方配置模板對照
| 模板文件 | 適用場景 | 內存假設 |
|---|
my-huge.cnf | 高端專用服務器 | 1-2 GB，主跑 MySQL |
my-innodb-heavy-4G.cnf | 純 InnoDB 大查詢低流量 | 4 GB |
my-large.cnf | 中等規模生產 | ~512 MB |
my-medium.cnf | 低端生產 | <128 MB |
my-small.cnf | 極小設備/開發機 | <64 MB |
原則：模板僅為起點，**必須根據實際硬體、負載、數據量二次調優**。
核心參數分類詳解
一、 基礎路徑與安全
| 參數 | 建議值/說明 |
|---|
basedir | 安裝根目錄，如 /usr/local/mysql |
datadir | 數據目錄，獨立磁盤/分區，如 /data/mysql |
socket | /tmp/mysql.sock 或 /var/lib/mysql/mysql.sock |
pid-file | /var/run/mysqld/mysqld.pid |
port | 3306 (生產建議修改非默認端口) |
skip-name-resolve | 必開，禁用 DNS 反向解析 |
skip-ssl | 內網可關閉，外網必開 SSL |
bind-address | 指定綁定 IP，如 10.0.0.1，禁用 0.0.0.0 |
max_allowed_packet | 64M-256M，防大字段斷包 |
二、 連接與線程
| 參數 | 建議值 | 調優邏輯 |
|---|
max_connections | 1000-5000 | 峰值並發 * 1.5，配合 Thread Pool 可更高 |
thread_cache_size | 100-500 | 避免頻繁創建銷毀線程，Threads_created / Connections < 0.1 |
back_log | 512-1024 | TCP 連接隊列長度，防連接風暴丟包 |
wait_timeout | 300 (5分鐘) | 非交互式連接空閒回收，防連接洩漏 |
interactive_timeout | 600 | 交互式客戶端空閒回收 |
三、 InnoDB 核心 (生產最關鍵)
| 參數 | 建議值 | 核心影響 |
|---|
innodb_buffer_pool_size | 物理內存 60-80% | 數據+索引熱點緩存，**單一最重要參數** |
innodb_buffer_pool_instances | 1-8 (≤ buffer_pool/1GB) | 減少鎖競爭，多核必調 |
innodb_flush_log_at_trx_commit | 1 (金融) / 2 (高性能) | 1=最安全，2=每秒刷盤，性能提升 2-3 倍 |
innodb_log_file_size | 512M-2G | 大日志減少 checkpoint 壓力，啟動恢復稍慢 |
innodb_log_files_in_group | 2-4 | 循環寫入組數 |
innodb_file_per_table | ON | 獨立表空間，支援 DROP TABLE 回收空間、可攜帶 |
innodb_io_capacity | SSD: 2000-5000 / HDD: 200-500 | 後台刷頁/合併/清理 IOPS 上限 |
innodb_read_io_threads | 4-16 | 讀線程數，SSD 可調大 |
innodb_write_io_threads | 4-16 | 寫線程數 |
innodb_lock_wait_timeout | 50 (默認) | 死鎖等待超時，應用層捕獲重試 |
innodb_deadlock_detect | ON | 主動死鎖檢測，高並發可關閉靠超時 |
四、 查詢緩存 (MySQL 8.0 已移除)
# 5.7 及以前
query_cache_type = 0 # 關閉，高並發下鎖競爭極大
query_cache_size = 0
# 替代方案：Redis / Query Cache 插件 / ProxySQL
五、 臨時表與排序
| 參數 | 建議值 | 說明 |
|---|
tmp_table_size | 64M-256M | 內存臨時表上限，超過轉 MyISAM 磁盤表 |
max_heap_table_size | 同 tmp_table_size | 兩者取小生效 |
sort_buffer_size | 2M-8M (會話級) | ORDER BY/GROUP BY 排序緩存，過大導致 OOM |
join_buffer_size | 2M-8M (會話級) | 無索引 JOIN 緩存，慎調大 |
read_buffer_size | 2M | 全表掃描順序讀緩存 |
read_rnd_buffer_size | 4M | 隨機讀 (如排序後) 緩存 |
六、 日志與複製
| 參數 | 建議值 |
|---|
log_bin | ON (主庫必開)，mysql-bin |
binlog_format | ROW (最安全，支援複製一致性) |
expire_logs_days | 7-14 |
max_binlog_size | 1G |
slow_query_log | ON |
long_query_time | 0.5-1 (秒) |
log_queries_not_using_indexes | ON (慎開，量大) |
sync_binlog | 1 (金融) / 100-1000 (高性能) |
server_id | 唯一整數，如 IP 末段 * 100 + 角色 |
gtid_mode | ON (5.6+ 推薦) |
enforce_gtid_consistency | ON |
內存分配公式 (防 OOM)
總內存 ≈
innodb_buffer_pool_size
+ key_buffer_size (MyISAM 少則 32M)
+ (read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) * max_connections
+ max_connections * thread_stack (256K)
+ table_open_cache * 表結構開銷 (~1-2KB)
+ 系統保留 (1-2 GB)
鐵律：會話級緩存 (sort/join/read) × max_connections 不得超過物理內存 30%。
生產環境最小可用模板 (8GB 內存、SSD、主庫)
[mysqld]
# 基礎
basedir = /usr/local/mysql
datadir = /data/mysql
socket = /tmp/mysql.sock
port = 3306
server_id = 101
skip-name-resolve
max_connections = 2000
thread_cache_size = 200
back_log = 1024
# InnoDB 核心
innodb_buffer_pool_size = 6G
innodb_buffer_pool_instances = 6
innodb_flush_log_at_trx_commit = 1
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_file_per_table = ON
innodb_io_capacity = 3000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
# 臨時表/排序
tmp_table_size = 128M
max_heap_table_size = 128M
sort_buffer_size = 4M
join_buffer_size = 4M
# 日志/複製
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
slow_query_log = ON
long_query_time = 0.5
gtid_mode = ON
enforce_gtid_consistency = ON
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
總結
MySQL 調優無銀彈，**從業務負載出發，監控關鍵指標 (Buffer Pool 命中率、QPS、慢查詢、鎖等待、IOPS)，針對性調整 3-5 個核心參數**，遠勝盲目套用大全模板。定期 SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS、sys.schema 觀測，建立基線，迭代優化。
