前言
MySQL 擁有數百個配置參數，但真正影響性能的核心參數僅 30 個左右。本文將其歸納為**連接層、查詢緩存層、臨時表層、會話內存層、慢查詢日誌層**五大類別，逐一解析參數含義、監控指標、調優邏輯、經驗值及避坑指南，構建系統化配置知識體系。
一、 連接層：連接管理與線程復用
| 參數 | 核心作用 | 監控指標 | 調優建議 | 避坑 |
|---|
| max_connections | 最大並發連接數 | Max_used_connections / max_connections 使用率 | 峰值 × 1.5，配合應用連接池。典型 500-2000。 | 設過大 (如 10000) 導致內存暴增風險；設過小報 "Too many connections"。 |
| max_allowed_packet | 單次網絡包/數據包大小限制 | Packet too large 報錯 | 應用層大字段 (Blob/LongText) 需調大，如 64M。mysqldump 需更大。 | 過大增加網絡緩衝壓力。 |
| thread_cache_size | 線程緩存池大小 | Threads_created / Connections (目標 < 1%) | 1G內存 8，4G 32，16G+ 128。寧大勿小，佔用極少內存。 | 誤以為連接池替代了它 (見 91369)。 |
| back_log | TCP 連接請求隊列長度 | 連接突發時錯誤率 | 高併發突發場景調大 (如 512)，受 OS somaxconn 限制。 | 需同步調大 OS net.core.somaxconn。 |
| wait_timeout / interactive_timeout | 空閒連接超時斷開 | Sleep 進程數量 | Web 應用 30-60s。配合連接池 testOnBorrow/validationQuery。 | 設太大導致連接洩漏佔滿槽位；設太小導致連接池驗證頻繁失效。 |
| max_connect_errors | 主機連接錯誤封鎖閾值 | Host 'x' is blocked 報錯 | 設大 (1000000) 防誤封，配合 flush hosts 解封。 | 不解決根因 (網絡/認證問題)。 |
| skip-name-resolve | 禁用 DNS 反向解析 | 連接建立延遲 | 生產強制開啟。授權表 Host 列必須用 IP。 | 若授權表含主機名將無法連接。 |
二、 查詢緩存層 (Query Cache, QC) —— 5.7 及之前版本
注意：MySQL 8.0 已移除 QC**。以下僅適用 5.7 及之前。
| 參數 | 核心作用 | 監控指標 (SHOW STATUS LIKE 'Qcache%') | 調優建議 |
|---|
| query_cache_type | 啟用模式 (0=OFF, 1=ON, 2=DEMAND) | Qcache_hits, Qcache_inserts | 高併發寫入 **建議 0 (關閉)**。讀多寫少可 1/2 配合 SQL_CACHE 提示。 |
| query_cache_size | QC 總內存大小 | Qcache_free_memory, Qcache_lowmem_prunes | 開啟時 64M-128M。**過大 (＞256M) 鎖爭用劇增，性能反降**。 |
| query_cache_limit | 單條查詢結果最大緩存大小 | Qcache_not_cached | 默認 1M。大結果集不緩存，防佔滿池。 |
| query_cache_min_res_unit | 緩存塊最小分配單位 | Qcache_free_blocks (碎片指標) | 默認 4K。結果集較小可調小 (如 1K) 減少碎片。 |
QC 核心公式與決策
命中率 = Qcache_hits / (Qcache_hits + Com_select)
平均塊大小 = (query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
碎片率 = Qcache_free_blocks / Qcache_total_blocks
決策樹：
IF 寫入佔比 > 20% OR 表頻繁更新 → QC=OFF (應用層用 Redis)
ELSE IF 命中率 > 80% AND 碎片率 < 20% → 保持
ELSE IF Lowmem_prunes 高 → 增大 size 或 降低 limit
ELSE → 關閉
三、 臨時表層：內存 vs 磁盤
| 參數 | 核心作用 | 關鍵邏輯 | 調優建議 |
|---|
| tmp_table_size | 內存臨時表最大值 (MEMORY 引擎) | 實際限制 = MIN(tmp_table_size, max_heap_table_size) | 64M-256M。監控 Created_tmp_disk_tables / Created_tmp_tables < 10%。 |
| max_heap_table_size | 用戶創建 MEMORY 表最大值 / 臨時表上限 | 同上，兩者取最小 | 與 tmp_table_size 保持一致。 |
關鍵限制：含 BLOB/TEXT 或 GROUP BY/ORDER BY 列超過 512 字節 → 強制磁盤臨時表 (MyISAM/InnoDB)，參數無效。
優化方向：優化 SQL (減少列、避免 SELECT *、利用索引排序) > 調大參數。
四、 會話內存層 (Per-Thread Buffers) —— ⚠️ 每連接獨享，乘以并發數！
| 參數 | 用途 | 默認/建議 | 調大場景 | 風險 |
|---|
| sort_buffer_size | ORDER BY / GROUP BY 排序緩衝 | 256K / 1-2M | 大結果集排序、無法用索引排序時，**Session 級 SET** 調大 (如 16M) | 1000 連接 × 16M = 16GB OOM！ |
| read_buffer_size | 全表掃描順序讀取緩衝 | 128K / 1-2M | 大表全掃描，**Session 級 SET** | 同上，乘以並發數 |
| read_rnd_buffer_size | 排序後隨機讀取 (回表) 緩衝 | 256K / 1-2M | 大範圍排序回表，**Session 級 SET** | 同上 |
| join_buffer_size | 無索引 Join (NLJ/BKA) 緩衝 | 256K / 256K-1M | **首選加索引**！無法加索引時 Session 級調大 | 每 Join 分配，多 Join 疊加，極危險 |
| binlog_cache_size | 事務 Binlog 內存緩衝 | 32K / 64K-256K | 大事務 (多行修改) 報告 Binlog_cache_use 增長 | 相對安全，但大事務多時注意 |
會話內存總量估算
總內存 ≈ Global Buffers (buffer_pool, log_buffer, key_buffer 等)
+ max_connections × (sort_buffer + read_buffer + read_rnd_buffer + join_buffer + thread_stack + binlog_cache + ...)
實際佔用遠小於理論峰值 (並非所有連接同時滿載)。
驗證方法：top -p $(pidof mysqld) 觀察 VIRT/RES，或 PMM/Grafana 監控內存趨勢。
五、 慢查詢日誌層：性能調優之眼
| 參數 | 核心作用 | 建議配置 | 說明 |
|---|
| slow_query_log | 啟用慢查詢日誌 | ON | **生產必開**。文件路徑建議指定獨立磁盤。 |
| long_query_time | 慢查詢閾值 (秒) | 0.5 - 1 (5.7+ 支持微秒，如 0.1) | 從 10s 降至 1s 再降至 0.5s，逐步收緊。 |
| log_queries_not_using_indexes | 記錄未用索引查詢 (含全表掃描) | ON | 發現潛在索引缺失。**會產生大量日志，需配合 log_slow_admin_statements 控制**。 |
| log_slow_admin_statements | 記錄管理命令 (ALTER, ANALYZE 等) | ON | 監控 DDL 影響。 |
| log_slow_slave_statements | 記錄從庫慢 SQL (5.6+) | ON | 從庫壓力來源排查。 |
| slow_query_log_file | 日誌文件路徑 | /var/log/mysql/slow.log | 獨立磁盤，配合 logrotate 切割。 |
分析工具鏈
- pt-query-digest (Percona Toolkit)：聚合分析 Top SQL、執行計劃、索引建議。**首選**。
- mysqldumpslow：內置簡易分析，無依賴。
- Slow Query Log 可視化：PMM, Grafana + Loki, Elasticsearch + Kibana。
六、 綜合調優決策矩陣
| 症狀 | 首查參數/指標 | 典型動作 |
|---|
| CPU 100% (用戶態) | 慢查詢、無索引 Join、QC 鎖爭用 | 開啟慢查詢 → pt-query-digest → 加索引/改 SQL / 關閉 QC |
| IOPS 100% / 磁盤讀寫高 | Buffer Pool 命中率、臨時表落盤、Redo Log 刷盤 | 增大 buffer_pool / 調大 log_file_size / 優化 SQL 減少掃描 |
| 內存不足 / OOM Killer | Max possible memory、Per-thread 緩存、連接數 | 降低 per-thread 參數 / 降低 max_connections / 增加物理內存 |
| 「Too many connections」 | Max_used_connections、連接池配置、wait_timeout | 調大 max_connections / 修復連接池洩漏 / 縮短 wait_timeout |
| 主從延遲大 | Seconds_Behind_Master、從庫 SQL 線程單線程、大事務 | 啟用 MGR/並行複製 (slave_parallel_workers) / 拆分大事務 |
七、 總結
MySQL 參數調優**無銀彈，有邏輯**；**無監控，不調優**；**業務場景定參數，壓測驗證定生產**。五大類別中，**會話內存層風險最高 (乘以並發)**，**查詢緩存層 5.7 高併發建議關閉**，**慢查詢日誌層價值最高 (指導 SQL 優化)**。建立參數基線模板 (如 4G/16G/64G 內存三套)，新實例套用基線 → 業務上線 → 持續監控 → 針對性微調 → 固化回模板。
