查看具體查是哪個線程占用CPU最高
# 在Linux中使用top命令找到mysql進程ID
top - 22:11:02 up 279 days, 3:05, 0 users, load average: 5.01, 6.78, 6.42
Tasks: 3 total, 1 running, 2 sleeping, 0 stopped, 0 zombie
%Cpu(s): 76.4 us, 6.8 sy, 0.0 ni, 13.3 id, 1.5 wa, 0.0 hi, 2.0 si, 0.0 st
MiB Mem : 7821.6 total, 133.8 free, 3278.6 used, 4409.2 buff/cache
MiB Swap: 2048.0 total, 393.7 free, 1654.3 used. 4243.3 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1 mysql 20 0 5850828 2.8g 9560 S 676.7 36.9 218111:07 mysqld
29839 root 20 0 5740 2044 1580 S 0.0 0.0 0:00.01 bash
29845 root 20 0 9816 1848 1388 R 0.0 0.0 0:00.00 top
# 指定進程ID楚昭，找到占用CPU最高的線程ID
top -H -p 1
top - 22:11:36 up 279 days, 3:05, 0 users, load average: 5.41, 6.70, 6.41
Threads: 1102 total, 4 running, 1098 sleeping, 0 stopped, 0 zombie
%Cpu(s): 39.3 us, 2.3 sy, 0.0 ni, 56.2 id, 1.7 wa, 0.0 hi, 0.4 si, 0.0 st
MiB Mem : 7821.6 total, 126.3 free, 3272.0 used, 4423.3 buff/cache
MiB Swap: 2048.0 total, 392.4 free, 1655.6 used. 4250.5 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
17943 mysql 20 0 5847756 2.8g 9560 R 81.5 36.8 12:02.04 mysqld
10532 mysql 20 0 5847756 2.8g 9560 R 58.7 36.8 9:18.74 mysqld
31587 mysql 20 0 5847756 2.8g 9560 S 30.7 36.8 52:08.27 mysqld
14865 mysql 20 0 5847756 2.8g 9560 R 27.7 36.8 37:45.00 mysqld
22558 mysql 20 0 5847756 2.8g 9560 S 21.1 36.8 22:24.18 mysqld
6064 mysql 20 0 5847756 2.8g 9560 S 16.8 36.8 4:01.18 mysqld
根據(jù)操作系統(tǒng)線程ID找到對應(yīng)的mysql 線程ID
SELECT
*
FROM
`performance_schema`.threads T
WHERE
T.THREAD_OS_ID = 17943
根據(jù)返回結(jié)果中的PROCESLIST_INFO等信息浸间，來判斷其執(zhí)行的語句是否可以進行優(yōu)化
SELECT * FROM `performance_schema`.`processlist` WHERE `processlist`.ID IN
(SELECT
T.PROCESSLIST_ID
FROM
`performance_schema`.threads T
WHERE
T.THREAD_OS_ID = 7013)
注意：docker運行的mysql要在容器內(nèi)找到線程ID
# 如果容易內(nèi)沒有top命令与境，如果昌官方基于 debian的mysql鏡像，使用如下命令安裝top命令
apt-get update
apt-get install procps
# 必須執(zhí)行apt-get update郊楣，否則可能會報E: Unable to locate package procps的錯
補充
如果覺得先從分析線程入手比較麻煩憔恳，也可以直接通過查詢當(dāng)前正在執(zhí)行的查詢?nèi)胧?/p>
SELECT * FROM `performance_schema`.`processlist`
WHERE COMMAND != 'SLEEP' AND TIME > 1 ORDER BY TIME DESC
直接找出當(dāng)前下大執(zhí)行的查詢，按執(zhí)行時間倒充值净蚤，占用CPU高的查詢往往耗時也比較長
