mysql占用CPU超過100%的詳細解決過程

更新時間：2023年10月22日 15:03:56 作者：jimshen

前段時間我的一個網站經常打不開,通過檢查發(fā)現服務器cpu占用超過100%,通過top命令發(fā)現是mysql占用cpu特別高導致的,下面這篇文章主要給大家介紹了關于mysql占用CPU超過100%的詳細解決過程,需要的朋友可以參考下

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

可以看到服務器負載很高，，mysql CPU使用已達到接近400%（因為是四核，所以會有超過100%的情況）。

二、在服務器上執(zhí)行mysql -u root -p之后，輸入show full processlist; 可以看到正在執(zhí)行的語句。

可以看到是下面的SQL語句執(zhí)行耗費了較長時間。

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語句似乎沒有優(yōu)化的空間。
直接執(zhí)行此條SQL，發(fā)現速度很慢，需要1-6秒的時間（跟mysql正在并發(fā)執(zhí)行的查詢有關，如果沒有并發(fā)的，需要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

可以看到，主select對27928條記錄使用filesort進行了排序，這是造成查詢速度慢的原因。然后8個并發(fā)的查詢使CPU專用很高。

五、優(yōu)化

首先是縮減查詢范圍

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

發(fā)現有一定效果，但效果不明顯，原因是每條記錄都要做一次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負載很輕