SQL 語句執行慢的原因是面試中經常會被問到的，對於服務端開發來說也是必須要關注的問題。
在生產環境中，SQL 執行慢是很嚴重的事件。那麼如何定位慢 SQL、慢的原因及如何防患於未然。接下來帶著這些問題讓我們開啟本期之旅！
圖注：思維導圖
寫操作
作為後端開發，日常運算元據庫最常用的是寫操作和讀操作。讀操作我們下邊會講，這個分類裡我們主要來看看寫操作時為什麼會導致 SQL 變慢。
刷髒頁
髒頁的定義是這樣的：記憶體資料頁和磁碟資料頁不一致時，那麼稱這個記憶體資料頁為髒頁。
那為什麼會出現髒頁，刷髒頁又怎麼會導致 SQL 變慢呢？那就需要我們來看看寫操作時的流程是什麼樣的。
對於一條寫操作的 SQL 來說，執行的過程中涉及到寫日誌，記憶體及同步磁碟這幾種情況。
圖注：Mysql 架構圖
這裡要提到一個日誌檔案，那就是 redo log，位於儲存引擎層，用來儲存物理日誌。在寫操作的時候，儲存引擎（這裡討論的是 Innodb）會將記錄寫入到 redo log 中，並更新快取，這樣更新操作就算完成了。後續操作儲存引擎會在適當的時候把操作記錄同步到磁碟裡。
看到這裡你可能會有個疑問，redo log 不是日誌檔案嗎，日誌檔案就儲存在磁碟上，那寫的時候豈不很慢嗎？
其實，寫redo log 的過程是順序寫磁碟的，磁碟順序寫減少了尋道等時間，速度比隨機寫要快很多（ 類似Kafka儲存原理），因此寫 redo log 速度是很快的。
好了，讓我們回到開始時候的問題，為什麼會出現髒頁，並且髒頁為什麼會使 SQL 變慢。你想想，redo log 大小是一定的，且是迴圈寫入的。在高併發場景下，redo log 很快被寫滿了，但是資料來不及同步到磁碟裡，這時候就會產生髒頁，並且還會阻塞後續的寫入操作。SQL 執行自然會變慢。
鎖
寫操作時 SQL 慢的另一種情況是可能遇到了鎖，這個很容易理解。舉個例子，你和別人合租了一間屋子，只有一個衛生間，你們倆同時都想去，但對方比你早了一丟丟。那麼此時你只能等對方出來後才能進去。
對應到 Mysql 中，當某一條 SQL 所要更改的行剛好被加了鎖，那麼此時只有等鎖釋放了後才能進行後續操作。
但是還有一種極端情況，你的室友一直佔用著衛生間，那麼此時你該怎麼整，總不能尿褲子吧，多丟人。對應到Mysql 裡就是遇到了死鎖或是鎖等待的情況。這時候該如何處理呢？
Mysql 中提供了檢視當前鎖情況的方式：
通過在命令列執行圖中的語句，可以檢視當前執行的事務情況，這裡介紹幾個查詢結果中重要的引數：
當前事務如果等待時間過長或出現死鎖的情況，可以通過 「kill 執行緒ID」 的方式釋放當前的鎖。
這裡的執行緒 ID 指表中 trx_mysql_thread_id 引數。
讀操作
說完了寫操作，讀操作大家可能相對來說更熟悉一些。SQL 慢導致讀操作變慢的問題在工作中是經常會被涉及到的。
慢查詢
在講讀操作變慢的原因之前我們先來看看是如何定位慢 SQL 的。Mysql 中有一個叫作慢查詢日誌的東西，它是用來記錄超過指定時間的 SQL 語句的。預設情況下是關閉的，通過手動配置才能開啟慢查詢日誌進行定位。
具體的配置方式是這樣的：
-
檢視當前慢查詢日誌的開啟情況：
-
開啟慢查詢日誌（臨時）：
注意這裡只是臨時開啟了慢查詢日誌，如果 mysql 重啟後則會失效。可以 my.cnf 中進行配置使其永久生效。
存在原因
知道了如何檢視執行慢的 SQL 了，那麼我們接著看讀操作時為什麼會導致慢查詢。
（1）未命中索引
SQL 查詢慢的原因之一是可能未命中索引，關於使用索引為什麼能使查詢變快以及使用時的注意事項，網上已經很多了，這裡就不多贅述了。
（2）髒頁問題
另一種還是我們上邊所提到的刷髒頁情況，只不過和寫操作不同的是，是在讀時候進行刷髒頁的。
是不是有點懵逼，別急，聽我娓娓道來：
為了避免每次在讀寫資料時訪問磁碟增加 IO 開銷，Innodb 儲存引擎通過把相應的資料頁和索引頁載入到記憶體的緩衝池（buffer pool）中來提高讀寫速度。然後按照最近最少使用原則來保留緩衝池中的快取資料。
那麼當要讀入的資料頁不在記憶體中時，就需要到緩衝池中申請一個資料頁，但緩衝池中資料頁是一定的，當資料頁達到上限時此時就需要把最久不使用的資料頁從記憶體中淘汰掉。但如果淘汰的是髒頁呢，那麼就需要把髒頁刷到磁碟裡才能進行復用。
你看，又回到了刷髒頁的情況，讀操作時變慢你也能理解了吧？
防患於未然
知道了原因，我們如何來避免或緩解這種情況呢？
首先來看未命中索引的情況：
不知道大家有沒有使用 Mysql 中 explain 的習慣，反正我是每次都會用它來檢視下當前 SQL 命中索引的情況。避免其帶來一些未知的隱患。
這裡簡單介紹下其使用方式，通過在所執行的 SQL 前加上 explain 就可以來分析當前 SQL 的執行計劃：
執行後的結果對應的欄位概要描述如下圖所示：
這裡需要重點關注以下幾個欄位：
1、type
表示 MySQL 在表中找到所需行的方式。其中常用的型別有：ALL、index、range、 ref、eq_ref、const、system、NULL 這些型別從左到右，效能逐漸變好。
-
ALL：Mysql 遍歷全表來找到匹配的行；
-
index：與 ALL 區別為 index 型別只遍歷索引樹；
-
range：只檢索給定範圍的行，使用一個索引來選擇行；
-
ref：表示上述表的連線匹配條件，哪些列或常量被用於查詢索引列上的值；
-
eq_ref：類似ref，區別在於使用的是否為唯一索引。對於每個索引鍵值，表中只有一條記錄匹配，簡單來說，就是多表連線中使用 primary key 或者 unique key作為關聯條件；
-
const、system：當 Mysql 對查詢某部分進行優化，並轉換為一個常量時，使用這些型別訪問。如將主鍵置於 where 列表中，Mysql 就能將該查詢轉換為一個常量，system 是 const型別的特例，當查詢的表只有一行的情況下，使用system；
-
NULL：Mysql 在優化過程中分解語句，執行時甚至不用訪問表或索引，例如從一個索引列裡選取最小值可以通過單獨索引查詢完成。
2、possible_keys
查詢時可能使用到的索引（但不一定會被使用，沒有任何索引時顯示為 NULL）。
3、key
實際使用到的索引。
4、rows
估算查詢到對應的記錄所需要的行數。
5、Extra
比較常見的是下面幾種：
-
Useing index：表明使用了覆蓋索引，無需進行回表；
-
Using where：不用讀取表中所有資訊，僅通過索引就可以獲取所需資料，這發生在對錶的全部的請求列都是同一個索引的部分的時候，表示mysql伺服器將在儲存引擎檢索行後再進行過濾；
-
Using temporary：表示MySQL需要使用臨時表來儲存結果集，常見於排序和分組查詢，常見 group by，order by；
-
Using filesort：當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“檔案排序”。
對於刷髒頁的情況，我們需要控制髒頁的比例，不要讓它經常接近 75%。同時還要控制 redo log 的寫盤速度，並且通過設定 innodb_io_capacity 引數告訴 InnoDB 你的磁碟能力。
總結
寫操作
-
當 redo log 寫滿時就會進行刷髒頁，此時寫操作也會終止，那麼 SQL 執行自然就會變慢。
-
遇到所要修改的資料行或表加了鎖時，需要等待鎖釋放後才能進行後續操作，SQL 執行也會變慢。
讀操作
- 讀操作慢很常見的原因是未命中索引從而導致全表掃描，可以通過 explain 方式對 SQL 語句進行分析。
- 另一種原因是在讀操作時，要讀入的資料頁不在記憶體中，需要通過淘汰髒頁才能申請新的資料頁從而導致執行變慢。
關於作者
作者：大家好，我是萊烏，BAT搬磚工一枚。從小公司進入大廠，一路走來收穫良多，想將這些經驗分享給有需要的人，因此建立了公眾號【IT界農民工】。定時更新，希望能幫助到你。同時，我給大家肝了一份Redis面經手冊，在我的公眾號內回覆【pdf】即可獲取，希望對你有所幫助。
