如何快速看懂MySQL執(zhí)行計(jì)劃

136次閱讀

共計(jì) 7357 個(gè)字符，預(yù)計(jì)需要花費(fèi) 19 分鐘才能閱讀完成。

這篇文章主要介紹了如何快速看懂 MySQL 執(zhí)行計(jì)劃的相關(guān)知識(shí)，內(nèi)容詳細(xì)易懂，操作簡(jiǎn)單快捷，具有一定借鑒價(jià)值，相信大家閱讀完這篇如何快速看懂 MySQL 執(zhí)行計(jì)劃文章都會(huì)有所收獲，下面我們一起來看看吧。

通常查詢慢查詢 SQL 語句時(shí)會(huì)使用 EXPLAIN 命令來查看 SQL 語句的執(zhí)行計(jì)劃，通過返回的信息，可以了解到 Mysql 優(yōu)化器是如何執(zhí)行 SQL 語句，通過分析可以幫助我們提供優(yōu)化的思路。

1. Explain 作用

explain 命令主要用于查看 SQL 語句的執(zhí)行計(jì)劃，該命令可以模擬優(yōu)化器執(zhí)行 SQL 查詢語句，可以幫助我們編寫和優(yōu)化 SQL。那么 explain 具體可以提供哪些信息，幫助我們?nèi)绾稳?yōu)化 SQl 的呢？

表的讀取順序

數(shù)據(jù)讀取操作的操作類型

哪些索引可以使用

哪些索引被實(shí)際使用

表之間的引用

每張表有多少行被優(yōu)化器查詢

2. Explain 如何使用

使用方式：explain + 待執(zhí)行的 sql

explain 會(huì)返回一個(gè)待執(zhí)行 SQL 的執(zhí)行計(jì)劃列表，列表包含了 12 個(gè)字段，字段共同描述了 SQL 在執(zhí)行計(jì)劃中將會(huì)采取何種方式執(zhí)行。以下列表詳細(xì)描述了執(zhí)行計(jì)劃表的字段含義：

字段名稱描述 id 執(zhí)行 select 語句查詢的序列號(hào)，決定表的讀取順序 select_type 查詢的類型，也就是數(shù)據(jù)讀取操作的操作類型 table 查詢的表名 partitions 表分區(qū) type 訪問類型 possible_keys 可使用的索引。查詢涉及到的字段上若存在索引，則該索引將被列出，但不一定被查詢實(shí)際使用到。如果這個(gè)字段為 null 但是字段 key 不為 null，這種情況就是在查找時(shí)沒有可以使用的二級(jí)索引樹，但是二級(jí)索引中包含了需要查詢的字段，于是就不再查找聚簇索引（聚簇索引比較大），轉(zhuǎn)而掃描這個(gè)二級(jí)索引樹（二級(jí)索引樹比較小），并且此時(shí)一般訪問類型 type 為 index，及掃描整棵索引樹。key 實(shí)際掃描使用的索引。如果為 null，則沒有使用索引；查詢中若使用了覆蓋索引，則該索引僅出現(xiàn)在 key 列表中；key_len 索引中使用的字節(jié)數(shù)。可通過該列計(jì)算查詢中使用的索引的長(zhǎng)度，在不損失精確性的情況下，長(zhǎng)度越短越好；key_len 顯示的值為索引字段的最大可能長(zhǎng)度，并非實(shí)際使用長(zhǎng)度，即 key_len 是根據(jù)表定義計(jì)算而得，不是通過表內(nèi)檢索出的；ref 顯示索引的哪一列被使用了。如果可能的話，是一個(gè)常數(shù)，哪些列或常量別用于查找索引列上的值；rows 根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需要讀取的行數(shù)；filtered 搜索條件過濾后剩余數(shù)據(jù)的百分比。Extra 包含不適合在其它列中顯示但十分重要的額外信息 3. 關(guān)鍵字段分析（1）id

執(zhí)行 select 語句查詢的序列號(hào)，包含一組數(shù)字，表示查詢中執(zhí)行 select 子句或操作表的順序，它有三種情況：

