您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能調優十大關鍵參數：key_buffer、max_connections、query_cache 等深度解析

編輯：MySQL 資料庫相關知識

MySQL 核心配置參數調優指南：十大關鍵變數與實戰建議值

MySQL 效能很大程度上取決於配置參數是否匹配硬體資源與業務負載。本文精選十大影響最大的參數，解析原理、預設值、調優方向與典型配置值（以 2GB 記憶體專用資料庫伺服器為例），助你快速構建高效能 MySQL 實例。

參數調優鐵律

一次只調一個參數，觀察負載測試結果
總記憶體佔用 ≤ 物理記憶體 80%（預留 OS、FS Cache、監控代理）
關鍵公式：全局緩衝 + (線程緩衝 × max_connections) ≤ 可用記憶體
修改後必須重啟（動態參數除外），生產環境走變更流程

十大關鍵參數詳細解析

#	參數	預設值	建議值 (2GB RAM)	核心作用與調優邏輯
1	`back_log`	50	500	TCP 連接請求佇列長度。高併發連接突發時（如 `unauthenticated user` 大量堆積）需調大，受 OS `somaxconn` 限制。
2	`interactive_timeout`	28800 (8h)	7200 (2h)	互動式連接空閒超時。縮短可釋放閒置連接資源，配合連接池 `idleTimeout` 設定。
3	`key_buffer_size`	8MB	400MB	MyISAM 索引塊緩衝區。所有線程共享。專用 DB 可設物理記憶體 25%~30%。InnoDB 主庫無效（看 `innodb_buffer_pool_size`）。
4	`max_connections`	100	1024	最大同時連接數。過小導致 `Too many connections`，過大消耗線程棧內存。配合 `thread_cache_size`、連接池大小規劃。
5	`read_buffer_size`	128KB	16MB	全表掃描時每線程分配的順序讀取緩衝。大量範圍查詢/全掃業務可適度調大（每線程獨享，乘以 max_connections 計算總量）。
6	`sort_buffer_size`	2MB	16MB	每線程排序緩衝（ORDER BY/GROUP BY）。調大可減少磁碟臨時檔排序。同樣為線程獨享，謹慎設定。
7	`table_open_cache` (舊 table_cache)	64	512	表緩存數量。避免頻繁開關表開銷。需 > `max_connections` × 最大單查詢涉及表數。受 OS 開檔限制 `ulimit -n` 影響。
8	`thread_cache_size`	0	80	可復用線程緩存。對比 `Connections` vs `Threads_created`，若後者持續增長需調大。減少線程創建/銷毀開銷。
9	`character_set_server`	latin1	utf8mb4	伺服器預設字符集。強制指定 utf8mb4 避免亂碼，而非依賴啟動參數 `--default-character-set`。
10	`wait_timeout`	28800 (8h)	7200 (2h)	非互動式連接空閒超時。應用層連接池 `maxLifetime` 必須 < 此值，否則連接被 DB 斷開導致報錯。

InnoDB 專用關鍵參數（補充）

現代生產環境以 InnoDB 為主，以下參數同樣關鍵：

參數	建議值 (2GB RAM)	說明
`innodb_buffer_pool_size`	1.2GB (≈ 60% RAM)	InnoDB 核心緩衝池，單一最重要參數
`innodb_log_file_size`	256MB	Redo Log 大小，大寫入負載調大，減少 Checkpoint 壓力
`innodb_flush_log_at_trx_commit`	1	1=最安全(ACID)，2=高效能(可能丟1秒數據)，0=最高效能
`innodb_file_per_table`	ON	獨立表空間，支援 OPTIMIZE TABLE、可移植性佳

配置檔範例

[mysqld]
# 基礎
basedir = /usr/local/mysql
datadir = /var/lib/mysql
port = 3306
socket = /tmp/mysql.sock
character_set_server = utf8mb4
collation_server = utf8mb4_unicode_ci

# 連接
max_connections = 1024
thread_cache_size = 80
back_log = 500
wait_timeout = 7200
interactive_timeout = 7200

# MyISAM (若仍使用)
key_buffer_size = 400M
read_buffer_size = 16M
sort_buffer_size = 16M

# InnoDB 核心
innodb_buffer_pool_size = 1200M
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1

# 表緩存
table_open_cache = 512

# 慢查詢
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 其他
skip_name_resolve = 1
sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

驗證與監控

SHOW VARIABLES LIKE 'param_name'; 確認生效
SHOW GLOBAL STATUS 觀察 Threads_created、Key_read_requests/Key_reads (MyISAM 命中率)、Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests (InnoDB 命中率)
目標：關鍵緩衝命中率 > 99%，Threads_created 穩定不持續增長

參數調優是持續迭代過程，隨業務增長、硬體升級、MySQL 版本迭代定期複盤，方能維持最佳效能。