MYSQL 性能分析器 EXPLAIN 用法實例分析

本文實例講述了MYSQL 性能分析器 EXPLAIN 用法。分享給大家供大家參考，具體如下：

使用方法：

EXPLAIN SELECT * FROM user;

環境和數據準備

-- 查看 MySQL 版本 SELECT VERSION(); -- MySQL 提供什麼存儲引擎 SHOW ENGINES; -- 查看默認存儲引擎 SHOW VARIABLES LIKE '%storage_engine%';

 id:輸出的是整數，用來標識整個 SQL 的執行順序。id 如果相同，從上往下依次執行id不同；id 值越大，執行優先級越高，越先被執行；如果行引用其他行的並集結果，則該值可以為NULL

select_type:[查詢類型]

SIMPLE：簡單的 SELECT 查詢，沒有 UNION 或者子查詢，包括單表查詢或者多表 JOIN 查詢

PRIMARY： 最外層的 select 查詢，常見於子查詢或 UNION 查詢 ，最外層的查詢被標識為 PRIMARY

UNION：UNION 操作的第二個或之後的 SELECT，不依賴於外部查詢的結果集（外部查詢指的就是 PRIMARY 對應的 SELECT）

DEPENDENT UNION：UNION 操作的第二個或之後的 SELECT，依賴於外部查詢的結果集

UNION RESULT：UNION 的結果（如果是 UNION ALL 則無此結果）

SUBQUERY：子查詢中的第一個 SELECT 查詢，不依賴於外部查詢的結果集

DEPENDENT SUBQUERY：子查詢中的第一個select查詢，依賴於外部查詢的結

DERIVED：派生表（臨時表），常見於 FROM 子句中有子查詢的情況

注意：MySQL5.7 中對 Derived table 做了一個新特性，該特性允許將符合條件的 Derived table 中的子表與父查詢的表合併進行直接JOIN，從而簡化簡化了執行計劃，同時也提高了執行效率；默認情況下，MySQL5.7 中這個特性是開啟的，所以默認情況下，上面的 SQL 的執行計劃應該是這樣的

MATERIALIZED：被物化的子查詢，MySQL5.6 引入的一種新的 select_type，主要是優化 FROM 或 IN 子句中的子查詢，更多詳情請查看：Optimizing Subqueries with Materialization

UNCACHEABLE SUBQUERY：對於外層的主表，子查詢不可被緩存，每次都需要計算

UNCACHEABLE UNION：類似於 UNCACHEABLE SUBQUERY，只是出現在 UNION 操作中

SIMPLLE、PRIMARY、SUBQUERY、DERIVED 這 4 個在實際工作中碰到的會比較多，看得懂這 4 個就行了，至於其他的，碰到了再去查資料就好了

table:顯示了對應行正在訪問哪個表(有別名就顯示別名)，還會有 <union2,3> 、 <subquery2> 、 <derived2> （這裡的 2,3、2、2 指的是 id 列的值）類似的值

partitions:查詢進行匹配的分區，對於非分區表，該值為NULL。大多數情況下用不到分區，所以這一列我們無需關注

type:

關聯類型或者訪問類型，它指明瞭 MySQL 決定如何查找表中符合條件的行，這是我們判斷查詢是否高效的重要依據,完整介紹請看：explain-join-types

system:該表只有一行（=系統表），是 const 類型的特例

const:確定只有一行匹配的時候，mysql 優化器會在查詢前讀取它並且只讀取一次，速度非常快。用於 primary key 或 unique 索引中有常亮值比較的情形

eq_ref:對於每個來自於前面的表的行，從該表最多隻返回一條符合條件的記錄。當連接使用的索引是 PRIMARY KEY 或 UNIQUE NOT NULL 索引時使用，非常高效

ref:索引訪問，也稱索引查找，它返回所有匹配某個單個值的行。此類型通常出現在多表的 JOIN 查詢, 針對於非 UNIQUE 或非 PRIMARY KEY, 或者是使用了最左前綴規則索引的查詢，換句話說，如果 JOIN 不能基於關鍵字選擇單個行的話，則使用ref

fulltext:當使用全文索引時會用到，這種索引一般用不到，會用專門的搜索服務(solr、elasticsearch等)來替代

ref_or_null:類似ref，但是添加了可以專門搜索 NULL 的行

這個是有前提條件的，前提為 weapon 列有索引，且 weapon 列存在  NULL 

index_merge:該訪問類型使用了索引合併優化方法

這個同樣也是有條件的， id 列和 weapon 列都有單列索引。如果出現 index_merge，並且這類 SQL 後期使用較頻繁，可以考慮把單列索引換為組合索引，這樣效率更高

unique_subquery:類似於兩表連接中被驅動表的 eq_ref 訪問方式，unique_subquery 是針對在一些包含 IN 子查詢的查詢語句中，如果查詢優化器決定將 IN 子查詢轉換為 EXISTS 子查詢，而且子查詢可以使用到主鍵或者唯一索引進行等值匹配時，則會使用 unique_subquery

index_subquery:index_subquery 與 unique_subquery類似，只不過訪問子查詢中的表時使用的是普通的索引

range:使用索引來檢索給定範圍的行，當使用 =、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符，用常量比較關鍵字列時，則會使用 rang,前提是必須基於索引，也就是 id 上必須有索引

index:當我們可以使用索引覆蓋，但需要掃描全部的索引記錄時，則會使用 index；進行統計時非常常見

ALL:我們熟悉的全表掃描

possible_keys:展示在這個 SQL 中，可能用到的索引有哪些，但不一定在查詢時使用。若為空則表示沒有可以使用的索引，此時可以通過檢查 WHERE 語句看是否可以引用某些列或者新建索引來提高性能

key:展示這個 SQL 實際使用的索引，如果沒有選擇索引，則此列為null，要想強制 MySQL 使用或忽視 possible_keys 列中的索引，在查詢中使用 FORCE INDEX、USE INDEX 或者I GNORE INDEX

key_len:展示 MySQL 決定使用的鍵長度（字節數）。如果 key 是 NULL，則長度為 NULL。在不損失精確性的情況下，長度越短越好

ref:展示的是與索引列作等值匹配的東東是個啥，比如只是一個常數或者是某個列。它顯示的列的名字（或const），此列多數時候為 Null

rows:展示的是 mysql 解析器認為執行此 SQL 時預計需要掃描的行數。此數值為一個預估值，不是具體值，通常比實際值小

filtered:展示的是返回結果的行數所佔需要讀到的行（rows 的值）的比例，當然是越小越好啦

extra:

表示不在其他列但也很重要的額外信息。取值有很多，我們挑一些比較常見的過一下

using index:表示 SQL 使用了使用覆蓋索引，而不用回表去查詢數據，性能非常不錯

using where:表示存儲引擎搜到記錄後進行了後過濾(POST-FILTER)，如果查詢未能使用索引，using where 的作用只是提醒我們 mysql 要用 where 條件過濾結果集

using temporary:表示 mysql 需要使用臨時表來存儲結果集，常見於排序和分組查詢

using filesort:表示 mysql 無法利用索引直接完成排序（排序的字段不是索引字段），此時會用到緩衝空間（內存或者磁盤）來進行排序；一般出現該值，則表示 SQL 要進行優化了，它對 CPU 的消耗是比較大的

impossible where:查詢語句的WHERE子句永遠為 FALSE 時將會提示該額外信息

當然還有其他的，不常見，等碰到了大家再去查吧!!!