類型名稱描述 id 相同執(zhí)行順序由上至下 id 不同如果是子查詢，id 的序號(hào)會(huì)遞增，id 值越大優(yōu)先級(jí)越高, 越先被執(zhí)行 id 相同不同，同時(shí)存在如果 id 相同，可以認(rèn)為是一組，從上往下順序執(zhí)行，在所有組中，id 值越大，優(yōu)先級(jí)越高，越先執(zhí)行（2）select_type

就是數(shù)據(jù)讀取操作的操作類型，他一共有以下幾種：

類型名稱描述 simple 簡(jiǎn)單的 select 查詢，查詢中不包含子查詢或者 union；primary 查詢中若包含任何復(fù)雜的子查詢，最外層查詢則被標(biāo)記；subquery 在 select 或者 where 列表中包含了子查詢；dependent subquery 子查詢中的第一個(gè) SELECT，取決于外面的查詢。即子查詢依賴于外層查詢的結(jié)果。derived 在 from 列表中包含的子查詢被標(biāo)記為 DERIVED（衍生表），mysql 會(huì)遞歸執(zhí)行這些子查詢，把結(jié)果放臨時(shí)表中；union 若第二個(gè) select 出現(xiàn)在 union 之后，則被標(biāo)記為 union，若 union 包含在 from 子句的子查詢中，外層 select 將被標(biāo)記為 DERIVED；union result 從 union 表（即 union 合并的結(jié)果集）中獲取 select 查詢的結(jié)果；meterialized 物化表，子查詢關(guān)聯(lián)查詢時(shí)，子查詢結(jié)果存儲(chǔ)在物化臨時(shí)表，然后根據(jù)臨時(shí)表中的數(shù)據(jù)去主表匹配。dependent unionUNION 中的第二個(gè)或后面的查詢語句，取決于外面的查詢（3）table

顯示的查詢表名，如果查詢使用了別名，那么這里顯示的是別名，如果不涉及對(duì)數(shù)據(jù)表的操作，那么這顯示為 null，也可以是以下之一：

類型名稱描述 derivedN 表示這個(gè)是臨時(shí)表，后邊的 N 就是執(zhí)行計(jì)劃中的 id，表示結(jié)果來自于這個(gè)查詢產(chǎn)生。unionM,N 與 derivedN 類似，也是一個(gè)臨時(shí)表，表示這個(gè)結(jié)果來自于 union 查詢的 id 為 M,N 的結(jié)果集。subqueryN 該行是指與物化子查詢?cè)撔械慕Y(jié)果 id 的值 N。（4）partitions

查詢將匹配記錄的分區(qū)。該值 NULL 用于非分區(qū)表。

（5）type

依次從好到差：

system const eq_ref ref ref_or_null range index ALL

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

我們自己創(chuàng)建一系列表來實(shí)驗(yàn)下：

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

表只有一行記錄（等于系統(tǒng)表），這是 const 類型的特例，平時(shí)不會(huì)出現(xiàn)，這個(gè)也可忽略不計(jì)；

const

表示通過索引一次就找到了，const 用于比較 primary key 或者 unique 索引。因?yàn)橹黄ヅ湟恍杏涗洠院芸臁Ｈ绻麑⒅麈I置于 where 列表中，mysql 就能將該查詢轉(zhuǎn)換成一個(gè)常量；

EXPLAIN SELECT * FROM sku WHERE id=1; 復(fù)制代碼

eq_ref

唯一性索引掃描，對(duì)于每一個(gè)索引鍵，表中只有一條記錄與之匹配，常用于主鍵或唯一索引掃描；此類型通常出現(xiàn)在多表的 join 等值查詢，表示對(duì)于前表的每一個(gè)結(jié)果，都只能匹配到后表的一行結(jié)果，查詢效率較高。

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

ref

非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值得所有行，本質(zhì)上也是一種索引訪問，它返回所有匹配某個(gè)單獨(dú)值的行，然而，它可能會(huì)找到多個(gè)符合條件的行，所以它應(yīng)該屬于查找和掃描的混合體；

EXPLAIN SELECT * FROM sku WHERE goods_id=1;

ref_or_null

二級(jí)索引等值比較同時(shí)限定 is null。

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

range

