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

MySQL my.cnf 效能調優參數詳解：key_buffer、sort_buffer、table_cache、max_connections 核心配置指南

編輯：MySQL 資料庫相關知識

配置檔結構與生效範圍

my.cnf 分區段：[mysqld] 服務端、[mysqldump]、[mysql] 客戶端、[isamchk]/[myisamchk] 維護工具、[mysqlhotcopy]、[mysqld_safe] 啟動腳本。修改後需 重啟 mysqld 生效（部分參數支援 SET GLOBAL 線上調整）。

核心參數詳細解析與建議值（以 8GB 記憶體專用 DB 為例）

參數	預設	建議值	作用與調優指引
`key_buffer_size`	16M	1-2GB (MyISAM) / 64M (InnoDB)	MyISAM 索引塊緩衝區。InnoDB 用 `innodb_buffer_pool_size`。專用 MyISAM 實例設為實體記憶體 25-30%
`innodb_buffer_pool_size`	128M	6-7GB	InnoDB 數據+索引緩衝池，最關鍵參數。設為實體記憶體 70-80%，預留 OS、連線、排序記憶體
`max_allowed_packet`	1M	64M	單次封包大小上限，防大 BLOB/長 SQL 斷連。客戶端 mysqldump 同步調大
`thread_stack`	192K	256K	每線程棧大小，複雜預存程序/遞歸需調大
`read_buffer_size`	128K	2M	全表掃描順序讀取緩衝，`SET SESSION` 可動態調大
`read_rnd_buffer_size`	256K	4M	排序後隨機讀取緩衝（ORDER BY、檔案排序）
`sort_buffer_size`	2M	8M	每線程排序緩衝，頻繁 ORDER BY/GROUP BY 必調大，但過大導致記憶體壓力
`join_buffer_size`	256K	4M	無索引 JOIN 全表掃描緩衝，慎用，優先加索引
`table_open_cache`	400	2000-4000	表緩存數量，避免頻繁開關表。監控 `Opened_tables` 增長速度調整
`max_connections`	151	500-1000	最大連線數，配合連線池上限設定。過大導致記憶體耗盡（每連線 ~sort_buffer+read_buffer+join_buffer）
`max_connect_errors`	100	10000	連線錯誤封鎖閾值，防誤封鎖合法客戶端
`wait_timeout`	28800	3600	非互動連線空閒超時，配合連線池 `validationQuery` 使用
`interactive_timeout`	28800	3600	互動式連線空閒超時
`innodb_flush_log_at_trx_commit`	1	2 (高效能) / 1 (金融級)	1=每提交刷盤（ACID）、2=每秒刷盤（效能）、0=OS 刷盤（風險）
`innodb_log_file_size`	48M	512M-1G	Redo Log 大小，大則減少 checkpoint 壓力，重啟恢復較慢
`innodb_log_buffer_size`	8M	64M	大事務寫入緩衝，減少刷盤頻率
`query_cache_type`	ON	OFF (0)	5.7+ 棄用，8.0 移除。高併發下鎖爭用嚴重，建議關閉
`tmp_table_size / max_heap_table_size`	16M	64M	內部臨時表記憶體上限，超過轉 MyISAM 磁碟表

客戶端與工具區段建議

[mysqldump]
quick
max_allowed_packet=64M

[mysql]
no-auto-rehash
# safe-updates

[myisamchk]
key_buffer_size=256M
sort_buffer_size=256M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

調優驗證指令

SHOW GLOBAL STATUS LIKE 'Key_read%';          -- Key_reads/Key_read_requests < 0.01 為佳
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%'; -- 免費頁數、髒頁比例
SHOW GLOBAL STATUS LIKE 'Opened_tables';       -- 成長快則調大 table_open_cache
SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';   -- >0 則調大 sort_buffer_size
SHOW GLOBAL VARIABLES LIKE 'innodb_%';         -- 確認參數生效

結語

my.cnf 調優無銀彈，**「測量 → 調整 → 驗證」閉環**才是正道。以 innodb_buffer_pool_size 為核心，配合 max_connections、sort_buffer_size、table_open_cache 等參數，根據實際負載（OLTP/OLAP/混合）與硬體資源迭代調整。建議將配置納入版本控制，變更留存審計記錄。