一文帶你快速看懂 MySQL 執(zhí)行計(jì)劃

程序員必備接口測(cè)試調(diào)試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設(shè)計(jì)、調(diào)試、文檔、自動(dòng)化測(cè)試工具
后端、前端、測(cè)試，同時(shí)在線協(xié)作，內(nèi)容實(shí)時(shí)同步

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

1. Explain 作用

explain 命令主要用于查看 SQL 語(yǔ)句的執(zhí)行計(jì)劃，該命令可以模擬優(yōu)化器執(zhí)行 SQL 查詢語(yǔ)句，可以幫助我們編寫(xiě)和優(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ì)劃表的字段含義：

3. 關(guān)鍵字段分析

（1）id

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

（2）select_type

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

（3）table

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

（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)建一系列表來(lái)實(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

表示通過(guò)索引一次就找到了，const 用于比較 primary key 或者 unique 索引。因?yàn)橹黄ヅ湟恍杏涗洠院芸臁?如果將主鍵置于 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ì)上也是一種索引訪問(wèn)，它返回所有匹配某個(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è)索引來(lái)選擇行，key列顯示使用哪個(gè)索引，一般就是在你的 where 語(yǔ)句中出現(xiàn)了 between、<、>、in 等的查詢；這種范圍索引掃描比全表掃描要好，因?yàn)樗恍枰_(kāi)始于索引的某一個(gè)點(diǎn)，結(jié)束于另一個(gè)點(diǎn)，不用掃描全部索引；

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

index

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

EXPLAIN SELECT barcode FROM sku WHERE deleted=0;

all

也就是全表掃描；

EXPLAIN SELECT * FROM sku WHERE deleted=0;

（6）possible_keys

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

（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ī)則如下表所示：

（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層過(guò)濾后，剩下多少滿足查詢的記錄數(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：說(shuō)明mysql會(huì)對(duì)數(shù)據(jù)使用一個(gè)外部的索引排序，而不是按照表內(nèi)的索引順序進(jìn)行讀取。mysql中無(wú)法利用索引完成的排序操作稱為“文件排序”。排序時(shí)無(wú)法使用到索引時(shí)，就會(huì)出現(xiàn)這個(gè)。常見(jiàn)于order by語(yǔ)句中，需要盡快優(yōu)化

4、using index：查詢時(shí)不需要回表查詢，直接通過(guò)索引就可以獲取查詢的數(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í)表和磁盤(pán)臨時(shí)表，執(zhí)行計(jì)劃中看不出來(lái)，需要查看status變量，used_tmp_table，used_tmp_disk_table才能看出來(lái)。常見(jiàn)于order by和分組查詢group by。group by一定要遵循所建索引的順序與個(gè)數(shù)。需要盡快優(yōu)化

8、using where：表示存儲(chǔ)引擎返回的記錄并不是所有的都滿足查詢條件，需要在server層進(jìn)行過(guò)濾。查詢條件中分為限制條件和檢查條件，5.6之前，存儲(chǔ)引擎只能根據(jù)限制條件掃描數(shù)據(jù)并返回，然后server層根據(jù)檢查條件進(jìn)行過(guò)濾再返回真正符合查詢的數(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開(kāi)始引入的優(yōu)化子查詢的新特性之一，常見(jiàn)于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)

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

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

【