只檢索給定范圍的行，使用一個(gè)索引來選擇行，key 列顯示使用哪個(gè)索引，一般就是在你的 where 語句中出現(xiàn)了 between、、、in 等的查詢；這種范圍索引掃描比全表掃描要好，因?yàn)樗恍枰_始于索引的某一個(gè)點(diǎn)，結(jié)束于另一個(gè)點(diǎn)，不用掃描全部索引；

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

index

index 和 all 區(qū)別為 index 類型只遍歷索引樹，這通常比 all 快，因?yàn)樗饕募ǔ１葦?shù)據(jù)文件小；也就是說雖然 all 和 index 都是讀寫表，但 index 是從索引中讀取的，而 all 是從硬盤中讀的；

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

all

也就是全表掃描；

EXPLAIN SELECT * FROM sku WHERE deleted=0;

（6）possible_keys

查詢可能使用到的索引都會(huì)在這里列出來。

（7）key

查詢真正使用到的索引，select_type 為 index_merge 時(shí)，這里可能出現(xiàn)兩個(gè)以上的索引，其他的 select_type 這里只會(huì)出現(xiàn)一個(gè)。

（8）key_len

key_len 表示該列計(jì)算查詢中使用的索引的長(zhǎng)度。例如：SELECT * FROM table where age = 1 and name like xx，假設(shè) age 是 int 類型且不可為 null；name 是 varchar(20) 類型且可以為 null，編碼為 utf8。若以這兩個(gè)字段為索引查詢，那么 key_len 的值為 4 + 3 * 20 + 2 + 1 = 67。具體計(jì)算規(guī)則如下表所示：

值類型值名稱描述字符串 CHAR(n)n 字節(jié)長(zhǎng)度
VARCHAR(n) 如果是 utf8 編碼，則是 3 n + 2 字節(jié);；如果是 utf8mb4 編碼，則是 4 n + 2 字節(jié)。數(shù)值類型 TINYINT1 字節(jié)
SMALLINT2 字節(jié)
MEDIUMINT3 字節(jié)
INT4 字節(jié)
BIGINT8 字節(jié)時(shí)間類型 DATE3 字節(jié)
TIMESTAMP4 字節(jié)
DATETIME8 字節(jié)字段屬性 NULL 屬性占用一個(gè)字節(jié)。如果一個(gè)字段是 NOT NULL 的，則不占用。
（9）ref

如果是使用的常數(shù)等值查詢，這里會(huì)顯示 const，如果是連接查詢，被驅(qū)動(dòng)表的執(zhí)行計(jì)劃這里會(huì)顯示驅(qū)動(dòng)表的關(guān)聯(lián)字段，如果是條件使用了表達(dá)式或者函數(shù)，或者條件列發(fā)生了內(nèi)部隱式轉(zhuǎn)換，這里可能顯示為 func。

（10）rows

這里是執(zhí)行計(jì)劃中估算的掃描行數(shù)，不是精確值。

（11）filtered

使用 explain extended 時(shí)會(huì)出現(xiàn)這個(gè)列，5.7 之后的版本默認(rèn)就有這個(gè)字段，不需要使用 explain extended 了。這個(gè)字段表示存儲(chǔ)引擎返回的數(shù)據(jù)在 server 層過濾后，剩下多少滿足查詢的記錄數(shù)量的比例，注意是百分比，不是具體記錄數(shù)。

（12）Extra

這個(gè)列可以顯示的信息非常多，有幾十種，常用的有：

1、distinct：在 select 部分使用了 distinct 關(guān)鍵字

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

3、using filesort：說明 mysql 會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。mysql 中無法利用索引完成的排序操作稱為“文件排序”。排序時(shí)無法使用到索引時(shí)，就會(huì)出現(xiàn)這個(gè)。常見于 order by 語句中，需要盡快優(yōu)化

4、using index：查詢時(shí)不需要回表查詢，直接通過索引就可以獲取查詢的數(shù)據(jù)。

5、using join buffer（block nested loop），using join buffer（batched key accss）：5.6.x 之后的版本優(yōu)化關(guān)聯(lián)查詢的 BNL，BKA 特性。主要是減少內(nèi)表的循環(huán)數(shù)量以及比較順序地掃描查詢。

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

