[[384119]]

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

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

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

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

在去年雙十一晚，我突然收到產(chǎn)品經(jīng)理的“催魂電話(huà)”并告知：整個(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)存泄露等問(wèn)題，但經(jīng)過(guò)排查發(fā)現(xiàn)代碼是正常。

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

jstack 線(xiàn)程堆棧

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

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

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

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

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

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

最后經(jīng)過(guò)耐心地對(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í)我自信不疑，就是字段類(lèi)型轉(zhuǎn)換導(dǎo)致的慢查詢(xún)，MySQL 不會(huì)自動(dòng)幫我們做字段值類(lèi)型轉(zhuǎn)換，定位出原因后，剩下只需要把字段的值改為字符串就可以了。

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

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

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

從上述圖可發(fā)現(xiàn)，用戶(hù)的請(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ì)釋放、銷(xiāo)毀。因此，當(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è)用戶(hù)發(fā)出請(qǐng)求、執(zhí)行邏輯時(shí)都需分配 JVM 內(nèi)存，當(dāng)前面的請(qǐng)求線(xiàn)程處于阻塞時(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)求過(guò)程如下：

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

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

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

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

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

如何優(yōu)化 SQL 編寫(xiě)的慢查詢(xún)

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

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

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

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

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

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

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

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

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

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

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

SQL 執(zhí)行流程

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

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

寫(xiě)在最后

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

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