MySql 執行計畫

　　mysql執行計畫是sql語句經過查詢最佳化工具後，查詢最佳化工具會根據使用者的sql語句所包含的欄位和內容數量等統計資訊，選擇出一個執行效率最優（mysql系統認為最優）的執行計畫，然後根據執行計畫，調用儲存引擎提供的介面，擷取資料。

　　但是，在mysql執行的時候，到底使用了一個什麼樣的執行計畫，有沒有用到索引。當資料規模比較大的時候，sql執行的時候，執行計畫不同，會直接影響sql的執行速度。這個時候，就需要對sql語句執行進行調試。

　　mysql我們在調試sql語句的時候，不會像我們寫java或者其他語言代碼那樣通過打斷點的方式進行代碼調試。這個時候，我們就需要通過查看執行計畫來調試我們的sql了。Mysql通過EXPLAIN來查看執行計畫，我們寫sql語句的時候，在語句之前加一個EXPLAIN就可以了。EXPLAIN可以用在SELECT、DELETE、INSERT、PEPLACE以及UPDATE等語句中，如：

1 EXPLAIN SELECT2     *3 FROM4     school5 WHERE6     school_nick = '縣第一小學'

EXPLAIN SQL

　　執行後，結果如下：

　　這裡介紹下每個欄位以及每個欄位的含義：

　　 id

　　id是執行計畫的標識符，是SELECT 查詢的序號。如果結果集會跟其他表的結果用UNION關鍵字相結合，那麼id可能為空白。

　　id是否為空白，對執行計畫的影響不大，如果有興趣可以看另外一篇部落格：mysql執行計畫id為空白—UNION關鍵字，這裡不再贅述。

　　 select_type

　　select_type表示sql語句查詢的類型。具體表示如下表：

select_type的值	含義
SIMPLE	簡單的select查詢，沒有使用關聯和子查詢。
PRIMARY	最外層select，包含子查詢的時候，最外層的查詢
UNION	在一個UNION查詢中，第二次或以後的子查詢操作
DEPENDENT UNION	在一個UNION查詢中，第二次子查詢或以後的SELECT查詢的時候需要依賴外部的查詢
UNION RESULT	UNION的返回結果集
SUBQUERY	子查詢語句的第一個select語句
DEPENDENT SUBQUERY	依賴外部查詢的第一個子查詢
DERIVED	派生表——該暫存資料表是從子查詢派生出來的，位於form中的子查詢
MATERIALIZED	物化子查詢（不確定啥意思，以後研究後再回來補充，或者大神指教）
UNCACHEABLE SUBQUERY	無法緩衝結果的子查詢，必須為外部查詢的每一行重新計算
UNCACHEABLE UNION	UNION中的第二個或以後的不可快取的子查詢。

　　table

　　輸出行引用的表的名稱。一般為表格名稱或別名，也可能為如下值：

　　1.UNION的並集結果集。

　　2.derivedN當前行指向派生結果集。可能是一個派生表，例如來自FROM子句的結果集。

　　3.subqueryN 當前行指向一個子查詢的結果集。

　　type

　　連線類型。該列輸出表示如何串連表。下面的類型表示從最好的到最壞的類型

　　1.system 該表只有一行（=系統資料表）。這是const連線類型的特例 。

　　2.const 最多隻有一行匹配，在查詢開始的時候，計算出常量對應的地址，直接存取，例如：select * from test where  name ='zhang' 當name是唯一索引的時候，就有可能出現const。const非常快，因為它唯讀一次。

　　3.eq_ref 除了 system和 const類型之外，這是最好的連線類型。當兩個表聯查時使用索引的所有部分（針對的是複合式索引），且索引是 主鍵或唯一索引時使用它。使用“=”運算子來進行索引列的比較。

　　4.ref 非唯一索引掃描，返回某個匹配值的所有行。常用語非唯一索引。這裡對於eq_ref 和ref不熟悉的同學，可以看以下代碼：

　　

 1 -- 給test表的name欄位加唯一索引，test2 的job 行添加非唯一索引。 2 -- 這個代碼執行後，首先執行test2 的查詢，查出job = ‘teacher’ 的所有集合。 3 -- 所以test2 的typ是ref 表示的是匹配job = ‘teacher’ 的一個結果集。 4 -- 然後從結果集中取出name的集合，去匹配test1.name的結果。因為test1.name是唯一索引，所以一個name最多匹配到一條記錄，所以test的type是ref 5 EXPLAIN SELECT 6     * 7 FROM 8     test, 9     test210 WHERE11     test. NAME = test2. NAME12 AND test2.job = '33'

test ref and eq_ref

 

　　執行結果如：

　　

　　5.fulltext 使用fulltext 索引進行查詢。

　　6.ref_or_null 這種連結類型類似於ref，但是，除了ref之外，還對包含null的值進行了搜尋。常用於解析子查詢。程式碼範例如下：

1 SELECT * FROM ref_table2   WHERE key_column=expr OR key_column IS NULL;

ref_or_null

　　7.index_merge 這個連結類型表示使用索引合并最佳化。輸出內容包含在索引列表中。

