MySQL 5.7定位消耗CPU高的SQL

冷不防 2022-05-11 11:04 202阅读 0赞

MySQL 5.7.23  
1.先查看CPU飙高的线程，top -H -p <mysqld进程id>  
top - 15:09:26 up 135 days, 7:11, 2 users, LOAD average: 0.12, 0.08, 0.02  
Tasks: 34 total, 1 running, 33 sleeping, 0 stopped, 0 zombie  
Cpu(s): 12.7%us, 0.0%sy, 0.0%ni, 87.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st  
Mem: 32880780k total, 17577884k used, 15302896k free, 601816k buffers  
Swap: 4128760k total, 3916k used, 4124844k free, 7478260k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND  
23974 mysql 20 0 1658m 358m 12m R 99.9 1.1 0:05.52 mysqld  
12295 mysql 20 0 1658m 358m 12m S 0.3 1.1 0:02.44 mysqld  
12283 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.60 mysqld  
12284 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:00.00 mysqld  
12285 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.63 mysqld  
12286 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.24 mysqld  
12287 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.68 mysqld  
12288 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.77 mysqld  
12289 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.64 mysqld  
12290 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:01.80 mysqld  
12291 mysql 20 0 1658m 358m 12m S 0.0 1.1 0:02.37 mysqld

2.使用MySQL内部的表定位问题SQL。  
SELECT a.THREAD\_OS\_ID,b.user,b.host,b.db,b.command,b.time,b.state,[b.info][]  
FROM performance\_schema.threads a,information\_schema.processlist b  
WHERE [b.id][] = a.processlist\_id;  
THREAD\_OS\_ID USER HOST db command TIME state info

--------------------

20891  root    10.10.18.153:1499   jira    SLEEP       345                (NULL)                                                                    
       20892  root    10.10.18.153:1500   jira    SLEEP       345                (NULL)                                                                    
       23651  root    10.10.18.201:19387  (NULL)  SLEEP      2198                (NULL)                                                                    
       23650  root    10.10.18.201:21445  (NULL)  QUERY         0  executing     SELECT a.THREAD_OS_ID,b.user,b.host,b.db,b.command,b.time,b.state,b.info  
                                                                                 FROM performance_schema.threads a,information_schema.processlist b        
                                                                                  WHERE b.id = a.processlist_id LIMIT 0, 1000                              
       23970  root    10.10.18.201:21446  (NULL)  SLEEP       199                (NULL)                                                                    
       23974  root    10.10.18.201:21466  sys     QUERY        29  Sending DATA  SELECT a.* FROM test a,test b,test c ,test d                              
                                                                                 ORDER BY a.value                                                          
                                                                                  LIMIT 0, 1000

[b.info]: http://b.info
[b.id]: http://b.id