MySQL CPU 占用過高時，排查具體占用資源的表需結合系統監控、數據庫分析工具和 SQL 診斷命令。

---

🔍 ?一、快速定位問題根源?

1. ?確認 MySQL 進程占用 CPU?

   • 使用 top 或 htop 命令查看系統進程，確認是否為 mysqld 進程導致 CPU 飆升。
   • 若 MySQL 進程持續占用 90% 以上 CPU，需深入分析數據庫內部操作。

2. ?區分負載類型：QPS 激增 vs. 慢查詢?

   • ?QPS 激增?：對比 CPU 曲線與 QPS（每秒查詢量）曲線是否同步波動。若同步，說明高并發導致 CPU 壓力。
     • 計算 QPS：

       SHOW GLOBAL STATUS LIKE 'Questions';  -- 獲取總查詢量
       SHOW GLOBAL STATUS LIKE 'Uptime';     -- 獲取運行時間（秒）
       -- QPS = Questions / Uptime

   • ?慢查詢為主?：若 CPU 飆高而 QPS 未明顯上升，大概率是慢 SQL 或鎖競爭導致。

---

?? ?二、定位高資源消耗的表?

?方法 1：實時分析活躍線程?

通過 SHOW FULL PROCESSLIST 或系統表查詢當前執行的 SQL 及操作的表：

-- 查看所有活躍線程（非 Sleep 狀態）
SELECT * FROM information_schema.PROCESSLIST 
WHERE COMMAND != 'Sleep' AND TIME > 10  -- 篩選執行時間>10秒的線程
ORDER BY TIME DESC;

• ?關鍵字段?：
  • STATE：若為 Sending data、Sorting result 或 Creating tmp table，表示可能涉及全表掃描或復雜計算。
  • INFO：顯示正在執行的 SQL，從中提取操作的表名。

?方法 2：分析慢查詢日志?

1. ?開啟慢查詢日志?：

   SET GLOBAL slow_query_log = 'ON';
   SET GLOBAL long_query_time = 1;  -- 記錄超過1秒的查詢
   SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

2. ?使用工具分析日志?：
   • ?**pt-query-digest（Percona Toolkit）?**?：

     pt-query-digest /var/log/mysql/slow.log --limit 10  -- 輸出消耗最高的前10個查詢

   • ?輸出結果關注點?：
     • Table：被頻繁操作的表名。
     • Rows_examined：掃描行數過大（如百萬級）的表。
     • Query_time：單次執行耗時長的 SQL。

?方法 3：通過 Performance Schema 定位表級操作?

-- 查看消耗 CPU 最高的 SQL 及其操作的表
SELECT DIGEST_TEXT AS query,SCHEMA_NAME AS db,COUNT_STAR AS exec_count,SUM_TIMER_WAIT/1e9 AS total_time_sec,SUM_ROWS_EXAMINED AS rows_examined
FROM performance_schema.events_statements_summary_by_digest 
ORDER BY total_time_sec DESC 
LIMIT 10;

• ?關鍵信息?：
  • query 字段可直接看到 SQL 操作的表（如 SELECT * FROM orders）。
  • exec_count 該SQL模式被執行的次數
  • ?total_time_sec?該SQL模式所有執行的總耗時（單位：皮秒，除以1e9轉換為秒；例如：SUM_TIMER_WAIT=1234567890000 → 1.23456789秒；識別最耗時的SQL模式

  • rows_examined?該SQL模式所有執行中檢查的總行數；?例如?：1000000（表示這個SQL模式總共掃描了100萬行）?，用于識別全表掃描或索引效率低下的查詢

?方法 4：檢查表大小與索引狀態?

1. ?查詢表空間占用?：

   SELECT TABLE_NAME,ROUND((DATA_LENGTH + INDEX_LENGTH)/1024/1024, 2) AS size_mb,TABLE_ROWS
   FROM information_schema.TABLES 
   WHERE TABLE_SCHEMA = 'your_database'
   ORDER BY size_mb DESC;

   • ?大表（GB 級）?? 更容易因全表掃描導致 CPU 飆升。
   • ?小表但高掃描頻次?：可能索引缺失或統計信息過期。

2. ?檢查索引有效性?：

   -- 查看表的索引情況
   SHOW INDEX FROM your_table;

   • 若 Cardinality（基數）遠小于實際行數，說明索引可能失效，需更新統計信息：

     ANALYZE TABLE your_table;

---

🛠? ?三、針對性優化措施?

1. ?緊急處理?：

   • 終止高消耗線程：

     KILL <thread_id>;  -- 從 PROCESSLIST 獲取 thread_id

2. ?索引優化?：

   • 為高頻查詢的 WHERE、JOIN、ORDER BY 字段添加索引。
   • 避免索引失效：
     • 禁止對索引列使用函數（如 WHERE DATE(create_time) = ...）。
     • 避免隱式類型轉換（如字符串字段用數字查詢）。

3. ?SQL 重寫?：

   • 拆分復雜查詢（如將子查詢改為 JOIN）。
   • 減少 SELECT *，僅返回必要字段。
   • 分頁查詢優化：用 WHERE id > last_id LIMIT n 替代 OFFSET。

4. ?配置調整?：

   • 增加臨時表大小，避免磁盤臨時表：

     tmp_table_size = 256M
     max_heap_table_size = 256M

   • 調整 InnoDB 緩沖池（通常設為物理內存的 70%）：

     innodb_buffer_pool_size = 8G

5. ?架構擴展?：

   • 讀寫分離：將查詢分流到只讀副本。
   • 分庫分表：對億級大表按業務拆分。

---

📊 ?排查工具推薦?

?工具類型?	?推薦工具?	?用途?
?系統監控?	top, htop, vmstat	定位進程及線程級 CPU 占用
?SQL 分析?	pt-query-digest, EXPLAIN	分析慢查詢及執行計劃
?實時診斷?	SHOW PROCESSLIST, sys.schema	查看活躍線程與資源消耗
?可視化監控?	Prometheus + Grafana, PMM	長期追蹤性能指標（QPS/CPU/鎖）

---

?? ?注意?

• ?鎖競爭問題?：若 SHOW PROCESSLIST 顯示大量線程狀態為 Waiting for table lock，需檢查長事務或死鎖（information_schema.INNODB_TRX）。
• ?外部因素?：備份任務、批量數據維護也可能導致 CPU 短暫飆高，需結合操作日志排查。