MySQL 性能優化方案分享：基於 Show Status 核心指標的 10 維精準調優實戰

MySQL 性能優化方案分享：基於 Show Status 核心指標的 10 維精準調優實戰

MySQL 優化無標準答案，同配置不同負載表現迥異。本文拒絕「複製粘貼 my.cnf」，確立「以 Show Status 實時指標為導向，針對性調整變量」的科學方法論，逐一拆解慢查詢、連接數、Key Buffer、臨時表、Open Table、線程緩存、查詢緩存、排序、文件句柄、表鎖、表掃描 10 大核心維度的監控指標、計算公式、健康閾值與調優動作。

一、 核心數據源

SHOW GLOBAL STATUS;   -- 運行時狀態計數器
SHOW VARIABLES;       -- 當前配置參數

二、 10 維核心指標深度解析

1. 慢查詢

SHOW VARIABLES LIKE '%slow%';
-- log_slow_queries=ON, slow_launch_time=2
SHOW GLOBAL STATUS LIKE '%slow%';
-- Slow_queries=4148

• 動作：開啟慢查詢日志，閾值 1-5 秒。分析日志定位惡劣 SQL。主從架構下僅從庫開啟減輕主庫壓力。
• 微秒級：打 Percona Patch 或升級 5.7+ 原生支持。

2. 連接數

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

• 健康比率：Max_used_connections / max_connections ≈ 85%。
• 過低 (<10%)：max_connections 設過大，浪費內存（每連接分配排序/連接緩存）。
• 動作：根據峰值使用率調整，預留 15% 余量。

3. Key Buffer (MyISAM 核心)

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 = 0.0244%（極優，<0.1% 即優秀）。
• 塊使用率：Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ 80% 為宜。
• 動作：未命中率高 → 增加 key_buffer_size；塊全滿且未命中率低 → 可能過度索引，審視索引策略。

4. 臨時表

SHOW GLOBAL STATUS LIKE 'created_tmp%';
-- Created_tmp_disk_tables=21197, Created_tmp_tables=1771587
SHOW VARIABLES LIKE 'tmp_table_size'; -- 512MB
SHOW VARIABLES LIKE 'max_heap_table_size'; -- 256MB

• 磁盤臨時表率：Created_tmp_disk_tables / Created_tmp_tables = 1.2%（優，<25%）。
• 限制：實際臨時表大小受 MIN(tmp_table_size, max_heap_table_size) 限制，超過落盤。
• 動作：磁盤率高 → 增加兩參數；但防止單連接佔用過多內存導致 OOM。

5. Open Table 緩存

SHOW GLOBAL STATUS LIKE 'open%tables%';
-- Open_tables=919, Opened_tables=1951
SHOW VARIABLES LIKE 'table_open_cache'; -- 2048 (5.1.3+ 替代 table_cache)

• 命中率：Open_tables / Opened_tables = 47%（偏低，建議 >85%）。
• 使用率：Open_tables / table_open_cache = 45%（健康，<95%）。
• 動作：命中率低 → 增加 table_open_cache；注意文件句柄限制 open_files_limit。

6. 線程緩存

SHOW GLOBAL STATUS LIKE 'Thread%';
-- Threads_cached=46, Threads_connected=2, Threads_created=570, Threads_running=1
SHOW VARIABLES LIKE 'thread_cache_size'; -- 64

• 創建率：Threads_created / Connections 高 → 增加 thread_cache_size（建議 64-256）。
• 示例服務器健康，無需調整。

7. 查詢緩存

SHOW GLOBAL STATUS LIKE 'qcache%';
-- Qcache_hits=213028692, Qcache_inserts=208894227, Qcache_lowmem_prunes=4010916
SHOW VARIABLES LIKE 'query_cache%';
-- query_cache_size=203MB, query_cache_min_res_unit=4KB

• 碎片率：Qcache_free_blocks / Qcache_total_blocks = 20.5%（>20% 需 FLUSH QUERY CACHE 整理）。
• 利用率：(query_cache_size - Qcache_free_memory) / query_cache_size = 62%（健康 25-80%）。
• 命中率：(Qcache_hits - Qcache_inserts) / Qcache_hits = 1.94%（極低，寫多讀少場景查詢緩存失效頻繁，建議關閉）。
• 動作：寫密集業務關閉 QC；讀密集業務調大 query_cache_size、調小 query_cache_min_res_unit 減少碎片。

8. 排序操作

SHOW GLOBAL STATUS LIKE 'sort%';
-- Sort_merge_passes=29, Sort_scan=1860569

• Sort_merge_passes > 0 表示內存不足落盤歸併排序。
• 動作：增加 sort_buffer_size（每連接分配，慎重，建議 256KB-2MB）；優化 SQL 避免大結果集排序；增加 read_rnd_buffer_size 輔助。

9. 文件句柄

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

• 使用率：Open_files / open_files_limit = 30.7%（健康，<75%）。
• 系統級 ulimit -n 需配合調大。

10. 表鎖

SHOW GLOBAL STATUS LIKE 'table_locks%';
-- Table_locks_immediate=490206328, Table_locks_waited=2084912
-- 即時/等待比 = 235 (<5000 尚可，>5000 建議遷移 InnoDB 行鎖)

11. 表掃描

SHOW GLOBAL STATUS LIKE 'handler_read%';
-- Handler_read_rnd_next=18912877839
SHOW GLOBAL STATUS LIKE 'com_select'; -- 222693559
-- 表掃描率 = Handler_read_rnd_next / Com_select = 84.9 (>4000 為嚴重，需建索引/增大 read_buffer_size 至 8MB 內)

三、 調優方法論總結

1. 建立基線：業務低峰期收集完整 SHOW GLOBAL STATUS 快照。
2. 對標閾值：逐項計算核心比率，標記異常指標。
3. 關聯 SQL：結合慢查詢日志、EXPLAIN 定位根因（缺索引、惡劣計劃、配置不足）。
4. 單點變更：每次調整一個參數，觀察負載測試/生產觀察窗口（至少 30 分鐘）。
5. 持續迭代：優化是持續過程，隨業務增長定期複盤。

拒絕盲目調參，用數據說話，讓 MySQL 在你的業務負載下跑出最優性能。