我們知道分析MySQL語(yǔ)句查詢(xún)性能的方法除了使用EXPLAIN 輸出執(zhí)行計(jì)劃，還可以讓MySQL記錄下查詢(xún)超過(guò)指定時(shí)間的語(yǔ)句，我們將超過(guò)指定時(shí)間的SQL語(yǔ)句查詢(xún)稱(chēng)為“慢查詢(xún)”。

一、 起因
研發(fā)反應(yīng)某臺(tái)數(shù)據(jù)庫(kù)僵死，后面的會(huì)話(huà)要么連接不上，要么要花費(fèi)大量的時(shí)間返回結(jié)果，哪怕是一個(gè)簡(jiǎn)單的查詢(xún)。

二、 處理
首先去監(jiān)控平臺(tái)查看服務(wù)器以及數(shù)據(jù)庫(kù)狀態(tài)，發(fā)現(xiàn)這臺(tái)數(shù)據(jù)庫(kù)有大量的慢查詢(xún)。繼續(xù)看服務(wù)器監(jiān)控，CPU 平均使用率較高，IO 讀寫(xiě)平均值正常。登錄到 MySQL，使用 SHOW PROCESSLIST 查看會(huì)話(huà)狀態(tài)，總數(shù)居然有 600+，這是很不正常的。查看慢查詢(xún)?nèi)罩荆l(fā)現(xiàn)出問(wèn)題的 SQL 主要集中在幾個(gè)，有 SUM、有 COUNT、有等值操作等等。這臺(tái) MySQL 服務(wù)器的 long_query_time 設(shè)置為 3秒，而一個(gè)簡(jiǎn)單的查詢(xún)卻要幾十秒，這顯然是有問(wèn)題的。寫(xiě)腳本試著 kill 掉相關(guān)的會(huì)話(huà)，發(fā)現(xiàn)于事無(wú)補(bǔ)，仍然有大量的連接進(jìn)來(lái)。此時(shí)使用 top 查看服務(wù)器狀態(tài)，mysqld 進(jìn)程占用內(nèi)存和 CPU 居高不下。

故障期間的慢查詢(xún)數(shù)，如圖：

CPU 平均使用率，如圖：

接著使用 SHOW FULL PROCESSLIST 查看完整狀態(tài)，在最上面居然發(fā)現(xiàn)幾條 SQL。這些 SQL 操作使用子查詢(xún)實(shí)現(xiàn)，TIME 列居然達(dá)到了 30000 秒，折算過(guò)來(lái)差不多 10 小時(shí)。EXPLAIN 這些語(yǔ)句，居然出現(xiàn)了 USING TEMPORY 和 USING FILESORT，可以看出這些語(yǔ)句是很糟糕的。于是跟開(kāi)發(fā)確認(rèn)，緊急把這些會(huì)話(huà) kill 掉。稍等片刻，會(huì)話(huà)數(shù)立馬降下來(lái)，只有 100+，top 查看 mysqld 進(jìn)程，內(nèi)存和 CPU 都呈現(xiàn)下降的趨勢(shì)。接著分析開(kāi)發(fā)說(shuō)上午 9 時(shí)寫(xiě)了這些 SQL，發(fā)現(xiàn)有問(wèn)題，注釋掉了。新的代碼雖然沒(méi)有此類(lèi) SQL，但之前建立的連接并不會(huì)釋放。解決問(wèn)題和出現(xiàn)問(wèn)題的時(shí)間差剛好可以和添加子查詢(xún)的時(shí)間對(duì)應(yīng)，就可以確認(rèn)子查詢(xún)是此次故障的罪魁禍?zhǔn)住?/p>

三、 總結(jié)
通過(guò)這個(gè)故障，總結(jié)如下幾點(diǎn)：

• MySQL 應(yīng)該盡量避免使用子查詢(xún)，即使使用，也要搞清楚大表和小表的關(guān)系；
• 出現(xiàn)這類(lèi)問(wèn)題的排查步驟：

第一，查看服務(wù)器監(jiān)控和 MySQL 監(jiān)控，分析服務(wù)器以及 MySQL 性能，找出異常；
第二，如果是慢查詢(xún)導(dǎo)致，查看慢查詢(xún)?nèi)罩?，找出出現(xiàn)問(wèn)題的 SQL，試著優(yōu)化，或者把結(jié)果緩存；
第三，分清主次，先解決大塊問(wèn)題，后解決細(xì)小問(wèn)題。 把大塊的異常解決，小問(wèn)題就迎刃而解了。比如本文中的例子，把耗費(fèi)時(shí)間長(zhǎng)的會(huì)話(huà) kill 掉后，后面的連接就正常了；
第四，總結(jié)分析。

• 高效的溝通會(huì)事半功倍；
• DBA 需要定期給出 Top N SQL（類(lèi) Oracle 的說(shuō)法），提供給開(kāi)發(fā)，并協(xié)助優(yōu)化；
• 查看監(jiān)控時(shí)，不管是服務(wù)器監(jiān)控還是 MySQL 監(jiān)控，需要做對(duì)比，比如和昨天甚至前天的同一時(shí)間對(duì)比，這會(huì)更加快速地定位問(wèn)題。

四、 技巧
最后，附上一個(gè)快速kill 掉 MySQL 會(huì)話(huà)的方法：

首先使用如下語(yǔ)句分析出有問(wèn)題的 SQL：

/usr/local/mysql/bin/mysql -uroot -p'XXX' \ -e "SHOW FULL PROCESSLIST;" | more

然后將 SHOW FULL PROCESSLIST 的結(jié)果保存到一個(gè)文件：

/usr/local/mysql/bin/mysql -uroot -p'XXX' \
-e "SHOW FULL PROCESSLIST;" | \
grep "XXX" | awk '{print $1}' > mysql_slow.txt

最后使用如下簡(jiǎn)單的 Shell 腳本 kill 掉相關(guān)會(huì)話(huà)：

SELECT concat('kill ',id,';')
FROM information_schema.processlist
WHERE info like 'XXX';

當(dāng)然也可以使用如下 SQL 拼接 kill 語(yǔ)句：

SELECT concat('kill ',id,';')
FROM information_schema.processlist
WHERE info like 'XXX';

本文對(duì)MySQL慢查詢(xún)導(dǎo)致故障的起因，處理方法，所需的技巧進(jìn)行了全面分析，希望可以讓大家更好的了解MySQL慢查詢(xún)，對(duì)大家的。

最新評(píng)論