MySQL-explain筆記

2020 年 1 月 23 日
筆記

使用 EXPLAIN 查看執行計劃， 5.6後可以加參數 EXPLAIN FORMAT=JSON xxx輸出json格式的資訊。

FORMAT=JSON格式的內容可參考8.8.2 EXPLAIN Output Format中的相關部分。

+----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  | id | select_type | table | partitions | type  | possible_keys              | key               | key_len | ref                  | rows  | filtered | Extra                             |  +----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  |  1 | SIMPLE      | bc    | NULL       | index | NULL                       | PRIMARY           | 62      | NULL                 | 1444 |      100 | Using index                        |  |  1 | SIMPLE      | gt    | NULL       | ref   | idx_tmp_1,idx_tmp_2        | idx_tmp_2         | 83      | func                 |  233 |      100 | Using index condition; Using where |  |  1 | SIMPLE      | oc    | NULL       | ref   | idx_tmp_class_1            | idx_tmp_class_1   | 9       | dwdb.gt.condition_id |    1 |      100 | NULL                               |  +----+-------------+-------+------------+-------+----------------------------------------------------------------------------------------------------------------------------------------+  3 rows in set

1. id

SELECT操作的序號(select_id)，用於標識查詢中執行SELECT子句或操作表的順序。永遠是id值越大，優先順序越高，越先被執行。

id值存在的情況

子查詢id順序遞增：內層的select語句一般會按順序增長編號，對應於其在原始語句中的位置。
id值為NULL：該行引用其他行的並集結果。
每行都顯示1：在語句中沒子查詢或關聯查詢，只有唯一的select。
NULL是最後執行,如合併結果集等。

執行順序

id相同，可以認為是一組，由上至下順序執行。
在所有組中，id值越大，優先順序越高，越先執行。

2. select_type

查詢的類型，用於區分是簡單查詢還是複雜查詢，以及其中的具體類型。

類型	含義
SIMPLE	簡單查詢，不使用 UNION 或子查詢。
PRIMARY	最外層查詢，當包含 UNION 或者子查詢等任何複雜的子部分，最外層的查詢被標為 PRIMARY。
UNION	位於 UNION 中第二個或其以後的SELECT語句，第一個就被標記為 PRIMARY；如果是union位於from中則標記為 DERIVED。
DEPENDENT UNION	位於UNION 中的第二個或其以後SELECT語句，同時該語句依賴外部的查詢。
UNION RESULT	UNION的結果。
SUBQUERY	子查詢中的第一個SELECT語句。
DEPENDENT SUBQUERY	子查詢中的第一個SELECT語句，同時該語句依賴外部的查詢。
DERIVED	派生表。位於form中的子查詢，MySQL會將其結果存放在一個臨時表中，即這裡的派生表。
MATERIALIZED	物化子查詢，優化 FROM/IN 子句中的子查詢。
UNCACHEABLE SUBQUERY	其結果無法快取的子查詢，必須針對外部查詢的每一行重新進行評估。
UNCACHEABLE UNION	位於 UNION 中的第二個或其以後SELECT語句，與上面的 UNCACHEABLE SUBQUERY 類似，屬於結果無法快取的子查詢。

2.1 DEPENDENT SUBQUERY 與 UNCACHEABLE SUBQUERY

兩者都是子查詢。

對於 DEPENDENT SUBQUERY ，子查詢對於外部上下文里每一個集合中不同的變星值僅僅重新計算一次。
對於 UNCACHEABLE SUBQUERY ，子查詢對於外部上下文里的每一行都會重新計算一次。

2.2 子查詢快取

子查詢的可快取性不同於查詢結果在查詢快取中的快取（8.10.3.1 How the Query Cache Operates）。

子查詢快取在查詢執行期間發生，而查詢快取僅在查詢執行完成後才用於存儲結果。

2.3 非查詢語句(non-SELECT statements)

非查詢語句(non-SELECT statements)的 select_type 值的顯示受影響表的語句類型(statement type)。如，DELETE 語句的 select_type 值為 DELETE。

2.4 MATERIALIZED

物化子查詢，也常被翻譯為「具體化」。通過生成子查詢結果作為臨時表（通常在記憶體中）來加快查詢的執行速度。

2.4.1 優化方式

