MySQL 執行計劃-有解無憂

EXPLAIN陳述句提供有關MySQL如何執行陳述句的資訊，EXPLAIN與SELECT，DELETE，INSERT，REPLACE和UPDATE陳述句一起使用，

EXPLAIN為SELECT陳述句中使用的每個表回傳一行資訊，它按照MySQL在處理陳述句時讀取它們的順序列出了輸出中的表， MySQL使用嵌套回圈連接方法決議所有連接，這意味著MySQL從第一個表中讀取一行，然后在第二個表，第三個表中找到匹配的行，依此類推，處理完所有表后，MySQL輸出所選列，并通過表串列回溯，直到找到一個表，其中有更多匹配的行，從這個表中讀取下一行，然后繼續處理下一個表，

1. EXPLAIN 輸出列

說下幾個關鍵的列：

type ：連接型別
possible_keys ：可選的索引
key ：實際執行時使用的索引
ref ：ref列顯示將哪些列或常量與前面key列中顯示的命名的索引進行比較以從表中選擇行
rows ：rows串列示MySQL認為執行查詢必須檢查的行數

2. 連接型別

連接型別，順序從最好到最差，依次是：

system

表只有一行，這是const join型別的特例，

const

表最多有一個匹配行，在查詢開始時讀取，因為只有一行，所以這一行中的列的值可以被優化器的其余部分視為常量，const表非常快，因為它們只被讀取一次，

當你用PRIMARY KEY或UNIQUE索引的所有部分與常量值進行比較時，將使用const，

例如，下面的表tbl_name可以被當做const表：

SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name WHERE primary_key_part1=1 AND primary_key_part2=2;

eq_ref

對于前表中的每一行組合，從這個表中讀取一行，除了system和const型別，這是可能的最好的聯接型別，當一個索引的所有部分都被聯接使用并且索引是PRIMARY KEY或UNIQUE NOT NULL索引時，使用它，

eq_ref可以用于使用=運算子進行比較的索引列，比較值可以是一個常量，也可以是使用在此表之前讀取的表中的列的運算式，

例如，下面的例子中MySQL可以使用eq_ref連接來處理ref_table：

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column AND ref_table.key_column_part2=1;

ref

對于前表中的行的每種組合，將從該表中讀取具有匹配索引值的所有行，如果聯接僅使用key的最左前綴，或者如果key不是PRIMARY KEY或UNIQUE索引（換句話說，如果聯接無法基于key值選擇單個行），則使用ref，如果使用的key僅匹配幾行，則這是一種很好的聯接型別，

ref可用于使用=或<=>運算子進行比較的索引列，

例如，下面的例子中，MySQL可以用ref連接來處理ref_table：

SELECT * FROM ref_table WHERE key_column=expr;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column=other_table.column;

SELECT * FROM ref_table,other_table
  WHERE ref_table.key_column_part1=other_table.column
  AND ref_table.key_column_part2=1;

fulltext

使用FULLTEXT索引執行連接

ref_or_null

這種連接型別類似于ref，但是MySQL會額外搜索包含NULL值的行，此聯接型別優化最常用于決議子查詢，

例如，下面的例子中，MYSQL可以使用ref_or_null來處理ref_table：

SELECT * FROM ref_table WHERE key_column=expr OR key_column IS NULL;

index_merge

這種連接型別表明使用了索引合并優化，在這種情況下，輸出行中的key列包含使用的索引串列，而key_len包含所使用索引的最長key部分串列，

unique_subquery

此型別將eq_ref替換為以下形式的某些IN子查詢：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

index_subquery

與unique_subquery類似，它代替了IN子查詢，但適用于以下形式的子查詢中的非唯一索引：

value IN (SELECT key_column FROM single_table WHERE some_expr)

range

只檢索給定范圍內的行，并使用索引來選擇行，輸出行中的key列指示使用了哪個索引，key_len包含所使用的最長的key部分，對于這種型別，ref列為NULL，

使用=，<>，>，> =，<，<=，IS NULL，<=>，BETWEEN，LIKE或IN()運算子將key列與常量進行比較時，可以使用range：

SELECT * FROM tbl_name WHERE key_column = 10;

SELECT * FROM tbl_name WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name WHERE key_column IN (10,20,30);

SELECT * FROM tbl_name WHERE key_part1 = 10 AND key_part2 IN (10,20,30);

index

index連接型別與all是一樣的，區別在于index連接型別掃描的時候索引樹，通常，只發生在以下兩種情況：

如果索引是查詢的覆寫索引，并且可用于滿足表中所需的所有資料，則僅掃描索引樹，在這種情況下，“Extra”列顯示“Using index”，僅索引掃描通常比ALL快，因為索引的大小通常小于表資料，
使用從索引讀取資料以按索引順序查找資料行來執行全表掃描，“Uses index”不會出現在Extra列中，

ALL

對前表的行的每個組合進行全表掃描，如果該表是未標記為const的第一個表，則通常不好，并且在所有其他情況下通常非常糟糕，通常，可以通過添加索引來避免ALL，這些索引允許基于早期表中的常量值或列值從表中檢索行，

3. Extra列

關于Extra列的輸出，只說幾個常見的：

Using filesort

MySQL必須做一次額外操作，以找出如何按排序順序檢索行，排序是通過根據聯接型別遍歷所有行并存盤與WHERE子句匹配的所有行的排序key和指向該行的指標來完成的，然后對key進行排序，并按排序順序檢索行，

Using index

僅使用索引樹中的資訊從表中檢索列資訊，而不需要執行額外的查找來讀取實際行，當查詢只使用屬于單個索引的列時，可以使用此策略，

Using temporary

為了決議查詢，MySQL需要創建一個臨時表來保存結果，通常，如果查詢包含以不同方式展示列的GROUP BY和ORDER BY子句，則會發生這種情況，

Using where

WHERE子句用于限制哪些行匹配下一個表或發送給客戶端，除非你打算從表中獲取或檢查所有行，否則如果額外的值沒有使用where，并且表連接型別是all或index，則查詢中可能出現錯誤，

4. 優化ORDER BY

在某些情況下，MySQL可能會使用一個索引來滿足ORDER BY子句，從而避免執行filesort操作所涉及的額外排序，

假設在(key_part1, key_part2)上有一個索引，下面的查詢可以使用索引來決議ORDER BY部分，優化器是否真的這樣做，取決于如果還必須讀取索引之外的時，讀取索引是否比表掃描更有效，

SELECT * FROM t1 ORDER BY key_part1, key_part2;

上面的陳述句，查詢使用SELECT *，這可能會選擇比key_part1和key_part2更多的列，在這種情況下，掃描整個索引并查找表行以查找索引中未包含的列可能比掃描表并排序結果要昂貴，如果是這樣，則優化器不太可能使用索引，如果SELECT *僅選擇索引列，則使用索引并避免排序，

下面這個查詢中，key_part1是常量，因此通過索引訪問的所有行都按key_part2順序排列，并且如果WHERE子句的選擇性足以使索引范圍掃描比表掃描便宜，則在（key_part1，key_part2）上的索引可以避免排序：

SELECT * FROM t1 WHERE key_part1 = constant ORDER BY key_part2;

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/239992.html

標籤：MySQL

上一篇：在Linux下，如何實作C++操作Mysql資料庫？

下一篇：Golang的ORM框架之gorm