MySQL 核心配置參數優化實戰指南

前言

MySQL 的效能表現高度依賴於配置參數的調校。本文整理三種常見優化方案，涵蓋從基礎參數設定到連接數管理、緩衝區分配的完整思路，並附帶實戰監控指標，幫助 DBA 根據硬體資源與業務負載靈活調整。

方案一：WinMySQLAdmin 產出的基礎範本（適用於 Windows 環境）

此配置由 WinMySQLAdmin 1.4 工具生成，適合中小型應用作為起始基準：

[mysqld]
basedir=D:/mysql
datadir=D:/mysql/data
# port=3306
max_connections=1500
skip-locking
key_buffer=384M
max_allowed_packet=1M
table_cache=512
sort_buffer=2M
record_buffer=2M
thread_cache=8
thread_concurrency=8
myisam_sort_buffer_size=64M
server-id = 1

[isamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=128M
sort_buffer=128M
read_buffer=2M
write_buffer=2M

調校重點：

• max_connections 不宜設過大（幾百通常足夠），過大會增加伺服器負擔，導致連接超時甚至當機。
• wait_timeout、interactive_timeout 需依實際訪問量調整；高併發建議設小（5-20 秒），搭配持久連接（pconnect）效果最佳。
• 觀察 Created_tmp_disk_tables、Slow_queries 等狀態值，越接近零越理想。

方案二：針對 2GB 記憶體的進階優化（Linux/Unix 環境）

核心策略：將熱點操作集中在記憶體，減少磁碟 I/O。

[mysqld]
skip-innodb
skip-bdb
skip-name-resolve
skip-locking
# log-bin 註解關閉二進制日誌

key_buffer=512M
max_allowed_packet=4M
table_cache=1024
thread_cache=64
join_buffer_size=32M
sort_buffer=32M
record_buffer=32M
max_connections=512
wait_timeout=120
interactive_timeout=120
max_connect_errors=30000
long_query_time=1
max_heap_table_size=256M
tmp_table_size=128M
thread_concurrency=8
myisam_sort_buffer_size=128M

關鍵說明：

• 關閉 skip-innodb、skip-bdb 可節省記憶體（若不使用該引擎）。
• skip-name-resolve 避免 DNS 解析延遲。
• join_buffer_size、sort_buffer、record_buffer 為每線程分配，設大時需評估 max_connections 總記憶體佔用。
• 定期執行 SHOW STATUS 監控 Created_tmp_disk_tables、Created_tmp_tables、Slow_queries，微調參數。

方案三：連接數耗盡問題的根因分析與解決

核心指標

• max_connections：MySQL 允許的最大連接數（預設 100，上限 16384）。
• threads_connected：當前活躍連接線程數（動態）。

排查步驟

1. 進入 MySQL 執行 SHOW VARIABLES 查看 max_connections。
2. 執行 SHOW STATUS 查看 threads_connected。
3. 若 threads_connected == max_connections，新連接將被拒絕。
4. 刷新頁面持續監控 threads_connected；若持續增長至上限，檢查應用程式是否未釋放連接（連接池洩漏）。

修改建議

[mysqld]
max_connections=32000  # 實際上限 16384，視記憶體而定

修改後重啟 MySQL 生效。注意：單純調大連接數不解決根本問題，需配合應用層連接池優化、SQL 調優、慢查詢治理。

總結

MySQL 優化無銀彈，必須根據「硬體規格、資料量、訪問模式、業務 SLA」綜合考量。建議建立基準測試，逐步調整並觀察核心指標變化，切勿生搬硬套他人配置。