[[359201]]

SQL 語(yǔ)句執(zhí)行慢的原因是面試中經(jīng)常會(huì)被問(wèn)到的，對(duì)于服務(wù)端開發(fā)來(lái)說(shuō)也是必須要關(guān)注的問(wèn)題。

在生產(chǎn)環(huán)境中，SQL 執(zhí)行慢是很嚴(yán)重的事件。那么如何定位慢 SQL、慢的原因及如何防患于未然。接下來(lái)帶著這些問(wèn)題讓我們開啟本期之旅!

- 思維導(dǎo)圖 -

寫操作

作為后端開發(fā)，日常操作數(shù)據(jù)庫(kù)最常用的是寫操作和讀操作。讀操作我們下邊會(huì)講，這個(gè)分類里我們主要來(lái)看看寫操作時(shí)為什么會(huì)導(dǎo)致 SQL 變慢。

刷臟頁(yè)

臟頁(yè)的定義是這樣的：內(nèi)存數(shù)據(jù)頁(yè)和磁盤數(shù)據(jù)頁(yè)不一致時(shí)，那么稱這個(gè)內(nèi)存數(shù)據(jù)頁(yè)為臟頁(yè)。

那為什么會(huì)出現(xiàn)臟頁(yè)，刷臟頁(yè)又怎么會(huì)導(dǎo)致 SQL 變慢呢?那就需要我們來(lái)看看寫操作時(shí)的流程是什么樣的。

對(duì)于一條寫操作的 SQL 來(lái)說(shuō)，執(zhí)行的過(guò)程中涉及到寫日志，內(nèi)存及同步磁盤這幾種情況。

- Mysql 架構(gòu)圖 -

這里要提到一個(gè)日志文件，那就是 redo log，位于存儲(chǔ)引擎層，用來(lái)存儲(chǔ)物理日志。在寫操作的時(shí)候，存儲(chǔ)引擎(這里討論的是 Innodb)會(huì)將記錄寫入到 redo log 中，并更新緩存，這樣更新操作就算完成了。后續(xù)操作存儲(chǔ)引擎會(huì)在適當(dāng)?shù)臅r(shí)候把操作記錄同步到磁盤里。

看到這里你可能會(huì)有個(gè)疑問(wèn)，redo log 不是日志文件嗎，日志文件就存儲(chǔ)在磁盤上，那寫的時(shí)候豈不很慢嗎?

其實(shí)，寫redo log 的過(guò)程是順序?qū)懘疟P的，磁盤順序?qū)憸p少了尋道等時(shí)間，速度比隨機(jī)寫要快很多( 類似Kafka存儲(chǔ)原理)，因此寫 redo log 速度是很快的。

好了，讓我們回到開始時(shí)候的問(wèn)題，為什么會(huì)出現(xiàn)臟頁(yè)，并且臟頁(yè)為什么會(huì)使 SQL 變慢。你想想，redo log 大小是一定的，且是循環(huán)寫入的。在高并發(fā)場(chǎng)景下，redo log 很快被寫滿了，但是數(shù)據(jù)來(lái)不及同步到磁盤里，這時(shí)候就會(huì)產(chǎn)生臟頁(yè)，并且還會(huì)阻塞后續(xù)的寫入操作。SQL 執(zhí)行自然會(huì)變慢。

鎖

寫操作時(shí) SQL 慢的另一種情況是可能遇到了鎖，這個(gè)很容易理解。舉個(gè)例子，你和別人合租了一間屋子，只有一個(gè)衛(wèi)生間，你們倆同時(shí)都想去，但對(duì)方比你早了一丟丟。那么此時(shí)你只能等對(duì)方出來(lái)后才能進(jìn)去。

對(duì)應(yīng)到 Mysql 中，當(dāng)某一條 SQL 所要更改的行剛好被加了鎖，那么此時(shí)只有等鎖釋放了后才能進(jìn)行后續(xù)操作。

