MySQL 性能調優實戰：基於 Global Status 核心指標的精準優化策略

前言：拒絕「複製貼上」配置，建立指標驅動優化體系

同一份 my.cnf 在不同硬體、業務負載下表現截然不同。盲目套用網上的「優化模板」往往適得其反。本文建立一套 「觀測指標 → 計算比率 → 定位瓶頸 → 精準調參」 的閉環方法論，覆蓋慢查詢、連接數、索引緩存、臨時表、表緩存、線程池、查詢緩存、排序、文件句柄、表鎖、全表掃描等 11 個核心維度。

一、慢查詢：可見化的性能殺手

# 開啟慢查詢日誌（建議 1-5 秒）
slow_query_log = ON
long_query_time = 2
slow_query_log_file = /var/log/mysql/slow.log

# 關鍵指標
SHOW GLOBAL STATUS LIKE 'Slow_queries';  -- 累計慢查詢數
SHOW GLOBAL STATUS LIKE 'Slow_launch_threads'; -- 慢啟動線程數

實戰建議：

• 慢查詢閾值不宜超過 5 秒，微秒級分析可使用 Percona Server 打補丁版本。
• 主從架構下，僅在從庫開啟慢查詢日誌，避免主庫寫入效能損耗。
• 定期使用 pt-query-digest 分析 Top N 慢 SQL，針對性建立索引或改寫語句。

二、連接數：預防「Too many connections」

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

健康度公式：Max_used_connections / max_connections ≈ 85%

• 比例 > 85%：需擴大 max_connections 或架構層面讀寫分離。
• 比例 < 10%：max_connections 設定過大，浪費記憶體（每連接預分配緩衝區）。

三、Key Buffer（MyISAM 核心）：命中率與利用率雙控

SHOW VARIABLES LIKE 'key_buffer_size';  -- 512MB
SHOW GLOBAL STATUS LIKE 'key_read%';
-- key_read_requests: 27,813,678,764
-- key_reads: 6,798,830

核心指標：

• 未命中率 = key_reads / key_read_requests × 100% = 0.024%（優秀，< 0.1% 即合格）。
• 塊利用率 = key_blocks_used / (key_blocks_used + key_blocks_unused) × 100% ≈ 80% 為宜。

若未命中率 < 0.01%，說明 key_buffer_size 過大可適當縮減；若塊利用率長期 100%，需排查是否存在過度索引。

四、臨時表：磁盤落盤比例決定查詢速度

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_tables: 1,771,587
-- Created_tmp_disk_tables: 21,197  -- 磁盤臨時表
-- 比例 = 1.2% （優秀，< 25% 即合格）

控制參數：tmp_table_size、max_heap_table_size 取較小值生效。範例中 256MB 閾值，超過即落盤。建議：優化 SQL 減少大臨時表，或適當調大參數（防止 OOM）。

五、表緩存：降低文件描述符開銷

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

判斷標準：

• Open_tables / Opened_tables ≥ 85%：緩存命中良好。
• Open_tables / table_open_cache ≤ 95%：未觸及上限。
• 若 Opened_tables 快速增長，需調大 table_open_cache（注意 OS ulimit -n 限制）。

六、線程池：減少頻繁創建銷毀開銷

SHOW GLOBAL STATUS LIKE 'Threads%';
-- Threads_created: 570（累計創建數），Threads_cached: 46
SHOW VARIABLES LIKE 'thread_cache_size'; -- 64

經驗值：Threads_created 快速增長時，調大 thread_cache_size（如 64→128），讓斷開連接的線程進入緩存池復用。

七、查詢緩存：雙刃劍，需精細把控

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

三大核心比率：

關鍵參數：query_cache_min_res_unit（默認 4KB），小查詢多時易碎片，大查詢多時宜調大；query_cache_limit 超過此大小不緩存。

八、排序優化：避免磁盤臨時文件合併

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

Sort_merge_passes 增加表示 sort_buffer_size 不足導致多路歸併。調大 sort_buffer_size（如 2MB→4MB）可減少，但盲目調大反而增加 CPU 開銷。輔助參數 read_rnd_buffer_size 亦可適當調整。

九、文件句柄：防止「Too many open files」

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

使用率 1410/4590 ≈ 30%（健康，< 75% 即可）。高併發場景需同步調整 OS /etc/security/limits.conf 及 open_files_limit。

十、表鎖爭用：MyISAM 痛點，InnoDB 行鎖優勢

SHOW GLOBAL STATUS LIKE 'Table_locks%';
-- Table_locks_immediate: 490,206,328
-- Table_locks_waited: 2,084,912
-- 即時/等待比 = 235

判斷準則：比值 > 5000 時強烈建議遷移至 InnoDB（行級鎖）。範例 235 屬可接受範圍，MyISAM 尚可支撐。

十一、全表掃描：索引缺失的鐵證

SHOW GLOBAL STATUS LIKE 'Handler_read%';
-- Handler_read_rnd_next: 18,912,877,839（全表掃描次數）
SHOW GLOBAL STATUS LIKE 'Com_select'; -- 222,693,559

表掃描率 = Handler_read_rnd_next / Com_select ≈ 85（優秀，< 4000 即合格）。若超標，優先檢查 EXPLAIN 確認索引缺失，輔助調大 read_buffer_size（≤ 8MB）。

總結：建立持續優化閉環

性能調優非一次性動作，而是「監控→分析→調整→驗證」的持續迭代。建議：

1. 部署 Prometheus + Grafana + mysqld_exporter 實時採集上述核心指標。
2. 設定告警閾值（如 Slow_queries 環比激增、Threads_created 異常增長）。
3. 每月執行一次全量 pt-table-checksum 校驗主從一致性。
4. 維護「參數變更記錄表」，記錄每次調整前後指標對比，沉澱團隊知識庫。

唯有數據驅動，方能讓 MySQL 在業務增長中持續穩健運行。