Explain 最完整總結(jié)，SQL優(yōu)化不再難！

先看看具體有哪些字段：

mysql>?EXPLAIN?SELECT?1;

其實(shí)除了以SELECT開(kāi)頭的查詢(xún)語(yǔ)句，其余的DELETE、INSERT、REPLACE以及UPDATE語(yǔ)句前邊都可以加上EXPLAIN這個(gè)詞兒，用來(lái)查看這些語(yǔ)句的執(zhí)行計(jì)劃

建兩張測(cè)試表：

CREATE?TABLE?t1?(
????id?INT?NOT?NULL?AUTO_INCREMENT,
????key1?VARCHAR(100),
????key2?VARCHAR(100),
????key3?VARCHAR(100),
????name?VARCHAR(100),
????PRIMARY?KEY?(id),
????KEY?idx_key1?(key1),
????KEY?idx_key2_key3(key2,?key3)
)?Engine=InnoDB?CHARSET=utf8;

CREATE?TABLE?t2?(
????id?INT?NOT?NULL?AUTO_INCREMENT,
????key1?VARCHAR(100),
????key2?VARCHAR(100),
????key3?VARCHAR(100),
????name?VARCHAR(100),
????PRIMARY?KEY?(id),
????KEY?idx_key1?(key1),
????KEY?idx_key2_key3(key2,?key3)
)?Engine=InnoDB?CHARSET=utf8;

兩個(gè)變種

explain extended

會(huì)在 explain 的基礎(chǔ)上額外提供一些查詢(xún)優(yōu)化的信息。緊隨其后通過(guò) show warnings 命令可以 得到優(yōu)化后的查詢(xún)語(yǔ)句，從而看出優(yōu)化器優(yōu)化了什么

explain?extended?SELECT?*?FROM?t1?where?key1?=?'11';
show?warnings;

explain partitions

相比 explain 多了個(gè) partitions 字段，如果查詢(xún)是基于分區(qū)表的話，會(huì)顯示查詢(xún)將訪問(wèn)的分區(qū)。

EXPLAIN?PARTITIONS?SELECT?*?FROM?t1?INNER?JOIN?t2?ON?t1.key3?=?t2.key3;

table列

這一列表示 explain 的一行正在訪問(wèn)哪個(gè)表

mysql>?EXPLAIN?SELECT?*?FROM?t1;

這個(gè)查詢(xún)語(yǔ)句只涉及對(duì)t1表的單表查詢(xún)，所以EXPLAIN輸出中只有一條記錄，其中的table列的值是t1，表明這條記錄是用來(lái)說(shuō)明對(duì)t1表的單表訪問(wèn)。

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2;

可以看到這個(gè)連接查詢(xún)的執(zhí)行計(jì)劃中有兩條記錄，這兩條記錄的table列分別是t1和t2，這兩條記錄用來(lái)分別說(shuō)明對(duì)t1表和t2表的訪問(wèn)

注意：

當(dāng) from 子句中有子查詢(xún)時(shí)，table列是 格式，表示當(dāng)前查詢(xún)依賴(lài) id=N 的查詢(xún)，于是先執(zhí)行 id=N 的查詢(xún)。當(dāng)有 union 時(shí)，UNION RESULT 的 table 列的值為，1和2表示參與 union 的 select 行id。

id列

id列的編號(hào)是 select 的序列號(hào)，有幾個(gè) select 就有幾個(gè)id，并且id的順序是按 select 出現(xiàn)的順序增長(zhǎng)的。

id列越大執(zhí)行優(yōu)先級(jí)越高，id相同則從上往下執(zhí)行，id為NULL最后執(zhí)行

比如下邊這個(gè)查詢(xún)中只有一個(gè)SELECT關(guān)鍵字，所以EXPLAIN的結(jié)果中也就只有一條id列為1的記錄：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'e038f672a8';

對(duì)于連接查詢(xún)來(lái)說(shuō)，一個(gè)SELECT關(guān)鍵字后邊的FROM子句中可以跟隨多個(gè)表，所以在連接查詢(xún)的執(zhí)行計(jì)劃中，每個(gè)表都會(huì)對(duì)應(yīng)一條記錄，但是這些記錄的id值都是相同的，比如：

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2;