但是還有一種極端情況，你的室友一直占用著衛(wèi)生間，那么此時(shí)你該怎么整，總不能尿褲子吧，多丟人。對(duì)應(yīng)到Mysql 里就是遇到了死鎖或是鎖等待的情況。這時(shí)候該如何處理呢?

Mysql 中提供了查看當(dāng)前鎖情況的方式：

通過(guò)在命令行執(zhí)行圖中的語(yǔ)句，可以查看當(dāng)前運(yùn)行的事務(wù)情況，這里介紹幾個(gè)查詢結(jié)果中重要的參數(shù)：

當(dāng)前事務(wù)如果等待時(shí)間過(guò)長(zhǎng)或出現(xiàn)死鎖的情況，可以通過(guò) 「kill 線程ID」 的方式釋放當(dāng)前的鎖。

這里的線程 ID 指表中 trx_mysql_thread_id 參數(shù)。

讀操作

說(shuō)完了寫操作，讀操作大家可能相對(duì)來(lái)說(shuō)更熟悉一些。SQL 慢導(dǎo)致讀操作變慢的問(wèn)題在工作中是經(jīng)常會(huì)被涉及到的。

慢查詢

在講讀操作變慢的原因之前我們先來(lái)看看是如何定位慢 SQL 的。Mysql 中有一個(gè)叫作慢查詢?nèi)罩镜臇|西，它是用來(lái)記錄超過(guò)指定時(shí)間的 SQL 語(yǔ)句的。默認(rèn)情況下是關(guān)閉的，通過(guò)手動(dòng)配置才能開啟慢查詢?nèi)罩具M(jìn)行定位。

具體的配置方式是這樣的：

查看當(dāng)前慢查詢?nèi)罩镜拈_啟情況：

• 開啟慢查詢?nèi)罩?臨時(shí))：

注意這里只是臨時(shí)開啟了慢查詢?nèi)罩?，如?mysql 重啟后則會(huì)失效?？梢?my.cnf 中進(jìn)行配置使其永久生效。

存在原因

知道了如何查看執(zhí)行慢的 SQL 了，那么我們接著看讀操作時(shí)為什么會(huì)導(dǎo)致慢查詢。

(1)未命中索引

SQL 查詢慢的原因之一是可能未命中索引，關(guān)于使用索引為什么能使查詢變快以及使用時(shí)的注意事項(xiàng)，網(wǎng)上已經(jīng)很多了，這里就不多贅述了。

(2)臟頁(yè)問(wèn)題

另一種還是我們上邊所提到的刷臟頁(yè)情況，只不過(guò)和寫操作不同的是，是在讀時(shí)候進(jìn)行刷臟頁(yè)的。

是不是有點(diǎn)懵逼，別急，聽我娓娓道來(lái)：

為了避免每次在讀寫數(shù)據(jù)時(shí)訪問(wèn)磁盤增加 IO 開銷，Innodb 存儲(chǔ)引擎通過(guò)把相應(yīng)的數(shù)據(jù)頁(yè)和索引頁(yè)加載到內(nèi)存的緩沖池(buffer pool)中來(lái)提高讀寫速度。然后按照最近最少使用原則來(lái)保留緩沖池中的緩存數(shù)據(jù)。

那么當(dāng)要讀入的數(shù)據(jù)頁(yè)不在內(nèi)存中時(shí)，就需要到緩沖池中申請(qǐng)一個(gè)數(shù)據(jù)頁(yè)，但緩沖池中數(shù)據(jù)頁(yè)是一定的，當(dāng)數(shù)據(jù)頁(yè)達(dá)到上限時(shí)此時(shí)就需要把最久不使用的數(shù)據(jù)頁(yè)從內(nèi)存中淘汰掉。但如果淘汰的是臟頁(yè)呢，那么就需要把臟頁(yè)刷到磁盤里才能進(jìn)行復(fù)用。

你看，又回到了刷臟頁(yè)的情況，讀操作時(shí)變慢你也能理解了吧?

防患于未然

知道了原因，我們?nèi)绾蝸?lái)避免或緩解這種情況呢?

