我嘞個(gè)去，慢查詢竟把系統(tǒng)搞崩了

作者： GoQeng 2021-03-01 08:05:09

通常情況下，SQL 慢查詢一般只會(huì)導(dǎo)致應(yīng)用服務(wù)響應(yīng)變慢，但在去年雙十一活動(dòng)時(shí)，我碰到了一個(gè)慢查詢把整個(gè)網(wǎng)站搞崩潰的問題。

本文轉(zhuǎn)載自微信公眾號(hào)「碼農(nóng)私房話」，作者GoQeng 。轉(zhuǎn)載本文請(qǐng)聯(lián)系碼農(nóng)私房話公眾號(hào)。

慢查詢請(qǐng)求流程

案發(fā)現(xiàn)場(chǎng)

在去年雙十一晚，我突然收到產(chǎn)品經(jīng)理的“催魂電話”并告知：整個(gè)網(wǎng)站的頁(yè)面一直在轉(zhuǎn)菊花，無(wú)法顯示數(shù)據(jù)，嚇得我立馬掏出電腦。

登錄服務(wù)器后竟發(fā)現(xiàn)服務(wù)內(nèi)存占用率接近 100%，CPU 長(zhǎng)期負(fù)荷高，我迅速地檢查是否代碼中出現(xiàn)死循環(huán)、大對(duì)象內(nèi)存泄露等問題，但經(jīng)過排查發(fā)現(xiàn)代碼是正常。

接著使用 jstatck pid 命令把線程堆棧信息 dump 出來(lái)，發(fā)現(xiàn)很多業(yè)務(wù)線程均處于 BLOCKED 狀態(tài)，同時(shí)也用 jstat -gcutil 觀察到 FULL GC 相當(dāng)頻繁。

jstack 線程堆棧

經(jīng)分析 dump 文件的內(nèi)容及代碼，發(fā)現(xiàn)是線程無(wú)法獲取數(shù)據(jù)庫(kù)連接，大量處于等待狀態(tài)。

隨后使用 show processlist 命令發(fā)現(xiàn)某個(gè) SQL 查詢耗時(shí)接近 10s 多，查詢的表數(shù)據(jù)量約在 1500W 左右。

我迫不及待地分析了慢 SQL select * from t_order where merchant_id= 1349865679 limit 0 10; 從表面看起來(lái)似乎用到了索引，可是為什么掃描到行還是這么多呢?

我就去看看表結(jié)構(gòu)，期望能從中找到點(diǎn)有價(jià)值的東西，通過 show index from t_order;后發(fā)現(xiàn)以下有用的信息：

從上述結(jié)果中看到 merchant_id 索引的離散程度還算大，它的 Cardinality 值接近于 PRIMARY 的值，說(shuō)明是比較正常的。

既然 merchant_id 索引沒問題，那么猜想就是使用姿勢(shì)不對(duì)的問題，我再通過 explain select * from t_order WHERE merchant_id= 1349865679 limit 0,10; 分析運(yùn)行的SQL，發(fā)現(xiàn)確實(shí)索引沒生效。

最后經(jīng)過耐心地對(duì)比代碼與 SQL 后，發(fā)現(xiàn) SQL 中 merchant_id 傳的是整形，而數(shù)據(jù)庫(kù)實(shí)際上是字符串，于是我更改字段值為字符串，再次執(zhí)行 explain select * from t_order WHERE merchant_id= '1349865679' limit 0,10; ，發(fā)現(xiàn)索引居然生效了。

此時(shí)我自信不疑，就是字段類型轉(zhuǎn)換導(dǎo)致的慢查詢，MySQL 不會(huì)自動(dòng)幫我們做字段值類型轉(zhuǎn)換，定位出原因后，剩下只需要把字段的值改為字符串就可以了。

慢查詢會(huì)造成系統(tǒng)奔潰?

1、TCP 連接、端口耗盡，無(wú)法響應(yīng)請(qǐng)求

首先我們先看看請(qǐng)求的流程：

從上述圖可發(fā)現(xiàn)，用戶的請(qǐng)求在服務(wù)器等待數(shù)秒后才向數(shù)據(jù)庫(kù)發(fā)起執(zhí)行命令，而當(dāng)時(shí)雙十一活動(dòng)火爆，請(qǐng)求量高，導(dǎo)致數(shù)據(jù)庫(kù)連接耗盡，大量請(qǐng)求阻塞在服務(wù)器上，同時(shí)系統(tǒng)不斷地創(chuàng)建 TCP 連接，這些連接直至整個(gè)請(qǐng)求結(jié)束后才會(huì)釋放、銷毀。因此，當(dāng)堆積大量請(qǐng)求無(wú)法及時(shí)處理時(shí)，則出現(xiàn)服務(wù)無(wú)法響應(yīng)，進(jìn)一步惡化為資源掛占。

2、對(duì)象在堆內(nèi)存無(wú)法回收，導(dǎo)致內(nèi)存不足

相信大家知道，每個(gè)用戶發(fā)出請(qǐng)求、執(zhí)行邏輯時(shí)都需分配 JVM 內(nèi)存，當(dāng)前面的請(qǐng)求線程處于阻塞時(shí)，后面又越來(lái)越多新請(qǐng)求不斷申請(qǐng)內(nèi)存分配，但舊請(qǐng)求中的對(duì)象無(wú)法回收并釋放內(nèi)存，最終導(dǎo)致內(nèi)存暴漲、系統(tǒng)響應(yīng)緩存，進(jìn)一步演化為系統(tǒng)奔潰，整個(gè)請(qǐng)求過程如下：

請(qǐng)求時(shí)線程申請(qǐng)資源流程

為什么會(huì)出現(xiàn)慢查詢?

