MySQL 系統變量與狀態變量詳解：動態調優、監控指標與實戰應用
MySQL 運行期間暴露兩大變量體系：**系統變量** 控制行為（可配置、可動態調整）、**狀態變量** 反映運行統計（只增不減、只讀）。理解兩者區別、作用域、修改方式，是動態調優與自動化監控的基石。
一、系統變量
1.1 兩大作用域
| 維度 | GLOBAL（全局） | SESSION（會話） |
|---|
| 影響範圍 | 整個實例，所有連線 | 當前連線/會話 |
|---|
| 初始化 | 啟動讀取配置檔/命令列參數 | 連線建立時複製對應 GLOBAL 值 |
|---|
| 修改權限 | 需 SUPER / SYSTEM_VARIABLES_ADMIN | 無特殊權限，僅影響自身 |
|---|
| 修改語法 | SET GLOBAL var=val; / SET @@GLOBAL.var=val; | SET SESSION var=val; / SET @@SESSION.var=val; / SET var=val; |
|---|
| 生效時間 | 立即對**新連線**生效，已有連線不變 | 立即對**當前會話**生效 |
|---|
| 持久化 | 8.0+ SET PERSIST var=val; 寫入 mysqld-auto.cnf，重啟保留 | 會話結束即失效 |
|---|
1.2 常用動態調優變量
| 變量 | 作用域 | 典型調優場景 |
|---|
sort_buffer_size | G/S | 會話級加大應對大排序 |
join_buffer_size | G/S | 會話級加大應對無索引 Join |
read_buffer_size | G/S | 順序掃描密集場景 |
max_allowed_packet | G/S | 大 Blob/長 SQL 防包過大報錯 |
innodb_buffer_pool_size | G | **需重啟**（5.7），8.0 支援線上擴容 |
innodb_flush_log_at_trx_commit | G | 1→2 犧牲 1s 數據換寫入吞吐 |
sync_binlog | G | 1→1000/0 大幅提升寫入，風險自評 |
max_connections | G | 連線不夠時緊急擴容 |
long_query_time | G/S | 會話級調小抓慢查詢 |
sql_mode | G/S | 會話級切換兼容模式 |
1.3 查看系統變量
SHOW VARIABLES; -- 全量
SHOW VARIABLES LIKE 'innodb_%'; -- 模糊匹配
SHOW GLOBAL VARIABLES LIKE 'max_connections';
SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
二、狀態變量
2.1 特徵
- **只讀、只增、全局唯一**（無 SESSION 維度，8.0 引入少量 session 級狀態）。
- 啟動歸零，
FLUSH STATUS 歸零（大部分）。
- 累計型：
Connections、Questions、Bytes_sent。
- 瞬間型：
Threads_running、Threads_connected、Innodb_buffer_pool_pages_free。
2.2 核心監控指標
| 指標 | 含義 | 告警閾值建議 |
|---|
Threads_running | 當前活躍執行線程數 | > CPU 核心數 × 2 持續 5min |
Threads_connected | 當前總連線數 | 接近 max_connections 80% |
Connections | 歷史總連線嘗試數 | 速率突增 = 連線風暴 |
Aborted_connects | 連線失敗數 | > 0 即告警 |
Questions / Queries | 執行語句數 / 含存儲過程內部 | QPS 計算基數 |
Slow_queries | 慢查詢累計 | 速率 > 10/min |
Innodb_buffer_pool_read_requests / _reads | 邏輯讀 / 物理讀 | 命中率 < 99% |
Innodb_row_lock_waits / _time_avg | 行鎖等待次數 / 平均耗時 | 待分析 |
Table_locks_waited | 表鎖等待次數 | > 0 需優化 |
Created_tmp_disk_tables | 磁盤臨時表數 | 佔比 > 10% |
Handler_read_rnd_next | 全表掃描行數 | 佔比高 = 缺索引 |
2.3 查看狀態變量
SHOW STATUS; -- 全量
SHOW STATUS LIKE 'Innodb_row_lock%';
SHOW GLOBAL STATUS LIKE 'Threads_running';
三、實戰組合拳
- 動態抓慢查詢：
SET SESSION long_query_time=0.1; SET SESSION log_slow_admin_statements=ON; 當前會話即時生效，不影響全局。
- 緊急擴容連線：
SET GLOBAL max_connections=2000; 立即生效，配合 SET PERSIST 固化。
- 只讀切換：
SET GLOBAL read_only=ON; 秒級切主從，配合 Proxy 自動故障轉移。
- 計算 QPS：
(Questions_now - Questions_1min_ago) / 60。
- Buffer Pool 命中率：
1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。
四、持久化配置
-- 8.0+ 持久化全局變量
SET PERSIST innodb_buffer_pool_size = 8589934592; -- 8G
-- 查看持久化記錄
SELECT * FROM performance_schema.persisted_variables;
-- 刪除持久化（恢復配置檔值）
SET PERSIST innodb_buffer_pool_size = DEFAULT;
五、自動化監控建議
- Prometheus
mysqld_exporter 采集 global_status、global_variables。
- Grafana 儀表盤覆蓋：連線池、QPS、延遲、緩存命中、鎖等待、磁盤臨時表、複寫延遲。
- 告警規則：
Threads_running > 200、Innodb_row_lock_time_avg > 100ms、Created_tmp_disk_tables / Created_tmp_tables > 0.1。
熟練運用系統/狀態變量，實現 MySQL「可觀測、可調優、可自癒」的運維閉環。
