十大核心參數深度解析
| 參數 | 作用 | 預設值 | 調優建議 |
|---|
max_connections | 最大同時連線數 | 100 | 視業務併發調整，典型 500-2000；過大導致記憶體耗盡 |
key_buffer_size | MyISAM 索引塊快取 | 8MB | 專用 MyISAM 庫設為物理記憶體 20-30%；InnoDB 為主則適度縮小 |
table_open_cache | 表快取數量 | 64 | 配合 open_files_limit，典型 1024-4096 |
sort_buffer_size | 排序緩衝區（每連線） | 2MB | 4-16MB；過大導致高併發時記憶體暴增 |
read_buffer_size | 順序掃描緩衝區（每連線） | 128KB | 1-4MB；全表掃描多的業務可適度增大 |
read_rnd_buffer_size | 隨機讀緩衝區（每連線） | 256KB | 4-16MB；ORDER BY、GROUP BY 多時增大 |
join_buffer_size | 關聯查詢緩衝區（每連線） | 128KB | 2-8MB；大表 Join 且無索引時關鍵 |
thread_cache_size | 執行緒快取數量 | 0-10 | 50-200；減少頻繁創建/銷毀執行緒開銷 |
query_cache_size | 查詢快取大小 | 0(關閉) | 5.7 建議關閉（有鎖爭用）；8.0 已移除。唯讀多讀少寫場景可開 64-256MB |
innodb_buffer_pool_size | InnoDB 緩衝池（核心） | 128MB | 專用 DB 伺服器設為物理記憶體 70-80%，單最關鍵參數 |
InnoDB 專屬核心參數
| 參數 | 建議值 | 說明 |
|---|
innodb_log_file_size | 256MB-1GB | 紅寫日誌檔案大小，總量建議為 Buffer Pool 25-50% |
innodb_log_buffer_size | 8-64MB | 日誌緩衝，大事務場景增大 |
innodb_flush_log_at_trx_commit | 1 | 1=最高安全（每提交刷盤）；2=折衷（每秒刷盤）；0=最高效（風險丟 1 秒資料） |
innodb_flush_method | O_DIRECT | 避免雙重快取，Linux 下推薦 |
innodb_file_per_table | ON | 獨立表空間，便於管理與回收空間 |
innodb_io_capacity | 2000-10000 | SSD 設高、HDD 設低，影響背景刷頁/合併速度 |
innodb_read_io_threads / innodb_write_io_threads | 4-16 | IO 執行緒數，高併發 IO 場景增大 |
兩套參考配置模板
方案 A：2GB 記憶體、多站點、抗壓型（高可用）
[mysqld]
# 連線與執行緒
max_connections=1024
thread_cache_size=120
back_log=500
# 快取與緩衝
table_open_cache=1024
key_buffer_size=256M
query_cache_size=64M
query_cache_type=0 # 5.7 建議關閉
# 讀寫緩衝
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
join_buffer_size=8M
# InnoDB 核心
innodb_buffer_pool_size=1536M # 75%
innodb_log_file_size=256M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_io_capacity=2000
innodb_read_io_threads=8
innodb_write_io_threads=8
# 日誌與安全
slow_query_log=1
slow_query_log_file=slow.log
long_query_time=2
log-error=error.log
skip-name-resolve
expire_logs_days=7
max_binlog_size=100M
# 字符集
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
方案 B：通用平衡型（4-8GB 記憶體）
[mysqld]
max_connections=1000
thread_cache_size=100
table_open_cache=512
key_buffer_size=128M
tmp_table_size=128M
read_buffer_size=4M
read_rnd_buffer_size=16M
sort_buffer_size=32M
join_buffer_size=8M
innodb_buffer_pool_size=3G
innodb_log_file_size=512M
innodb_log_buffer_size=16M
innodb_flush_log_at_trx_commit=1
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
slow_query_log=1
long_query_time=1
skip-name-resolve
character-set-server=utf8mb4
調優核心原則
- 測量優於猜測：用
SHOW GLOBAL STATUS、sys.schema、Performance Schema 觀察實際指標
- 單一變更：每次調整一個參數，觀察負載測試結果
- 記憶體預算：全域快取 + (每連線緩衝 × max_connections) ≤ 物理記憶體 85%
- 業務導向：讀多寫少調快取，寫多調 Redo Log/Flush 策略，Join 多調 Join Buffer
- 版本感知：5.7 關閉 Query Cache，8.0 移除 Query Cache、調整預設值
監控關鍵指標
-- 連線使用率
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';
-- InnoDB 緩衝池命中率
SHOW ENGINE INNODB STATUS\G
-- 關注 Buffer pool hit rate，目標 > 99%
-- 慢查詢分析
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;
-- 表快取命中
SHOW GLOBAL STATUS LIKE 'Open_tables';
SHOW GLOBAL STATUS LIKE 'Opened_tables';
-- Opened_tables / Uptime 過高需增大 table_open_cache
參數調優是持續迭代過程，無「萬能配置」，只有「適合當前業務與硬體的配置」。建議建立變更記錄與基準測試流程。