MySQL第一次需要子查詢結果時，會將結果具體化為臨時表。
任何隨後的需要結果的時間裡，MySQL都會再次引用臨時表。
優化器可以使用哈希索引為表編製索引，以使查找快速、低成本。
索引包含唯一值，以消除重複項並使表更小。

物化子查詢儘可能使用記憶體中存儲臨時表，若表太大，會退回到磁碟上存儲(可參考8.4.4 Internal Temporary Table Use in MySQL)。

2.4.2 與不使用物化的比較

物化子查詢使用臨時表避免了優化器的某些重寫，並使得子查詢儘可能只執行一次，而不是對外部查詢的每一行執行一次。

如果未使用物化，則優化器有時會將不相關的子查詢重寫為相關的子查詢。例如，以下IN子查詢是不相關的（where_condition 僅涉及from t2和not中的列t1）：

SELECT * FROM t1  WHERE t1.a IN (SELECT t2.b FROM t2 WHERE where_condition);

優化器可能將此重寫為 EXISTS相關子查詢：

SELECT * FROM t1  WHERE EXISTS (SELECT t2.b FROM t2 WHERE where_condition AND t1.a=t2.b);

2.4.3 開啟方式

使用物化子查詢前需開啟 optimizer_switch 系統變數的 materialization：

set optimizer_switch='materialization=on';

開啟之後，物化子查詢適用於出現在任何地方的子查詢謂詞(in the select list，WHERE， ON，GROUP BY， HAVING，或ORDER BY)。

詳情可見8.2.2.2 Optimizing Subqueries with Materialization

3. table

輸出行所引用的表的名稱，顯示這一行的數據是關於哪張表的。不一定是真實的表名/別名，也可以是以下值之一：

<unionM,N>：ID值是M和N的兩行union產生的結果集(The row refers to the union of the rows with id values of M and N.)，。
<derivedN>：ID值n的行的派生表結果，派生表可能是從FROM子句中的一個子查詢生成的(The row refers to the derived table result for the row with an id value of N. A derived table may result, for example, from a subquery in the FROM clause.)。
<subqueryN>：ID值n的行的物化子查詢結果。

4. partitions

給定表所使用的分區，未分區的表該值為NULL。

5. type

連接類型，即MySQL如何查找表中的行。-待補

system>const>eq_ref>ref>range>index>all

類型	說明
all	全表掃描，性能最差。
index	和全表掃描一樣，只是掃描的索引,主要優點就是避免了排序。
range	範圍掃，僅檢索給定範圍內的行。
ref	對於每個來自於前面的表的行組合，所有有匹配索引值的行將從這張表中讀取。
eq_ref	對於先前表中的每行組合，從此表中讀取一行。除了 system和 const類型，這是最好的聯接類型。當使用主鍵索引(PRIMARY KEY)或不為空的唯一索引(UNIQUE NOT NULL index)
const	該表最多具有一個匹配行，該行在查詢開始時讀取。因為只有一行，所以優化器的其餘部分可以將這一行中列的值視為常量。 const表非常快，因為只讀取一次。
system	const的一種特殊情況，表僅有一行滿足條件

5.1 index

index時存在兩種情況：

如果索引是查詢的覆蓋索引，並且可用於滿足表中所需的所有數據，則僅掃描索引樹。在這種情況下，Extra列顯示為 Using index。僅索引掃描(index-only)通常比全表掃描(ALL)更快，因為索引的大小通常小於表數據。
使用對索引的讀取執行全表掃描，以按索引順序查找數據行。 Uses index不會出現在Extra列中。

5.2 range

當使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比較關鍵字列時,可以使用 range

5.3 ref

如果聯接只使用鍵的最左邊的前綴，或如果鍵不是UNIQUE或PRIMARY KEY（換句話說，如果聯接不能基於關鍵字選擇單個行的話），則使用ref。

如果使用的鍵僅僅匹配少量行，該聯接類型是不錯的。

ref可以用於使用=或<=>操作符的帶索引的列。

5.4 Null

意味說mysql能在優化階段分解查詢語句，在執行階段甚至用不到訪問表或索引（高效）。

網上好多的資料有這麼一條，但暫時未在官方文檔8.8.2 EXPLAIN Output Format一節中找到相關資訊。

6. possible_keys

