程序員必備接口測試調試工具：立即使用
Apipost = Postman + Swagger + Mock + Jmeter
Api設計、調試、文檔、自動化測試工具
后端、前端、測試，同時在線協(xié)作，內容實時同步

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

1. Explain 作用

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

• 表的讀取順序

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

• 哪些索引可以使用

• 哪些索引被實際使用

• 表之間的引用

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

2. Explain 如何使用

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

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

3. 關鍵字段分析

（1）id

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

（2）select_type

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

（3）table

顯示的查詢表名，如果查詢使用了別名，那么這里顯示的是別名，如果不涉及對數(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只可以用到一個索引。

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

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 類型的特例，平時不會出現(xiàn)，這個也可忽略不計；

const

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

EXPLAIN SELECT * FROM sku WHERE id=1;復制代碼

eq_ref

唯一性索引掃描，對于每一個索引鍵，表中只有一條記錄與之匹配，常用于主鍵或唯一索引掃描；此類型通常出現(xiàn)在多表的 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 語句中出現(xiàn)了 between、<、>、in 等的查詢；這種范圍索引掃描比全表掃描要好，因為它只需要開始于索引的某一個點，結束于另一個點，不用掃描全部索引；

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

index

index 和 all 區(qū)別為 index 類型只遍歷索引樹，這通常比 all 快，因為索引文件通常比數(shù)據(jù)文件?。灰簿褪钦f雖然 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時，這里可能出現(xiàn)兩個以上的索引，其他的select_type這里只會出現(xiàn)一個。

（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。具體計算規(guī)則如下表所示：

（9）ref

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

（10）rows

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

（11）filtered

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

（12）Extra

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

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

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

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

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

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

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

• using intersect：表示使用and的各個索引的條件時，該信息表示是從處理結果獲取交集
• using union：表示使用or連接各個使用索引的條件時，該信息表示從處理結果獲取并集
• using sort_union和using sort_intersection：與前面兩個對應的類似，只是他們是出現(xiàn)在用and和or查詢信息量大時，先查詢主鍵，然后進行排序合并后，才能讀取記錄并返回。

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

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

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

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

4. Explain 主要關注點

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

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

【