MySQL 服務器配置優化完全指南

MySQL 服務器配置優化完全指南

MySQL 服務器的效能很大程度上取決於配置參數的正確設定。透過 mysqld --help 指令可以查看所有可用的配置變數及其預設值，而 mysqladmin variables 則能顯示正在運行的服務器實際使用的參數值。本文將詳細解釋關鍵配置參數的含義與優化策略，幫助您根據硬體資源與業務場景調整 MySQL 以獲得最佳效能。

核心配置參數詳解

連接相關參數

• max_connections：允許的最大同時客戶端連接數。預設 100，高併發場景需適當調大，但會增加文件描述符消耗。
• back_log：MySQL 暫停響應新請求前，堆疊中可容納的連接請求數量。當短時間內湧入大量連接時需調大，但受作業系統 listen() 系統呼叫限制。
• connect_timeout：服務器等待連接封包的秒數，超時則返回「Bad handshake」。
• max_connect_errors：單一主機中斷連接超過此次數將被封鎖，可用 FLUSH HOSTS 解除。

緩衝區與快取參數

• key_buffer_size：索引塊緩衝區大小，所有線程共享。對 MyISAM 表至關重要，建議設為實體記憶體的 25%-33%，但不可過大導致系統換頁。
• query_cache_size：查詢快取總大小。適合讀多寫少的場景，需監控 Qcache_lowmem_prunes 與 Qcache_hits 調整。
• sort_buffer_size、read_buffer_size、join_buffer_size、read_rnd_buffer_size：每連接獨佔的緩衝區，用於排序、讀取、聯結等操作。設定時需考慮最大連接數乘以單連接緩衝區不超過可用記憶體。
• table_open_cache (舊稱 table_cache)：所有線程打開表的數量，影響文件描述符使用。
• thread_cache_size：線程快取大小，減少頻繁建立/銷毀線程的開銷。

InnoDB 專用參數

• innodb_buffer_pool_size：InnoDB 最關鍵參數，緩存數據與索引。純 InnoDB 環境建議設為實體記憶體 60%-80%，或比表空間大 10%。
• innodb_log_file_size：重做日誌文件大小，影響恢復速度與寫入效能，建議 256M 平衡兩者。
• innodb_flush_log_at_trx_commit：事務提交時日誌刷盤策略。1=最安全(ACID)，2=每秒刷盤(僅 OS 崩潰丟失 1 秒)，0=每秒刷盤(MySQL 崩潰丟失 1 秒)。
• innodb_flush_method：Linux 下建議 O_DIRECT 避免雙重緩衝，尤其在 RAID 上效能提升明顯。
• innodb_file_per_table：啟用後每個表獨立表空間，便於管理與回收空間。
• innodb_thread_concurrency：InnoDB 內核最大併發線程數，建議 (CPU核心數 + 磁碟數) * 2，或設 0 禁用限制。

其他重要參數

• max_allowed_packet：單個封包最大值，含 BLOB 大數據時需調大。
• tmp_table_size、max_heap_table_size：臨時表大小限制，超過則轉磁碟 MyISAM 表，影響效能。
• wait_timeout、interactive_timeout：非互動/互動連接空閒超時秒數，防止閒置連接佔用資源。
• long_query_time：慢查詢閾值(秒)，配合 log-slow-queries 定位效能瓶頸。
• skip-name-resolve：禁用 DNS 解析，消除解析延遲，但授權表需改用 IP 地址。
• low_priority_updates、concurrent_insert：MyISAM 讀寫優先級與並發插入控制。

典型場景配置範例

大記憶體、中等連接、追求極致效能

mysqld_safe \
  --key_buffer_size=16M \
  --table_open_cache=128 \
  --sort_buffer_size=4M \
  --read_buffer_size=1M &

記憶體有限、大量連接

mysqld_safe \
  --key_buffer_size=512K \
  --sort_buffer_size=100K \
  --read_buffer_size=100K &

極度受限環境

mysqld_safe \
  --key_buffer_size=512K \
  --sort_buffer_size=16K \
  --table_open_cache=32 \
  --read_buffer_size=8K \
  --net_buffer_length=1K &

驗證配置生效

修改參數後，執行 mysqld --key_buffer=32M --help (注意 --help 必須在最後) 可查看實際生效值。亦可用 SHOW VARIABLES 與 SHOW STATUS 監控運行狀態。

優化原則總結

1. 記憶體分配：InnoDB 用 innodb_buffer_pool_size，MyISAM 用 key_buffer_size，兩者互斥場景勿同時設大。
2. 每連接緩衝區：sort_buffer_size 等乘以 max_connections 不得超過物理記憶體 70%。
3. 避免 Swap：總記憶體佔用超過物理記憶體會觸發 Swap，效能劇降。
4. 監控指標：定期檢查 Key_reads/Key_read_requests、Qcache_lowmem_prunes、Created_tmp_disk_tables、Threads_created 等狀態值微調參數。
5. 硬體配合：SSD/RAID 存放 innodb_data_file_path、innodb_log_file、tmpdir；分離數據與日誌磁碟；足夠記憶體是最高性價比投資。

MySQL 配置無銀彈，需根據實際業務讀寫比例、數據量、併發數、硬體規格持續觀測與調整。建立基準測試，單一變數調優，方能獲得穩定可預期的效能提升。