基於 MySQL 狀態變量的系統參數精準調優指南

核心監控入口

SHOW GLOBAL STATUS;      -- 運行時計數器
SHOW VARIABLES;          -- 當前配置
SHOW VARIABLES LIKE 'xxx%'; -- 模糊查詢配置

一、慢查詢日志：發現性能殺手

配置

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2          -- 超過 2 秒記錄
log_queries_not_using_indexes = ON  -- 無索引查詢也記錄

分析工具

• mysqldumpslow -s t -t 10 /var/log/mysql/slow.log：按耗時排序 Top 10
• pt-query-digest：Percona Toolkit 深度分析

主從架構技巧

僅在從庫開啟慢查詢日志，主庫零性能損耗。

二、連接數管理：拒絕「Too many connections」

關鍵指標

SHOW VARIABLES LIKE 'max_connections';        -- 上限
SHOW GLOBAL STATUS LIKE 'Max_used_connections'; -- 歷史峰值

健康標準

Max_used_connections / max_connections ≈ 85%

• < 10%：上限設過大，浪費內存
• > 95%：臨近上限，需擴容或優化應用連接池

應用層配合

• 連接池大小 < max_connections * 0.7
• 啟用 wait_timeout=300、interactive_timeout=300 快速回收空閒連接

三、Key Buffer (MyISAM 專屬)

指標

SHOW VARIABLES LIKE 'key_buffer_size';
SHOW GLOBAL STATUS LIKE 'Key_read%';

計算

Miss Rate = Key_reads / Key_read_requests

• < 0.01%：極優
• 0.01% - 0.1%：良好
• > 1%：需擴大 key_buffer_size 或減少索引

填充度

SHOW GLOBAL STATUS LIKE 'Key_blocks_u%';
Used Ratio = Key_blocks_used / (Key_blocks_used + Key_blocks_unused) ≈ 80%

四、臨時表與排序

磁盤臨時表比率

SHOW GLOBAL STATUS LIKE 'Created_tmp%';
Disk Ratio = Created_tmp_disk_tables / Created_tmp_tables < 25%

調優參數

• tmp_table_size、max_heap_table_size：取較小值為內存臨時表上限
• 超過上限自動轉磁盤（MyISAM），建議設為 64M-256M

排序緩衝

SHOW GLOBAL STATUS LIKE 'Sort_merge_passes';  -- 越大越慢
SHOW VARIABLES LIKE 'sort_buffer_size';       -- 推薦 2M-8M（會話級）

Sort_merge_passes 大 → 增加 sort_buffer_size 或優化 SQL 避免 filesort。

五、表緩存

SHOW GLOBAL STATUS LIKE 'Open%tables%';
SHOW VARIABLES LIKE 'table_open_cache';  -- 5.1.3+ 參數名
Hit Ratio = Open_tables / Opened_tables > 85%
Cache Usage = Open_tables / table_open_cache < 95%

六、線程緩存

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW VARIABLES LIKE 'thread_cache_size';
-- Threads_created 過大 → 增加 thread_cache_size (推薦 32-128)

七、查詢緩存 (已廢棄，僅 5.7 參考)

SHOW GLOBAL STATUS LIKE 'Qcache%';
SHOW VARIABLES LIKE 'query_cache%';
Hit Rate = (Qcache_hits - Qcache_inserts) / Qcache_hits
Frag Rate = Qcache_free_blocks / Qcache_total_blocks
Mem Usage = (query_cache_size - Qcache_free_memory) / query_cache_size

建議：query_cache_type=0、query_cache_size=0 釋放內存給 Buffer Pool。

八、文件描述符

SHOW GLOBAL STATUS LIKE 'Open_files';
SHOW VARIABLES LIKE 'open_files_limit';
Usage = Open_files / open_files_limit < 80%

系統層面：ulimit -n 65535、/etc/security/limits.conf。

九、表鎖爭用 (MyISAM)

SHOW GLOBAL STATUS LIKE 'Table_locks%';
Wait Ratio = Table_locks_waited / Table_locks_immediate
-- > 0.0002 (1/5000) 考慮遷移 InnoDB 行鎖

十、全表掃描監控

SHOW GLOBAL STATUS LIKE 'Handler_read%';
SHOW GLOBAL STATUS LIKE 'Com_select';
Scan Ratio = Handler_read_rnd_next / Com_select
-- > 4000 表明嚴重缺索引，需結合 EXPLAIN 分析

調優執行清單

1. 建立基線：記錄關鍵指標當前值
2. 識別瓶頸：按「連接→緩存→I/O→鎖→CPU」順序排查
3. 單項調整：修改一項參數，觀察 30 分鐘
4. 壓測驗證：sysbench / tpcc-mysql 模擬業務負載
5. 固化配置：寫入 /etc/my.cnf，重啟生效