MySQL 參數超過 100 個，但生產環境只需調整核心少數即可獲得 80% 性能收益。本文精選 10 項必改配置，附帶原理、推薦值、避坑指南，助你快速構建穩健實例。
核心配置清單（按重要度排序）
1. innodb_buffer_pool_size —— 核心中的核心
- 作用：緩存數據頁、索引頁、鎖、自適應哈希索引，直接決定讀寫性能。
- 推薦：物理記憶體 60-80%（專用 DB 服務器）。
例：8GB→5-6GB、32GB→20-25GB、128GB→100-120GB。
- 注意：單位為 bytes，配置文件可用
G/M 後綴；過大導致 OS Swap 反而拖垮性能。
2. innodb_log_file_size —— 寫入性能與恢復平衡
- 作用：Redo Log 文件大小，決定檢查點頻率與崩潰恢復時間。
- 推薦：512M 起步（總 1GB，兩文件），寫密集業務可調 1-4GB（5.6+ 單文件上限提高）。
- 修改步驟：停庫 → 刪舊
ib_logfile* → 改配置 → 啟庫。
3. max_connections —— 連接數上限
- 現狀：預設 151，極易觸發
Too many connections。
- 推薦：500-1000，配合應用層連接池（HikariCP）限制實際併發。
- 風險：過大（>2000）無連接池時活躍事務過多導致鎖競爭、內存耗盡。
4. innodb_file_per_table —— 獨立表空間
- 預設：5.6+ 默認 ON，5.5 需手動開啟。
- 收益：
DROP/TRUNCATE 瞬間回收磁盤空間、支援壓縮、便於單表遷移/備份。
- 例外：超大量表（>10萬）可能導致文件描述符耗盡，考慮關閉或分庫。
5. innodb_flush_log_at_trx_commit —— 持久性與性能權衡
| 值 | 行為 | 持久性 | 性能 | 適用場景 |
|---|
| 1 (默認) | 每提交 fsync 到 redo log | 最強 | 最低 | 主庫、核心業務 |
| 2 | 提交寫 OS 緩存，每秒 fsync | 掛電丟 1 秒 | 高 | 備庫、可容忍秒級丟失 |
| 0 | 每秒寫入並 fsync | 崩潰丟 1 秒 | 最高 | 測試、日誌、可重建數據 |
6. innodb_flush_method —— I/O 路徑選擇
- O_DIRECT：繞過 OS 緩存，直寫磁盤。有 RAID 卡 + BBU/WB 緩存時首選。
- fdatasync (默認)：經 OS 緩存，
fdatasync() 刷盤。無 RAID 卡或不確定時安全選擇。
- 用
sysbench 實測兩者吞吐差異決定。
7. innodb_log_buffer_size —— 大事務緩衝
- 默認：1MB（5.6+ 8MB）。
- 調大觸發：
SHOW GLOBAL STATUS LIKE 'Innodb_log_waits' 非 0，或事務含 BLOB/TEXT 大字段。
- 推薦：16-64MB，避免過大佔用內存。
8. query_cache_size —— 關閉查詢緩存
- 現狀：5.6+ 默認關閉（
query_cache_type=OFF），5.7 移除相關代碼。
- 原因：全局鎖競爭極大，高併發下成為瓶頸，命中率低時負收益。
- 替代：應用層 Redis/Memcached、Covering Index、只讀從庫分流。
9. log_bin —— 二進制日誌（主從/時間點恢復必備）
- 開啟：
log_bin=mysql-bin + server_id=唯一值。
- 清理策略：
expire_logs_days=7 或定期 PURGE BINARY LOGS BEFORE 'date'。
- 從庫：如不做時間點恢復可關閉以節省 I/O。
10. skip_name_resolve —— 關閉 DNS 反解
- 效果：連接建立跳過
gethostbyaddr，顯著降低連接延遲（特別是 DNS 故障/慢時）。
- 代價：
GRANT 僅能用 IP，不可用主機名，現有帳號需遷移。
- 建議：新實例直接開啟，老實例評估風險後維護窗口變更。
配置文件模板片段
[mysqld]
# 核心 10 項
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
max_connections=800
innodb_file_per_table=ON
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=32M
query_cache_size=0
query_cache_type=OFF
log_bin=mysql-bin
server_id=101
expire_logs_days=7
skip_name_resolve
# 基礎
datadir=/data/mysql
socket=/data/mysql/mysql.sock
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
explicit_defaults_for_timestamp=ON
變更驗證清單
- 單項變更、重啟觀察、對比基準（sysbench/業務流量回放）。
- 確認單位正確：
G/M/K 後綴 vs 無單位（如 max_connections）。
- 配置文件無重複項，使用版本控制（Git）管理歷史。
- 避免「記憶體翻倍參數翻倍」朴素線性擴展，需實測。
- 上一篇文章： SQL 實現九九乘法表四種變體：WHILE 迴圈與條件控制輸出左下/右下/右上/左上三角
- 下一篇文章： MySQL SELECT 語句執行順序詳解：11 步驟從 FROM 到 LIMIT 的邏輯流程
Copyright ©
程式碼之家 All Rights Reserved
