MySQL 執行狀態深度解析：show processlist 實戰排查指南

MySQL 執行狀態深度解析：show processlist 實戰排查指南

當 MySQL 性能抖動、查詢堆積、連接數飆升時，SHOW [FULL] PROCESSLIST 是第一現場勘察工具。本文系統解析 8 大列含義、5 類核心 State 狀態成因與處置策略，助你快速定位性能瓶頸。

一、 八大列含義速查表

💡 進階：SHOW FULL PROCESSLIST 顯示完整 SQL；performance_schema.threads / events_statements_current 提供更豐富元數據（無截斷、含執行計劃）。

二、 五大高頻 State 狀態深度解析

1. Sleep — 連接空閒，資源未釋放

成因：

• 應用使用連接池，執行完查詢未 close() 歸還池中
• 長事務提交前的思考時間
• 網絡傳輸大結果集時的等待

判斷標準：連接池配置合理時，Sleep 數量應穩定在 pool_size * 0.2 以內。大量 Sleep + Time 增長 → 連接泄漏或池配置過大。

處置：

1. 檢查應用代碼：確保 try-with-resources / finally close()
2. 調整連接池：maxIdle、minIdle、maxWait 根據 QPS 科學設定
3. MySQL 端：wait_timeout=28800（8h），interactive_timeout 區分交互/非交互
4. 定時清理：KILL 超過 wait_timeout 的 Sleep 進程（慎用，可能誤傷）

2. Locked — 鎖等待，寫寫/寫讀衝突

成因：

• MyISAM 表鎖：長時間 SELECT 阻塞 INSERT/UPDATE
• InnoDB 行鎖：未提交事務持有鎖、死鎖等待
• MDL 鎖：DDL 與 DML 並發（ALTER TABLE 阻塞增刪改查）

處置：

• 遷移 MyISAM → InnoDB（行鎖粒度細、支持 MVCC）
• 優化長事務：拆分批量操作、縮短事務範圍
• DDL 變更：使用 pt-online-schema-change / gh-ost 在線變更
• 監控：SHOW ENGINE INNODB STATUS 死鎖日志、performance_schema.data_locks

3. Copy to tmp table — 臨時表物化，I/O 殺手

觸發場景：

• GROUP BY / DISTINCT 無索引覆蓋
• ORDER BY 與 GROUP BY 列不一致
• UNION / 派生表 / CTE 物化
• JOIN 查詢驅動表無索引、需構建臨時表

影響：磁盤 I/O 飆升（Created_tmp_disk_tables 計數器增長）、並發下鎖競爭加劇。

優化：

1. 增加覆蓋索引：INDEX (group_col, order_col, select_col)
2. 調整 tmp_table_size/max_heap_table_size（默認 16MB），盡量在內存完成
3. SQL 改寫：拆分複雜查詢、避免 SELECT *、減少關聯表數量
4. 緊急熔斷：KILL 長時間佔用臨時表的查詢

4. Sending data — 並非「發送數據」，實為「讀取數據」

本質：存儲引擎層面讀取數據頁、組裝結果集的過程。包含：

• 索引範圍掃描 / 全表掃描讀取數據頁
• 回表查詢聚簇索引
• 結果集在內存中組裝、排序

高頻原因：

• 結果集過大（缺乏 LIMIT/分頁優化）
• 索引失效導致全表掃描、大量回表
• 磁盤碎片化、IOPS 瓶頸

排查：

1. EXPLAIN 確認 type=ALL / rows 巨大 / Using where 無 Using index
2. 檢查 Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率
3. 優化索引、增加 LIMIT、分頁優化（延遲關聯/游標分頁）

5. Storing result to query cache — 查詢緩存寫入開銷

現象：頻繁出現且佔比高（即使耗時極短）。

根因：Query Cache 碎片化嚴重、鎖競爭（全局互斥鎖）、失效頻繁（表任意修改導致相關緩存全部失效）。

處置：

• FLUSH QUERY CACHE 即時碎片整理
• 調整 query_cache_size（建議 ≤ 512MB，大則鎖競爭劇增）
• MySQL 5.7+ 建議關閉：query_cache_type=0、query_cache_size=0，改用 Redis 等外部緩存
• MySQL 8.0 已移除 Query Cache

三、 實戰排查儀表盤

-- 1. 當前活躍查詢（排除 Sleep）
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

-- 2. 長事務檢測（>60s）
SELECT * FROM information_schema.INNODB_TRX 
WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

-- 3. 鎖等待鏈
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;

-- 4. 臨時表監控
SHOW GLOBAL STATUS LIKE 'Created_tmp%';

四、 總結

State 列是 MySQL 內部執行流水線的「實時顯微鏡」。掌握 Sleep（連接管理）、Locked（鎖設計）、Copy to tmp table（索引覆蓋）、Sending data（訪問路徑）、Query Cache（架構選型） 五大狀態的成因與對策，配合 performance_schema、sys schema 現代化觀測體系，可將故障 MTTR（平均修復時間）壓縮至分鐘級。