一文帶你快速看懂 MySQL 執行計劃

通常查詢慢查詢SQL語句時會使用EXPLAIN指令來查看SQL語句的執行計劃，透過傳回的訊息，可以了解到Mysql優化器是如何執行SQL語句，透過分析可以幫助我們提供最佳化的思路。

1. Explain 作用

explain 指令主要用於檢視SQL 語句的執行計劃，該指令可以模擬最佳化器執行SQL 查詢語句，可以幫助我們編寫和優化SQL。那麼 explain 具體可以提供哪些訊息，幫助我們如何去優化 SQl 的呢？

• 表格的讀取順序

• 資料讀取操作的運算類型

• 哪些索引可以使用

• 哪些索引被實際使用

• #表之間的引用

• 每張表格有多少行被最佳化器查詢

2. Explain 如何使用

使用方式： explain 待執行的sql

　　explain 會傳回一個待執行SQL 的執行計劃列表，列表包含了12 個字段，字段共同描述了SQL 在執行計劃中將會採取何種方式執行。以下清單詳細描述了執行計劃表的欄位意義：

##partitions表分區type存取類型#possible_keyskeykey_lenref#rowsfilteredExtra

欄位名稱	描述
#id	執行select 語句查詢的序號，決定表的讀取順序
select_type	查詢的類型，也就是資料讀取操作的操作類型
table	查詢的表名
##可使用的索引。查詢所涉及的欄位上若存在索引，則該索引將被列出，但不一定被查詢實際使用到。如果這個字段為null 但是字段key 不為null，這種情況就是在查找時沒有可以使用的二級索引樹，但是二級索引中包含了需要查詢的字段，於是就不再查找聚簇索引（聚簇索引比較大），轉而掃描這個二級索引樹（二級索引樹比較小），並且此時一般訪問類型type 為index，及掃描整棵索引樹。
實際掃描使用的索引。如果為null，則沒有使用索引；查詢中若使用了覆蓋索引，則該索引僅出現在key清單中；
#索引中使用的位元組數。可透過此列計算查詢中使用的索引的長度，在不損失精確性的情況下，長度越短越好；key_len 顯示的值為索引欄位的最大可能長度，並非實際使用長度，即key_len 是根據表定義計算而得，不是透過表格內檢索出的；
#顯示索引的哪一列被使用了。如果可能的話，是一個常數，哪些列或常數別用於查找索引列上的值；
根據表統計資訊及索引選用情況，大致估算出找到所需的記錄所需讀取的行數；
搜尋條件過濾後剩餘資料的百分比。
包含不適合在其它列中顯示但十分重要的額外資訊

3. 關鍵字段分析

（1）id

#執行select 語句查詢的序號，包含一組數字，表示查詢中執行select子句或操作表的順序，它有三種情況：

類型名稱id相同#id不同id相同不同，同時存在#

（2）select_type

就是資料讀取運算的運算類型，他一共有以下幾種：

描述
執行順序由上到下
如果是子查詢，id 的序號會遞增， id 值越大優先順序越高,越先被執行
如果id 相同，可以認為是一組，從上往下順序執行，在所有群組中，id值越大，優先權越高，越先執行

類型名稱	描述
simple	簡單的select 查詢，查詢中不包含子查詢或union；
primary	查詢中若包含任何複雜的子查詢，最外層查詢則被標記；
subquery	在select 或where 清單中包含了子查詢；
#dependent subquery	子查詢中的第一個SELECT， 取決於外面的查詢。即子查詢依賴外層查詢的結果。
derived	在from 清單中包含的子查詢被標記為DERIVED（衍生表），mysql 會遞歸執行這些子查詢，把結果放到臨時表中;
union	若第二個select 出現在union 之後，則被標記為union，若union 包含在from 子句的子查詢中，外層select 將被標記為DERIVED；
union result	從union 表（即union 合併的結果集）中取得select 查詢的結果；
meterialized	物化表，當子查詢關聯查詢時，子查詢結果儲存在物化暫存表，然後根據暫存表中的資料去主表匹配。
dependent union	UNION 中的第二個或後面的查詢語句，取決於外面的查詢

（3）table

顯示的查詢表名，如果查詢使用了別名，那麼這裡顯示的是別名，如果不涉及對資料表的操作，那麼這顯示為null，也可以是以下之一：

NN#>N

#類型名稱	#描述
>表示這個是臨時表，後邊的N就是執行計畫中的id，表示結果來自於這個查詢產生。	M,
與N> ;類似，也是臨時表，表示這個結果來自於union 查詢的id 為M,N 的結果集。

>######該行是指與物化子查詢該行的結果 id 的值 N。 ############

（4）partitions

查詢將符合記錄的分割區。該值NULL用於非分割表。

（5）type

依序從好到差：

system>const >eq_ref>ref>ref_or_null>range>#index> ALL

除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一個索引。

我們自己建立一系列表格來實驗下：

