一、背景

在我們?nèi)粘９ぷ髦?#xff0c;我們可能會(huì)遇到一些慢SQL語句或者要對(duì)一些SQL進(jìn)行性能優(yōu)化，那么就需要使用explain對(duì)SQL進(jìn)行執(zhí)行計(jì)劃分析了。Mysql中的執(zhí)行計(jì)劃可以通過EXPLAIN或DESCRIBE關(guān)鍵字獲取，當(dāng)我們拿到執(zhí)行計(jì)劃后可以幫助我們分析這條sql執(zhí)行的性能瓶頸在哪里。

例如，我有一張user表，我想分析一下查詢的執(zhí)行計(jì)劃。

SELECT * FROM user WHERE age > 28;

可以使用EXPLAIN來獲取這條sql語句的執(zhí)行計(jì)劃。

EXPLAIN SELECT * FROM user WHERE age > 28;

通過執(zhí)行上面的EXPLAIN語句后，我們就能拿到這條sql的執(zhí)行計(jì)劃了。

+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table| partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user | NULL       | range| age           | age  | 5       | NULL |    50|    33.33 | Using where |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------------+

接下來我們就可以對(duì)執(zhí)行計(jì)劃進(jìn)行分析了。

二、執(zhí)行計(jì)劃分析

從上面的執(zhí)行計(jì)劃可以看到，一共有12個(gè)字段，我們先對(duì)這些字段依次介紹一下。

1. id?

執(zhí)行計(jì)劃中每個(gè)操作的唯一標(biāo)識(shí)，對(duì)于執(zhí)行計(jì)劃中的每條sql，可能會(huì)有多個(gè)操作，每個(gè)操作都有一個(gè)唯一的id。

2. select_type

操作類型，一共包含一項(xiàng)幾種類型：

• SIMPLE：表示這個(gè)查詢是最簡(jiǎn)單的形式，不包含任何的子查詢或聯(lián)合查詢。
• PRIMARY：表示查詢中的最外層或最頂層SELECT，它內(nèi)部可能會(huì)包含嵌套的子查詢或其它復(fù)雜構(gòu)造。
• SUBQUERY：表示這種類型的SELECT出現(xiàn)在了另外一個(gè)查詢的from字句或where字句查詢中，作為獨(dú)立的查詢被執(zhí)行。
• DEPENDENT SUBQUERY：類似于SUBQUERY，這個(gè)子查詢的執(zhí)行依賴于外部查詢的某一行，它會(huì)為外部查詢的每一行執(zhí)行一次。
• DERIVED：表示MySQL需要?jiǎng)?chuàng)建一張臨時(shí)表來存儲(chǔ)子查詢的結(jié)果。
• UNCACHEABLE SUBQUERY：對(duì)于不能緩存結(jié)果的子查詢，MySQL不能將它的值計(jì)算出來重復(fù)使用，而是在外部查詢的時(shí)候每次都需要重新計(jì)算。
• UNION ：出現(xiàn)在UNION查詢中的第二個(gè)或后續(xù)的查詢語句。
• UNION RESULT：用于合并UNION查詢的結(jié)果集，不是實(shí)際的查詢操作。
• MATERIALIZED：Mysql8.0引入的新類型，表示子查詢結(jié)果被物化為臨時(shí)表，以便重復(fù)使用。

3. table

當(dāng)前操作鎖涉及的表。

4.?partitions?

當(dāng)前操作所涉及的分區(qū)。

5.?type

表示MySQL在執(zhí)行查詢時(shí)所采用的檢索方式，他是衡量查詢性能的重要指標(biāo)之一。以下時(shí)常用的類型和含義：

• system：系統(tǒng)表，數(shù)據(jù)量很少，往往不需要進(jìn)行磁盤IO。
• const：表中僅有一行數(shù)據(jù)匹配，使用主鍵查詢或唯一索引查詢。
• eq_ref：使用主鍵或者唯一約束列，進(jìn)行關(guān)聯(lián)查詢時(shí)使用。
• ref：非唯一索引掃描，只會(huì)掃描索引樹中的一部分來查找匹配的行，使用非唯一索引進(jìn)行查詢時(shí)使用。
• range：范圍掃描，使用索引進(jìn)行范圍查詢，只會(huì)掃描索引樹中的一個(gè)范圍來查找匹配的行。
• index：全索引掃描，會(huì)遍歷索引樹來查找匹配的行。通常時(shí)不符合最左匹配的查詢。
• all：全表掃描，當(dāng)使用非索引字段查詢時(shí)，將會(huì)遍歷全表來找到匹配的行。

故以上類型執(zhí)行效率由高到低：system > const >?eq_ref >?ref >?range >?index >?all

6.?possible_keys?

表示查詢中可以使用的索引，不一定實(shí)際使用了這些索引。這個(gè)字段列出了可能用于這個(gè)查詢的所有索引，也包括聯(lián)合索引。

7. key

表示實(shí)際查詢使用的索引。

8. key_len

表示索引的長度，索引的長度越短，查詢時(shí)的效率越高。

9. ref

用來表示哪些列或常量被用來與key列中命名的索引進(jìn)行比較。

10. rows

表示操作需要掃描的行數(shù)，也就是說需要掃描表中多少行才能得到結(jié)果。

11. filtered

表示本次操作過濾掉的行數(shù)占掃描行數(shù)的百分比。值越大，則查詢結(jié)果越準(zhǔn)確。

12. extra

這個(gè)字段經(jīng)常會(huì)被忽略，其實(shí)也很重要。這個(gè)字段表示MySQL在執(zhí)行查詢時(shí)所作的一些附加操作。下面是一些常見的extra類型及其含義：

• Using where：表示查詢的列未被索引覆蓋，或where篩選條件非索引列，或者where篩選條件非索引的前導(dǎo)列。
• Using index：本次查詢使用了索引覆蓋，只需要掃描索引，無需回表。
• Using index condition：表示本次查詢?cè)谒饕蠄?zhí)行了部分條件過濾。
• Using where;Using index：查詢列被索引覆蓋，并且where條件中使用了索引列，但不是索引的前導(dǎo)列。或者where條件是索引前導(dǎo)列的一個(gè)范圍。通常是未遵循最左匹配原則。
• Using filesort：表示MySQL將使用文件排序，而不是索引排序，通常發(fā)生在無法使用索引來進(jìn)行排序。我們應(yīng)當(dāng)盡量避免這種情況。

三、總結(jié)

通過對(duì)執(zhí)行計(jì)劃的各個(gè)字段進(jìn)行了分析和說明，我們?cè)谶M(jìn)行sql優(yōu)化的時(shí)候，盡可能使用最優(yōu)的方式來提高性能。