MySQL優化從執行計劃開始(explain超詳細)

Code綜藝圈發表於2021-03-17

前言

小夥伴一定遇到過這樣反饋：這頁面載入資料太慢啦，甚至有的超時了，使用者體驗極差，需要趕緊優化；

反饋等同於投訴啊，多有幾次，估計領導要找你談話啦。

於是不得不停下手裡頭的活，趕緊進行排查，最終可能是程式處理的問題、也可能是併發量大導致排隊問題、也可能是SQL查詢效能導致等；而在很多時候，SQL查詢緩慢是最直接拖慢系統的罪魁禍首，同樣是實現一個功能，有的小夥伴毫秒級呈現效果，有的卻要好幾秒，而調優需要的花費時間不容小覷，最終可能就體現到個人業務能力上和形象上：哇，真牛逼，分分鐘搞定；菜鳥，居然寫出這樣的SQL；

而對於SQL調優，搜尋引擎一查，72般絕技絕對夠秀，於是照著開始實操，運氣好一下就解決啦，運氣差的時候怎麼用都不行；所以更重要的是業務場景，要學會分析原因，最後才知道用什麼方式解決；而這個系列就來聊聊資料庫優化，聊聊原因，聊聊方法。

1. MySQL邏輯結構先知

關於MySQL的邏輯結構，將其理解為四層，就像專案分層一樣，每一層處理不同的業務邏輯，先看圖後說話：

上圖概述：

客戶端：這裡指連線MySQL各種形式，如.Net中使用的ADO連線、Java使用JDBC連線等；MySQL是客戶端和伺服器模式，前提先建立連線，才能傳輸資料，處理相關邏輯；
業務邏輯：在MySQL內部有很多模組組成，分別處理相關業務邏輯；

連線管理：負責連線認證、連線數判斷、連線池處理等業務邏輯處理；

查詢快取：當一個SQL進來時，如果開啟查詢快取功能，MySQL會優先去查詢快取中檢查是否有資料匹配，如果匹配上，就不會再去解析對應的SQL啦，但如果語句中有使用者自定義函式、儲存函式、使用者變數、臨時表、mysql庫中的系統表時，都不會走快取；對於查詢快取來說，在MySQL8.0已經去除，官方回應的是在一定場景上，查詢快取會導致效能上的瓶頸。

解析器：對於一個SQL語句，MySql根據語法規則需要對其進行解析，並生成一個內部能識別的解析樹；

優化器：負責對解析器得到的解析樹進行優化，MySQL會根據內部演算法找到一個MySQL認為最優的執行計劃，後續就按照這個執行計劃執行。所以後續我們分析的就是MySQL針對SQL語句選擇出來的最優執行計劃，結合業務，根據規則對SQL進行優化，從而讓SQL語句在MySQL內部達到真正的最優。

執行器：得到執行計劃之後，就會找到對應的儲存引擎，根據執行計劃給出的指令依次執行。
儲存引擎：資料的儲存和提取最後是靠儲存引擎；MySQL內部實現可插拔式的儲存引擎機制，不同的儲存引擎執行不同的邏輯；
物理檔案：資料儲存的最終位置，即磁碟上；協同儲存引擎對資料進行讀寫操作。

關於MySql的邏輯結構，以上只是簡單描述，業務邏輯層的功能模組遠不止上面提到的，小夥伴有興趣可以專門研究一下，這裡的目的就是為了體現SQL語句到伺服器上時經過的幾個關鍵步驟，方便後續優化的理解。

2. SQL語句的中關鍵字執行順序須知

在編寫一條查詢語句時，習慣性的從頭到尾開始敲出來，應該都是從select 開始吧，但似乎沒太注意它們真正的執行順序；既然要優化，肯定需要得知道一條SQL語句大概的執行流程，結合執行計劃，目的就更加清晰啦；上一張一看就明白的圖：

關鍵字簡述：

FROM：確定資料來源，即指定表；
JOIN...ON：確定關聯表和關聯條件；
WHERE：指定過濾條件，過濾出滿足條件的資料；
GROUP BY：按指定的欄位對過濾後的資料進行分組；
HAVING：對分組之後的資料指定過濾條件；
SELECT：查詢想要的欄位資料；
DISTINCT：針對查詢出來的資料進行去重；
ORDER BY：對去重後的資料指定欄位進行排序；
LIMIT：對去重後的資料限制獲取到的條數，即分頁；

好啦，大概瞭解MySQL的邏輯結構和SQL查詢關鍵字執行順序之後，接下來就可以好好說說執行計劃啦。

3. 好好說說執行計劃

