命令介紹與訪問方式
-- 方式 1：MySQL 客戶端 (推薦，信息完整)
SHOW [FULL] PROCESSLIST;
-- 方式 2：命令行工具
mysqladmin -u root -p processlist
-- 方式 3：信息表 (可用於程序化監控)
SELECT * FROM information_schema.PROCESSLIST;
SELECT * FROM performance_schema.threads WHERE TYPE='FOREGROUND'; -- 5.6+ 更豐富
權限： 需 PROCESS 權限。擁有 SUPER 可見所有線程，否則僅見當前用戶線程。
輸出列詳細解讀
| 列名 | 含義 | 排查價值 |
|---|
| Id | 線程唯一標識 | KILL id 終止問題會話 |
| User | 連接用戶 | 定責：應用賬號/根用戶/監控賬號 |
| Host | 客戶端 IP:Port | 追蹤來源應用服務器、防火牆驗證 |
| db | 當前默認數據庫 | 定位業務域 |
| Command | 執行命令類型 | Sleep/Query/Connect/Binlog Dump/Execute 等 |
| Time | 當前狀態持續秒數 | 長時間 Sleep = 連接池洩漏；長時間 Query = 慢查詢 |
| State | 線程內部狀態 核心診斷指標 | 見下文狀態詳解 |
| Info | 具體 SQL 文本 (截斷顯示) | SHOW FULL PROCESSLIST 顯示完整 SQL |
State 狀態全景圖譜 (按生命週期分類)
🔍 查詢執行階段
| State | 含義 | 異常判斷 |
|---|
| Starting | 線程啟動初始化 | 極短，持續>1s 異常 |
| Checking permissions | 檢查表/列權限 | 權限表大/複雜時可見 |
| Opening tables | 打開表句柄 | 受 table_open_cache、DDL 鎖影響，持續高 = 緩存不足/頻繁 DDL |
| System lock / Table lock | 等待外部/內部表鎖 | MyISAM 明顯，InnoDB 行鎖不顯示此狀態 |
| Init / Optimizing / Statistics / Preparing | 優化器階段 | 復雜聯接/子查詢耗時長，關注 Statistics (統計信息採樣) |
| Executing | 開始執行查詢計劃 | 極短 |
| Sending data | 讀取行並發送給客戶端 | 最關鍵狀態：長時間 = 大結果集/網絡慢/無索引全掃描 |
| End / Query end | 查詢結束清理 | 極短 |
🔄 特殊操作階段
| State | 含義 | 場景 |
|---|
| Copying to tmp table | 創建內存臨時表 | GROUP BY/DISTINCT/UNION/ORDER BY 異列 |
| Copying to tmp table on disk | 內存臨時表超限轉磁盤 | 性能殺手：增大 tmp_table_size/max_heap_table_size、優化 SQL 避免臨時表 |
| Sorting for group / Sorting for order | 分組/排序操作 | 無索引支持時觸發，Filesort 開銷大 |
| Creating sort index | 構建排序索引 | 大結果集排序 |
| Repair by sorting / Repair with keycache | MyISAM 表修復 | 手動/自動修復 |
| Waiting for tables | 等待表結構變更生效 | FLUSH TABLES / ALTER TABLE / RENAME 後 |
🔒 鎖與併發相關
| State | 含義 | 排查方向 |
|---|
| Locked | 等待表/行鎖 | InnoDB 查 SHOW ENGINE INNODB STATUS 鎖等待鏈 |
| User Lock | 等待 GET_LOCK() | 應用層邏輯鎖 |
| Upgrading lock | INSERT DELAYED 升級鎖 | 已廢棄特性 |
😴 空閒與異常
| State | 含義 | 處理 |
|---|
| Sleep | 等待客戶端發送新請求 | 大量 Sleep = 連接池未釋放/配置不當，調整 wait_timeout/連接池 maxIdleTime |
| Killed | 收到 KILL 信號，等待釋放資源 | 若長時間不死，檢查是否被鎖阻塞 |
| Flushing tables | 執行 FLUSH TABLES，等待其他線程關閉表 | 備份/鎖表操作期間 |
實戰診斷場景
場景 1：大量 Sleep 線程佔滿連接數
-- 現象：Time 巨大、State=Sleep、Info=NULL
-- 根因：應用連接池未正確歸還、wait_timeout 過長、無心跳檢測
-- 解決：
-- 1. 應用層：配置連接池 validationQuery、testOnBorrow、maxIdleTime
-- 2. 數據庫層：降低 wait_timeout (建議 7200)、interactive_timeout
-- 3. 緊急清理：KILL 空閒超時線程 (腳本化)
場景 2：長時間 Sending data
-- 現象：Time 增長、State=Sending data、Info=大查詢
-- 根因：無索引全表掃描、大結果集傳輸、網絡延遲
-- 解決：
-- 1. EXPLAIN 分析 SQL，加索引/改寫
-- 2. 應用分頁/流式處理，避免 SELECT * 大結果集
-- 3. 檢查網絡：mtu、tcp_window_scaling、跨機房延遲
場景 3：Locked / Waiting for table metadata lock
-- 現象：State=Locked 或 Waiting for table metadata lock
-- 根因：DDL (ALTER) 持有 MDL 鎖、長事務未提交、外鍵檢查
-- 解決：
-- 1. 避開業務高峰做 DDL，使用 OSC/gh-ost
-- 2. 檢查長事務：SELECT * FROM information_schema.INNODB_TRX WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;
-- 3. 5.6+ 可用 performance_schema.metadata_locks 精準定位
場景 4：Copying to tmp table on disk
-- 現象：State=Copying to tmp table on disk
-- 根因：臨時表超過 tmp_table_size/max_heap_table_size (取較小值)
-- 解決：
-- 1. 優化 SQL：避免 SELECT *、減少 JOIN、利用索引消除排序/分組
-- 2. 適度增大參數 (但防止 OOM)：tmp_table_size=64M, max_heap_table_size=64M
-- 3. 5.6+ 可用 internal_tmp_disk_storage_engine=InnoDB (默認 MyISAM)
進階監控與自動化
-- 1. 實時監控腳本 (每秒採集)
watch -n 1 'mysql -e "SHOW FULL PROCESSLIST" | grep -v Sleep'
-- 2. 歷史分析：啟用 General Log / Audit Log，配合 pt-query-digest
-- 3. Prometheus 采集 (mysqld_exporter)
# 關鍵指標：
mysql_global_status_threads_connected
mysql_global_status_threads_running
mysql_global_status_slow_queries
mysql_global_status_aborted_connects
-- 4. 告警規則示例
- alert: MySQLTooManyConnections
expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
for: 2m
labels: {severity: warning}
annotations:
summary: "MySQL 連接數超過 80%"
總結
SHOW PROCESSLIST 是 MySQL 實時診斷的「聽診器」。核心在於讀懂 State：Sending data 看查詢效率，Locked/Waiting 看鎖爭用，Copying to tmp table on disk 看臨時表溢出，Sleep 看連接池健康。結合 EXPLAIN、Performance Schema、sys schema、監控體系，構建「秒級發現、分級告警、自動化處理」的運維閉環。記住：最好的優化是防患於未然，索引設計、SQL 規範、連接池配置，源頭治理勝過事後救火。
