SQL慢的原因有哪些

本篇內(nèi)容主要講解“SQL慢的原因有哪些”，感興趣的朋友不妨來(lái)看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來(lái)帶大家學(xué)習(xí)“SQL慢的原因有哪些”吧!

- 思維導(dǎo)圖 -

寫(xiě)操作

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

刷臟頁(yè)

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

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

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

- Mysql 架構(gòu)圖 -

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

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

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

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

鎖

寫(xiě)操作時(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è)查詢(xún)結(jié)果中重要的參數(shù)：

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

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

讀操作

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

慢查詢(xún)

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

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

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

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

注意這里只是臨時(shí)開(kāi)啟了慢查詢(xún)?nèi)罩荆绻?mysql 重啟后則會(huì)失效。可以 my.cnf 中進(jìn)行配置使其永久生效。

存在原因

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

(1)未命中索引

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

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

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

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

為了避免每次在讀寫(xiě)數(shù)據(jù)時(shí)訪問(wèn)磁盤(pán)增加 IO 開(kāi)銷(xiāo)，Innodb 存儲(chǔ)引擎通過(guò)把相應(yīng)的數(shù)據(jù)頁(yè)和索引頁(yè)加載到內(nèi)存的緩沖池(buffer  pool)中來(lái)提高讀寫(xiě)速度。然后按照最近最少使用原則來(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è)刷到磁盤(pán)里才能進(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 在表中找到所需行的方式。其中常用的類(lèi)型有：ALL、index、range、 ref、eq_ref、const、system、NULL  這些類(lèi)型從左到右，性能逐漸變好。

• ALL：Mysql 遍歷全表來(lái)找到匹配的行;

• index：與 ALL 區(qū)別為 index 類(lèi)型只遍歷索引樹(shù);

• range：只檢索給定范圍的行，使用一個(gè)索引來(lái)選擇行;

• ref：表示上述表的連接匹配條件，哪些列或常量被用于查找索引列上的值;

• eq_ref：類(lèi)似ref，區(qū)別在于使用的是否為唯一索引。對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配，簡(jiǎn)單來(lái)說(shuō)，就是多表連接中使用 primary key 或者  unique key作為關(guān)聯(lián)條件;

• const、system：當(dāng) Mysql 對(duì)查詢(xún)某部分進(jìn)行優(yōu)化，并轉(zhuǎn)換為一個(gè)常量時(shí)，使用這些類(lèi)型訪問(wèn)。如將主鍵置于 where 列表中，Mysql  就能將該查詢(xún)轉(zhuǎn)換為一個(gè)常量，system 是 const類(lèi)型的特例，當(dāng)查詢(xún)的表只有一行的情況下，使用system;

• NULL：Mysql 在優(yōu)化過(guò)程中分解語(yǔ)句，執(zhí)行時(shí)甚至不用訪問(wèn)表或索引，例如從一個(gè)索引列里選取最小值可以通過(guò)單獨(dú)索引查找完成。

2、possible_keys

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

3、key

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

4、rows

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

5、Extra

比較常見(jiàn)的是下面幾種：

• 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é)果集，常見(jiàn)于排序和分組查詢(xún)，常見(jiàn) group by，order by;

• Using filesort：當(dāng)Query中包含 order by 操作，而且無(wú)法利用索引完成的排序操作稱(chēng)為“文件排序”。

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

總結(jié)

寫(xiě)操作

• 當(dāng) redo log 寫(xiě)滿(mǎn)時(shí)就會(huì)進(jìn)行刷臟頁(yè)，此時(shí)寫(xiě)操作也會(huì)終止，那么 SQL 執(zhí)行自然就會(huì)變慢。

• 遇到所要修改的數(shù)據(jù)行或表加了鎖時(shí)，需要等待鎖釋放后才能進(jìn)行后續(xù)操作，SQL 執(zhí)行也會(huì)變慢。

讀操作

• 讀操作慢很常見(jiàn)的原因是未命中索引從而導(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í)行變慢。

到此，相信大家對(duì)“SQL慢的原因有哪些”有了更深的了解，不妨來(lái)實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢(xún)，關(guān)注我們，繼續(xù)學(xué)習(xí)！