可以看到，上述連接查詢(xún)中參與連接的t1和t2表分別對(duì)應(yīng)一條記錄，但是這兩條記錄對(duì)應(yīng)的id值都是1。

注意：

在連接查詢(xún)的執(zhí)行計(jì)劃中，每個(gè)表都會(huì)對(duì)應(yīng)一條記錄，這些記錄的id列的值是相同的，出現(xiàn)在前邊的表表示驅(qū)動(dòng)表，出現(xiàn)在后邊的表表示被驅(qū)動(dòng)表。所以從上邊的EXPLAIN輸出中我們可以看出，查詢(xún)優(yōu)化器準(zhǔn)備讓t2表作為驅(qū)動(dòng)表，讓t1表作為被驅(qū)動(dòng)表來(lái)執(zhí)行查詢(xún)

對(duì)于包含子查詢(xún)的查詢(xún)語(yǔ)句來(lái)說(shuō)，就可能涉及多個(gè)SELECT關(guān)鍵字，所以在包含子查詢(xún)的查詢(xún)語(yǔ)句的執(zhí)行計(jì)劃中，每個(gè)SELECT關(guān)鍵字都會(huì)對(duì)應(yīng)一個(gè)唯一的id值，比如這樣：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key1?FROM?t2)?OR?key3?=?'a1b6cee57a';

從輸出結(jié)果中我們可以看到，t1表在外層查詢(xún)中，外層查詢(xún)有一個(gè)獨(dú)立的SELECT關(guān)鍵字，所以第一條記錄的id值就是1，t2表在子查詢(xún)中，子查詢(xún)有一個(gè)獨(dú)立的SELECT關(guān)鍵字，所以第二條記錄的id值就是2。

但是這里大家需要特別注意，查詢(xún)優(yōu)化器可能對(duì)涉及子查詢(xún)的查詢(xún)語(yǔ)句進(jìn)行重寫(xiě)，從而轉(zhuǎn)換為連接查詢(xún)。所以如果我們想知道查詢(xún)優(yōu)化器對(duì)某個(gè)包含子查詢(xún)的語(yǔ)句是否進(jìn)行了重寫(xiě)，直接查看執(zhí)行計(jì)劃就好了，比如說(shuō)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key3?FROM?t2?WHERE?t1.key1?=?'a1b6cee57a');

可以看到，雖然我們的查詢(xún)語(yǔ)句是一個(gè)子查詢(xún)，但是執(zhí)行計(jì)劃中t1和t2表對(duì)應(yīng)的記錄的id值全部是1，這就表明了查詢(xún)優(yōu)化器將子查詢(xún)轉(zhuǎn)換為了連接查詢(xún)。

對(duì)于包含UNION子句的查詢(xún)語(yǔ)句來(lái)說(shuō)，每個(gè)SELECT關(guān)鍵字對(duì)應(yīng)一個(gè)id值也是沒(méi)錯(cuò)的，不過(guò)還是有點(diǎn)兒特別的東西，比方說(shuō)下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?UNION?SELECT?*?FROM?t2;

UNION子句是為了把id為1的查詢(xún)和id為2的查詢(xún)的結(jié)果集合并起來(lái)并去重，所以在內(nèi)部創(chuàng)建了一個(gè)名為的臨時(shí)表（就是執(zhí)行計(jì)劃第三條記錄的table列的名稱(chēng)），id為NULL表明這個(gè)臨時(shí)表是為了合并兩個(gè)查詢(xún)的結(jié)果集而創(chuàng)建的。

跟UNION對(duì)比起來(lái)，UNION ALL就不需要為最終的結(jié)果集進(jìn)行去重，它只是單純的把多個(gè)查詢(xún)的結(jié)果集中的記錄合并成一個(gè)并返回給用戶，所以也就不需要使用臨時(shí)表。所以在包含UNION ALL子句的查詢(xún)的執(zhí)行計(jì)劃中，就沒(méi)有那個(gè)id為NULL的記錄，如下所示：

mysql>?EXPLAIN?SELECT?*?FROM?t1?UNION?ALL?SELECT?*?FROM?t2;

select_type列

