您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能調優實戰：透過 SHOW STATUS 與 SHOW VARIABLES 精準診斷

編輯：MySQL 資料庫相關知識

MySQL 效能調優的核心在於「測量、分析、調整」閉環。善用 SHOW GLOBAL STATUS 與 SHOW VARIABLES 觀察關鍵指標，能快速定位瓶頸並制定針對性優化策略。本文整理十一大核心維度的監控指標、計算公式與調優建議。

一、慢查詢分析

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

log_slow_queries = ON、slow_launch_time = 2：啟用慢查詢日誌，閾值 2 秒。
Slow_queries = 4148：累積慢查詢數。
建議：閾值設 1~5 秒；主從架構下僅在從庫開啟慢查詢日誌以減輕主庫負擔；微秒級分析可使用 Percona Patch。

二、連接數監控

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

理想比例：Max_used_connections / max_connections ≈ 85%
若比例 < 10%，說明 max_connections 設過大，浪費記憶體。

三、Key Buffer 命中率

SHOW VARIABLES LIKE 'key_buffer_size';        -- 512MB
SHOW GLOBAL STATUS LIKE 'key_read%';
-- Key_read_requests = 27813678764
-- Key_reads = 6798830

未命中率 = Key_reads / Key_read_requests × 100% = 0.0244%（極佳）
目標：< 0.1%（每千次請求僅 1 次讀碟）；< 0.01% 則 buffer 過大可縮減。
區塊使用率：Key_blocks_used / (Key_blocks_unused + Key_blocks_used) ≈ 80% 為宜。

四、臨時表使用情況

SHOW GLOBAL STATUS LIKE 'created_tmp%';
-- Created_tmp_tables = 1,771,587
-- Created_tmp_disk_tables = 21,197
-- 磁碟臨時表比率 = 1.20% （優秀，建議 ≤ 25%）

調整 tmp_table_size、max_heap_table_size（取較小者）控制記憶體臨時表上限，超過則轉磁碟。

五、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_cache（5.1.3+ 稱 table_open_cache）過小。

六、執行緒快取

SHOW GLOBAL STATUS LIKE 'Thread%';
-- Threads_created = 570, 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 × 100% > 20% 需 FLUSH QUERY CACHE 或降低 query_cache_min_res_unit。
利用率 = (query_cache_size - Qcache_free_memory) / query_cache_size × 100%：< 25% 過大、> 80% 且 Qcache_lowmem_prunes > 50 過小。
命中率 = (Qcache_hits - Qcache_inserts) / Qcache_hits × 100%：本例 1.94% 極低，可能寫入頻繁導致快取失效。

八、排序使用情況

SHOW GLOBAL STATUS LIKE 'sort%';
-- Sort_merge_passes = 29（需增加 sort_buffer_size 減少磁碼合併排序）

增加 sort_buffer_size、read_rnd_buffer_size 可改善排序效能，但盲目調大反而可能降低效能。

九、開啟檔案數

SHOW GLOBAL STATUS LIKE 'open_files';     -- 1410
SHOW VARIABLES LIKE 'open_files_limit';   -- 4590
-- 比率 30.7% （建議 ≤ 75%）

十、表鎖爭用

SHOW GLOBAL STATUS LIKE 'table_locks%';
-- Table_locks_immediate = 490,206,328
-- Table_locks_waited = 2,084,912
-- 比率 235 （> 5000 建議改用 InnoDB 行鎖）

十一、表掃描率

SHOW GLOBAL STATUS LIKE 'handler_read%';
SHOW GLOBAL STATUS LIKE 'com_select';
-- 掃描率 = Handler_read_rnd_next / Com_select
-- > 4000 表示索引不足，需檢查索引設計或適度增加 read_buffer_size（≤ 8MB）