通過上面的邏輯結構，當一個SQL傳送到MySQL執行時，需要經過內部優化器進行優化，而使用explain關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理SQL的，即SQL的執行計劃；根據explain提供的執行計劃資訊分析SQL語句，然後進行相關優化操作。接下來的示例演示用到五張表：USER(使用者表)、MENU(選單表)、ROLE(角色表)、USER_ROLE(使用者角色關係表)、ROLE_MENU(角色選單關係表)、ADDR(使用者地址表，這裡認為和使用者一一對應)、FRIEND(朋友表，一對多關係)，它們的關係這裡就不詳細說了吧，小夥伴肯定都明白，這是管控選單許可權的五張基礎表和兩個基礎資訊表；

演示用的版本是MySql5.5，各版本之間會有不同，所以小夥伴用的版本測試結果不一樣的時候，千萬別罵我渣哦；其實重要的是檢視的思路，整體是大同小異。(求原諒......)

通過explain會輸出如下資訊，很多小夥伴只關注紅框標註部分(即索引)，但其實是不夠的，接下來就一個一個好好說說。

id

這個id和我們們平時表結構設計的主鍵ID不太一樣，這裡的id代表了每一條SQL語句執行計劃中表載入的順序，分為三種情況：

id相同的時候：這時是從上到下依次執行；
```
EXPLAIN SELECT t.ID,t.USER_NAME,r.ROLE_NAME FROM USER t 
	JOIN USER_ROLE tr ON t.ID = tr.USER_ID
	JOIN ROLE r ON tr.ROLE_ID = r.ID
```
執行如下語句，得如下結果：

如上圖所示，id一樣，從上到下依次執行，所對應表載入順序為t->tr->r(這裡的表是別名)；

id不同的時候：當id不同的時，id越大的越先執行；
```
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1))
```
子查詢會導致id遞增，結果如下：

如上圖所示，id遞增啦，所對應表的載入順序為ur->rm->t(這裡的表是別名)；

id相同和不同同時存在時：id相同的認為是同一組，還是從上往下載入；不一樣的情況還是越大越優先執行
```
EXPLAIN SELECT t.ROLE_ID,m.ID,m.MENU_NAME,m.MENU_URL FROM 
	(SELECT ROLE_ID FROM USER_ROLE WHERE USER_ID=3) t,ROLE_MENU rm,MENU m
	WHERE t.ROLE_ID=rm.ROLE_ID
	AND rm.MENU_ID=m.ID
```
執行結果如下：

如上圖所示，id有一樣的，也有不同的，則對應表的載入順序為USER_ROLE->derived2 (衍生表)->rm->m；衍生表表名後面的2代表的是id，所以可以通過衍生表表名後面的id知道是哪一步產生的，即derived2衍生表是id為2的這一步產生的。
select_type

select_type 是表示每一步的查詢型別，方便分析人員很直接的看到當前步驟執行的是什麼查詢，有多種型別，見下圖：

1> SIMPLE：簡單的SELECT查詢，不包含子查詢或UNION的那種；
```
EXPLAIN SELECT * FROM USER;
```
輸出結果如下：

2> PRIMARY：查詢語句中包含其他子查詢或UNION操作，那最外層的SELECT就被標記為該型別；

如上圖所示，查詢中包含子查詢，最外層查詢被標記為PRIMARY；

3> SUBQUERY：在SELECT或WHERE中包含的子查詢會被標記為該型別；

見PRIMARY圖，當存在子查詢時，會將子查詢標記為SUBQUERY

4> MATERIALIZED：被物化的子查詢，即針對對應的子查詢將其物化為一個臨時表；
```
EXPLAIN SELECT t.ID,t.MENU_NAME,t.MENU_URL FROM MENU t
	WHERE t.ID IN (SELECT MENU_ID FROM ROLE_MENU rm 
		WHERE rm.ROLE_ID IN(SELECT ROLE_ID FROM USER_ROLE ur WHERE ur.USER_ID=1));
```
測試物化用的是MySQL8.0，和5.*版本有所不同，輸出結果如下：

如上圖所示，將子查詢物化為一個臨時表subquery2，這個功能是可以通過設定優化器對應的開關的。

5> DERIVED：在FROM之後的子查詢會被標記為該型別，同樣會把結果放在一個臨時表中；
```
EXPLAIN SELECT tm.MENU_NAME,rm.ROLE_ID FROM 
	(SELECT * FROM MENU WHERE ID >3 ) tm ,ROLE_MENU rm 
	WHERE tm.ID=rm.MENU_ID AND rm.ROLE_ID=1
```
輸出結果：

如圖所示，FROM後面跟的子查詢就被標記為DERIVED，對應步驟產生的衍生表為derived2。高版本好像對其進行了優化，8.0版本這種形式認為是簡單查詢。

6> UNION：UNION操作中，查詢中處於內層的SELECT；
```
EXPLAIN SELECT * FROM USER_ROLE T1 WHERE T1.USER_ID=1
UNION
SELECT * FROM USER_ROLE T2 WHERE T2.USER_ID=2
```
輸出結果如下：