MySQL每一個(gè)SELECT關(guān)鍵字代表的小查詢(xún)都定義了一個(gè)稱(chēng)之為select_type的屬性，意思是我們只要知道了某個(gè)小查詢(xún)的select_type屬性，就知道了這個(gè)小查詢(xún)?cè)谡麄€(gè)大查詢(xún)中扮演了一個(gè)什么角色

下面是官方文檔介紹：

https://dev.mysql.com/doc/ref...

SIMPLE

查詢(xún)語(yǔ)句中不包含UNION或者子查詢(xún)的查詢(xún)都算作是SIMPLE類(lèi)型，比方說(shuō)下邊這個(gè)單表查詢(xún)的select_type的值就是SIMPLE：

mysql>?EXPLAIN?SELECT?*?FROM?t1;

PRIMARY

對(duì)于包含UNION、UNION ALL或者子查詢(xún)的大查詢(xún)來(lái)說(shuō)，它是由幾個(gè)小查詢(xún)組成的，其中最左邊的那個(gè)查詢(xún)的select_type值就是PRIMARY，比方說(shuō)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?UNION?SELECT?*?FROM?t2;

從結(jié)果中可以看到，最左邊的小查詢(xún)SELECT * FROM t1對(duì)應(yīng)的是執(zhí)行計(jì)劃中的第一條記錄，它的select_type值就是PRIMARY。

UNION

對(duì)于包含UNION或者UNION ALL的大查詢(xún)來(lái)說(shuō)，它是由幾個(gè)小查詢(xún)組成的，其中除了最左邊的那個(gè)小查詢(xún)以外，其余的小查詢(xún)的select_type值就是UNION，可以對(duì)比上一個(gè)例子的效果

UNION RESULT

MySQL選擇使用臨時(shí)表來(lái)完成UNION查詢(xún)的去重工作，針對(duì)該臨時(shí)表的查詢(xún)的select_type就是UNION RESULT，同樣對(duì)比上面的例子

SUBQUERY

如果包含子查詢(xún)的查詢(xún)語(yǔ)句不能夠轉(zhuǎn)為對(duì)應(yīng)的semi-join的形式，并且該子查詢(xún)是不相關(guān)子查詢(xún)，并且查詢(xún)優(yōu)化器決定采用將該子查詢(xún)物化的方案來(lái)執(zhí)行該子查詢(xún)時(shí)，該子查詢(xún)的第一個(gè)SELECT關(guān)鍵字代表的那個(gè)查詢(xún)的select_type就是SUBQUERY，比如下邊這個(gè)查詢(xún)：

概念解釋?zhuān)?/p>

semi-join子查詢(xún)，是指當(dāng)一張表在另一張表找到匹配的記錄之后，半連接（semi-jion）返回第一張表中的記錄。與條件連接相反，即使在右節(jié)點(diǎn)中找到幾條匹配的記錄，左節(jié)點(diǎn) 的表也只會(huì)返回一條記錄。另外，右節(jié)點(diǎn)的表一條記錄也不會(huì)返回。半連接通常使用IN 或 EXISTS 作為連接條件

物化：這個(gè)將子查詢(xún)結(jié)果集中的記錄保存到臨時(shí)表的過(guò)程稱(chēng)之為物化（Materialize）。那個(gè)存儲(chǔ)子查詢(xún)結(jié)果集的臨時(shí)表稱(chēng)之為物化表。正因?yàn)槲锘碇械挠涗浂冀⒘怂饕ɑ趦?nèi)存的物化表有哈希索引，基于磁盤(pán)的有B+樹(shù)索引），通過(guò)索引執(zhí)行IN語(yǔ)句判斷某個(gè)操作數(shù)在不在子查詢(xún)結(jié)果集中變得非常快，從而提升了子查詢(xún)語(yǔ)句的性能。

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key1?FROM?t2)?OR?key3?=?'a1b6cee57a';

可以看到，外層查詢(xún)的select_type就是PRIMARY，子查詢(xún)的select_type就是SUBQUERY。

DEPENDENT SUBQUERY

如果包含子查詢(xún)的查詢(xún)語(yǔ)句不能夠轉(zhuǎn)為對(duì)應(yīng)的semi-join的形式，并且該子查詢(xún)是相關(guān)子查詢(xún)，則該子查詢(xún)的第一個(gè)SELECT關(guān)鍵字代表的那個(gè)查詢(xún)的select_type就是DEPENDENT SUBQUERY，比如下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key1?FROM?t2?WHERE?t1.key2?=?t2.key2)?OR?key3?=?'a1b6cee57a';