顯示查詢可以使用的索引,這是基於查詢訪問的列和使用的比較操作符來判斷的。這個列表在優化過程的早期創建,因此有些列出來的索引對後續優化是沒有用的。

7. key

MySQL實際決定使用的鍵（索引）

如果為NULL可能是沒有索引或建了沒用，即索引失效。
如果MySQL決定使用possible_keys中的索引之一來查找行，值為該索引。
key也可能會是 possible_keys中之外的索引，如某個覆蓋索引。當possible_keys中沒有適合的索引，但是該查詢的所有列都是某個索引的列，即索引覆蓋了選定的列，此時儘管不使用索引來確定要檢索的行，但索引掃描依舊比數據行掃描更高效。
要想強制MySQL使用或忽視possible_keys列中的索引，在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

8. key_len

MySQL決定使用的key的長度,即MySQL在索引里使用的位元組數，它包含了在索引欄位中可能的最大長度,而不是表中使用的實際位元組數。

key_len 值能夠使你判定 MySQL 實際使用了 multiple-part key 中的多少個 part，即在組合索引裡面可以更清楚的了解到了哪部分欄位使用到了索引。

如果key是NULL，則長度為NULL

由於key存儲格式的原因，key的值可以為NULL的列比不能為NULL列長度多一位元組。

8.1 key_len的長度計算公式

key_len = L * C （+N）（+S）

L：索引列所定義欄位類型字元長度
C：不同編碼下一個字元所佔的位元組數（如utf8=3，gbk=2）
N：欄位為空標記，佔1位元組（非空欄位此標記不佔用位元組）
S：索引列欄位是否定長（int、char、datetime為定長，varchar為不定長），不定長欄位類型需記錄長度資訊，佔2位元組

9. ref

顯示了之前的表在key列記錄的索引中查找值所用的列或常量，即顯示索引key使用了之前表的哪一列或常量。。

如果值為func，則使用的值是某些函數的結果。要查看是哪個功能，請在 EXPLAIN 命令之後接著使用SHOW WARNINGS 命令查看擴展EXPLAIN 輸出。該函數實際上可能是算術運算符之類的運算符。

10. rows

該rows列指示MySQL認為執行查詢必須檢查的行數。對於InnoDB表，此數字是估計值，可能並不總是準確的。原則上值越小越好。

11. filtered

將被表條件過濾的錶行的估計百分比,最大值為100，這表示未過濾行。值從100減小表示過濾量增加。

該值和rows列的值一起使用，rows × filtered顯示將與下表(比當前id列小的表)連接的行數。例如，rows 為 1000 且 filtered 為50.00（50％），則與下表連接的行數為1000×50％= 500。

12. Extra

Extra是EXPLAIN輸出中另外一個很重要的列，該列顯示MySQL在查詢過程中的一些詳細資訊，MySQL查詢優化器執行查詢的過程中對查詢計劃的重要補充資訊。

類型	說明
Using filesort	MYSQL需要進行額外的步驟來發現如何對返回的行排序。排序是通過根據聯接類型遍歷所有行並存儲與WHERE子句匹配的所有行的排序key和指向該行的指針來完成的,然後對key進行排序，並按排序順序檢索行。看到這個的時候，查詢需要優化。
Using temporary	用臨時表保存中間結果，常用於GROUP BY 和 ORDER BY操作中，一般看到它說明查詢需要優化了，就算避免不了臨時表的使用也要盡量避免硬碟臨時表的使用。
Not exists	MYSQL優化了LEFT JOIN，一旦它找到了匹配LEFT JOIN標準的行，就不再搜索了。
Using index	僅使用索引樹中的資訊從表中檢索列資訊，而不必進行其他查找以讀取實際行。當查詢僅使用屬於單個索引的列時，可以使用此策略。對於InnoDB具有用戶定義的聚集索引的表，如果type為index,key為PRIMARY，即使Extra中沒有Using index，該index 依舊可以被使用
Using index condition	通過訪問索引元組並首先對其進行測試以確定是否讀取完整的錶行來讀取表。這樣，除非有必要，否則索引資訊將用於延遲（「下推」）整個錶行的讀取。
Using where	使用了WHERE從句來限制哪些行將與下一張表匹配或者是返回給用戶。除非想返回表中的全部行，否則如果查詢中的Extra值不是 Using where且表聯接類型為ALL或Index ，則查詢中可能會有問題。
Using join buffer	使用了連接快取：Block Nested Loop，連接演算法是塊嵌套循環連接;Batched Key Access，連接演算法是批量索引連接
Impossible where	該WHERE子句始終為false，不能選擇任何行。
Select tables optimized away	在沒有GROUP BY子句的情況下，基於索引優化MIN/MAX操作，或者對於MyISAM存儲引擎優化COUNT(*)操作，不必等到執行階段再進行計算，查詢執行計劃生成的階段即完成優化。
Distinct	在找到第一匹配的元組後即停止找同樣值的動作
Range checked for each Record(index map: N)	沒有找到理想的索引，因此對於從前面表中來的每一個行組合，MYSQL檢查使用哪個索引，並用它來從表中返回行。這是使用索引的最慢的連接之一
FirstMatch(tbl_name)	5.6.x開始引入的優化子查詢的新特性之一，常見於where字句含有in()類型的子查詢。如果內表的數據量比較大，就可能出現這個。
LooseScan(m..n)	使用半連接的LooseScan策略。 m和n是key的行號。 5.6.x之後引入的優化子查詢的新特性之一，在in()類型的子查詢中，子查詢返回的可能有重複記錄時，就可能出現這個。

