快捷導(dǎo)航

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

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

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

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

可以看到服務(wù)器負(fù)載很高，，mysql CPU使用已達(dá)到接近400%（因?yàn)槭撬暮?，所以?huì)有超過(guò)100%的情況）。

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

可以看到是下面的SQL語(yǔ)句執(zhí)行耗費(fèi)了較長(zhǎng)時(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ù)庫(kù)設(shè)計(jì)方面來(lái)說(shuō)，該做的索引都已經(jīng)做了，SQL語(yǔ)句似乎沒(méi)有優(yōu)化的空間。
直接執(zhí)行此條SQL，發(fā)現(xiàn)速度很慢，需要1-6秒的時(shí)間（跟mysql正在并發(fā)執(zhí)行的查詢(xún)有關(guān)，如果沒(méi)有并發(fā)的，需要1秒多）。如果把排序依據(jù)改為一個(gè)，則查詢(xún)時(shí)間可以縮短至0.01秒（most_top）或者0.001秒（posttime）。

三、修改mysql配置文件中的pool/buffer等數(shù)值，重啟mysql都沒(méi)有作用。

四、通過(guò)EXPLAIN分析SQL語(yǔ)句

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對(duì)27928條記錄使用filesort進(jìn)行了排序，這是造成查詢(xún)速度慢的原因。然后8個(gè)并發(fā)的查詢(xún)使CPU專(zhuān)用很高。

五、優(yōu)化

首先是縮減查詢(xú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 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

查詢(xún)速度大幅提高。在PHP中，日期閾值通過(guò)計(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
"

六、效果

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