DEPENDENT UNION

在包含UNION或者UNION ALL的大查詢(xún)中，如果各個(gè)小查詢(xún)都依賴(lài)于外層查詢(xún)的話，那除了最左邊的那個(gè)小查詢(xún)之外，其余的小查詢(xún)的select_type的值就是DEPENDENT UNION。比方說(shuō)下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key1?FROM?t2?WHERE?key1?=?'a1b6cee57a'?UNION?SELECT?key1?FROM?t1?WHERE?key1?=?'a1b6cee57a');

這個(gè)查詢(xún)比較復(fù)雜啊，大查詢(xún)里包含了一個(gè)子查詢(xún)，子查詢(xún)里又是由UNION連起來(lái)的兩個(gè)小查詢(xún)。從執(zhí)行計(jì)劃中可以看出來(lái)，SELECT key1 FROM t2 WHERE key1 = 'a1b6cee57a'這個(gè)小查詢(xún)由于是子查詢(xún)中第一個(gè)查詢(xún)，所以它的select_type是DEPENDENT SUBQUERY，而SELECT key1 FROM t1 WHERE key1 = 'a1b6cee57a'這個(gè)查詢(xún)的select_type就是DEPENDENT UNION。

DERIVED

對(duì)于采用物化的方式執(zhí)行的包含派生表的查詢(xún)，該派生表對(duì)應(yīng)的子查詢(xún)的select_type就是DERIVED，比方說(shuō)下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?(SELECT?key1,?count(*)?as?t?FROM?t1?GROUP?BY?key1)?AS?derived_t1?where?t?>?1;

從執(zhí)行計(jì)劃中可以看出，id為2的記錄就代表子查詢(xún)的執(zhí)行方式，它的select_type是DERIVED，說(shuō)明該子查詢(xún)是以物化的方式執(zhí)行的。id為1的記錄代表外層查詢(xún)，大家注意看它的table列顯示的是，表示該查詢(xún)是針對(duì)將派生表物化之后的表進(jìn)行查詢(xún)的。

MATERIALIZED

當(dāng)查詢(xún)優(yōu)化器在執(zhí)行包含子查詢(xún)的語(yǔ)句時(shí)，選擇將子查詢(xún)物化之后與外層查詢(xún)進(jìn)行連接查詢(xún)時(shí)，該子查詢(xún)對(duì)應(yīng)的select_type屬性就是MATERIALIZED，比如下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?(SELECT?key1?FROM?t2);

執(zhí)行計(jì)劃的第三條記錄的id值為2，說(shuō)明該條記錄對(duì)應(yīng)的是一個(gè)單表查詢(xún)，從它的select_type值為MATERIALIZED可以看出，查詢(xún)優(yōu)化器是要把子查詢(xún)先轉(zhuǎn)換成物化表。然后看執(zhí)行計(jì)劃的前兩條記錄的id值都為1，說(shuō)明這兩條記錄對(duì)應(yīng)的表進(jìn)行連接查詢(xún)，需要注意的是第二條記錄的table列的值是，說(shuō)明該表其實(shí)就是id為2對(duì)應(yīng)的子查詢(xún)執(zhí)行之后產(chǎn)生的物化表，然后將s1和該物化表進(jìn)行連接查詢(xún)。

type列

這一列表示關(guān)聯(lián)類(lèi)型或訪問(wèn)類(lèi)型，即MySQL決定如何查找表中的行，查找數(shù)據(jù)行記錄的大概范圍。依次從最優(yōu)到最差分別為：system > const > eq_ref > ref > range > index > ALL一般來(lái)說(shuō)，得保證查詢(xún)達(dá)到range級(jí)別，最好達(dá)到ref

NULL

mysql能夠在優(yōu)化階段分解查詢(xún)語(yǔ)句，在執(zhí)行階段用不著再訪問(wèn)表或索引。例如：在索引列中選取最小值，可以單獨(dú)查找索引來(lái)完成，不需要在執(zhí)行時(shí)訪問(wèn)表

