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

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

使用方法：

EXPLAIN SELECT * FROM user;

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

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

 輸出結(jié)果：

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

select_type:[查詢(xún)類(lèi)型]

SIMPLE：簡(jiǎn)單的 SELECT 查詢(xún)，沒(méi)有 UNION 或者子查詢(xún)，包括單表查詢(xún)或者多表 JOIN 查詢(xún)

PRIMARY： 最外層的 select 查詢(xún)，常見(jiàn)于子查詢(xún)或 UNION 查詢(xún) ，最外層的查詢(xún)被標(biāo)識(shí)為 PRIMARY

UNION：UNION 操作的第二個(gè)或之后的 SELECT，不依賴(lài)于外部查詢(xún)的結(jié)果集（外部查詢(xún)指的就是 PRIMARY 對(duì)應(yīng)的 SELECT）

DEPENDENT UNION：UNION 操作的第二個(gè)或之后的 SELECT，依賴(lài)于外部查詢(xún)的結(jié)果集

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

SUBQUERY：子查詢(xún)中的第一個(gè) SELECT 查詢(xún)，不依賴(lài)于外部查詢(xún)的結(jié)果集

DEPENDENT SUBQUERY：子查詢(xún)中的第一個(gè)select查詢(xún)，依賴(lài)于外部查詢(xún)的結(jié)

DERIVED：派生表（臨時(shí)表），常見(jiàn)于 FROM 子句中有子查詢(xún)的情況

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

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

UNCACHEABLE SUBQUERY：對(duì)于外層的主表，子查詢(xún)不可被緩存，每次都需要計(jì)算

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

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

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

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

type:

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

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

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

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

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

fulltext:當(dāng)使用全文索引時(shí)會(huì)用到，這種索引一般用不到，會(huì)用專(zhuān)門(mén)的搜索服務(wù)(solr、elasticsearch等)來(lái)替代

ref_or_null:類(lèi)似ref，但是添加了可以專(zhuān)門(mén)搜索 NULL 的行

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

index_merge:該訪(fǎng)問(wèn)類(lèi)型使用了索引合并優(yōu)化方法

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

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

index_subquery:index_subquery 與 unique_subquery類(lèi)似，只不過(guò)訪(fǎng)問(wèn)子查詢(xún)中的表時(shí)使用的是普通的索引

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

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

ALL:我們熟悉的全表掃描

possible_keys:展示在這個(gè) SQL 中，可能用到的索引有哪些，但不一定在查詢(xún)時(shí)使用。若為空則表示沒(méi)有可以使用的索引，此時(shí)可以通過(guò)檢查 WHERE 語(yǔ)句看是否可以引用某些列或者新建索引來(lái)提高性能

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

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

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

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

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

extra:

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

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

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

using temporary:表示 mysql 需要使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢(xún)

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

impossible where:查詢(xún)語(yǔ)句的WHERE子句永遠(yuǎn)為 FALSE 時(shí)將會(huì)提示該額外信息

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

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

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