mysql的cpu使用率100%問題排查解決方案
更新時間：2026年04月07日 08:48:30 作者：劉大貓.
MySQL是一種常用的關系型數(shù)據(jù)庫管理系統(tǒng),但有時候你可能會發(fā)現(xiàn)MySQL服務器進程的CPU占用率達到100%,這篇文章主要介紹了mysql的cpu使用率100%問題排查解決的相關資料,需要的朋友可以參考下
一、背景
- java項目服務：xpt-windows-service
- mysql數(shù)據(jù)庫版本：8.0.17
項目接收中轉臺上報的業(yè)務數(shù)據(jù)，頻率大概是1秒4-5條，240/1分鐘，由于上報的比較頻繁會看到數(shù)據(jù)庫CPU占用率非常高，導致數(shù)據(jù)入庫卡頓現(xiàn)象。
因為我的是事后整理所有有的截圖不是當時的錯誤截圖，線上mysql服務器經常性出現(xiàn)cpu使用率100%的告警， 因此整理一下排查該問題的常規(guī)流程。
二、排查方案
首先不要重啟xpt-windows-service服務或者mysql服務，因為如果重啟了可能錯誤現(xiàn)象就不復現(xiàn)了。
2.1第一步：先排查資源管理器查看CPU、內存、磁盤空間、網(wǎng)絡
結論：按照當時的錯誤截圖CPU直接占100%，然后去資源管理器查看
結論：當時我的錯誤場景不是這塊的原因，所以當時的截圖都是正常的，沒看到任何異常數(shù)據(jù)，只能看到mysql的CPU占用100%。
2.2第二步：排查排查鎖爭用與事務
SHOW ENGINE INNODB STATUS;
===================================== 2026-01-09 16:52:02 0x4f0 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 34 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5897 srv_active, 0 srv_shutdown, 2421 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 65084 OS WAIT ARRAY INFO: signal count 68837 RW-shared spins 31956, rounds 35392, OS waits 3409 RW-excl spins 1611, rounds 48034, OS waits 3041 RW-sx spins 66, rounds 1799, OS waits 52 Spin rounds per wait: 1.11 RW-shared, 29.82 RW-excl, 27.26 RW-sx ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2026-01-09 15:40:50 0x2f34 *** (1) TRANSACTION: TRANSACTION 281952663838920, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 28 row lock(s) MySQL thread id 52, OS thread handle 15000, query id 806996727 localhost 127.0.0.1 root executing /* dynamic native SQL query */ INSERT INTO stac_site_day (SiteID, `Date`, BizType, BizTime, Count) SELECT r.site_id, rd.`date`, rd.biztype, SUM(BizTime) AS BizTime, SUM(Count) AS Count FROM stac_rpt rd INNER JOIN repeater r ON rd.RptSN = r.serial_no WHERE r.deleted = 0 AND DATE_FORMAT(rd.date, '%Y-%m-%d') = '2026-01-08' GROUP BY r.site_id, rd.`date`, rd.biztype *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 4 n bits 128 index PRIMARY of table `xpt_db`.`repeater` trx id 281952663838920 lock mode S waiting Record lock, heap no 56 PHYSICAL RECORD: n_fields 50; compact format; info bits 0 0: len 4; hex 8000001c; asc ;; 1: len 6; hex 0000004a007a; asc J z;; 2: len 7; hex 01000001a8038e; asc ;; 3: len 4; hex 8000000b; asc ;; 4: len 13; hex 31312d31205852413830205531; asc 11-1 XRA80 U1;; 5: len 11; hex 31302e3131352e352e3839; asc 10.115.5.89;; 6: len 10; hex 30363132524433363337; asc 0612RD3637;; 7: len 4; hex 80000001; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 800000a1; asc ;; 10: len 4; hex 80000000; asc ;; 11: len 4; hex 80000000; asc ;; 12: len 14; hex 3139322e3136382e31312e313031; asc 192.168.11.101;; 13: len 11; hex 31302e3131352e352e3631; asc 10.115.5.61;; 14: len 4; hex 8000c350; asc P;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 8000c350; asc P;; 17: len 7; hex 80000000000000; asc ;; 18: len 3; hex 800000; asc ;; 19: len 3; hex 800000; asc ;; 20: len 5; hex 8000000000; asc ;; 21: len 7; hex 80000000000000; asc ;; 22: len 3; hex 800000; asc ;; 23: len 3; hex 800000; asc ;; 24: len 5; hex 8000000000; asc ;; 25: len 6; hex 800000000000; asc ;; 26: len 4; hex 80000000; asc ;; 27: len 5; hex 5852413830; asc XRA80;; 28: len 28; hex 58524138302d30303030423030302d3030303044432d55312d302d42; asc XRA80-0000B000-0000DC-U1-0-B;; 29: len 11; hex 41312e302e30312e303034; asc A1.0.01.004;; 30: len 0; hex ; asc ;; 31: len 9; hex 343033353632373730; asc 403562770;; 32: len 9; hex 343134393235373330; asc 414925730;; 33: len 1; hex 32; asc 2;; 34: len 0; hex ; asc ;; 35: len 4; hex 80000004; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: len 6; hex 7075626c6963; asc public;; 40: len 6; hex 7075626c6963; asc public;; 41: len 1; hex 30; asc 0;; 42: len 1; hex 30; asc 0;; 43: len 0; hex ; asc ;; 44: len 1; hex 30; asc 0;; 45: len 0; hex ; asc ;; 46: len 0; hex ; asc ;; 47: len 1; hex 30; asc 0;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; *** (2) TRANSACTION: TRANSACTION 4849786, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1 MySQL thread id 55, OS thread handle 12084, query id 807009811 localhost 127.0.0.1 root updating /* dynamic native SQL query */ UPDATE REPEATER SET FIRMWAVE_NO = 'A3.0.20.011', CURRENT_RX_FREQUENCE = '428255000', CURRENT_TX_FREQUENCE = '438375000', CUR_MODE = '2', IPADDRESS = '10.115.5.61', REAL_ACCESS_CODE = 'public', MODEL_NAME = 'HR1060', MODEL_NO = 'HRA60-00000000-0000DC-U1-0-B' WHERE SERIAL_NO = '0612RD3617' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 37 page no 4 n bits 128 index PRIMARY of table `xpt_db`.`repeater` trx id 4849786 lock_mode X locks rec but not gap Record lock, heap no 56 PHYSICAL RECORD: n_fields 50; compact format; info bits 0 0: len 4; hex 8000001c; asc ;; 1: len 6; hex 0000004a007a; asc J z;; 2: len 7; hex 01000001a8038e; asc ;; 3: len 4; hex 8000000b; asc ;; 4: len 13; hex 31312d31205852413830205531; asc 11-1 XRA80 U1;; 5: len 11; hex 31302e3131352e352e3839; asc 10.115.5.89;; 6: len 10; hex 30363132524433363337; asc 0612RD3637;; 7: len 4; hex 80000001; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 800000a1; asc ;; 10: len 4; hex 80000000; asc ;; 11: len 4; hex 80000000; asc ;; 12: len 14; hex 3139322e3136382e31312e313031; asc 192.168.11.101;; 13: len 11; hex 31302e3131352e352e3631; asc 10.115.5.61;; 14: len 4; hex 8000c350; asc P;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 8000c350; asc P;; 17: len 7; hex 80000000000000; asc ;; 18: len 3; hex 800000; asc ;; 19: len 3; hex 800000; asc ;; 20: len 5; hex 8000000000; asc ;; 21: len 7; hex 80000000000000; asc ;; 22: len 3; hex 800000; asc ;; 23: len 3; hex 800000; asc ;; 24: len 5; hex 8000000000; asc ;; 25: len 6; hex 800000000000; asc ;; 26: len 4; hex 80000000; asc ;; 27: len 5; hex 5852413830; asc XRA80;; 28: len 28; hex 58524138302d30303030423030302d3030303044432d55312d302d42; asc XRA80-0000B000-0000DC-U1-0-B;; 29: len 11; hex 41312e302e30312e303034; asc A1.0.01.004;; 30: len 0; hex ; asc ;; 31: len 9; hex 343033353632373730; asc 403562770;; 32: len 9; hex 343134393235373330; asc 414925730;; 33: len 1; hex 32; asc 2;; 34: len 0; hex ; asc ;; 35: len 4; hex 80000004; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: len 6; hex 7075626c6963; asc public;; 40: len 6; hex 7075626c6963; asc public;; 41: len 1; hex 30; asc 0;; 42: len 1; hex 30; asc 0;; 43: len 0; hex ; asc ;; 44: len 1; hex 30; asc 0;; 45: len 0; hex ; asc ;; 46: len 0; hex ; asc ;; 47: len 1; hex 30; asc 0;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 37 page no 4 n bits 128 index PRIMARY of table `xpt_db`.`repeater` trx id 4849786 lock_mode X locks rec but not gap waiting Record lock, heap no 12 PHYSICAL RECORD: n_fields 50; compact format; info bits 0 0: len 4; hex 8000000b; asc ;; 1: len 6; hex 0000004a0075; asc J u;; 2: len 7; hex 0200000153119f; asc S ;; 3: len 4; hex 80000003; asc ;; 4: len 11; hex 332d322041363055314144; asc 3-2 A60U1AD;; 5: len 11; hex 31302e3131352e352e3631; asc 10.115.5.61;; 6: len 10; hex 30363132524433363137; asc 0612RD3617;; 7: len 4; hex 80000002; asc ;; 8: len 4; hex 80000000; asc ;; 9: len 4; hex 800000a3; asc ;; 10: len 4; hex 80000000; asc ;; 11: len 4; hex 80000000; asc ;; 12: len 14; hex 3139322e3136382e37302e313032; asc 192.168.70.102;; 13: len 7; hex 302e302e302e30; asc 0.0.0.0;; 14: len 4; hex 8000c350; asc P;; 15: len 4; hex 80000001; asc ;; 16: len 4; hex 8000c350; asc P;; 17: len 7; hex 80000000000000; asc ;; 18: len 3; hex 800000; asc ;; 19: len 3; hex 800000; asc ;; 20: len 5; hex 8000000000; asc ;; 21: len 7; hex 80000000000000; asc ;; 22: len 3; hex 800000; asc ;; 23: len 3; hex 800000; asc ;; 24: len 5; hex 8000000000; asc ;; 25: len 6; hex 800000000000; asc ;; 26: len 4; hex 80000000; asc ;; 27: len 6; hex 485231303630; asc HR1060;; 28: len 28; hex 48524136302d30303030303030302d3030303044432d55312d302d42; asc HRA60-00000000-0000DC-U1-0-B;; 29: len 11; hex 41332e302e32302e303131; asc A3.0.20.011;; 30: len 0; hex ; asc ;; 31: len 9; hex 343238323535303030; asc 428255000;; 32: len 9; hex 343338333735303030; asc 438375000;; 33: len 1; hex 32; asc 2;; 34: len 0; hex ; asc ;; 35: len 4; hex 80000002; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: len 6; hex 7075626c6963; asc public;; 40: len 6; hex 7075626c6963; asc public;; 41: len 1; hex 30; asc 0;; 42: len 1; hex 30; asc 0;; 43: len 0; hex ; asc ;; 44: len 1; hex 30; asc 0;; 45: len 0; hex ; asc ;; 46: len 0; hex ; asc ;; 47: len 1; hex 30; asc 0;; 48: len 0; hex ; asc ;; 49: len 0; hex ; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 4873681 Purge done for trx's n:o < 4873680 undo n:o < 0 state: running but idle History list length 21 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 281952663849000, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663847320, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663843120, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663840600, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663838080, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663841440, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663837240, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663846480, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663843960, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663842280, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663839760, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663838920, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663851520, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663850680, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663849840, not started 0 lock struct(s), heap size 1136, 0 row lock(s) ---TRANSACTION 281952663836400, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 1472 OS file reads, 256750 OS file writes, 79077 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 31.76 writes/s, 9.50 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 3 buffer(s) Hash table size 34679, node heap has 2 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 1 buffer(s) Hash table size 34679, node heap has 6 buffer(s) Hash table size 34679, node heap has 18 buffer(s) 595.95 hash searches/s, 659.60 non-hash searches/s --- LOG --- Log sequence number 6358686886 Log buffer assigned up to 6358686886 Log buffer completed up to 6358686886 Log written up to 6358686886 Log flushed up to 6358686886 Added dirty pages up to 6358686886 Pages flushed up to 6358620756 Last checkpoint at 6358620756 140068 log i/o's done, 16.74 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 137363456 Dictionary memory allocated 742479 Buffer pool size 8192 Free buffers 6530 Database pages 1629 Old database pages 581 Modified db pages 81 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3480, not young 150 0.00 youngs/s, 0.00 non-youngs/s Pages read 1373, created 262, written 108066 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 1629, unzip_LRU len: 0 I/O sum[654]:cur[4], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 3 read views open inside InnoDB Process ID=6696, Main thread ID=00000000000020A8 , state=sleeping Number of rows inserted 2117, updated 32276, deleted 1632, read 6527074908 0.24 inserts/s, 3.65 updates/s, 0.24 deletes/s, 1515209.85 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================
通過deepseek分析發(fā)現(xiàn)
然后我的解決方案就是：我們這個統(tǒng)計是每10分鐘執(zhí)行一次，我直接給它代碼注釋不讓它執(zhí)行，然后替換jar包重啟服務發(fā)現(xiàn)無效，數(shù)據(jù)庫CPUh還是占滿，說明問題也不是它。
2.3第三步：排查檢查MySQL配置連接數(shù)是否達到最大
-- 當前連接數(shù) SHOW STATUS LIKE 'Threads_connected';
-- 最大允許連接數(shù) SHOW VARIABLES LIKE 'max_connections';
結論：一看也不是這塊原因，排查到這一步說明數(shù)據(jù)庫層面沒啥問題，接下來重啟mysql服務驗證是否有問題。
2.4第四步：重啟mysql服務驗證是否有問題
結論：重啟后發(fā)現(xiàn)無任何改進，mysql的CPU還是占100%，說明不是它的問題，繼續(xù)排查。
2.5第五步：重啟xpt-windows-service服務
重啟后發(fā)現(xiàn)CPU占用率立刻降下去了，說明問題就出在這個服務上（即：說明是某個sql導致的）。到這一步說明有2個排查方向導致的：
- 排查方向1：程序在頻繁執(zhí)行某個sql，比如一分鐘上百次查詢，導致mysql占用率高。
- 排查方向2：某個業(yè)務sql查詢很慢導致，比如某個統(tǒng)計柱狀圖sql查詢相應時長10s+的，導致后面的數(shù)據(jù)處理慢堆積了。
2.6第六步：查看慢sql文件，看看那個sql執(zhí)行慢
查看慢日志是否開啟
SHOW VARIABLES LIKE 'slow_query_log';
查看慢日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';
C:\Program Files (x86)\xnms-install\mysql\bin\mysqld.exe, Version: 8.0.17 (MySQL Community Server - GPL). started with: TCP Port: 0, Named Pipe: (null) Time Id Command Argument C:\Program Files (x86)\xnms-install\mysql\bin\mysqld.exe, Version: 8.0.17 (MySQL Community Server - GPL). started with: TCP Port: 13306, Named Pipe: (null) Time Id Command Argument # Time: 2026-01-01T01:20:18.547950Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 1907 # Query_time: 13.071278 Lock_time: 0.000818 Rows_sent: 0 Rows_examined: 166336 use xpt_db; SET timestamp=1767230405; /* dynamic native SQL query */ INSERT INTO stac_rpt (rptsn, date, timesolt, biztype, biztime, count, h0001biztime, h0001bizcount, h0102biztime, h0102bizcount, h0203biztime, h0203bizcount, h0304biztime, h0304bizcount, h0405biztime, h0405bizcount, h0506biztime, h0506bizcount, h0607biztime, h0607bizcount, h0708biztime, h0708bizcount, h0809biztime, h0809bizcount, h0910biztime, h0910bizcount, h1011biztime, h1011bizcount, h1112biztime, h1112bizcount, h1213biztime, h1213bizcount, h1314biztime, h1314bizcount, h1415biztime, h1415bizcount, h1516biztime, h1516bizcount, h1617biztime, h1617bizcount, h1718biztime, h1718bizcount, h1819biztime, h1819bizcount, h1920biztime, h1920bizcount, h2021biztime, h2021bizcount, h2122biztime, h2122bizcount, h2223biztime, h2223bizcount, h2324biztime, h2324bizcount) SELECT serial_no AS RptSN, DATE_FORMAT(starttime, '%Y-%m-%d'), slot AS TimeSolt, type AS BizType, SUM(duration) AS biztime, COUNT(*) AS count, SUM(CASE WHEN HOUR(starttime) = 0 THEN duration ELSE 0 END) AS H0001BizTime, COUNT(CASE WHEN HOUR(starttime) = 0 THEN 1 ELSE NULL END) AS H0001BizCount, SUM(CASE WHEN HOUR(starttime) = 1 THEN duration ELSE 0 END) AS H0102BizTime, COUNT(CASE WHEN HOUR(starttime) = 1 THEN 1 ELSE NULL END) AS H0102BizCount, SUM(CASE WHEN HOUR(starttime) = 2 THEN duration ELSE 0 END) AS H0203BizTime, COUNT(CASE WHEN HOUR(starttime) = 2 THEN 1 ELSE NULL END) AS H0203BizCount, SUM(CASE WHEN HOUR(starttime) = 3 THEN duration ELSE 0 END) AS H0304BizTime, COUNT(CASE WHEN HOUR(starttime) = 3 THEN 1 ELSE NULL END) AS H0304BizCount, SUM(CASE WHEN HOUR(starttime) = 4 THEN duration ELSE 0 END) AS H0405BizTime, COUNT(CASE WHEN HOUR(starttime) = 4 THEN 1 ELSE NULL END) AS H0405BizCount, SUM(CASE WHEN HOUR(starttime) = 5 THEN duration ELSE 0 END) AS H0506BizTime, COUNT(CASE WHEN HOUR(starttime) = 5 THEN 1 ELSE NULL END) AS H0506BizCount, SUM(CASE WHEN HOUR(starttime) = 6 THEN duration ELSE 0 END) AS H0607BizTime, COUNT(CASE WHEN HOUR(starttime) = 6 THEN 1 ELSE NULL END) AS H0607BizCount, SUM(CASE WHEN HOUR(starttime) = 7 THEN duration ELSE 0 END) AS H0708BizTime, COUNT(CASE WHEN HOUR(starttime) = 7 THEN 1 ELSE NULL END) AS H0708BizCount, SUM(CASE WHEN HOUR(starttime) = 8 THEN duration ELSE 0 END) AS H0809BizTime, COUNT(CASE WHEN HOUR(starttime) = 8 THEN 1 ELSE NULL END) AS H0809BizCount, SUM(CASE WHEN HOUR(starttime) = 9 THEN duration ELSE 0 END) AS H0910BizTime, COUNT(CASE WHEN HOUR(starttime) = 9 THEN 1 ELSE NULL END) AS H0910BizCount, SUM(CASE WHEN HOUR(starttime) = 10 THEN duration ELSE 0 END) AS H1011BizTime, COUNT(CASE WHEN HOUR(starttime) = 10 THEN 1 ELSE NULL END) AS H1011BizCount, SUM(CASE WHEN HOUR(starttime) = 11 THEN duration ELSE 0 END) AS H1112BizTime, COUNT(CASE WHEN HOUR(starttime) = 11 THEN 1 ELSE NULL END) AS H1112BizCount, SUM(CASE WHEN HOUR(starttime) = 12 THEN duration ELSE 0 END) AS H1213BizTime, COUNT(CASE WHEN HOUR(starttime) = 12 THEN 1 ELSE NULL END) AS H1213BizCount, SUM(CASE WHEN HOUR(starttime) = 13 THEN duration ELSE 0 END) AS H1314BizTime, COUNT(CASE WHEN HOUR(starttime) = 13 THEN 1 ELSE NULL END) AS H1314BizCount, SUM(CASE WHEN HOUR(starttime) = 14 THEN duration ELSE 0 END) AS H1415BizTime, COUNT(CASE WHEN HOUR(starttime) = 14 THEN 1 ELSE NULL END) AS H1415BizCount, SUM(CASE WHEN HOUR(starttime) = 15 THEN duration ELSE 0 END) AS H1516BizTime, COUNT(CASE WHEN HOUR(starttime) = 15 THEN 1 ELSE NULL END) AS H1516BizCount, SUM(CASE WHEN HOUR(starttime) = 16 THEN duration ELSE 0 END) AS H1617BizTime, COUNT(CASE WHEN HOUR(starttime) = 16 THEN 1 ELSE NULL END) AS H1617BizCount, SUM(CASE WHEN HOUR(starttime) = 17 THEN duration ELSE 0 END) AS H1718BizTime, COUNT(CASE WHEN HOUR(starttime) = 17 THEN 1 ELSE NULL END) AS H1718BizCount, SUM(CASE WHEN HOUR(starttime) = 18 THEN duration ELSE 0 END) AS H1819BizTime, COUNT(CASE WHEN HOUR(starttime) = 18 THEN 1 ELSE NULL END) AS H1819BizCount, SUM(CASE WHEN HOUR(starttime) = 19 THEN duration ELSE 0 END) AS H1920BizTime, COUNT(CASE WHEN HOUR(starttime) = 19 THEN 1 ELSE NULL END) AS H1920BizCount, SUM(CASE WHEN HOUR(starttime) = 20 THEN duration ELSE 0 END) AS H2021BizTime, COUNT(CASE WHEN HOUR(starttime) = 20 THEN 1 ELSE NULL END) AS H2021BizCount, SUM(CASE WHEN HOUR(starttime) = 21 THEN duration ELSE 0 END) AS H2122BizTime, COUNT(CASE WHEN HOUR(starttime) = 21 THEN 1 ELSE NULL END) AS H2122BizCount, SUM(CASE WHEN HOUR(starttime) = 22 THEN duration ELSE 0 END) AS H2223BizTime, COUNT(CASE WHEN HOUR(starttime) = 22 THEN 1 ELSE NULL END) AS H2223BizCount, SUM(CASE WHEN HOUR(starttime) = 23 THEN duration ELSE 0 END) AS H2324BizTime, COUNT(CASE WHEN HOUR(starttime) = 23 THEN 1 ELSE NULL END) AS H2324BizCount FROM rptbiz202553 WHERE type != -1 AND DATE_FORMAT(starttime, '%Y-%m-%d') = '2025-12-31' GROUP BY serial_no, DATE_FORMAT(starttime, '%Y-%m-%d'), slot, type; # Time: 2026-01-02T10:20:23.331405Z # User@Host: root[root] @ localhost [127.0.0.1] Id: 3256 # Query_time: 10.106222 Lock_time: 0.011833 Rows_sent: 0 Rows_examined: 370067 SET timestamp=1767349213; /* dynamic native SQL query */ INSERT INTO stac_rpt (rptsn, date, timesolt, biztype, biztime, count, h0001biztime, h0001bizcount, h0102biztime, h0102bizcount, h0203biztime, h0203bizcount, h0304biztime, h0304bizcount, h0405biztime, h0405bizcount, h0506biztime, h0506bizcount, h0607biztime, h0607bizcount, h0708biztime, h0708bizcount, h0809biztime, h0809bizcount, h0910biztime, h0910bizcount, h1011biztime, h1011bizcount, h1112biztime, h1112bizcount, h1213biztime, h1213bizcount, h1314biztime, h1314bizcount, h1415biztime, h1415bizcount, h1516biztime, h1516bizcount, h1617biztime, h1617bizcount, h1718biztime, h1718bizcount, h1819biztime, h1819bizcount, h1920biztime, h1920bizcount, h2021biztime, h2021bizcount, h2122biztime, h2122bizcount, h2223biztime, h2223bizcount, h2324biztime, h2324bizcount) SELECT serial_no AS RptSN, DATE_FORMAT(starttime, '%Y-%m-%d'), slot AS TimeSolt, type AS BizType, SUM(duration) AS biztime, COUNT(*) AS count, SUM(CASE WHEN HOUR(starttime) = 0 THEN duration ELSE 0 END) AS H0001BizTime, COUNT(CASE WHEN HOUR(starttime) = 0 THEN 1 ELSE NULL END) AS H0001BizCount, SUM(CASE WHEN HOUR(starttime) = 1 THEN duration ELSE 0 END) AS H0102BizTime, COUNT(CASE WHEN HOUR(starttime) = 1 THEN 1 ELSE NULL END) AS H0102BizCount, SUM(CASE WHEN HOUR(starttime) = 2 THEN duration ELSE 0 END) AS H0203BizTime, COUNT(CASE WHEN HOUR(starttime) = 2 THEN 1 ELSE NULL END) AS H0203BizCount, SUM(CASE WHEN HOUR(starttime) = 3 THEN duration ELSE 0 END) AS H0304BizTime, COUNT(CASE WHEN HOUR(starttime) = 3 THEN 1 ELSE NULL END) AS H0304BizCount, SUM(CASE WHEN HOUR(starttime) = 4 THEN duration ELSE 0 END) AS H0405BizTime, COUNT(CASE WHEN HOUR(starttime) = 4 THEN 1 ELSE NULL END) AS H0405BizCount, SUM(CASE WHEN HOUR(starttime) = 5 THEN duration ELSE 0 END) AS H0506BizTime, COUNT(CASE WHEN HOUR(starttime) = 5 THEN 1 ELSE NULL END) AS H0506BizCount, SUM(CASE WHEN HOUR(starttime) = 6 THEN duration ELSE 0 END) AS H0607BizTime, COUNT(CASE WHEN HOUR(starttime) = 6 THEN 1 ELSE NULL END) AS H0607BizCount, SUM(CASE WHEN HOUR(starttime) = 7 THEN duration ELSE 0 END) AS H0708BizTime, COUNT(CASE WHEN HOUR(starttime) = 7 THEN 1 ELSE NULL END) AS H0708BizCount, SUM(CASE WHEN HOUR(starttime) = 8 THEN duration ELSE 0 END) AS H0809BizTime, COUNT(CASE WHEN HOUR(starttime) = 8 THEN 1 ELSE NULL END) AS H0809BizCount, SUM(CASE WHEN HOUR(starttime) = 9 THEN duration ELSE 0 END) AS H0910BizTime, COUNT(CASE WHEN HOUR(starttime) = 9 THEN 1 ELSE NULL END) AS H0910BizCount, SUM(CASE WHEN HOUR(starttime) = 10 THEN duration ELSE 0 END) AS H1011BizTime, COUNT(CASE WHEN HOUR(starttime) = 10 THEN 1 ELSE NULL END) AS H1011BizCount, SUM(CASE WHEN HOUR(starttime) = 11 THEN duration ELSE 0 END) AS H1112BizTime, COUNT(CASE WHEN HOUR(starttime) = 11 THEN 1 ELSE NULL END) AS H1112BizCount, SUM(CASE WHEN HOUR(starttime) = 12 THEN duration ELSE 0 END) AS H1213BizTime, COUNT(CASE WHEN HOUR(starttime) = 12 THEN 1 ELSE NULL END) AS H1213BizCount, SUM(CASE WHEN HOUR(starttime) = 13 THEN duration ELSE 0 END) AS H1314BizTime, COUNT(CASE WHEN HOUR(starttime) = 13 THEN 1 ELSE NULL END) AS H1314BizCount, SUM(CASE WHEN HOUR(starttime) = 14 THEN duration ELSE 0 END) AS H1415BizTime, COUNT(CASE WHEN HOUR(starttime) = 14 THEN 1 ELSE NULL END) AS H1415BizCount, SUM(CASE WHEN HOUR(starttime) = 15 THEN duration ELSE 0 END) AS H1516BizTime, COUNT(CASE WHEN HOUR(starttime) = 15 THEN 1 ELSE NULL END) AS H1516BizCount, SUM(CASE WHEN HOUR(starttime) = 16 THEN duration ELSE 0 END) AS H1617BizTime, COUNT(CASE WHEN HOUR(starttime) = 16 THEN 1 ELSE NULL END) AS H1617BizCount, SUM(CASE WHEN HOUR(starttime) = 17 THEN duration ELSE 0 END) AS H1718BizTime, COUNT(CASE WHEN HOUR(starttime) = 17 THEN 1 ELSE NULL END) AS H1718BizCount, SUM(CASE WHEN HOUR(starttime) = 18 THEN duration ELSE 0 END) AS H1819BizTime, COUNT(CASE WHEN HOUR(starttime) = 18 THEN 1 ELSE NULL END) AS H1819BizCount, SUM(CASE WHEN HOUR(starttime) = 19 THEN duration ELSE 0 END) AS H1920BizTime, COUNT(CASE WHEN HOUR(starttime) = 19 THEN 1 ELSE NULL END) AS H1920BizCount, SUM(CASE WHEN HOUR(starttime) = 20 THEN duration ELSE 0 END) AS H2021BizTime, COUNT(CASE WHEN HOUR(starttime) = 20 THEN 1 ELSE NULL END) AS H2021BizCount, SUM(CASE WHEN HOUR(starttime) = 21 THEN duration ELSE 0 END) AS H2122BizTime, COUNT(CASE WHEN HOUR(starttime) = 21 THEN 1 ELSE NULL END) AS H2122BizCount, SUM(CASE WHEN HOUR(starttime) = 22 THEN duration ELSE 0 END) AS H2223BizTime, COUNT(CASE WHEN HOUR(starttime) = 22 THEN 1 ELSE NULL END) AS H2223BizCount, SUM(CASE WHEN HOUR(starttime) = 23 THEN duration ELSE 0 END) AS H2324BizTime, COUNT(CASE WHEN HOUR(starttime) = 23 THEN 1 ELSE NULL END) AS H2324BizCount FROM rptbiz202601 WHERE type != -1 AND DATE_FORMAT(starttime, '%Y-%m-%d') = '2026-01-01' GROUP BY serial_no, DATE_FORMAT(starttime, '%Y-%m-%d'), slot, type;
結論：從圖片上能分析出，確實有個統(tǒng)計的sql執(zhí)行很慢，查詢13s才出結果，但是它執(zhí)行時間間隔很大，說明問題也不是它。
2.7第七步：實時分析MySQL活動，查看當前運行的SQL
該命令類似查詢當前時刻正在執(zhí)行的sql
SHOW PROCESSLIST
重復執(zhí)行了這個sql好幾次，發(fā)現(xiàn)這條紅框的每次都有它，感覺它執(zhí)行的很頻繁。繼續(xù)執(zhí)行其他命令，查看近半小時哪些sql執(zhí)行的頻率比較高。
SELECT
DIGEST_TEXT as 'SQL模板',
COUNT_STAR as '執(zhí)行次數(shù)',
SUM_TIMER_WAIT/1000000000 as '總耗時(秒)',
AVG_TIMER_WAIT/1000000000 as '平均耗時(秒)',
SUM_ROWS_EXAMINED as '總檢查行數(shù)',
SUM_ROWS_SENT as '總返回行數(shù)',
FIRST_SEEN as '首次出現(xiàn)時間',
LAST_SEEN as '最后執(zhí)行時間'
FROM performance_schema.events_statements_summary_by_digest
WHERE LAST_SEEN >= NOW() - INTERVAL 30 MINUTE
AND DIGEST_TEXT IS NOT NULL
AND DIGEST_TEXT NOT LIKE '%performance_schema%'
ORDER BY COUNT_STAR DESC
LIMIT 20;
結論：從這里就發(fā)現(xiàn)問題就在這了，它在近半個小時執(zhí)行太多次了，導致mysql服務一直卡住占用CPU，接下來就是看這個sql是哪塊服務在使用。
解決方案：這塊采用緩存查詢，優(yōu)先查詢緩存，只有緩存為空情況下再去查詢數(shù)據(jù)庫。
最后替換jar重啟服務后發(fā)現(xiàn)數(shù)據(jù)庫CPU使用率降到20%-30%左右了，就說明問題正確解決了。
總結
到此這篇關于mysql的cpu使用率100%問題排查解決方案的文章就介紹到這了,更多相關mysql cpu使用率100%內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
這篇文章主要介紹了MAC版修改MySQL初始密碼的方法,非常不錯，具有參考借鑒價值，需要的朋友可以參考下2017-02-02
mysql數(shù)據(jù)表的基本操作之表結構操作，字段操作實例分析
這篇文章主要介紹了mysql數(shù)據(jù)表的基本操作之表結構操作，字段操作,結合實例形式分析了mysql表結構操作，字段操作常見增刪改查實現(xiàn)技巧與操作注意事項,需要的朋友可以參考下2020-04-04
MySQL主從一直是面試常客,里面的知識點雖然基礎,但是能回答全的同學不多,如果我們遇到問如何解決MySQL主從復制延時問題呢,所以本文給大家講解了MySQL主從復制延時問題的解決方法,需要的朋友可以參考下2023-12-12
通俗地講表分區(qū)是將一大表,根據(jù)條件分割成若干個小表,mysql5.1開始支持數(shù)據(jù)表分區(qū)了,這篇文章主要給大家介紹了關于mysql表分區(qū)的使用與底層原理的相關資料,需要的朋友可以參考下2021-08-08
mysql 卡死 大部分線程長時間處于sending data的狀態(tài)
首先說明一下，這是個無頭的案子，雖然問題貌似解決了，不過到現(xiàn)在我也沒有答案，只是把這個問題拿出來晾晾2008-11-11
這篇文章主要介紹了MySQL索引的各種類型，幫助大家更好的理解和學習MySQL索引，感興趣的朋友可以了解下2020-09-09
這篇文章主要為大家詳細介紹了CentOS 6.5安裝mysql5.7教程，包括mysal舊版本的卸載、新版本的升級，具有一定的參考價值，感興趣的小伙伴們可以參考一下2017-04-04
這篇文章主要為大家詳細介紹了mysql?8.0.26?安裝配置方法圖文教程，文中安裝步驟介紹的非常詳細，具有一定的參考價值，感興趣的小伙伴們可以參考一下2022-04-04
這篇文章主要介紹了MySql中的存儲引擎和索引，文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-08-08
這篇文章主要介紹了MySQL之FIND_IN_SET()的用法及說明，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2023-01-01