mysql>?explain?select?min(id)?from?t1;

eq_ref

primary key 或 unique key 索引的所有部分被連接使用 ，最多只會(huì)返回一條符合條件的記錄。這可能是在 const 之外最好的聯(lián)接類(lèi)型了，簡(jiǎn)單的 select 查詢(xún)不會(huì)出現(xiàn)這種 type。

在連接查詢(xún)時(shí)，如果被驅(qū)動(dòng)表是通過(guò)主鍵或者唯一二級(jí)索引列等值匹配的方式進(jìn)行訪問(wèn)的（如果該主鍵或者唯一二級(jí)索引是聯(lián)合索引的話，所有的索引列都必須進(jìn)行等值比較），則對(duì)該被驅(qū)動(dòng)表的訪問(wèn)方法就是eq_ref，比方說(shuō)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2?ON?t1.id?=?t2.id;

從執(zhí)行計(jì)劃的結(jié)果中可以看出，MySQL打算將t2作為驅(qū)動(dòng)表，t1作為被驅(qū)動(dòng)表，重點(diǎn)關(guān)注t1的訪問(wèn)方法是eq_ref，表明在訪問(wèn)t1表的時(shí)候可以通過(guò)主鍵的等值匹配來(lái)進(jìn)行訪問(wèn)。

ref

當(dāng)通過(guò)普通的二級(jí)索引列與常量進(jìn)行等值匹配時(shí)來(lái)查詢(xún)某個(gè)表，那么對(duì)該表的訪問(wèn)方法就可能是ref

相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前綴，索引要和某個(gè)值相比較，可能會(huì)找到多個(gè)符合條件的行。

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'a';

可以看到type列的值是ref，表明MySQL即將使用ref訪問(wèn)方法來(lái)執(zhí)行對(duì)t1表的查詢(xún)

system，const

mysql能對(duì)查詢(xún)的某部分進(jìn)行優(yōu)化并將其轉(zhuǎn)化成一個(gè)常量（可以看show warnings 的結(jié)果）。用于 primary key 或 unique key 的所有列與常數(shù)比較時(shí)，所以表最多有一個(gè)匹配行，讀取1次，速度比較快。system是const的特例，表里只有一條元組匹配時(shí)為system

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?id?=?5;

ref_or_null

當(dāng)對(duì)普通二級(jí)索引進(jìn)行等值匹配查詢(xún)，該索引列的值也可以是NULL值時(shí)，那么對(duì)該表的訪問(wèn)方法就可能是ref_or_null，比如說(shuō)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'a'?OR?key1?IS?NULL;

index_merge

一般情況下對(duì)于某個(gè)表的查詢(xún)只能使用到一個(gè)索引，但在某些場(chǎng)景下可以使用多種索引合并的方式來(lái)執(zhí)行查詢(xún)，我們看一下執(zhí)行計(jì)劃中是怎么體現(xiàn)MySQL使用索引合并的方式來(lái)對(duì)某個(gè)表執(zhí)行查詢(xún)的：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'a'?OR?key2?=?'a';

從執(zhí)行計(jì)劃的type列的值是index_merge就可以看出，MySQL打算使用索引合并的方式來(lái)執(zhí)行對(duì)t1表的查詢(xún)。

unique_subquery

類(lèi)似于兩表連接中被驅(qū)動(dòng)表的eq_ref訪問(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)行等值匹配的話，那么該子查詢(xún)執(zhí)行計(jì)劃的type列的值就是unique_subquery，比如下邊的這個(gè)查詢(xún)語(yǔ)句：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key2?IN?(SELECT?id?FROM?t2?where?t1.key1?=?t2.key1)?OR?key3?=?'a';

可以看到執(zhí)行計(jì)劃的第二條記錄的type值就是unique_subquery，說(shuō)明在執(zhí)行子查詢(xún)時(shí)會(huì)使用到id列的索引。

range

范圍掃描通常出現(xiàn)在 in(), between ,> ,<, >= 等操作中。使用一個(gè)索引來(lái)檢索給定范圍的行。

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?IN?('a',?'b',?'c');

index

當(dāng)我們可以使用索引覆蓋，但需要掃描全部的索引記錄時(shí)，該表的訪問(wèn)方法就是index

