快捷導(dǎo)航

MySQL中通過(guò)EXPLAIN如何分析SQL的執(zhí)行計(jì)劃詳解

更新時(shí)間：2018年08月13日 09:41:42 作者：擼碼那些事

這篇文章主要給大家介紹了關(guān)于MySQL中通過(guò)EXPLAIN如何分析SQL的執(zhí)行計(jì)劃的相關(guān)資料，文中通過(guò)圖文以及示例代碼介紹的非常詳細(xì)，對(duì)大家的學(xué)習(xí)或者工作具有一定的安康學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

在MySQL中，我們可以通過(guò)EXPLAIN命令獲取MySQL如何執(zhí)行SELECT語(yǔ)句的信息，包括在SELECT語(yǔ)句執(zhí)行過(guò)程中表如何連接和連接的順序。

下面分別對(duì)EXPLAIN命令結(jié)果的每一列進(jìn)行說(shuō)明：

.select_type:表示SELECT的類型，常見的取值有：

類型	說(shuō)明
SIMPLE	簡(jiǎn)單表，不使用表連接或子查詢
PRIMARY	主查詢，即外層的查詢
UNION	UNION中的第二個(gè)或者后面的查詢語(yǔ)句
SUBQUERY	子查詢中的第一個(gè)

.table:輸出結(jié)果集的表（表別名）

.type:表示MySQL在表中找到所需行的方式，或者叫訪問(wèn)類型。常見訪問(wèn)類型如下，從上到下，性能由差到最好：

ALL	全表掃描
index	索引全掃描
range	索引范圍掃描
ref	非唯一索引掃描
eq_ref	唯一索引掃描
const,system	單表最多有一個(gè)匹配行
NULL	不用掃描表或索引

1、type=ALL，全表掃描，MySQL遍歷全表來(lái)找到匹配行

一般是沒(méi)有where條件或者where條件沒(méi)有使用索引的查詢語(yǔ)句

EXPLAIN SELECT * FROM customer WHERE active=0;

2、type=index，索引全掃描，MySQL遍歷整個(gè)索引來(lái)查詢匹配行，并不會(huì)掃描表

一般是查詢的字段都有索引的查詢語(yǔ)句

EXPLAIN SELECT store_id FROM customer;

3、type=range，索引范圍掃描，常用于<、<=、>、>=、between等操作

EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;

注意這種情況下比較的字段是需要加索引的，如果沒(méi)有索引，則MySQL會(huì)進(jìn)行全表掃描，如下面這種情況，create_date字段沒(méi)有加索引：

EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;

4、type=ref，使用非唯一索引或唯一索引的前綴掃描，返回匹配某個(gè)單獨(dú)值的記錄行

store_id字段存在普通索引（非唯一索引）

EXPLAIN SELECT * FROM customer WHERE store_id=10;

ref類型還經(jīng)常會(huì)出現(xiàn)在join操作中：

customer、payment表關(guān)聯(lián)查詢，關(guān)聯(lián)字段customer.customer_id（主鍵），payment.customer_id（非唯一索引）。表關(guān)聯(lián)查詢時(shí)必定會(huì)有一張表進(jìn)行全表掃描，此表一定是幾張表中記錄行數(shù)最少的表，然后再通過(guò)非唯一索引尋找其他關(guān)聯(lián)表中的匹配行，以此達(dá)到表關(guān)聯(lián)時(shí)掃描行數(shù)最少。

因?yàn)閏ustomer、payment兩表中customer表的記錄行數(shù)最少，所以customer表進(jìn)行全表掃描，payment表通過(guò)非唯一索引尋找匹配行。

EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;

5、type=eq_ref，類似ref，區(qū)別在于使用的索引是唯一索引，對(duì)于每個(gè)索引鍵值，表中只有一條記錄匹配

eq_ref一般出現(xiàn)在多表連接時(shí)使用primary key或者unique index作為關(guān)聯(lián)條件。

film、film_text表關(guān)聯(lián)查詢和上一條所說(shuō)的基本一致，只不過(guò)關(guān)聯(lián)條件由非唯一索引變成了主鍵。

EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;

6、type=const/system，單表中最多有一條匹配行，查詢起來(lái)非常迅速，所以這個(gè)匹配行的其他列的值可以被優(yōu)化器在當(dāng)前查詢中當(dāng)作常量來(lái)處理

const/system出現(xiàn)在根據(jù)主鍵primary key或者唯一索引 unique index 進(jìn)行的查詢

根據(jù)主鍵primary key進(jìn)行的查詢：

EXPLAIN SELECT * FROM customer WHERE customer_id =10;

根據(jù)唯一索引unique index進(jìn)行的查詢：

EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';

7、type=NULL，MySQL不用訪問(wèn)表或者索引，直接就能夠得到結(jié)果

.possible_keys: 表示查詢可能使用的索引

.key: 實(shí)際使用的索引

.key_len: 使用索引字段的長(zhǎng)度

.ref: 使用哪個(gè)列或常數(shù)與key一起從表中選擇行。

.rows: 掃描行的數(shù)量

.filtered: 存儲(chǔ)引擎返回的數(shù)據(jù)在server層過(guò)濾后，剩下多少滿足查詢的記錄數(shù)量的比例(百分比)

.Extra: 執(zhí)行情況的說(shuō)明和描述，包含不適合在其他列中顯示但是對(duì)執(zhí)行計(jì)劃非常重要的額外信息

最主要的有一下三種：

Using Index	表示索引覆蓋，不會(huì)回表查詢
Using Where	表示進(jìn)行了回表查詢
Using Index Condition	表示進(jìn)行了ICP優(yōu)化
Using Flesort	表示MySQL需額外排序操作, 不能通過(guò)索引順序達(dá)到排序效果

什么是ICP？

MySQL5.6引入了Index Condition Pushdown（ICP）的特性，進(jìn)一步優(yōu)化了查詢。Pushdown表示操作下放，某些情況下的條件過(guò)濾操作下放到存儲(chǔ)引擎。

EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;

在5.6版本之前：

優(yōu)化器首先使用復(fù)合索引idx_rental_date過(guò)濾出符合條件rental_date='2005-05-25'的記錄，然后根據(jù)復(fù)合索引idx_rental_date回表獲取記錄，最終根據(jù)條件customer_id>=300 AND customer_id<=400過(guò)濾出最后的查詢結(jié)果（在服務(wù)層完成）。

在5.6版本之后：

MySQL使用了ICP來(lái)進(jìn)一步優(yōu)化查詢，在檢索的時(shí)候，把條件customer_id>=300 AND customer_id<=400也推到存儲(chǔ)引擎層完成過(guò)濾，這樣能夠降低不必要的IO訪問(wèn)。Extra為Using index condition就表示使用了ICP優(yōu)化。