詳解 MySQL 執(zhí)行計(jì)劃

EXPLAIN語句提供有關(guān)MySQL如何執(zhí)行語句的信息。EXPLAIN與SELECT，DELETE，INSERT，REPLACE和UPDATE語句一起使用。

EXPLAIN為SELECT語句中使用的每個(gè)表返回一行信息。它按照MySQL在處理語句時(shí)讀取它們的順序列出了輸出中的表。 MySQL使用嵌套循環(huán)連接方法解析所有連接。這意味著MySQL從第一個(gè)表中讀取一行，然后在第二個(gè)表，第三個(gè)表中找到匹配的行，依此類推。處理完所有表后，MySQL輸出所選列，并通過表列表回溯，直到找到一個(gè)表，其中有更多匹配的行。從這個(gè)表中讀取下一行，然后繼續(xù)處理下一個(gè)表。

1.  EXPLAIN 輸出列

說下幾個(gè)關(guān)鍵的列：

• type ：連接類型
• possible_keys ：可選的索引
• key ：實(shí)際執(zhí)行時(shí)使用的索引
• ref ：ref列顯示將哪些列或常量與前面key列中顯示的命名的索引進(jìn)行比較以從表中選擇行
• rows ：rows列表示MySQL認(rèn)為執(zhí)行查詢必須檢查的行數(shù)
  

2.  連接類型

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

system

表只有一行。這是const join類型的特例。

const

表最多有一個(gè)匹配行，在查詢開始時(shí)讀取。因?yàn)橹挥幸恍校赃@一行中的列的值可以被優(yōu)化器的其余部分視為常量。const表非?？?，因?yàn)樗鼈冎槐蛔x取一次。

當(dāng)你用PRIMARY KEY或UNIQUE索引的所有部分與常量值進(jìn)行比較時(shí)，將使用const。

例如，下面的表tbl_name可以被當(dāng)做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

對(duì)于前表中的每一行組合，從這個(gè)表中讀取一行。除了system和const類型，這是可能的最好的聯(lián)接類型。當(dāng)一個(gè)索引的所有部分都被聯(lián)接使用并且索引是PRIMARY KEY或UNIQUE NOT NULL索引時(shí)，使用它。

eq_ref可以用于使用=操作符進(jìn)行比較的索引列。比較值可以是一個(gè)常量，也可以是使用在此表之前讀取的表中的列的表達(dá)式。

例如，下面的例子中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

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

ref可用于使用=或=>運(yùn)算符進(jìn)行比較的索引列。

例如，下面的例子中，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索引執(zhí)行連接

ref_or_null

這種連接類型類似于ref，但是MySQL會(huì)額外搜索包含NULL值的行。此聯(lián)接類型優(yōu)化最常用于解析子查詢。

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

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

index_merge

這種連接類型表明使用了索引合并優(yōu)化。在這種情況下，輸出行中的key列包含使用的索引列表，而key_len包含所使用索引的最長(zhǎng)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

只檢索給定范圍內(nèi)的行，并使用索引來選擇行。輸出行中的key列指示使用了哪個(gè)索引。key_len包含所使用的最長(zhǎng)的key部分。對(duì)于這種類型，ref列為NULL。

使用=，>，>，> =，，=，IS NULL，=>，BETWEEN，LIKE或IN()運(yùn)算符將key列與常量進(jìn)行比較時(shí)，可以使用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是一樣的，區(qū)別在于index連接類型掃描的時(shí)候索引樹。通常，只發(fā)生在以下兩種情況：

• 如果索引是查詢的覆蓋索引，并且可用于滿足表中所需的所有數(shù)據(jù)，則僅掃描索引樹。在這種情況下，“Extra”列顯示“Using index”。僅索引掃描通常比ALL快，因?yàn)樗饕拇笮⊥ǔＰ∮诒頂?shù)據(jù)。
• 使用從索引讀取數(shù)據(jù)以按索引順序查找數(shù)據(jù)行來執(zhí)行全表掃描?！癠ses index”不會(huì)出現(xiàn)在Extra列中。

ALL

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

3.  Extra列

關(guān)于Extra列的輸出，只說幾個(gè)常見的：

Using filesort

MySQL必須做一次額外操作，以找出如何按排序順序檢索行。排序是通過根據(jù)聯(lián)接類型遍歷所有行并存儲(chǔ)與WHERE子句匹配的所有行的排序key和指向該行的指針來完成的。然后對(duì)key進(jìn)行排序，并按排序順序檢索行。

Using index

僅使用索引樹中的信息從表中檢索列信息，而不需要執(zhí)行額外的查找來讀取實(shí)際行。當(dāng)查詢只使用屬于單個(gè)索引的列時(shí)，可以使用此策略。

Using temporary

為了解析查詢，MySQL需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來保存結(jié)果。通常，如果查詢包含以不同方式展示列的GROUP BY和ORDER BY子句，則會(huì)發(fā)生這種情況。

Using where 

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

4.  優(yōu)化ORDER BY

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

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

SELECT * FROM t1 ORDER BY key_part1, key_part2;

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

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

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

以上就是詳解 MySQL 執(zhí)行計(jì)劃的詳細(xì)內(nèi)容，更多關(guān)于MySQL 執(zhí)行計(jì)劃的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！