mysqld占用 100%CPU的解决方案

说明

打开宝塔, 发现占用100%

查看宝塔的慢查询日志, 这个查询是订单列表中的模糊搜索, 当时不知道

排查

登录ssh, 使用top命令, 查看Mysqld占用100%, 进入Mysql控制台, 执行: SHOW PROCESSLIST 可以看到一些查询正在等待发送数据(Sending data)，这可能是导致MySQL卡住的原因

1850700	my_db	localhost:40516	my_db	Query	2984	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851013	my_db	localhost:43448	my_db	Query	1982	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851238	my_db	localhost:45256	my_db	Query	1404	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851459	my_db	localhost:47258	my_db	Query	758	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851570	my_db	localhost:48346	my_db	Query	397	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851631	my_db	localhost:48870	my_db	Query	243	Sending data	SELECT COUNT(*) AS tp_count FROM cc_order main LEFT JOIN ( SELECT og.order_id FROM cc_order_
1851647	my_db	119.126.117.00:58389		Sleep	149		
1851648	my_db	119.126.117.00:58391	my_db	Sleep	148		
1851649	my_db	119.126.117.00:58395	my_db	Sleep	142		
1851661	my_db	119.126.117.00:58397	my_db	Sleep	140		
1851686	my_db	localhost:49490	my_db	Sleep	3		
1851699	my_db	119.126.117.00:58446	my_db	Sleep	1		
1851700	my_db	119.126.117.00:58447	my_db	Query	0		SHOW PROCESSLIST

由于显示不全, 使用: SHOW FULL PROCESSLIST查询 查看其中一个完整的sql:

SELECT
	COUNT(*) AS tp_count
FROM
	cc_order main
LEFT JOIN (
	省略

解决

意识到是在订单列表中搜索了 DIY_ORDER 然后逐个kill 1850700 掉查询进程 最后CPU恢复正常