面對日均 20 萬+ 訪問量的高併發場景，MySQL 預設參數遠不能滿足需求。本文基於實戰經驗，梳理 10 個核心參數的調優邏輯、配置前後對比、監控驗證指標與避坑指南，助你構建高吞吐、低延遲、穩定可靠的資料庫實例。
| 參數 | 預設值 | 調優值 (示例: 2GB 內存專用 DB) | 核心作用 | 調整依據 |
|---|---|---|---|---|
back_log |
50 | 500 | 監聽隊列長度，應對連線風暴 | 出現大量 unauthenticated user 待連線進程時調大 |
interactive_timeout |
28800 (8h) | 7200 (2h) | 交互式連線空閒斷開 | 釋放閒置連線資源，防連線洩漏佔用 |
key_buffer_size |
8MB | 400MB | MyISAM 索引塊緩衝區 (共享) | 專用 DB 內存 60-70% 給 InnoDB，MyISAM 視表量分配 |
max_connections |
100 | 1024 | 最大並發連線數 | 避免 "Too many connections"，配合連線池上限設定 |
read_buffer_size / read_rnd_buffer_size |
128KB / 256KB | 16MB / 8MB | 全表掃描/排序讀取緩衝 (每線程) | 大表頻繁全掃描場景調大，但總量 = 值 × max_connections |
sort_buffer_size |
2MB | 16MB | 排序緩衝區 (每線程) | ORDER BY/GROUP BY 多、臨時表多場景調大，防磁碟臨時表 |
table_open_cache (舊 table_cache) |
64 | 512 | 表緩存數量 (含 .frm、文件描述符) | 表數量多、高併發場景調大，受限於 OS open-files-limit |
thread_cache_size |
0-8 (版本差) | 80 | 線程緩存池，減少創建/銷毀開銷 | 對比 Connections vs Threads_created，比值 > 0.9 即可 |
wait_timeout |
28800 (8h) | 7200 (2h) | 非交互連線空閒斷開 | 配合連線池 idle-timeout，防殭屍連線佔用槽位 |
innodb_buffer_pool_size |
128MB (舊版) | 1.5GB (2GB 內存專用機 75%) | InnoDB 核心緩衝池：數據、索引、鎖、插入緩衝、自適應雜湊 | **最關鍵參數**，專用機 70-80% 物理內存，監控命中率 > 99% |
[mysqld]
# 基礎
port = 3306
bind-address = 0.0.0.0
datadir = /data/mysql
socket = /tmp/mysql.sock
pid-file = /data/mysql/mysqld.pid
user = mysql
# 字符集
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-character-set-client-handshake = 1
# 連線層
max_connections = 1024
back_log = 500
thread_cache_size = 80
wait_timeout = 7200
interactive_timeout = 7200
max_connect_errors = 100000
# InnoDB 核心 (專用機 2GB 內存示例)
innodb_buffer_pool_size = 1536M
innodb_buffer_pool_instances = 8
innodb_log_file_size = 256M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 16M
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_thread_concurrency = 0 # 0 表示不限制
# MyISAM (視需求)
key_buffer_size = 400M
read_buffer_size = 16M
read_rnd_buffer_size = 8M
sort_buffer_size = 16M
# 表緩存
table_open_cache = 512
table_definition_cache = 400
# 日誌
log_error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
# 二進制日誌 (複寫/PITR 必備)
log_bin = /data/mysql/logs/binlog
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 100M
sync_binlog = 1
# 其他
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
tmp_table_size = 64M
max_heap_table_size = 64M
| 指標 | 命令/來源 | 健康閾值 | 異常處理 |
|---|---|---|---|
| InnoDB 緩衝池命中率 | SHOW ENGINE INNODB STATUS\G → Buffer pool hit rate |
> 99% (OLTP) | 調大 buffer_pool_size、檢查熱數據分布 |
| 線程緩存命中率 | Threads_created / Connections |
< 0.1 (即 > 90% 復用) | 調大 thread_cache_size |
| 表緩存命中率 | Open_tables / Opened_tables |
< 0.05 | 調大 table_open_cache、檢查 open_files_limit |
| 排序溢寫磁碟率 | Sort_merge_passes / Sort_scan |
< 0.01 | 調大 sort_buffer_size、優化 ORDER BY 索引 |
| 臨時表磁碟化率 | Created_tmp_disk_tables / Created_tmp_tables |
< 0.1 | 調大 tmp_table_size/max_heap_table_size、優化查詢 |
| 鎖等待佔比 | Innodb_row_lock_waits / Innodb_rows_inserted+updated |
< 0.01 | 優化索引、縮短事務、檢查熱點行 |
| 連線使用率 | Threads_connected / max_connections |
< 0.8 | 調大 max_connections、檢查連線池洩漏 |
| 慢查詢增長率 | Slow_queries (單位時間增量) |
趨勢收斂 | 定期分析 slow.log、優化 Top N |
sort_buffer_size=256MB → 並發 1000 時佔 250GB 內存，直接 OOMopen_files_limit、ulimit -n、systemd LimitNOFILE 未同步調大，導致 table_open_cache 無效SET GLOBAL 運行時生效但重啟失效，持久化必改配置文件table_open_cache 替代 table_cache，新增 table_definition_cacheinnodb_buffer_pool_size 支援線上動態調整 (無需重啟)innodb_buffer_pool_instances 建議 = CPU 核心數 (上限 64)，innodb_redo_log_capacity 替代 innodb_log_file_size參數調優是「測量 → 假設 → 實驗 → 驗證」的科學過程。建立基線、單變量調整、壓測對比、監控固化，拒絕玄學調參，讓每一個參數都經得起生產環境考驗。
