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

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

使用方法：

EXPLAIN SELECT * FROM user;

環(huán)境和數據準備

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

 輸出結果：

 id:輸出的是整數，用來標識整個 SQL 的執(zhí)行順序。id 如果相同，從上往下依次執(zhí)行id不同；id 值越大，執(zhí)行優(yōu)先級越高，越先被執(zhí)行；如果行引用其他行的并集結果，則該值可以為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，從而簡化簡化了執(zhí)行計劃，同時也提高了執(zhí)行效率；默認情況下，MySQL5.7 中這個特性是開啟的，所以默認情況下，上面的 SQL 的執(zhí)行計劃應該是這樣的

MATERIALIZED：被物化的子查詢，MySQL5.6 引入的一種新的 select_type，主要是優(yōu)化 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:查詢進行匹配的分區(qū)，對于非分區(qū)表，該值為NULL。大多數情況下用不到分區(qū)，所以這一列我們無需關注

type:

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

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

const:確定只有一行匹配的時候，mysql 優(yōu)化器會在查詢前讀取它并且只讀取一次，速度非?？臁Ｓ糜?primary key 或 unique 索引中有常亮值比較的情形

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

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

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

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

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

index_merge:該訪問類型使用了索引合并優(yōu)化方法

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

unique_subquery:類似于兩表連接中被驅動表的 eq_ref 訪問方式，unique_subquery 是針對在一些包含 IN 子查詢的查詢語句中，如果查詢優(yōu)化器決定將 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 決定使用的鍵長度（字節(jié)數）。如果 key 是 NULL，則長度為 NULL。在不損失精確性的情況下，長度越短越好

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

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

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

extra:

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

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

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

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

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

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

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

更多關于MySQL相關內容感興趣的讀者可查看本站專題：《MySQL查詢技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數據庫鎖相關技巧匯總》及《MySQL常用函數大匯總》

希望本文所述對大家MySQL數據庫計有所幫助。