MySQL 效能調優實戰經驗：核心參數配置與狀態分析

問題背景

隨著線上併發增長，1GB 記憶體伺服器顯吃力，嚴重時每天死機或頻繁卡頓，困擾半月之久。MySQL 採用伸縮性演算法，通常能在少量記憶體下運行，或透過分配更多記憶體獲取更好效能。

配置檔案選擇與核心參數

安裝後配置檔位於 /usr/local/mysql/share/mysql/，包含 my-huge.cnf、my-medium.cnf、my-large.cnf、my-small.cnf 四種範本。一般複製 my-medium.cnf 至 /etc/my.cnf 修改。三大核心參數：

• key_buffer_size
• query_cache_size
• table_cache

可用 mysqladmin variables extended-status -u root -p 查看當前值，推薦使用 phpMyAdmin 監控狀態。

key_buffer_size：MyISAM 索引緩衝區

僅對 MyISAM 生效，決定索引處理速度（特別是讀取）。一般設 16MB，較大站點遠不夠。檢查狀態值 Key_read_requests 與 Key_reads，比例 Key_reads / Key_read_requests 應盡可能低，至少 1:100，1:1000 更佳（可用 SHOW STATUS LIKE 'key_read%' 獲取）。

實例分析（運行 20 天）：

• key_buffer_size = 128MB
• key_read_requests = 650,759,289
• key_reads = 79,112
• 比例 ≈ 1:8000，狀態極佳

另一估算法：統計所有表索引大小總和。該伺服器大表索引約 125MB，隨表增長而增長。

query_cache_size：查詢快取

MySQL 4.0.1+ 提供查詢快取機制：將 SELECT 語句與結果存入快取，後續相同查詢（區分大小寫）直接讀取結果，官方稱最高可達 238% 效率提升。

關鍵狀態值判斷設定合理性：

• Qcache_inserts
• Qcache_hits
• Qcache_lowmem_prunes（值大表快取不足）
• Qcache_free_blocks（值大表碎片多）
• Qcache_total_blocks

判斷邏輯：

• Qcache_lowmem_prunes 大 + Qcache_hits 大 → 需增大快取
• Qcache_hits 小 → 查詢重複率低，快取反而拖累效能，考慮停用
• 特定查詢可加 SQL_NO_CACHE 禁用快取

query_cache_type 控制是否啟用。筆者設定：query_cache_size = 32M。

[1] [2] [3] [4] [5] [6] [7] 下一頁