首先來(lái)看未命中索引的情況：

不知道大家有沒(méi)有使用 Mysql 中 explain 的習(xí)慣，反正我是每次都會(huì)用它來(lái)查看下當(dāng)前 SQL 命中索引的情況。避免其帶來(lái)一些未知的隱患。

這里簡(jiǎn)單介紹下其使用方式，通過(guò)在所執(zhí)行的 SQL 前加上 explain 就可以來(lái)分析當(dāng)前 SQL 的執(zhí)行計(jì)劃：

執(zhí)行后的結(jié)果對(duì)應(yīng)的字段概要描述如下圖所示：

這里需要重點(diǎn)關(guān)注以下幾個(gè)字段：

1、type

表示 MySQL 在表中找到所需行的方式。其中常用的類型有：ALL、index、range、 ref、eq_ref、const、system、NULL 這些類型從左到右，性能逐漸變好。

• ALL：Mysql 遍歷全表來(lái)找到匹配的行;
• index：與 ALL 區(qū)別為 index 類型只遍歷索引樹;
• range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行;
• ref：表示上述表的連接匹配條件，哪些列或常量被用于查找索引列上的值;
• eq_ref：類似ref，區(qū)別在于使用的是否為唯一索引。對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用 primary key 或者 unique key作為關(guān)聯(lián)條件;
• const、system：當(dāng) Mysql 對(duì)查詢某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類型訪問(wèn)。如將主鍵置于 where 列表中，Mysql 就能將該查詢轉(zhuǎn)換為一個(gè)常量，system 是 const類型的特例，當(dāng)查詢的表只有一行的情況下，使用system;
• NULL：Mysql 在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引，例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。

2、possible_keys

查詢時(shí)可能使用到的索引(但不一定會(huì)被使用，沒(méi)有任何索引時(shí)顯示為 NULL)。

3、key

實(shí)際使用到的索引。

4、rows

估算查找到對(duì)應(yīng)的記錄所需要的行數(shù)。

5、Extra

比較常見的是下面幾種：

• Useing index：表明使用了覆蓋索引，無(wú)需進(jìn)行回表;
• Using where：不用讀取表中所有信息，僅通過(guò)索引就可以獲取所需數(shù)據(jù)，這發(fā)生在對(duì)表的全部的請(qǐng)求列都是同一個(gè)索引的部分的時(shí)候，表示mysql服務(wù)器將在存儲(chǔ)引擎檢索行后再進(jìn)行過(guò)濾;
• Using temporary：表示MySQL需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見于排序和分組查詢，常見 group by，order by;
• Using filesort：當(dāng)Query中包含 order by 操作，而且無(wú)法利用索引完成的排序操作稱為“文件排序”。

對(duì)于刷臟頁(yè)的情況，我們需要控制臟頁(yè)的比例，不要讓它經(jīng)常接近 75%。同時(shí)還要控制 redo log 的寫盤速度，并且通過(guò)設(shè)置 innodb_io_capacity 參數(shù)告訴 InnoDB 你的磁盤能力。

總結(jié)

寫操作

• 當(dāng) redo log 寫滿時(shí)就會(huì)進(jìn)行刷臟頁(yè)，此時(shí)寫操作也會(huì)終止，那么 SQL 執(zhí)行自然就會(huì)變慢。
• 遇到所要修改的數(shù)據(jù)行或表加了鎖時(shí)，需要等待鎖釋放后才能進(jìn)行后續(xù)操作，SQL 執(zhí)行也會(huì)變慢。

讀操作

• 讀操作慢很常見的原因是未命中索引從而導(dǎo)致全表掃描，可以通過(guò) explain 方式對(duì) SQL 語(yǔ)句進(jìn)行分析。
• 另一種原因是在讀操作時(shí)，要讀入的數(shù)據(jù)頁(yè)不在內(nèi)存中，需要通過(guò)淘汰臟頁(yè)才能申請(qǐng)新的數(shù)據(jù)頁(yè)從而導(dǎo)致執(zhí)行變慢。