掃描全表索引，這通常比ALL快一些。（index是從索引中讀取的，而all是從硬盤(pán)中讀取）

ALL

最熟悉的全表掃描

mysql>?explain?select?*?from?t2;

一般來(lái)說(shuō)，這些訪問(wèn)方法按照我們介紹它們的順序性能依次變差。其中除了All這個(gè)訪問(wèn)方法外，其余的訪問(wèn)方法都能用到索引，除了index_merge訪問(wèn)方法外，其余的訪問(wèn)方法都最多只能用到一個(gè)索引。

possible_keys和key列

possible_keys列顯示查詢(xún)可能使用哪些索引來(lái)查找。

explain 時(shí)可能出現(xiàn) possible_keys 有列，而 key 顯示 NULL 的情況，這種情況是因?yàn)楸碇袛?shù)據(jù)不多，mysql認(rèn)為索引對(duì)此查詢(xún)幫助不大，選擇了全表查詢(xún)。

如果possible_keys列是NULL，則沒(méi)有相關(guān)的索引。在這種情況下，可以通過(guò)檢查 where 子句看是否可以創(chuàng)造一個(gè)適當(dāng)?shù)乃饕齺?lái)提高查詢(xún)性能，然后用 explain 查看效果。

key列顯示mysql實(shí)際采用哪個(gè)索引來(lái)優(yōu)化對(duì)該表的訪問(wèn)。如果沒(méi)有使用索引，則該列是 NULL。如果想強(qiáng)制mysql使用或忽視possible_keys列中的索引，在查詢(xún)中使用 force index、ignore index。

比方說(shuō)下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?>?'z'?AND?key2?=?'a';

上述執(zhí)行計(jì)劃的possible_keys列的值是idx_key1,idx_key2_key3，表示該查詢(xún)可能使用到idx_key1,idx_key2_key3兩個(gè)索引，然后key列的值是idx_key3，表示經(jīng)過(guò)查詢(xún)優(yōu)化器計(jì)算使用不同索引的成本后，最后決定使用idx_key3來(lái)執(zhí)行查詢(xún)比較劃算。

需要注意的一點(diǎn)是，possible_keys列中的值并不是越多越好，可能使用的索引越多，查詢(xún)優(yōu)化器計(jì)算查詢(xún)成本時(shí)就得花費(fèi)更長(zhǎng)時(shí)間，所以如果可以的話，盡量刪除那些用不到的索引。

key_len列

這一列顯示了mysql在索引里使用的字節(jié)數(shù)，通過(guò)這個(gè)值可以算出具體使用了索引中的哪些列

對(duì)于使用固定長(zhǎng)度類(lèi)型的索引列來(lái)說(shuō)，它實(shí)際占用的存儲(chǔ)空間的最大長(zhǎng)度就是該固定值，對(duì)于指定字符集的變長(zhǎng)類(lèi)型的索引列來(lái)說(shuō)，比如某個(gè)索引列的類(lèi)型是VARCHAR(100)，使用的字符集是utf8，那么該列實(shí)際占用的最大存儲(chǔ)空間就是100 × 3 = 300個(gè)字節(jié)。

如果該索引列可以存儲(chǔ)NULL值，則key_len比不可以存儲(chǔ)NULL值時(shí)多1個(gè)字節(jié)。

對(duì)于變長(zhǎng)字段來(lái)說(shuō)，都會(huì)有2個(gè)字節(jié)的空間來(lái)存儲(chǔ)該變長(zhǎng)列的實(shí)際長(zhǎng)度。

當(dāng)字符串過(guò)長(zhǎng)時(shí)，mysql會(huì)做一個(gè)類(lèi)似左前綴索引的處理，將前半部分的字符提取出來(lái)做索引。

key_len計(jì)算規(guī)則如下：字符串 char(n)：n字節(jié)長(zhǎng)度 varchar(n)：2字節(jié)存儲(chǔ)字符串長(zhǎng)度，如果是utf-8，則長(zhǎng)度 3n + 2 數(shù)值類(lèi)型 tinyint：1字節(jié) smallint：2字節(jié) int：4字節(jié) bigint：8字節(jié)　　 時(shí)間類(lèi)型　 date：3字節(jié) timestamp：4字節(jié) datetime：8字節(jié)