　　8.unique_subquery 索引尋找，替換子查詢，以提高效率。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery

　　9. index_subquery 類似於unique_subquery 但是替換in子查詢，適用於非唯一索引，代碼： value IN (SELECT key_column FROM single_table WHERE some_expr) 

　　10.range 掃描部分索引，對索引的掃描從某一點開始，返回的是某個索引地區的值。常見的有基於索引的 < ,> 等的查詢。

　　11. index 掃描全部索引，對索引進行整體掃描。

　　12.all 全表掃描，最慢的查詢 。應該避免

 　possible_keys

　　可能使用的key，指出當前查詢涉及到的行都含有那些索引。如果有索引就會列出，但是不一定會被使用。

　　key

　　實際使用的索引。如果沒有使用索引，顯示null。

　 key_len

　　表中對應的索引最大可能長度。可以通過設定索引長度改變該值。例如：一個varchar（255）的索引長度為255，可是我們使用不到那麼長，我們可以取字串的前五位作為索引。這時key_len 就是5.這裡關於索引值的長度的選取規則，以後有機會再寫一篇部落格詳細介紹。

　　ref

　　哪些欄位和key一起被使用。沒用過。

　　rows

　　受影響的行數。不是特別精確的。

　　Extra

　　解釋額外的資訊。包含mysql對於query最佳化的時候的一些附加資訊。非常有用。可能出現的結果如下：

• const row not found 該表為空白
  
• Deleting all rows   表格內資料被標記刪除，正在刪除中（某些儲存引擎支援一種方法，以簡單快捷的方式刪除所有行，這時查詢就會出現這個提示）
• Distinct  MySQL正在尋找不同的值，因此它在找到第一個匹配行後停止為當前行組合搜尋更多行。
• FirstMatch(tbl_name) 半串連FirstMatch串連捷徑策略用於tbl_name。
• Full scan on NULL key當最佳化程式無法使用索引尋找存取方法時，子查詢最佳化將作為回退策略發生。
• Impossible HAVING該HAVING子句始終為false，無法選擇任何行。
• Impossible WHERE 該WHERE子句始終為false，無法選擇任何行。
• Impossible WHERE noticed after reading const tables MySQL已經讀取了所有 const（和 system）表，並注意到該WHERE子句始終為false。
• No matching min/max row 沒有行滿足查詢的條件的行
• no matching row in const table  對於具有串連的查詢，有一個空表或沒有滿足唯一索引條件的行的表。
• No matching rows after partition pruning  對於DELETE或 UPDATE，最佳化器在分區修剪後發現沒有刪除或更新的內容。
• No tables used 查詢沒有FROM子句
• Not exists 查詢的內容不存在 
• Plan isn't ready yet 最佳化程式尚未完成為在命名串連中執行的語句建立執行計畫時， 會出現此值。
• Range checked for each record MySQL發現沒有好的索引可以使用，但發現在前面的表的列值可能會使用某些索引。
• Recursive  遞迴
• Skip_open_table， Open_frm_only， Open_full_table  

  • Skip_open_table：表檔案不需要開啟。該資訊已從資料字典中獲得。

  • Open_frm_only：只需要讀取表資訊的資料字典。

  • Open_full_table：未最佳化的資訊尋找。必須從資料字典中讀取表資訊並讀取表檔案。

• unique row not found  對於查詢，沒有行滿足 索引或表的條件。
• Using filesort  使用檔案排序。MySQL必須執行額外的傳遞以找出如何按排序次序檢索行。排序是通過根據連線類型遍曆所有行並將排序鍵和指標儲存到與該WHERE子句匹配的所有行的行來完成的。然後對鍵進行排序，並按排序次序檢索行
• Using index 僅使用索引樹中的資訊從表中檢索列資訊，而不必另外尋找讀取實際行。當查詢僅使用屬於單個索引的列時，可以使用此策略。
• Using index condition  通過首先訪問索引，確定是否可以讀取完整的表行。
• Using index for group-by  使用索引分組。表示MySQL找到了一個索引，可用於檢索GROUP BY或 DISTINCT查詢的所有列，而無需對實際表進行任何額外的磁碟訪問。此外，索引以最有效方式使用，因此對於每個組，唯讀取少數索引條目。
• Using index for skip scan  使用索引跳過掃描範圍
• Using join buffer  將表資料讀入緩衝，然後從緩衝中讀資料來執行操作。
• Using MRR 使用多範圍讀取最佳化策略讀取表。
• Using temporary 使用暫存資料表，MySQL需要建立一個暫存資料表來儲存結果。如果查詢包含以不同方式列出列的GROUP BY和 ORDER BY子句，則通常會發生這種情況。
• Using where  使用上了where限制，表示MySQL伺服器在儲存引擎受到記錄後進行“後過濾”（Post-filter），如果查詢未能使用索引，Using where的作用只是提醒我們MySQL將用where子句來過濾結果集。
• Zero limit  查詢有一個LIMIT 0子句，不能選擇任何行。
• Only index  這意味著資訊只用索引樹中的資訊檢索出的，這比掃描整個表要快。