前言：參數調優的核心哲學
MySQL 參數調優無銀彈，需依業務負載（OLTP/OLAP）、資料量、併發數、硬體配置（CPU/記憶體/SSD）綜合權衡。本文詳細解析生產環境核心參數，提供調優方向與避坑指南。
連接層與線程配置
| 參數 | 建議值/策略 | 核心影響 |
|---|
max_connections | 1000~5000 (視記憶體) | 最大連接數，過大導致 OOM，配合 thread_cache_size 緩解頻繁建連開銷 |
back_log | 600~2000 | 連接請求隊列長度，應對突發連接風暴 |
thread_cache_size | 8 (1G) / 16 (2G) / 32 (4G+) | 線程緩存，降低 Threads_created，觀察 Connections/Threads_created 比值調整 |
wait_timeout / interactive_timeout | 28800 (8h) 或更短 | 清理閒置連接防洩漏，需同時設定生效 |
max_connect_errors | 6000+ | 防止暴力破解導致 Host 被封鎖 |
記憶體分配核心參數 (InnoDB 專用)
| 參數 | 建議值/策略 | 核心影響 |
|---|
innodb_buffer_pool_size | 物理記憶體 60%~80% (專用 DB) | 最關鍵參數，緩存數據與索引，直接決定讀寫效能。32 位系統受限 2-3.5GB |
innodb_buffer_pool_instances | 1~8 (建議 1GB/實例) | 減少全域互斥鎖爭用，提升併發 |
innodb_log_buffer_size | 16M~64M | 大事務需調大，減少磁碟 I/O |
innodb_log_file_size | 256M~1G (總大小 1G~4G) | 大檔減少 Checkpoint 壓力，但增加恢復時間 |
innodb_log_files_in_group | 3~4 | 循環寫入 Redo Log |
innodb_flush_log_at_trx_commit | 1 (金融/核心) / 2 (高併發日誌/可容忍 1s 數據丟失) | ACID 與效能權衡核心。1=最安全；2=寫入 OS Cache 每秒刷盤；0=每秒刷盤 |
innodb_file_per_table | ON (1) | 獨立表空間，支援線上 DDL、空間回收、單表遷移 |
innodb_open_files | 500~2000+ | 配合獨立表空間，避免「Too many open files」 |
查詢緩存與排序緩衝 (慎用)
query_cache_type / query_cache_size：MySQL 5.7+ 已棄用，8.0 移除。高併發寫入場景下鎖爭用嚴重，建議關閉 (0)，改用 Redis 等外部緩存。sort_buffer_size / read_buffer_size / join_buffer_size / read_rnd_buffer_size：每連接獨享，預設 256K~2M 即可，盲目調大極易導致 OOM。僅在特定慢查詢經 EXPLAIN 分析確需 Filesort 時酌情調大 Session 級別。
索引與表緩存
| 參數 | 建議值/策略 |
|---|
table_open_cache | 2000~10000+ (配合 open_files_limit) |
table_definition_cache | 1400~4000+ |
key_buffer_size | MyISAM 專用，InnoDB 略設 32M~64M 供系統表 |
複寫與二進位日誌
log_bin = mysql-bin
binlog_format = ROW # ROW 格式最安全，支援 GTID
expire_logs_days = 7 # 磁碟空間許可下保留更久
sync_binlog = 1 # 每次提交同步刷盤，配合 innodb_flush_log_at_trx_commit=1 保證不丟數據
gtid_mode = ON
enforce_gtid_consistency = ON
master_info_repository = TABLE
relay_log_info_repository = TABLE
字符集與 SQL 模式
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE'
其他關鍵參數
skip_name_resolve = ON：禁用 DNS 反解，加速連接建立，需授權表改用 IP。explicit_defaults_for_timestamp = ON：避免 TIMESTAMP 隱式預設值行為差異。tmp_table_size / max_heap_table_size = 64M~256M：內部臨時表大小，超限轉 MyISAM 磁碟表。bulk_insert_buffer_size = 64M：MyISAM 批次插入優化。
調優方法論
- 基線測量：收集 `SHOW GLOBAL STATUS`、`SHOW ENGINE INNODB STATUS`、慢查詢日誌、`pmem`/`iostat`/`vmstat` 系統指標。
- 定位瓶頸：CPU 高？鎖爭用？I/O 等待？記憶體不足？
- 單參數變更：一次只調一個核心參數，觀察負載測試結果。
- 持續迭代：業務增長、版本升級、硬體變更皆需重新評估。
總結
MySQL 參數調優的核心在於 InnoDB Buffer Pool、Redo Log、連接管理、SQL 模式 四大支柱。拒絕盲目複製網路模板，需建立「監控→分析→調整→驗證」的閉環機制，才能釋放硬體最大效能。