比如下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?s1?WHERE?id?=?5;

由于id列的類(lèi)型是INT，并且不可以存儲(chǔ)NULL值，所以在使用該列的索引時(shí)key_len大小就是4。

對(duì)于可變長(zhǎng)度的索引列來(lái)說(shuō)，比如下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'a';

由于key1列的類(lèi)型是VARCHAR(100)，所以該列實(shí)際最多占用的存儲(chǔ)空間就是300字節(jié)，又因?yàn)樵摿性试S存儲(chǔ)NULL值，所以key_len需要加1，又因?yàn)樵摿惺强勺冮L(zhǎng)度列，所以key_len需要加2，所以最后ken_len的值就是303。

rows列

這一列是mysql估計(jì)要讀取并檢測(cè)的行數(shù)，注意這個(gè)不是結(jié)果集里的行數(shù)。

如果查詢(xún)優(yōu)化器決定使用全表掃描的方式對(duì)某個(gè)表執(zhí)行查詢(xún)時(shí)，執(zhí)行計(jì)劃的rows列就代表預(yù)計(jì)需要掃描的行數(shù)，如果使用索引來(lái)執(zhí)行查詢(xún)時(shí)，執(zhí)行計(jì)劃的rows列就代表預(yù)計(jì)掃描的索引記錄行數(shù)。比如下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?>?'a';

我們看到執(zhí)行計(jì)劃的rows列的值是113，這意味著查詢(xún)優(yōu)化器在經(jīng)過(guò)分析使用idx_key1進(jìn)行查詢(xún)的成本之后，覺(jué)得滿足key1 > 'a'這個(gè)條件的記錄只有113條。

ref列

這一列顯示了在key列記錄的索引中，表查找值所用到的列或常量，常見(jiàn)的有：const（常量），字段名（例：t1.id）

ref列展示的就是與索引列作等值匹配的值什么，比如只是一個(gè)常數(shù)或者是某個(gè)列。大家看下邊這個(gè)查詢(xún)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?=?'a';

可以看到ref列的值是const，表明在使用idx_key1索引執(zhí)行查詢(xún)時(shí)，與key1列作等值匹配的對(duì)象是一個(gè)常數(shù)，當(dāng)然有時(shí)候更復(fù)雜一點(diǎn)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2?ON?t1.id?=?t2.id;

可以看到對(duì)被驅(qū)動(dòng)表t1的訪問(wèn)方法是eq_ref，而對(duì)應(yīng)的ref列的值是canal_manager.t2.id，這說(shuō)明在對(duì)被驅(qū)動(dòng)表進(jìn)行訪問(wèn)時(shí)會(huì)用到PRIMARY索引，也就是聚簇索引與一個(gè)列進(jìn)行等值匹配的條件，于t2表的id作等值匹配的對(duì)象就是canal_manager.t2.id列（注意這里把數(shù)據(jù)庫(kù)名也寫(xiě)出來(lái)了）。

有的時(shí)候與索引列進(jìn)行等值匹配的對(duì)象是一個(gè)函數(shù)，比方說(shuō)下邊這個(gè)查詢(xún)

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2?ON?t2.key1?=?UPPER(t1.key1);

我們看執(zhí)行計(jì)劃的第二條記錄，可以看到對(duì)t2表采用ref訪問(wèn)方法執(zhí)行查詢(xún)，然后在查詢(xún)計(jì)劃的ref列里輸出的是func，說(shuō)明與t2表的key1列進(jìn)行等值匹配的對(duì)象是一個(gè)函數(shù)。

Extra列

顧名思義，Extra列是用來(lái)說(shuō)明一些額外信息的，我們可以通過(guò)這些額外信息來(lái)更準(zhǔn)確的理解MySQL到底將如何執(zhí)行給定的查詢(xún)語(yǔ)句。

Using index

查詢(xún)的列被索引覆蓋，并且where篩選條件是索引的前導(dǎo)列，是性能高的表現(xiàn)。一般是使用了覆蓋索引(索引包含了所有查詢(xún)的字段)。對(duì)于innodb來(lái)說(shuō)，如果是輔助索引性能會(huì)有不少提高

mysql>?EXPLAIN?SELECT?key1?FROM?t1?WHERE?key1?=?'a';

