mysql占用CPU超过100%解决过程

我不是女神ヾ 2021-11-09 23:02 507阅读 0赞

2017年12月2日上午，将学校新闻网2015年之前的45000多条记录迁移到了新网站的mysql数据库，新网站上有2015年1月1日之后的9000多条记录，数据量一下子增加了5倍。  
2017年12月3日晚上9点多，有领导和老师反映新闻网无法访问，立即登录服务器进行排查。

一、使用top命令看到的情况如下：

![850699-20190620105502927-357969819.jpg][]

可以看到服务器负载很高，，mysql CPU使用已达到接近400%（因为是四核，所以会有超过100%的情况）。

二、在服务器上执行mysql -u root -p之后，输入show full processlist; 可以看到正在执行的语句。

![850699-20190620105606147-1880255327.jpg][]

可以看到是下面的SQL语句执行耗费了较长时间。  
SELECT id,title,most\_top,view\_count,posttime FROM article  
where status=3 AND catalog\_id in (select catalog\_id from catalog where catalog\_id=17 or parent\_id=17)  
order by most\_top desc,posttime desc limit 0,8  
但是从数据库设计方面来说，该做的索引都已经做了，SQL语句似乎没有优化的空间。  
直接执行此条SQL，发现速度很慢，需要1-6秒的时间（跟mysql正在并发执行的查询有关，如果没有并发的，需要1秒多）。如果把排序依据改为一个，则查询时间可以缩短至0.01秒（most\_top）或者0.001秒（posttime）。

三、修改mysql配置文件中的pool/buffer等数值，重启mysql都没有作用。

四、通过EXPLAIN分析SQL语句  
EXPLAIN SELECT id,title,most\_top,view\_count,posttime FROM article  
where status=3 AND catalog\_id in (select catalog\_id from catalog where catalog\_id=17 or parent\_id=17)  
order by most\_top desc,posttime desc limit 0,8

![850699-20190620105621807-93515157.png][]

可以看到，主select对27928条记录使用filesort进行了排序，这是造成查询速度慢的原因。然后8个并发的查询使CPU专用很高。

五、优化  
首先是缩减查询范围  
SELECT id,title,most\_top,view\_count,posttime FROM article  
where status=3 AND catalog\_id in (select catalog\_id from catalog where catalog\_id=17 or parent\_id=17) and DATEDIFF(NOW(),posttime)<=90  
order by most\_top desc,posttime desc limit 0,8  
发现有一定效果，但效果不明显，原因是每条记录都要做一次DATEDIFF运算。后改为  
SELECT id,title,most\_top,view\_count,posttime FROM article  
where status=3 AND catalog\_id in (select catalog\_id from catalog where catalog\_id=17 or parent\_id=17) and postime>='2017-09-05'  
order by most\_top desc,posttime desc limit 0,8  
查询速度大幅提高。在PHP中，日期阈值通过计算得到  
$d = date("Y-m-d", strtotime('-90 day'));  
$sql = "  
SELECT id,title,most\_top,view\_count,posttime FROM article  
where status=3 AND catalog\_id in (select catalog\_id from catalog where catalog\_id=17 or parent\_id=17) and postime>='$d'  
order by most\_top desc,posttime desc limit 0,8  
"

六、效果  
查询时间大幅度缩短，CPU负载很轻

![850699-20190620105635982-56287361.jpg][]

![850699-20190620105649253-507699614.png][]  
\---------------------  
作者：jimshen  
来源：CSDN  
原文：https://blog.csdn.net/jimshen/article/details/78706538  
版权声明：本文为博主原创文章，转载请附上博文链接！

转载于:https://www.cnblogs.com/wcm19910616/p/11057446.html

[850699-20190620105502927-357969819.jpg]: /images/20211109/0171fb59aae0493e8ff3db11c337483e.png
[850699-20190620105606147-1880255327.jpg]: /images/20211109/03d6b71d9c604eb7b7bab27b2d8788b2.png
[850699-20190620105621807-93515157.png]: /images/20211109/7a5f1db1316d4474be3f74c4f59175df.png
[850699-20190620105635982-56287361.jpg]: /images/20211109/383fcc3b1f034a548bcdf15e4ca50b62.png
[850699-20190620105649253-507699614.png]: /images/20211109/cea9d0a2270548178ade9281dc32ba34.png