using intersect：表示使用 and 的各個(gè)索引的條件時(shí)，該信息表示是從處理結(jié)果獲取交集

using union：表示使用 or 連接各個(gè)使用索引的條件時(shí)，該信息表示從處理結(jié)果獲取并集

using sort_union 和 using sort_intersection：與前面兩個(gè)對(duì)應(yīng)的類似，只是他們是出現(xiàn)在用 and 和 or 查詢信息量大時(shí)，先查詢主鍵，然后進(jìn)行排序合并后，才能讀取記錄并返回。

7、using temporary：表示使用了臨時(shí)表存儲(chǔ)中間結(jié)果。臨時(shí)表可以是內(nèi)存臨時(shí)表和磁盤臨時(shí)表，執(zhí)行計(jì)劃中看不出來，需要查看 status 變量，used_tmp_table，used_tmp_disk_table 才能看出來。常見于 order by 和分組查詢 group by。group by 一定要遵循所建索引的順序與個(gè)數(shù)。需要盡快優(yōu)化

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

9、firstmatch(tb_name)：5.6.x 開始引入的優(yōu)化子查詢的新特性之一，常見于 where 字句含有 in()類型的子查詢。如果內(nèi)表的數(shù)據(jù)量比較大，就可能出現(xiàn)這個(gè)

10、loosescan(m..n)：5.6.x 之后引入的優(yōu)化子查詢的新特性之一，在 in()類型的子查詢中，子查詢返回的可能有重復(fù)記錄時(shí)，就可能出現(xiàn)這個(gè)

4. Explain 主要關(guān)注點(diǎn)

總的來說，我們只需要關(guān)注結(jié)果中的幾列：

列名備注 type 本次查詢表聯(lián)接類型，從這里可以看到本次查詢大概的效率 key 最終選擇的索引，如果沒有索引的話，本次查詢效率通常很差 key_len 本次查詢用于結(jié)果過濾的索引實(shí)際長(zhǎng)度 rows 預(yù)計(jì)需要掃描的記錄數(shù)，預(yù)計(jì)需要掃描的記錄數(shù)越小越好 Extra 額外附加信息，主要確認(rèn)是否出現(xiàn) Using filesort、Using temporary 這兩種情況

再來看下 Extra 列中需要注意出現(xiàn)的幾種情況：

關(guān)鍵字備注 Using filesort 將用外部排序而不是按照索引順序排列結(jié)果，數(shù)據(jù)較少時(shí)從內(nèi)存排序，否則需要在磁盤完成排序，代價(jià)非常高，需要添加合適的索引 Using temporary 需要?jiǎng)?chuàng)建一個(gè)臨時(shí)表來存儲(chǔ)結(jié)果，這通常發(fā)生在對(duì)沒有索引的列進(jìn)行 GROUP BY 時(shí)，或者 ORDER BY 里的列不都在索引里，需要添加合適的索引 Using index 表示 MySQL 使用覆蓋索引避免全表掃描，不需要再到表中進(jìn)行二次查找數(shù)據(jù)，這是比較好的結(jié)果之一。注意不要和 type 中的 index 類型混淆 Using where 通常是進(jìn)行了全表 / 全索引掃描后再用 WHERE 子句完成結(jié)果過濾，需要添加合適的索引 Impossible WHERE 對(duì) Where 子句判斷的結(jié)果總是 false 而不能選擇任何數(shù)據(jù)，例如 where 1=0，無需過多關(guān)注 Select tables optimized away 使用某些聚合函數(shù)來訪問存在索引的某個(gè)字段時(shí)，優(yōu)化器會(huì)通過索引直接一次定位到所需要的數(shù)據(jù)行完成整個(gè)查詢，例如 MIN()\MAX()，這種也是比較好的結(jié)果之一

關(guān)于“如何快速看懂 MySQL 執(zhí)行計(jì)劃”這篇文章的內(nèi)容就介紹到這里，感謝各位的閱讀！相信大家對(duì)“如何快速看懂 MySQL 執(zhí)行計(jì)劃”知識(shí)都有一定的了解，大家如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完