MYSQL 性能分析器 EXPLAIN 用法實(shí)例分析

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

使用方法：

EXPLAIN SELECT * FROM user;

環(huán)境和數(shù)據(jù)準(zhǔn)備

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

 輸出結(jié)果：

 id:輸出的是整數(shù)，用來標(biāo)識整個 SQL 的執(zhí)行順序。id 如果相同，從上往下依次執(zhí)行id不同；id 值越大，執(zhí)行優(yōu)先級越高，越先被執(zhí)行；如果行引用其他行的并集結(jié)果，則該值可以為NULL

select_type:[查詢類型]

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

PRIMARY： 最外層的 select 查詢，常見于子查詢或 UNION 查詢 ，最外層的查詢被標(biāo)識為 PRIMARY

UNION：UNION 操作的第二個或之后的 SELECT，不依賴于外部查詢的結(jié)果集（外部查詢指的就是 PRIMARY 對應(yīng)的 SELECT）

DEPENDENT UNION：UNION 操作的第二個或之后的 SELECT，依賴于外部查詢的結(jié)果集

UNION RESULT：UNION 的結(jié)果（如果是 UNION ALL 則無此結(jié)果）

SUBQUERY：子查詢中的第一個 SELECT 查詢，不依賴于外部查詢的結(jié)果集

DEPENDENT SUBQUERY：子查詢中的第一個select查詢，依賴于外部查詢的結(jié)

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

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

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

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

UNCACHEABLE UNION：類似于 UNCACHEABLE SUBQUERY，只是出現(xiàn)在 UNION 操作中

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

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

partitions:查詢進(jìn)行匹配的分區(qū)，對于非分區(qū)表，該值為NULL。大多數(shù)情況下用不到分區(qū)，所以這一列我們無需關(guān)注

type:

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

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

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

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

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

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

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

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

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

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

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

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

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

index:當(dāng)我們可以使用索引覆蓋，但需要掃描全部的索引記錄時，則會使用 index；進(jìn)行統(tǒng)計時非常常見

ALL:我們熟悉的全表掃描

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

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

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

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

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

filtered:展示的是返回結(jié)果的行數(shù)所占需要讀到的行（rows 的值）的比例，當(dāng)然是越小越好啦

extra:

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

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

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

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

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

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

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

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

希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。