MySQL 高併發效能調優實戰：8 大核心參數深度解析與配置模板

調優前提：測量而非猜測

所有參數調整必須基於 實際負載基準測試 (sysbench、tpcc-mysql) 與 線上監控指標 (PMQPS、延遲分位數、執行緒狀態、緩衝區命中率)。

八大核心參數深度解析

1. back_log = 500 (預設 80/50)

-- 作用：TCP 連接請求佇列長度 (SYN_RECV 狀態)
-- 適用場景：突發連線風暴 (閃購、定時任務、連線池預熱)
-- 監控指標：Aborted_connects 增長、error log 出現 "Connection refused"
-- 系統限制：受限於 /proc/sys/net/core/somaxconn (預設 128) 與 tcp_max_syn_backlog
-- 設定建議：min(500, somaxconn)；同步調整作業系統參數：
sysctl -w net.core.somaxconn=1024
sysctl -w net.ipv4.tcp_max_syn_backlog=1024

2. max_connections = 1024 (預設 151)

-- 作用：最大並發客戶連線數
-- 影響：每連線消耗 thread_stack (256KB) + sort_buffer + read_buffer + join_buffer + 連線控制結構
-- 記憶體估算：~ 4-10 MB/連線 (視緩衝區大小)
-- 監控：Threads_connected / Max_used_connections 趨勢
-- 關聯：open_files_limit ≥ max_connections × 5；table_open_cache ≥ max_connections × 2

3. key_buffer_size = 400M (預設 8M) — 僅 MyISAM

-- 作用：索引塊緩衝區 (MyISAM 專用)
-- InnoDB 使用 innodb_buffer_pool_size，勿混淆
-- 設定原則：MyISAM 索引總大小 × 1.2，但不超過實體記憶體 25%
-- 監控：Key_read_requests / Key_reads > 100:1 (命中率 > 99%)
-- 共享主機常見誤區：將大量記憶體給 key_buffer 導致 InnoDB buffer pool 不足

4. innodb_buffer_pool_size = 4G (預設 128M) — InnoDB 核心

-- 作用：資料頁、索引頁、自適應雜湊、鎖資訊、插入緩衝、UNDO 日誌
-- 黃金法則：專用 DB 伺服器分配 70-80% 實體記憶體
-- 實例規格對照：
--   8GB RAM  → 5-6 GB
--   16GB RAM → 12-13 GB
--   32GB RAM → 24-26 GB
-- 進階：innodb_buffer_pool_instances = 8 (每實例 ≥ 1GB) 減少鎖競爭
-- 監控：Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests < 1%

5. sort_buffer_size = 4M / 16M (預設 256K)

-- 作用：ORDER BY、GROUP BY、DISTINCT、窗口函數、檔案排序臨時空間
-- 分配方式：每執行緒 (Thread) 獨享，按需分配至上限
-- 風險：設定過大 (如 16M) × 1000 連線 = 16 GB 記憶體暴增 → OOM Killer
-- 最佳實踐：
--   全域設定保守 256K-512K
--   Session 級按需調大：SET SESSION sort_buffer_size = 16M; -- 僅大報表查詢
-- 監控：Sort_merge_passes 高表示記憶體不足導致磁碟臨時表

6. read_buffer_size = 2M / 16M (預設 128K)

-- 作用：全表掃描循序讀取緩衝 (MyISAM/InnoDB 皆用)
-- 同樣為 Thread 級私有，設定過大同樣導致記憶體壓力
-- 建議：全域 128K-256K，Session 級大掃描查詢調大
-- 關聯：read_rnd_buffer_size (隨機讀取緩衡，預設 256K) 排序後回表用

7. table_open_cache = 2048 (預設 4000/2000)

-- 作用：表定義緩存 (FRM) + 表實例緩存，減少 open/close 系統呼叫
-- 設定：≥ max_connections × 平均每連線操作表數 × 2
-- 監控：Opened_tables / Uptime > 50/秒 需調大
-- 系統限制：open_files_limit ≥ table_open_cache × 3 (每表約 2-3 個檔案描述符)
-- 關聯：table_definition_cache (預設 1400/400) 獨立緩存 FRM，通常無需調整

8. thread_cache_size = 64 (預設 0-100 自動)

-- 作用：閒置執行緒快取，避免頻繁 create/destroy thread 系統呼叫開銷
-- 監控：Threads_created / Connections < 0.01 (即 99% 連線複用)
-- 設定：觀察峰值 Threads_connected，設為該值 10-20%
-- 8.0+ 自動調整機制已優秀，通常保持預設即可

超時參數雙雄

關鍵：連線池 (HikariCP、Druid、PgBouncer for MySQL) **必須**配置 validationQuery=SELECT 1、testOnBorrow=true、maxLifetime < wait_timeout。

完整生產配置模板 (MySQL 8.0, 16GB RAM, 專用 DB 伺服器)

[mysqld]
# === 基礎身份 ===
server_id = 1
datadir = /data/mysql
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
user = mysql
port = 3306
bind_address = 10.0.1.10  # 僅內網 IP

# === 連線與執行緒 ===
max_connections = 1000
thread_cache_size = 100
back_log = 200
max_connect_errors = 100000
connect_timeout = 10
wait_timeout = 300
interactive_timeout = 1800
max_allowed_packet = 64M

# === InnoDB 核心 (16GB RAM) ===
innodb_buffer_pool_size = 12G
innodb_buffer_pool_instances = 12
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_page_cleaners = 4
innodb_lock_wait_timeout = 10
innodb_deadlock_detect = ON
innodb_online_alter_log_max_size = 128M

# === 緩衝區 (保守全域，Session 按需) ===
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K
join_buffer_size = 256K
binlog_cache_size = 32K

# === 表快取 ===
table_open_cache = 4000
table_definition_cache = 2000
open_files_limit = 65535

# === 日誌與複寫 ===
log_bin = mysql-bin
binlog_format = ROW
binlog_expire_logs_seconds = 604800  # 7 天
max_binlog_size = 1G
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1
relay_log = relay-bin
relay_log_recovery = ON

# === 慢查詢與審計 ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# === 安全與字符集 ===
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci
skip_name_resolve = 1
local_infile = 0
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

# === 客戶端預設 ===
[client]
default_character_set = utf8mb4
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysql]
default_character_set = utf8mb4
auto_rehash = 1
safe_updates = 1

調優驗證清單

1. 重啟後觀察 30 分鐘：錯誤日誌無警告、慢查詢日誌正常寫入、監控指標平穩
2. 壓測驗證：sysbench oltp_read_write --threads=200 --time=300 --rate=0，對比 QPS、P99 延遲、CPU/IO 利用率
3. 線上金絲雀：單節點套用配置，流量逐步切換 10% → 50% → 100%，監控無異常後全量推廣
4. 定期複盤：每月導出 SHOW GLOBAL STATUS、SHOW GLOBAL VARIABLES、SHOW ENGINE INNODB STATUS 存檔對比趨勢