MySQL 5.7定位消耗CPU高的SQL

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