2017年12月2日上午，將學校新聞網2015年之前的45000多條記錄遷移到了新網站的mysql數據庫，新網站上有2015年1月1日之后的9000多條記錄，數據量一下子增加了5倍。
2017年12月3日晚上9點多，有領導和老師反映新聞網無法訪問，立即登錄服務器進行排查。
一、使用top命令看到的情況如下：
可以看到服務器負載很高，，mysql CPU使用已達到接近400%（因為是四核，所以會有超過100%的情況）。
二、在服務器上執行mysql -u root -p之后，輸入show full processlist; 可以看到正在執行的語句。
可以看到是下面的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
可以看到，主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負載很輕