#

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for goods
-- ----------------------------
DROP TABLE IF EXISTS `goods`;
CREATE TABLE `goods`  (
  `id` int(11) NOT NULL,
  `sn` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of goods
-- ----------------------------
INSERT INTO `goods` VALUES (1, 'sn123456', '衣服');

-- ----------------------------
-- Table structure for sku
-- ----------------------------
DROP TABLE IF EXISTS `sku`;
CREATE TABLE `sku`  (
  `id` int(11) NOT NULL,
  `goods_id` int(11) NOT NULL,
  `status` int(11) NOT NULL,
  `deleted` int(11) NOT NULL,
  `barcode` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE INDEX `index_2`(`name`) USING BTREE,
  INDEX `index_1`(`goods_id`, `status`, `deleted`, `barcode`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sku
-- ----------------------------
INSERT INTO `sku` VALUES (1, 1, 1, 0, 'kt123456', '黑色');

SET FOREIGN_KEY_CHECKS = 1;

##system

#表格只有一行記錄（等於系統表），這是const 類型的特例，平時不會出現，這個也可忽略不計；

const

表示透過索引一次就找到了，const 用來比較primary key 或unique 索引。因為只匹配一行記錄，所以很快。如果將主鍵置於where 清單中，mysql 就能將該查詢轉換成一個常數；

EXPLAIN SELECT * FROM sku WHERE id=1;复制代码

##eq_ref

#唯一性索引掃描，對於每一個索引鍵，表中只有一筆記錄與之匹配，常用於主鍵或唯一索引掃描；此類型通常出現在多表的join 等值查詢，表示對於前表的每一個結果，都只能配對到後表的一行結果，查詢效率較高。

EXPLAIN SELECT * FROM sku,goods WHERE sku.goods_id=goods.id;

ref

非唯一性索引掃描，傳回符合某個單獨值得所有行，本質上也是一種索引訪問，它返回所有匹配某個單獨值的行，然而，它可能會找到多個符合條件的行，所以它應該屬於查找和掃描的混合體；

EXPLAIN SELECT * FROM sku WHERE goods_id=1;

#ref_or_null

二級索引等值比較同時限定is null 。

EXPLAIN SELECT * FROM sku WHERE name='123456' or name IS NULL;

range

只檢索給定範圍的行，使用一個索引來選擇行，key列顯示使用哪個索引，一般就是在你的where 語句中出現了between、、in 等的查詢；這種範圍索引掃描比全表掃描要好，因為它只需要開始於索引的某一個點，結束於另一個點，不用掃描全部索引；

EXPLAIN SELECT * FROM sku WHERE id BETWEEN 1 and 10;

index

#index 和all 區別為index 類型只遍歷索引樹，這通常比all 快，因為索引檔案通常比資料檔案小；也就是說雖然all 和index 都是讀寫表，但index 是從索引中讀取的，而all 是從硬碟中讀的；

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

all

也就是全表掃描；

EXPLAIN SELECT * FROM sku WHERE deleted=0;

（6）possible_keys

查詢可能使用到的索引都會在這裡列出來。

（7）key

當查詢真正使用到的索引，

select_type

為index_merge時，這裡可能出現兩個以上的索引，其他的select_type這裡只會出現一個。

（8）key_len

key_len 表示該列計算查詢中所使用的索引的長度。例如：

SELECT * FROM table where age = 1 and name like 'xx'

，假設 age 是 int 類型且不可為 null；name 是 varchar(20) 類型且可以為 null，編碼為 utf8。若以這兩個欄位為索引查詢，那麼 key_len 的值為 4 3 * 20 2 1 = 67。具體計算規則如下表所示：<table> <thead><tr class="firstRow"> <th>值類型</th> <th>值名稱</th> <th>描述</th> </tr></thead> <tbody> <tr> <td>字串</td> <td>CHAR(n)</td> <td>n 位元組長度</td> </tr> <tr> <td><br></td> <td>VARCHAR(n)</td> <td>如果是utf8 編碼，則是3 n 2位元組;；如果是utf8mb4 編碼，則是4 n 2 位元組。 </td> </tr> <tr> <td>數值類型</td> <td>TINYINT</td> <td>1位元組</td> </tr> <tr> <td><br></td> <td></td> <td></td>1位元組</tr> <tr> <td><br></td> <td></td>SMALLINT<td></td>2位元組</tr> <tr> <td><br></td> <td></td>#MEDIUMINT<td></td>3位元組</tr> <tr> <td><br></td> <td>##INT</td> <td>4位元組</td> </tr> <tr> <td></td> <td></td>BIGINT<td></td>8位元組</tr> <tr> <td> <br>時間類型</td> <td>DATE</td> <td>#3位元組</td> </tr> <tr> <td><br></td> <td>TIMESTAMP</td> <td>4位元組</td> </tr> <tr> <td></td> <td></td>#DATETIME<td> <br>#8位元組</td> </tr> </tbody> </table>欄位屬性######NULL 屬性佔用一個位元組。如果一個欄位是 NOT NULL 的， 則不佔用。 #####################<h3 id="strong-ref-strong"><strong>（9）ref</strong></h3> <p>如果是使用的常數等值查詢，這裡會顯示<code>const，如果是連接查詢，被驅動表的執行計劃這裡會顯示驅動表的關聯字段，如果是條件使用了表達式或函數，或者條件列發生了內部隱式轉換，這裡可能顯示為func。

（10）rows

這裡是執行計畫中估算的掃描行數，不是精確值。

（11）filtered

使用explain extended時會出現這個列，5.7之後的版本預設就有這個字段，不需要使用explain extended了。這個欄位表示儲存引擎傳回的資料在server層過濾後，剩下多少滿足查詢的記錄數量的比例，注意是百分比，不是具體記錄數。

（12）Extra

這個欄位可以顯示的資訊非常多，有數十種，常用的有：

1 、distinct：在select部分使用了distinct關鍵字

2、no tables used：不帶from 字句的查詢或From dual查詢。使用not in()形式子查詢或not exists()運算子的連接查詢，這種叫做反連接。即，一般連接查詢是先查詢內表，再查詢外表，反連接就是先查詢外表，再查詢內表。

3、using filesort：說明mysql會對資料使用一個外部的索引排序，而不是依照表內的索引順序讀取。 mysql中無法利用索引完成的排序操作稱為「檔案排序」。排序時無法使用到索引時，就會出現這個。常見於order by語句中，需要盡快優化

4、using index：查詢時不需要回表查詢，直接透過索引就可以取得查詢的數據。

5、using join buffer（block nested loop），using join buffer（batched key accss） ：5.6.x之後的版本最佳化關聯查詢的 BNL，BKA特性。主要是減少內表的循環數量以及比較順序地掃描查詢。

6、using sort_union，using_union，using intersect，using sort_intersection：

• using intersect：表示使用and的各個索引的條件時，該資訊表示是從處理結果取得交集
• using union：表示使用or連接各個使用索引的條件時，該資訊表示從處理結果取得並集
• using sort_union和using sort_intersection：與前面兩個對應的類似，只是他們是出現在用and和or查詢資訊量大時，先查詢主鍵，然後進行排序合併後，才能讀取記錄並回傳。

7、using temporary：表示使用了暫存表儲存中間結果。臨時表可以是記憶體臨時表和磁碟臨時表，執行計劃中看不出來，需要查看status變量，used_tmp_table，used_tmp_disk_table才能看出來。常見於order by和分組查詢group by。 group by一定要遵循所建索引的順序與個數。需要盡快最佳化

8、using where：表示儲存引擎傳回的記錄並不是所有的都符合查詢條件，需要在server層進行篩選。查詢條件中分為限制條件和檢查條件，5.6之前，儲存引擎只能根據限制條件掃描資料並返回，然後server層根據檢查條件進行過濾再返回真正符合查詢的資料。 5.6.x之後支援ICP特性(index condition pushdown，索引下推)，可以把檢查條件也下推到儲存引擎層，不符合檢查條件和限制條件的數據，直接不讀取，這樣就大大減少了儲存引擎掃描的記錄數量。 extra列顯示using index condition

#9、firstmatch(tb_name) ：5.6.x#開始引入的最佳化子查詢的新特性之一，常見於where字句含有in()類型的子查詢。如果內表的資料量比較大，就可能出現這個

10、loosescan(m..n) ：5.6.x之後引進的最佳化子查詢的新特性之一，在in()類型的子查詢中，當子查詢傳回的可能有重複記錄時，就可能出現這個

4. Explain 主要關注點

總的來說，我們只需要專注在結果中的幾列：

列名	備註
type	本次查詢表聯結類型，從這裡可以看到本次查詢大概的效率
key	最終選擇的索引，如果沒有索引的話，本次查詢效率通常很差
key_len	本次查詢用於結果篩選的索引實際長度
rows	預計需要掃描的記錄數，預計需要掃描的記錄數越小越好
#Extra	額外附加資訊，主要確認是否出現Using filesort、Using temporary這兩種情況

再來看下Extra列中需要注意出現的幾種情況：

##對##Select tables optimized awayMIN()\MAX ()【相關推薦：

關鍵字	備註
Using filesort	將用外部排序而不是按照索引順序排列結果，資料較少時從記憶體排序，否則需要在磁碟完成排序，代價非常高，需要添加合適的索引
#Using temporary	需要建立一個暫存表來儲存結果，這通常發生在對沒有索引的資料列進行GROUP BY時，或 ORDER BY裡的列不都在索引裡，需要加入適當的索引
Using index	表示MySQL 使用覆蓋索引避免全表掃描，不需要再到表中進行二次查找數據，這是比較好的結果之一。注意不要和type中的index類型混淆
#Using where	通常是進行了全表/全索引掃描後再用WHERE子句完成結果過濾，需要加入適當的索引
Impossible WHERE	Where子句判斷的結果總是false而不能選擇任何數據，例如where 1=0，無須過度關注
使用某些聚合函數來存取存在索引的某個欄位時，最佳化器會透過索引直接一次定位到所需的資料行完成整個查詢，例如	，這種也是比較好的結果之一

mysql影片教學

】

以上是一文帶你快速看懂 MySQL 執行計劃的詳細內容。更多資訊請關注PHP中文網其他相關文章！