如上圖所示，將第二個SELECT標註為UNION ，即對應載入的表為T2。

7> UNIOIN RESULT：UNION操作的結果，對應的id為空，代表的是一個結果集；

見UNIOIN圖，UNIOIN RESULT代表的是UNION之後的結果，對應id為空。
table

table代表對應步驟載入的是哪張表，中間會出現一些臨時表，比如subquery2、derived2等這種，最後的數字代表產生該表對應步驟的id。
type

代表訪問型別，MySQL內部將其分為多型別，常用的型別從好到差的順序展示如下：

system->const->eq_ef->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->ALL;

而在實際開發場景中，比較常見的幾種型別如下：const->eq_ref->ref->range->index->ALL(順序從好到差)，通常優化至少在range級別或以上，比如ref算是比較不錯的啦；

上面說到的從好到差指的是查詢效能。

1>const：表示通過索引一次就找到資料，用於比較primary key或者unique索引，很快就能找到對應的資料；

2>eq_ref：唯一性索引掃描，對於每個索引鍵，表中只有一條記錄與之匹配，常用於主鍵或唯一索引掃描；

3>ref：非唯一索引掃描，返回匹配的所有行，如建立一個朋友維護表，維護使用者對應的朋友，而在使用者ID建立非唯一索引；

4>range：使用一個索引檢索指定範圍的行，一般在where語句中會出現between、<、>、in等範圍查詢；

5>index：全索引掃描，只遍歷索引樹；

6>ALL：全表掃描，找到匹配行。與index比較，ALL需要掃描磁碟資料，index值需要遍歷索引樹。
possible_keys

顯示可能被用到的索引，但在實際查詢中不一定能用到；查詢涉及到欄位，如果存在索引，會被列出，但如果使用的是覆蓋索引，只會在key中列出；
key

實際使用到的索引，如果為NULL代表沒有使用到索引；這也是平時小夥伴判斷是否用上索引的關鍵。

key_len

key_len表示索引使用的位元組數，根據這個值可以判斷索引的使用情況，特別是在組合索引的時候，判斷該索引有多少部分被使用到，非常重要；key_len是根據表定義計算而得。這裡測試在USER表中對USER_NAME建立一個非唯一索引，如下：

這裡key_len是這麼計算的，前提是指定的字串集是utf8，可變長且允許為空，計算過程如下：

128(設定的可變長度)*3(utf8佔3位元組)+1(允許為空標識佔一個位元組)+2(長度資訊佔兩個位元組)=387；

key_len針對不同型別欄位的計算規則不一樣，這裡用USER(使用者表)簡單計算為例：

欄位	Key_len	說明
ID(int，不為空)	4	int為4個位元組，不為空
USER_NAME(varchar(128)，utf8，可為空)	128*3+1+2=387	可變為128，utf8每個佔3位元組，1個位元組標識可控，兩個位元組標識長度

不同型別佔用的位元組不一樣，字符集不一樣佔用的位元組也不一樣，允許為空的欄位需要1個位元組做標識，可變長度的欄位需要2個位元組標識長度。小夥伴照著這個思路就可以計算其他型別啦。

ref

顯示索引的哪些列被引用了，通常是對應欄位或const；
rows

根據表統計資訊和索引的使用情況，大概估算出找到所需記錄資料所掃描的資料行數；不是所需資料的行數。
Extra

這個欄位裡包含一些其他資訊，但也是優化SQL的重要參考，通常會出現以下幾種資訊：

Using index：表示查詢語句中用到了覆蓋索引，不訪問表的資料行，查詢效率比較好。

如果用SELECT *進行查詢，就不會有Using index，關於索引的介紹下篇好好說說。

Using filesort：代表MySQL會使用一個外部索引對資料進行排序(檔案排序)，而不是使用表內索引。這種情況在SQL查詢需要避免，最好不要在Extra中出現此型別：

通常會是使用ORDER BY語句導致，上圖中使用無索引的欄位進行排序會出現，同樣如果使用有索引的欄位，但用法不對也會出現，比如使用組合索引不規範時。

Using temporary：產生臨時表儲存中間結果，這種SQL是不允許的，遇見資料量大的場景，基本就跑不動啦；

這種型別常常因為ORDER BY 和 GROUP BY導致，所以在進行資料排序和分組查詢時，要注意索引的合理利用。

Using where：使用where過濾資料，小夥伴試一把。

Using join buffer：表示使用到了表連線快取；當表資料量大，可能導致buffer過大，查詢效率比較低，這種情況注意在表連線欄位上正確使用索引。

如果表連線查詢慢時，在連線欄位上加個索引試試，藥到病除；

impossible where：代表where後面的條件永遠為false，匹配不到資料；

用到的表及資料從Gitgub中獲取：https://github.com/zyq025/SQL_Optimize