配置環境基準
- 物理記憶體：32 GB
- 專用資料庫服務器
- InnoDB 為主儲存引擎
- 高併發讀寫負載
核心參數配置與說明
| 參數 | 建議值 | 說明 |
|---|
innodb_file_per_table | 1 | 啟用獨立表空間，便於管理、回收空間、傳輸表 |
back_log | 300 | 監聽隊列連接數，高併發時避免連接被拒 |
max_connections | 8000 | 最大同時連接數，配合連接池使用，防止過載 |
max_connect_errors | 30 | 單客戶端最大錯誤次數，超過則封鎖 IP |
table_open_cache | 4096 | 所有線程打開表的快取數量（舊版 table_cache） |
open_files_limit | 10240 | 作業系統開啟檔案描述符上限，需同步調整 ulimit -n |
max_allowed_packet | 32M | 單次封包最大值，支援大欄位、批次插入 |
wait_timeout | 10 | 非互動連接閒置秒數，短連接應用建議 10-30 秒 |
sort_buffer_size | 16M | 每連接排序緩衝（ORDER BY/GROUP BY），按連接分配 |
join_buffer_size | 16M | 無索引 JOIN 優化緩衝，按連接分配，謹慎調大 |
query_cache_size | 128M | 查詢快取大小，**MySQL 8.0 已移除**，5.7 建議關閉或小值 |
transaction_isolation | REPEATABLE-READ | 預設事務隔離級別，InnoDB 預設且支援 MVCC |
thread_stack | 512K | 線程堆疊大小，預設通常足夠 |
log_bin | mysql-bin | 啟用二進位日誌，主從複制、時間點恢復必備 |
InnoDB 核心參數（重中之重）
| 參數 | 建議值 | 計算依據與說明 |
|---|
innodb_buffer_pool_size | 6G | 物理記憶體 60-80%，32G×75%=24G，但需預留 OS、連接緩衝、排序緩衝等，建議 20-24G。此處 6G 偏保守，視實際資料熱度調整 |
innodb_file_io_threads | 4 | 同步 IO 線程數，SSD 可調高至 8-16 |
innodb_thread_concurrency | 16 | InnoDB 內部併發線程上限，0 為不限制，現代版本建議 0 或 CPU 核心數×2 |
innodb_log_buffer_size | 16M | 日誌緩衝，大事務建議 32-64M |
innodb_log_file_size | 512M | 單日誌檔大小，總大小 = size × files_in_group，建議 1-4G 總量 |
innodb_log_files_in_group | 3 | 日誌檔組數量，通常 2-4 個 |
innodb_lock_wait_timeout | 120 | 鎖等待超時秒數，長事務業務可調大 |
innodb_flush_log_at_trx_commit | 1 | 必須設 1 保證 ACID，極高寫入可考慮 2（風險自負） |
innodb_flush_method | O_DIRECT | 避免雙重緩衝，直接 I/O，搭配 RAID/SSD 最佳 |
重要補充參數（建議加入）
[mysqld]
# 連接與緩衝
thread_cache_size = 100
read_buffer_size = 2M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 32M
# InnoDB 進階
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000 # SSD 建議 2000-5000
innodb_io_capacity_max = 5000
innodb_adaptive_hash_index = 1
innodb_adaptive_flushing = 1
# 日誌與恢復
sync_binlog = 1 # 雙 1 配置保證不丟數據
binlog_format = ROW # ROW 格式主從一致性最佳
expire_logs_days = 7 # 自動清理舊 binlog
# 效能Schema監控
performance_schema = ON
調優核心原則
- Buffer Pool 最大化：熱數據全在記憶體，是效能第一要素
- 避免 Swap：
vm.swappiness=1，鎖定 MySQL 記憶體不被換出
- IO 能力匹配：SSD 調高
innodb_io_capacity，HDD 則保守
- 監控為先：配合
PM2、Prometheus+Grafana、sysbench 持續基準測試
配置生效方式
# 修改配置後重啟
systemctl restart mysqld
# 線上動態調整（部分需重啟）
SET GLOBAL innodb_buffer_pool_size = 21474836480; -- 20G 線上擴容（5.7.5+）
