MySQL 服務器效能優化實戰：從狀態變數到參數調整

前言

優化 MySQL 不是套用模板，而是「觀察狀態 → 找出瓶頸 → 調整參數 → 驗證效果」的迭代過程。本文整理 11 大核心指標的計算公式、經驗閾值與調整建議，並附實際 SHOW GLOBAL STATUS / SHOW VARIABLES 範例。

一、慢查詢

SHOW VARIABLES LIKE '%slow%';
SHOW GLOBAL STATUS LIKE '%slow%';

• 開啟：slow_query_log=ON、long_query_time=1（秒）、log_queries_not_using_indexes=ON。
• 分析工具：mysqldumpslow -s t -t 20 slow.log（按耗時 Top 20）。
• 主從架構建議：只在從庫開啟慢查詢日誌，減輕主庫負擔。

二、連接數

SHOW VARIABLES LIKE 'max_connections';        -- 256
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 245

使用率 = Max_used_connections / max_connections × 100%。
目標： 85% 左右。
< 10% → 設定過大；> 95% → 需調大或架構擴展（讀寫分離、連線池）。

三、Key Buffer（MyISAM）

SHOW VARIABLES LIKE 'key_buffer_size';        -- 536870912 (512MB)
SHOW GLOBAL STATUS LIKE 'key_read%';          -- Requests: 27813678764, Reads: 6798830

未命中率 = Key_reads / Key_read_requests = 0.0244%（優）。
經驗值： < 0.1% 良好；< 0.01% 可能過大可縮減。
另觀察 Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ 80% 為宜。

四、臨時表

SHOW GLOBAL STATUS LIKE 'created_tmp%';
-- Created_tmp_disk_tables: 21197, Created_tmp_tables: 1771587
SHOW VARIABLES LIKE 'tmp_table_size';         -- 536870912 (512MB)
SHOW VARIABLES LIKE 'max_heap_table_size';    -- 268435456 (256MB)

磁碟臨時表比率 = 1.20%（優，< 25%）。
實際上限受限於 max_heap_table_size（256MB），超過即落地。

五、Open Table

SHOW GLOBAL STATUS LIKE 'open%tables%';
-- Open_tables: 919, Opened_tables: 1951
SHOW VARIABLES LIKE 'table_cache';            -- 2048

Open_tables / Opened_tables ≥ 85%、Open_tables / table_cache ≤ 95%。
若 Opened_tables 快速增長 → 調大 table_open_cache。

六、執行緒快取

SHOW GLOBAL STATUS LIKE 'Thread%';
-- Threads_created: 570, Threads_cached: 46
SHOW VARIABLES LIKE 'thread_cache_size';      -- 64

Threads_created 快速增長 → 調大 thread_cache_size。

七、查詢快取

SHOW GLOBAL STATUS LIKE 'qcache%';
SHOW VARIABLES LIKE 'query_cache%';

關鍵指標：

• 碎片率 = Qcache_free_blocks / Qcache_total_blocks > 20% → FLUSH QUERY CACHE 或降低 query_cache_min_res_unit。
• 利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size：25% 以下過大、80% 以上且 Qcache_lowmem_prunes > 50 過小。
• 命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits：低命中率常見於高寫入場景，建議關閉查詢快取（query_cache_type=OFF、query_cache_size=0），MySQL 8.0 已移除。

八、排序緩衝

SHOW GLOBAL STATUS LIKE 'sort%';
-- Sort_merge_passes: 29（需關注）

Sort_merge_passes 大 → 增加 sort_buffer_size（連線級參數，慎調大）。
輔助：read_rnd_buffer_size。

九、檔案描述符

SHOW GLOBAL STATUS LIKE 'open_files';       -- 1410
SHOW VARIABLES LIKE 'open_files_limit';     -- 4590

使用率 < 75% 為宜。系統層面 ulimit -n 需配合調大。

十、表鎖

SHOW GLOBAL STATUS LIKE 'table_locks%';
-- Table_locks_immediate: 490206328, Table_locks_waited: 2084912

即時/等待比 > 5000 → 考慮 InnoDB 行鎖替代 MyISAM 表鎖。

十一、表掃描

SHOW GLOBAL STATUS LIKE 'handler_read%';
SHOW GLOBAL STATUS LIKE 'com_select';

掃描率 = Handler_read_rnd_next / Com_select。
> 4000 → 索引缺失或查詢寫法不當，優先優化 SQL、索引；read_buffer_size 微調不超過 8MB。

總結與工具推薦

• 參考值非絕對，需結合業務負載、硬體規格綜合判斷。
• 作業系統層面：top、iostat -x 1（磁碟 I/O 通常是最終瓶頸）。
• 自動化巡檢腳本：tuning-primer.sh。
• 官方文件：Server Status Variables、Server System Variables。