參數調優不是「調大就快」，而是匹配硬件資源與業務模式。核心公式：有效緩存 = 內存 - (連接緩存 × 最大連接數) - 系統預留 - 其他引擎緩存。
| 參數 | 默認 | 建議值 | 調優邏輯 |
|---|---|---|---|
innodb_buffer_pool_size | 128M | 物理內存 60-80% (專用 DB 服務器) | 數據+索引+鎖+自適應哈希+插入緩衝全部在這。設太小→磁盤 IO 暴增；設太大→Swap 災難。 監控： Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1%。 |
innodb_buffer_pool_instances | 1 (或 8) | min(8, buffer_pool_GB) | 減少全局互斥鎖競爭，每實例 ≥ 1GB。MySQL 8.0 自動調整。 |
innodb_log_file_size | 48M | 1-4 GB (總大小 = size × files) | 大日誌減少 checkpoint 刷盤頻率，提升寫入吞吐。 調整步驟：關閉實例 → 刪舊日誌 → 改配置 → 啟動。 |
innodb_log_files_in_group | 2 | 2-4 | 循環寫入，配合大小。 |
innodb_flush_log_at_trx_commit | 1 | 1 (核心業務) / 2 (可容忍 1 秒丟失) / 0 (高吞吐非核心) | 1=每提交 fsync，ACID；2=寫 OS 緩存；0=每秒刷盤。 配合 sync_binlog=1 實現雙 1 安全。 |
innodb_flush_method | fsync | O_DIRECT (Linux 專用裝置) | 避免雙重緩存 (OS page cache + InnoDB buffer pool)，減少 CPU/內存開銷。 |
innodb_io_capacity | 200 | SSD: 2000-5000 / NVMe: 10000+ / HDD: 200 | 告知 InnoDB 底層 IO 能力，控制後台刷頁、合併、清理速度。innodb_io_capacity_max = 2-3× capacity。 |
| 參數 | 默認 | 建議值 | 調優邏輯 |
|---|---|---|---|
max_connections | 151 | 預估峰值並發 × 1.5 (如 500-2000) | 太小→「Too many connections」拒絕服務；太大→線程棧內存累積 (256KB-1MB/連接) 導致 OOM。 監控： Threads_connected / max_connections < 80%。 |
thread_cache_size | 自動 | 100-200 (或 max_connections/10) | 緩存空閒線程，減少創建/銷毀開銷。 監控： Threads_created / Connections < 1%。 |
back_log | 80 | max_connections / 5 | TCP 連接隊列長度，突發連接風暴時緩衝。 |
wait_timeout / interactive_timeout | 28800 | 300-600 (應用連接池場景) | 快速回收空閒連接，防止連接洩漏佔用槽位。應用層連接池需配置驗證查詢。 |
query_cache_type=OFF, query_cache_size=0。全局鎖爭用極大，寫入即失效，高併發下性能殺手。| 參數 | 建議值 | 關聯限制 |
|---|---|---|
table_open_cache | 4000-8000+ | 受 open_files_limit 限制，需同步調大 OS ulimit -n (65535+)。 |
table_definition_cache | 2000-4000 | .frm/.sdi 緩存，加速元數據訪問。 |
| 參數 | 默認 | 建議調整 | 風險提示 |
|---|---|---|---|
sort_buffer_size | 256K | 256K-2M (僅針對大排序會話動態 SET) | 每連接分配，1000 連接 × 2M = 2GB 內存！ |
join_buffer_size | 256K | 256K-2M (同理，僅無索引 JOIN 會話動態 SET) | 同上，慎用全局大值。 |
read_rnd_buffer_size | 256K | 256K-1M | 二次掃描排序行指針緩存。 |
tmp_table_size / max_heap_table_size | 16M | 64M-256M | 內存臨時表上限，超過轉 MyISAM 磁盤表。監控 Created_tmp_disk_tables。 |
sync_binlog=1、binlog_format=ROW、gtid_mode=ON、expire_logs_days=7 (配合歸檔)。slave_parallel_workers (5.7+) / replica_parallel_workers (8.0+)：CPU 核心數 × 2，基於邏輯時鐘/GTID 並行回放。slave_preserve_commit_order=ON 保證從庫一致性讀。SHOW GLOBAL STATUS、SHOW ENGINE INNODB STATUS、Performance Schema、sys schema、Prometheus + mysqld_exporter。Innodb_buffer_pool_reads → 擴大 buffer pool 或優化索引/查詢。Created_tmp_disk_tables → 調大 tmp_table_size / 優化查詢避免臨時表。Threads_created → 調大 thread_cache_size。Innodb_log_waits → 擴大 innodb_log_file_size。
[mysqld]
# 基礎
datadir=/var/lib/mysql
socket=/var/run/mysqld/mysqld.sock
port=3306
# 內存核心 (假設 32GB 內存專用 DB)
innodb_buffer_pool_size=22G
innodb_buffer_pool_instances=16
innodb_log_file_size=2G
innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=4000
innodb_io_capacity_max=10000
# 連接
max_connections=1000
thread_cache_size=200
back_log=200
wait_timeout=300
# 表緩存
table_open_cache=8000
table_definition_cache=4000
open_files_limit=65535
# 複製
log_bin=mysql-bin
binlog_format=ROW
gtid_mode=ON
enforce_gtid_consistency=ON
sync_binlog=1
expire_logs_days=7
replica_parallel_workers=16
slave_preserve_commit_order=ON
# 臨時表 (保守)
tmp_table_size=64M
max_heap_table_size=64M
# 禁用查詢緩存
query_cache_type=OFF
query_cache_size=0
參數調優是「在約束邊界上跳舞」。**沒有通用最佳配置，只有特定負載下的最優配置**。建立「參數變更單 → 灰度驗證 → 全量推廣 → 持續觀測」的變更管理流程，讓調優成為可控、可追溯、可回滾的工程實踐。
