MySQL 進階優化實戰：配置檔選擇與關鍵參數調校指南

一、my.cnf 配置檔的選擇策略

透過 RPM 包安裝的 MySQL，其預設配置檔通常位於 /usr/local/mysql/share/MySQL/ 目錄下。該目錄提供了四份針對不同硬體規模的範本配置：

• my-huge.cnf：適用於記憶體 1GB-2GB、專職資料庫伺服器
• my-large.cnf：適用於記憶體 512MB 左右的伺服器
• my-medium.cnf：適用於記憶體 128MB-256MB 的中小型伺服器
• my-small.cnf：適用於記憶體 64MB 以下或共享主機環境

實務上建議根據實際記憶體大小選擇最接近的範本，再針對業務特性進行微調，而非直接套用預設值。

二、針對 MyISAM 引擎的核心參數調優

若生產環境以 MyISAM 為主，以下三個參數對效能影響最大：

• key_buffer_size：索引緩衝區大小，直接決定索引讀取速度
• query_cache_size：查詢快取大小，適合「讀多寫少」場景
• table_cache（即 table_open_cache）：同時開啟的資料表數量，減少開關表開銷

三、十大優化策略與監控指標

1. 慢查詢分析

SHOW VARIABLES LIKE '%slow%';     -- 確認慢查詢日誌是否開啟
SHOW GLOBAL STATUS LIKE '%slow%'; -- 查看超過長查詢時間（預設 2 秒）的查詢數

建議開啟 slow_query_log 並設定合理的 long_query_time（如 1 秒），定期分析慢查詢日誌。

2. 連接數監控

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';

理想公式：Max_used_connections / max_connections × 100% ≈ 85%。若長期接近 100%，需擴大 max_connections 或檢查連接洩漏。

3. Key Buffer 命中率

SHOW STATUS LIKE 'Key_read%';

關鍵比率：Key_reads / Key_read_requests ≤ 1:100（最佳 1:1000）。若比率過高，需增加 key_buffer_size。

4. 臨時表磁盤化比率

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

目標：Created_tmp_disk_tables / Created_tmp_tables × 100% ≤ 25%。超標時調大 tmp_table_size 與 max_heap_table_size（兩者取較小值生效）。

5. 表快取命中情況

SHOW GLOBAL STATUS LIKE 'Open%tables%';

若 Open_tables = table_cache 且 Opened_tables 持續增長，說明快取不足，需增大 table_open_cache。但切勿盲目設過大，以免耗盡檔案描述符導致不穩定。

6. 執行緒快取效率

SHOW GLOBAL STATUS LIKE 'Thread%';
SHOW VARIABLES LIKE 'thread_cache_size';

Threads_created 過大表示頻繁建立銷毀執行緒，建議適當增加 thread_cache_size（如 50-100），降低 CPU 開銷。

7. 查詢快取健康度

SHOW STATUS LIKE 'Qcache%';

• Qcache_lowmem_prunes 大 → 快取不足，需增大 query_cache_size
• Qcache_hits 大且 Qcache_lowmem_prunes 小 → 快取有效
• Qcache_hits 小 → 查詢重複率低，快取反成負擔，建議關閉
• Qcache_free_blocks 大 → 碎片多，執行 FLUSH QUERY CACHE 整理

8. 排序緩衝區調優

SHOW GLOBAL STATUS LIKE 'Sort%';

Sort_merge_passes 過大表示記憶體不足導致多路歸併排序，需增大 sort_buffer_size。但此參數是 per-connection 分配，不可設過大（建議 2-8MB）。

9. 開啟檔案數監控

SHOW GLOBAL STATUS LIKE 'Open_files';
SHOW VARIABLES LIKE 'open_files_limit';

安全閾值：Open_files / open_files_limit × 100% ≤ 75%。接近上限時需調大系統 ulimit -n 及 MySQL open_files_limit。

10. 表鎖爭用分析

SHOW GLOBAL STATUS LIKE 'Table_locks%';

若 Table_locks_immediate / Table_locks_waited > 5000，高併發寫入場景強烈建議遷移至 InnoDB（行級鎖），MyISAM 僅適合讀多寫少。

11. 全表掃描偵測

SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Com_select';

掃描率公式：Handler_read_rnd_next / Com_select。若超過 4000，說明索引嚴重缺失或 SQL 需重寫。可適度增大 read_buffer_size（≤ 8MB）緩解，但根治在於補索引與 SQL 優化。