快捷導(dǎo)航

mysql占用CPU超過100%的詳細(xì)解決過程

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

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

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

可以看到服務(wù)器負(fù)載很高，，mysql CPU使用已達(dá)到接近400%（因?yàn)槭撬暮耍詴谐^100%的情況）。

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

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

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

但是從數(shù)據(jù)庫設(shè)計(jì)方面來說，該做的索引都已經(jīng)做了，SQL語句似乎沒有優(yōu)化的空間。
直接執(zhí)行此條SQL，發(fā)現(xiàn)速度很慢，需要1-6秒的時(shí)間（跟mysql正在并發(fā)執(zhí)行的查詢有關(guān)，如果沒有并發(fā)的，需要1秒多）。如果把排序依據(jù)改為一個，則查詢時(shí)間可以縮短至0.01秒（most_top）或者0.001秒（posttime）。

三、修改mysql配置文件中的pool/buffer等數(shù)值，重啟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進(jìn)行了排序，這是造成查詢速度慢的原因。然后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ā)現(xiàn)有一定效果，但效果不明顯，原因是每條記錄都要做一次DATEDIFF運(yùn)算。后改為

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中，日期閾值通過計(jì)算得到

$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
"

六、效果

查詢時(shí)間大幅度縮短，CPU負(fù)載很輕