Using filesort

僅僅表示沒有使用索引的排序，事實上filesort這個名字很糟糕，並不意味著在硬碟上排序，filesort與文件無關。因此消除Using filesort的方法就是讓查詢sql的排序走索引。

filesort使用的演算法是QuickSort，即對需要排序的記錄生成元數據進行分塊排序，然後再使用mergesort方法合併塊。其中filesort可以使用的記憶體空間大小為參數sort_buffer_size的值，默認為2M。當排序記錄太多sort_buffer_size不夠用時，mysql會使用臨時文件來存放各個分塊，然後各個分塊排序後再多次合併分塊最終全局完成排序。

Using index condition

這是MySQL 5.6出來的新特性，叫做「索引條件推送」。簡單說一點就是MySQL原來在索引上是不能執行如like這樣的操作的，但是現在可以了，這樣減少了不必要的IO操作，但是只能用在二級索引上。

請參見Section 8.2.1.5, 「Index Condition Pushdown Optimization

Using join buffer

Using join buffer (Block Nested Loop)

使用塊嵌套循環演算法。

大表 join 操作雖然對 IO 有影響，但是在語句執行結束後，對 IO 的影響也就結束了。但是，對 Buffer Pool 的影響就是持續性的，需要依靠後續的查詢請求慢慢恢復記憶體命中率。

BNL 演算法對系統的影響主要包括三個方面： – 可能會多次掃描被驅動表，佔用磁碟 IO 資源； – 判斷 join 條件需要執行 M*N 次對比（M、N 分別是兩張表的行數），如果是大表就會佔用非常多的 CPU 資源； – 可能會導致 Buffer Pool 的熱數據被淘汰，影響記憶體命中率。

Using join buffer (Batched Key Access)

使用批量密鑰訪問演算法

BNL轉BKA

想要穩定地使用 MRR 優化的話，需要設置set optimizer_switch="mrr_cost_based=off"。（官方文檔的說法，是現在的優化器策略，判斷消耗的時候，會更傾向於不使用 MRR，把 mrr_cost_based 設置為 off，就是固定使用 MRR 了。）

select * from t1 join t2 on (t1.b=t2.b) where t2.b>=1 and t2.b<=2000;

JOIN 優化

BKA 優化是 MySQL 已經內置支援的，建議你默認使用；
BNL 演算法效率低，建議你都盡量轉成 BKA 演算法。
優化的方向就是給被驅動表的關聯欄位加上索引；
基於臨時表的改進方案，對於能夠提前過濾出小數據的 join 語句來說，效果還是很好的；
MySQL 目前的版本還不支援 hash join，但你可以配合應用端自己模擬出來，理論上效果要好於臨時表的方案。
8.8.2 EXPLAIN Output Format
MySQL高級第二章——索引優化分析
MySQL EXPLAIN詳解
mysql explain詳解
MySQL 執行計劃中Extra(Using where,Using index,Using index condition,Using index,Using where)的淺析
MySQL優化之EXPLAIN詳解
MySQL key_len 大小的計算
性能優化系列七：SQL優化
Using temporary與Using filesort