在遇到慢查詢的情況時(shí)， SQL 編寫問題是最常見的因素，但實(shí)際上導(dǎo)致慢 SQL 有很多因素，甚至包括硬件和 MySQL 本身的 Bug 等，以下情況都有可能導(dǎo)致慢 SQL 的出現(xiàn)：

SQL 編寫問題
鎖競(jìng)爭(zhēng)激烈
業(yè)務(wù)實(shí)例相互干繞，爭(zhēng)用 IO/CPU 資源
服務(wù)器硬件配置
MySQL Bug

而本次的問題是屬于 SQL 編寫導(dǎo)致，其根本原因是索引使用不當(dāng)，查詢時(shí)進(jìn)行全表掃描。

如何優(yōu)化 SQL 編寫的慢查詢

針對(duì) SQL 編寫導(dǎo)致的慢查詢，正確地使用索引就能加快查詢速度，避免全表掃描。

SQL 全表掃描數(shù)據(jù)流程

然而在編寫 SQL 時(shí)需要注意與索引相關(guān)的一些規(guī)則：

字段類型轉(zhuǎn)換導(dǎo)致索引失效，如字符串類型的不用引號(hào)，數(shù)字類型的用引號(hào)等，這可能使索引失效導(dǎo)致全表掃描;
MySQL 不支持函數(shù)轉(zhuǎn)換，因此索引字段上不能加函數(shù)，否則這將用不到索引;
不在索引字段上做計(jì)算，對(duì)于需要計(jì)算的字段，可考慮將計(jì)算方法放在“=”后面;
like 模糊查詢，一般禁止使用 % 前導(dǎo)，防止索引失效，如 like %liew;
不使用 select *，應(yīng)按需加載需要的字段，查詢無(wú)用的列在數(shù)據(jù)傳輸和解析綁定過程中會(huì)增加網(wǎng)絡(luò)IO及CPU的開銷;
排序請(qǐng)盡量使用升序，因?yàn)榈剐蚨嗔宋募判虿僮鳎瑘?zhí)行效率變低，而 MySQL 8 開始支持降序索引解決排序性能問題;
盡量使用 union 代替 or，使用 or 可能會(huì)導(dǎo)致放棄使用索引而全表掃描;
最左匹配原則，索引是有順序的，查詢條件中缺失索引列之后的，其他條件都不會(huì)走索引，比如(a, b, c)索引，只使用b, c索引，就不會(huì)走索引;
在 order by / group by 子查詢的字段盡量建立索引，減少文件排序;

除了上述索引使用規(guī)則外，在編寫 SQL 時(shí)還需要特別注意一下幾點(diǎn)：

盡量規(guī)避大事務(wù)的 SQL，大事務(wù)的 SQL 會(huì)影響數(shù)據(jù)庫(kù)的并發(fā)性能及主從同步;
刪除表所有記錄請(qǐng)使用 truncate，而不用 delete，因?yàn)?truncate 執(zhí)行時(shí)不會(huì)生成 UNDO 信息;
在 InnoDB 引擎上請(qǐng)謹(jǐn)慎使用 select count(*) 語(yǔ)句，該統(tǒng)計(jì)可能會(huì)全表掃描數(shù)據(jù)，而 MyISAM內(nèi)置了一個(gè)計(jì)數(shù)器可直接獲取總數(shù);
慎用 oder by rand()，因?yàn)?rand() 放在 order by 子句中會(huì)被執(zhí)行多次，效率很低;
負(fù)向查詢一般都不會(huì)走索引，如 !=, <>, not in, not like等;
刪除不再使用或很少使用的索引，從而減少索引對(duì)更新操作的影響;

避免、發(fā)現(xiàn)慢查詢的措施

針對(duì) SQL 編寫導(dǎo)致的慢查詢，正確地使用索引能加快查詢速度，避免全表掃描。

在工作中，每個(gè)公司使用 MySQL 的版本可能都大不相同，總會(huì)存在一些莫名其妙、不確定的問題，因此為了驗(yàn)證索引的有效性，推薦把主要的 SQL 都通過 explain 命令查看一下執(zhí)行計(jì)劃，是否會(huì)用到索引。

explain select * from t_order WHERE merchant_id= '1349865679' limit 0 , 10;

然而 explain 工具分析的結(jié)果只是 MySQL 評(píng)估反饋的執(zhí)行計(jì)劃，最終還是依賴 MySQL 執(zhí)行引擎會(huì)根據(jù)一定算法落地：

SQL 執(zhí)行流程

因此有可能 explain 分析的結(jié)果顯示索引生效，但實(shí)際執(zhí)行 SQL 語(yǔ)句時(shí)卻是全表掃描。

這時(shí)候就需要開啟 MySQL 的慢查詢功能，再通過監(jiān)控工具 Zabbix 或Grafana輔助及時(shí)發(fā)現(xiàn)慢查詢 SQL 、連接數(shù)過多等問題并告警。

寫在最后

慢查詢的破壞力很大，輕則出現(xiàn)系統(tǒng)響應(yīng)緩慢，重則導(dǎo)致系統(tǒng)癱瘓、無(wú)法使用。

因此在日常開發(fā)中，我們需合理地設(shè)計(jì)、使用索引，避免出現(xiàn)慢查詢，同時(shí)利用工具實(shí)時(shí)監(jiān)控?cái)?shù)據(jù)庫(kù)的連接數(shù)、慢查詢語(yǔ)句等，并建立告警機(jī)制，以便能主動(dòng)地及時(shí)發(fā)現(xiàn)、定位問題，盡可能減少給客戶帶來(lái)的損失。

責(zé)任編輯：武曉燕來(lái)源：碼農(nóng)私房話

慢查詢 SQL