Using where

當(dāng)我們使用全表掃描來(lái)執(zhí)行對(duì)某個(gè)表的查詢(xún)，并且該語(yǔ)句的WHERE子句中有針對(duì)該表的搜索條件時(shí)，在Extra列中會(huì)提示上述額外信息。比如下邊這個(gè)查詢(xún)

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?name=?'a1b6cee57a';

Using where Using index

查詢(xún)的列被索引覆蓋，并且where篩選條件是索引列之一但是不是索引的前導(dǎo)列，意味著無(wú)法直接通過(guò)索引查找來(lái)查詢(xún)到符合條件的數(shù)據(jù)

mysql>?EXPLAIN?SELECT?id?FROM?t1?WHERE?key3=?'a1b6cee57a';

NULL

查詢(xún)的列未被索引覆蓋，并且where篩選條件是索引的前導(dǎo)列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過(guò)“回表”來(lái)實(shí)現(xiàn)，不是純粹地用到了索引，也不是完全沒(méi)用到索引

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key2=?'a1b6cee57a';

Using index condition

與Using where類(lèi)似，查詢(xún)的列不完全被索引覆蓋，where條件中是一個(gè)前導(dǎo)列的范圍；

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?key1?like?'1';

Using temporary

在許多查詢(xún)的執(zhí)行過(guò)程中，MySQL可能會(huì)借助臨時(shí)表來(lái)完成一些功能，比如去重、排序之類(lèi)的，比如我們?cè)趫?zhí)行許多包含DISTINCT、GROUP BY、UNION等子句的查詢(xún)過(guò)程中，如果不能有效利用索引來(lái)完成查詢(xún)，MySQL很有可能尋求通過(guò)建立內(nèi)部的臨時(shí)表來(lái)執(zhí)行查詢(xún)。如果查詢(xún)中使用到了內(nèi)部的臨時(shí)表，在執(zhí)行計(jì)劃的Extra列將會(huì)顯示Using temporary提示，比方說(shuō)這樣：

name沒(méi)有索引，此時(shí)創(chuàng)建了張臨時(shí)表來(lái)distinct

mysql>?explain?select?distinct?name?from?t1;

key1建立了idx_key1索引，此時(shí)查詢(xún)時(shí)extra是using index,沒(méi)有用臨時(shí)表

mysql>?explain?select?distinct?key1?from?t1;

Using filesort

mysql 會(huì)對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按索引次序從表里讀取行。此時(shí)mysql會(huì)根據(jù)聯(lián)接類(lèi)型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行信息。這種情況下一般也是要考慮使用索引來(lái)優(yōu)化的。

name未創(chuàng)建索引，會(huì)瀏覽t1整個(gè)表，保存排序關(guān)鍵字name和對(duì)應(yīng)的id，然后排序name并檢索行記錄

mysql>?explain?select?*?from?t1?order?by?name;

key1建立了idx_key1索引,此時(shí)查詢(xún)時(shí)extra是using index

mysql>?explain?select?*?from?t1?order?by?key1;

Using join buffer (Block Nested Loop)

在連接查詢(xún)執(zhí)行過(guò)程中，當(dāng)被驅(qū)動(dòng)表不能有效的利用索引加快訪問(wèn)速度，MySQL一般會(huì)為其分配一塊名叫join buffer的內(nèi)存塊來(lái)加快查詢(xún)速度，也就是我們所講的基于塊的嵌套循環(huán)算法，比如下邊這個(gè)查詢(xún)語(yǔ)句：

mysql>?EXPLAIN?SELECT?*?FROM?t1?INNER?JOIN?t2?ON?t1.key3?=?t2.key3;

No tables used

當(dāng)查詢(xún)語(yǔ)句的沒(méi)有FROM子句時(shí)將會(huì)提示該額外信息，比如：

mysql>?EXPLAIN?SELECT?1;

Impossible WHERE

查詢(xún)語(yǔ)句的WHERE子句永遠(yuǎn)為FALSE時(shí)將會(huì)提示該額外信息，比方說(shuō)：

mysql>?EXPLAIN?SELECT?*?FROM?t1?WHERE?1?!=?1;

參考：

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

戳原文，獲取精選面試題！