MySQL定位CPU利用率过高的SQL方法

MySQL定位CPU利用率过高的SQL方法

(1).获取Mysql的服务器进程号,登陆mysql所在的Linux服务器，执行命令

[root@test ~]# top

在COMMAND列找到mysqld，并且%CPU使用率高的，比如数值超过80的，获取PID号。

------------------------------------------------------------------------------
PID  USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
3684 mysql     20   0   62.8g  53.3g   7060 S  86.6 42.4  13332:37 mysqld
------------------------------------------------------------------------------ 

3684为mysql进程ID，接下来再用它查询出占用CPU多的线程。

(2).查询进程中的线程
使用命令

[root@test ~]#top -H -p 3684

找到线程

------------------------------------------------------------------------------
PID USER      PR  NI    VIRT    RES    SHR S %CPU %MEM     TIME+ COMMAND                                                                               
4258 root      20   0 1443252 356688  11748 R 99.7  4.4   2:25.74 mysqld 
------------------------------------------------------------------------------

其中PID 4258为线程id号。

(3).根据线程ID去mysql查询出对应的SQL

select a.user,a.host,a.db,b.thread_os_id,b.thread_id,a.id processlist_id,a.command,a.time,a.state,a.info 
from information_schema.processlist a,performance_schema.threads b 
where a.id = b.processlist_id and b.thread_os_id=4258;

查询结果：

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| user | host                            | db   | thread_os_id | thread_id | processlist_id | command | time    | state             | info                                                                                                                          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| uts   |192.168.40.172:58025	|tdxdb|4258	|2109703	 |2109670	        |Query        | 1	       |updating	|DELETE FROM`141702004`WHERE`seq_id` BETWEEN 22450234331 AND 22450234331    |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

其中，info列显示的SQL就是占用CPU较大的SQL，针对其进行优化即可。