一文讀懂 MySQL Explain 執(zhí)行計劃

一鞍历、前言

上周老周的一個好朋友讓我出一篇教你讀懂 SQL 執(zhí)行計劃，和我另一位讀者反饋的面試題如何排查慢 SQL 的強相關(guān)，索性先出一篇一文讀懂 MySQL Explain 執(zhí)行計劃。Explain 執(zhí)行計劃你一定得會看，不然你簡歷上就別去寫什么你會 SQL 調(diào)優(yōu)谱俭，不然面試官會覺得，Explain 執(zhí)行計劃你都不會看宵蛀，那你還 SQL 調(diào)啥優(yōu)袄ブ？SQL 調(diào)你吧术陶？凑懂？？開個小玩笑梧宫，玩笑歸玩笑接谨，重要是真的重要！Ｌ料弧脓豪！

二、Explain 執(zhí)行計劃是什么忌卤？

什么是執(zhí)行計劃扫夜？簡而言之，就是 SQL 在數(shù)據(jù)庫中執(zhí)行時的表現(xiàn)情況驰徊，通常用于 SQL 性能分析笤闯、優(yōu)化和加鎖分析等場景，執(zhí)行過程會在 MySQL 查詢過程中由解析器棍厂，預(yù)處理器和查詢優(yōu)化器共同生成颗味。在 MySQL 中使用 explain 關(guān)鍵字來查看。

2.1 執(zhí)行計劃有什么用牺弹？

它可以用來分析 SQL 語句和表結(jié)構(gòu)的性能瓶頸

關(guān)聯(lián)查詢的執(zhí)行順序
查詢操作的操作類型
哪些索引可以被命中
哪些索引實際被命中
每張表有多少記錄參與查詢
...

2.2 MySQL 執(zhí)行過程

[圖片上傳失敗...(image-ea6abe-1649170227750)]

如上圖所示浦马，MySQL 數(shù)據(jù)庫由 Server 層和 Engine 層組成：

Server 層有 SQL 分析器时呀、SQL優(yōu)化器、SQL 執(zhí)行器捐韩，用于負(fù)責(zé) SQL 語句的具體執(zhí)行過程退唠；
Engine 層負(fù)責(zé)存儲具體的數(shù)據(jù)，如最常使用的 MyISAM荤胁、InnoDB 存儲引擎，還有用于在內(nèi)存中存儲臨時結(jié)果集的 TempTable 引擎屎债。

SQL 優(yōu)化器會分析所有可能的執(zhí)行計劃仅政，選擇成本最低的執(zhí)行，這種優(yōu)化器稱之為：CBO（Cost-based Optimizer盆驹，基于成本的優(yōu)化器）圆丹。

而在 MySQL 中，一條 SQL 的計算成本計算如下所示：

Cost = Server Cost + Engine Cost
= CPU Cost + IO Cost

其中躯喇，CPU Cost 表示計算的開銷辫封，比如索引鍵值的比較、記錄值的比較廉丽、結(jié)果集的排序等這些操作都在 Server 層完成倦微；

IO Cost 表示引擎層 IO 的開銷，MySQL 8.0 可以通過區(qū)分一張表的數(shù)據(jù)是否在內(nèi)存中正压，分別計算讀取內(nèi)存 IO 開銷以及讀取磁盤 IO 的開銷欣福。

數(shù)據(jù)庫 mysql 下的表 server_cost、engine_cost 則記錄了對于各種成本的計算焦履，如：

[圖片上傳失敗...(image-a029e1-1649170227751)]
表 server_cost 記錄了 Server 層優(yōu)化器各種操作的成本拓劝，這里面包括了所有 CPU Cost，其具體含義如下：

disk_temptable_create_cost：創(chuàng)建磁盤臨時表的成本嘉裤，默認(rèn)為 20郑临。
disk_temptable_row_cost：磁盤臨時表中每條記錄的成本，默認(rèn)為 0.5屑宠。
key_compare_cost：索引鍵值比較的成本厢洞，默認(rèn)為 0.05，成本最小侨把。
memory_temptable_create_cost：創(chuàng)建內(nèi)存臨時表的成本：默認(rèn)為 1犀变。
memory_temptable_row_cost：內(nèi)存臨時表中每條記錄的成本，默認(rèn)為 0.1秋柄。
row_evaluate_cost：記錄間的比較成本获枝，默認(rèn)為 0.1。

可以看到骇笔， MySQL 優(yōu)化器認(rèn)為如果一條 SQL 需要創(chuàng)建基于磁盤的臨時表省店，則這時的成本是最大的嚣崭，其成本是基于內(nèi)存臨時表的 20 倍。而索引鍵值的比較懦傍、記錄之間的比較雹舀，其實開銷是非常低的，但如果要比較的記錄數(shù)非常多粗俱，則成本會變得非常大说榆。

而表 engine_cost 記錄了存儲引擎層各種操作的成本，這里包含了所有的 IO Cost寸认，具體含義如下：

io_block_read_cost：從磁盤讀取一個頁的成本签财，默認(rèn)值為 1。
memory_block_read_cost：從內(nèi)存讀取一個頁的成本偏塞，默認(rèn)值為 0.25唱蒸。

也就是說， MySQL 優(yōu)化器認(rèn)為從磁盤讀取的開銷是內(nèi)存開銷的 4 倍灸叼。

三神汹、Explain 執(zhí)行計劃詳解

我們先來準(zhǔn)備以下 SQL 腳本：

CREATE TABLE `user` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR (20) DEFAULT NULL COMMENT "用戶名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用戶表";

CREATE TABLE `user_robot_relate` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `user_id` INT (11) NOT NULL COMMENT "用戶id",
    `robot_id` INT (11) NOT NULL COMMENT "機器人id",
    PRIMARY KEY (`id`), 
    KEY `idx_user_id` (`user_id`), 
    KEY `idx_robot_id` (`robot_id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用戶與機器人表";

CREATE TABLE `robot` (
    `id` INT (11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR ( 20 ) DEFAULT NULL COMMENT "機器人名",
    PRIMARY KEY (`id`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "機器人表";

INSERT INTO user VALUES (1, 'riemann');
INSERT INTO user VALUES (2, 'andy');

INSERT INTO user_robot_relate VALUES (1, 1, 1);
INSERT INTO user_robot_relate VALUES (2, 1, 2);
INSERT INTO user_robot_relate VALUES (3, 2, 3);

INSERT INTO robot VALUES (1, '小白鯨');
INSERT INTO robot VALUES (2, '掃地機');
INSERT INTO robot VALUES (3, '掃拖一體機');

我們創(chuàng)建三張表 user、user_robot_relate古今、robot屁魏，表之間的關(guān)系 user.id = user_robot_relate.user_id AND user_robot_relate.robot_id = robot.id。

先來看下我的 MySQL 版本沧卢，是 5.7.37 的蚁堤。
[圖片上傳失敗...(image-50b394-1649170227751)]
接著我們看一下執(zhí)行計劃有哪些字段，先看個整體的但狭，讓大家有個大概的認(rèn)識后披诗，我們再逐一去詳解分析。

[圖片上傳失敗...(image-669021-1649170227751)]
explain 執(zhí)行后輸出的結(jié)果集包含 12 列立磁，分別是 id呈队、select_type、table唱歧、partitions宪摧、type、possible_keys颅崩、key几于、key_len、ref沿后、rows沿彭、filtered 和 Extra，下面對這些字段進行解釋尖滚。

id：Query Optimizer 所選定的執(zhí)行計劃中查詢的序列號
select_type：顯示本行是簡單或復(fù)雜 select喉刘。如果查詢有任何復(fù)雜的子查詢瞧柔，則最外層標(biāo)記為PRIMARY、DERIVED.睦裳、UNION造锅、UNION RESUIT 等。
table：顯示這一步所訪問的數(shù)據(jù)庫中的表的名稱
partitions：查詢時匹配到的分區(qū)信息廉邑，對于非分區(qū)表值為 NULL哥蔚，當(dāng)查詢的是分區(qū)表時，partitions 顯示分區(qū)表命中的分區(qū)情況鬓催。
type：數(shù)據(jù)訪問肺素、讀取操作類型（ALL、index宇驾、range、ref猴伶、eq_ref课舍、const、system）等
possible_keys：該查詢可以利用的索引他挎，如果沒有任何索引可以使用筝尾，就會顯示成 null，這一
項內(nèi)容對于優(yōu)化時候索引的調(diào)整非常重要办桨。
key：MySQL Query Optimizer 從 possible_keys 中所選擇使用的索引
key_len：被選中使用索引的索引鍵長度
ref：列出是通過常量（const）筹淫，還是某個表的某個字段（如果是 join）來過濾（通過 key）
的。
rows：MySQL Query Optimizer 通過系統(tǒng)收集到的統(tǒng)計信息估算出來的結(jié)果集記錄條數(shù)
filtered：表示存儲引擎返回的數(shù)據(jù)在經(jīng)過過濾后呢撞，剩下滿足條件的記錄數(shù)量的比例损姜。
Extra：查詢中每一步實現(xiàn)的額外細(xì)節(jié)信息，如 Using filesort殊霞、index 等摧阅。

3.1 id

看到三條記錄的 id 都相同，可以理解成這三個表為一組绷蹲，具有同樣的優(yōu)先級棒卷，執(zhí)行順序由上而下，具體順序由優(yōu)化器決定祝钢。

3.1.1 id 相同

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '掃地機'));
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys            | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | ur    | NULL       | ALL  | idx_user_id,idx_robot_id | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY                  | NULL | NULL    | NULL |    3 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+--------------------------+------+---------+------+------+----------+----------------------------------------------------+

3.1.2 id 不同

如果我們的 SQL 中存在子查詢比规，那么 id 的序號會遞增，id 值越大優(yōu)先級越高拦英，越先被執(zhí)行蜒什。當(dāng)三個表依次嵌套，發(fā)現(xiàn)最里層的子查詢 id 最大龄章，最先執(zhí)行吃谣。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '掃地機'));
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.1.3 以上兩種同時存在

將上邊的 SQL 稍微修改一下乞封，增加一個子查詢，發(fā)現(xiàn) id 的以上兩種同時存在岗憋。相同 id 劃分為一組肃晚，這樣就有三個組，同組的從上往下順序執(zhí)行仔戈，不同組 id 值越大关串，優(yōu)先級越高，越先執(zhí)行监徘。

mysql> EXPLAIN SELECT * FROM user u WHERE u.id = (SELECT ur.user_id FROM user_robot_relate ur WHERE ur.robot_id = (SELECT r.id FROM robot r WHERE r.name = '掃地機')) AND u.id IN (SELECT u.id FROM user u WHERE u.name = 'riemann');
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  1 | PRIMARY     | u     | NULL       | const | PRIMARY       | PRIMARY      | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | ur    | NULL       | ref   | idx_robot_id  | idx_robot_id | 4       | const |    1 |   100.00 | Using where |
|  3 | SUBQUERY    | r     | NULL       | ALL   | NULL          | NULL         | NULL    | NULL  |    3 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+--------------+---------+-------+------+----------+-------------+

3.2 select_type

select_type：表示 select 查詢的類型晋修，主要是用于區(qū)分各種復(fù)雜的查詢，例如：普通查詢凰盔、聯(lián)合查詢墓卦、子查詢等。

SIMPLE：表示最簡單的 select 查詢語句户敬，也就是在查詢中不包含子查詢或者 union 交并差集等操作落剪。
PRIMARY：當(dāng)查詢語句中包含任何復(fù)雜的子部分，最外層查詢則被標(biāo)記為 PRIMARY尿庐。
SUBQUERY：當(dāng) select 或 where 列表中包含了子查詢忠怖，該子查詢被標(biāo)記為 SUBQUERY。
DERIVED：表示包含在 from 子句中的子查詢的 select抄瑟，在我們的 from 列表中包含的子查詢會被標(biāo)記為 derived凡泣。
UNION：如果 union 后邊又出現(xiàn)的 select 語句，則會被標(biāo)記為 union皮假；若 union 包含在 from 子句的子查詢中鞋拟，外層 select 將被標(biāo)記為 derived。

UNION RESULT：代表從 union 的臨時表中讀取數(shù)據(jù)钞翔，而 table 列的 <union1,4> 表示用第一個和第四個 select 的結(jié)果進行 union 操作严卖。

mysql> EXPLAIN SELECT t.user_id, (SELECT u.id FROM user u) o FROM (SELECT ur.user_id, ur.robot_id FROM user_robot_relate ur WHERE ur.id = 2) t UNION (SELECT r.id, r.name FROM robot r);
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | ur         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL            |
|  2 | SUBQUERY     | u          | NULL       | index | NULL          | PRIMARY | 4       | NULL  |    2 |   100.00 | Using index     |
|  4 | UNION        | r          | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    3 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,4> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------+

3.3 table

查詢的表名，并不一定是真實存在的表布轿，有別名顯示別名哮笆，也可能為臨時表，例如上邊的 DERIVED汰扭、 <union1,4> 等稠肘。

3.4 partitions

查詢時匹配到的分區(qū)信息，對于非分區(qū)表值為 NULL萝毛，當(dāng)查詢的是分區(qū)表時项阴，partitions 顯示分區(qū)表命中的分區(qū)情況。

3.5 type

type：查詢使用了何種類型，它在 SQL優(yōu)化中是一個非常重要的指標(biāo)环揽，以下性能從好到壞依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system：當(dāng)表僅有一行記錄時（系統(tǒng)表）略荡，數(shù)據(jù)量很少，往往不需要進行磁盤 IO歉胶，速度非逞炊担快。

const：表示查詢時命中 primary key 主鍵或者 unique 唯一索引通今，或者被連接的部分是一個常量（const）值粥谬。這類掃描效率極高，返回數(shù)據(jù)量少辫塌，速度非陈┎撸快。

mysql> EXPLAIN SELECT * FROM robot WHERE id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

eq_ref：查詢時命中主鍵 primary key 或者 unique key 索引臼氨， type 就是 eq_ref掺喻。

mysql> EXPLAIN SELECT u.name FROM user u, user_robot_relate ur WHERE u.id = ur.id; 
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref       | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL      |    2 |   100.00 | NULL        |
|  1 | SIMPLE      | ur    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-------------+

ref：區(qū)別于 eq_ref，ref 表示使用非唯一性索引储矩，會找到很多個符合條件的行巢寡。

mysql> EXPLAIN SELECT id FROM user_robot_relate WHERE user_id = 2; 
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | ref  | idx_user_id   | idx_user_id | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+

ref_or_null：這種連接類型類似于 ref，區(qū)別在于 MySQL 會額外搜索包含 NULL 值的行椰苟。

-- 為了模擬這個場景，我又新增了一個 user_test 表树叽。
mysql> CREATE TABLE `user_test` (
    -> `id` INT (11) NOT NULL AUTO_INCREMENT,
    -> `name` VARCHAR (20) DEFAULT NULL COMMENT "用戶名",
    -> PRIMARY KEY (`id`), 
    -> KEY `idx_name` (`name`)
    -> ) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = "用戶測試表";
Query OK, 0 rows affected (0.01 sec)

mysql> EXPLAIN SELECT id FROM user_test WHERE name = 'riemann' OR name IS NULL; 
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
| id | select_type | table     | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | user_test | NULL       | ref_or_null | idx_name      | idx_name | 83      | const |    2 |   100.00 | Using where; Using index |
+----+-------------+-----------+------------+-------------+---------------+----------+---------+-------+------+----------+--------------------------+

index_merge：使用了索引合并優(yōu)化方法舆蝴，查詢使用了兩個以上的索引。

-- 下邊示例中同時使用到主鍵 id 和字段 user_id 的索引题诵。
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id > 1 AND user_id = 2; 
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table             | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | index_merge | PRIMARY,idx_user_id | idx_user_id,PRIMARY | 8,4     | NULL |    1 |   100.00 | Using intersect(idx_user_id,PRIMARY); Using where |
+----+-------------+-------------------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+

unique_subquery：替換下面的 IN 子查詢洁仗，子查詢返回不重復(fù)的集合。
```
value IN (SELECT primary_key FROM single_table WHERE some_expr)
```
index_subquery：區(qū)別于 unique_subquery性锭，用于非唯一索引赠潦，可以返回重復(fù)值。
```
value IN (SELECT key_column FROM single_table WHERE some_expr)
```

range：使用索引選擇行草冈，僅檢索給定范圍內(nèi)的行烤送。簡單點說就是針對一個有索引的字段蟆豫，給定范圍檢索數(shù)據(jù)。在 where 語句中使用 bettween...and、<好渠、>、<=终畅、in 等條件查詢 type 都是 range渴肉。

-- user_robot_relate 表中 id 為唯一主鍵，name 普通字段未建索引谬运。
mysql> EXPLAIN SELECT * FROM user_robot_relate WHERE id BETWEEN 2 AND 3;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user_robot_relate | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

從結(jié)果中看到只有對設(shè)置了索引的字段隙赁，做范圍檢索 type 才是 range垦藏。

mysql> EXPLAIN SELECT * FROM user WHERE name BETWEEN 2 AND 3;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

index：Index 與 ALL 其實都是讀全表，區(qū)別在于 index 是遍歷索引樹讀取伞访，而 ALL 是從硬盤中讀取掂骏。

-- id 為主鍵，不帶 where 條件全表查詢咐扭，type 結(jié)果為 index芭挽。
mysql> EXPLAIN SELECT id FROM robot;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | robot | NULL       | index | NULL          | PRIMARY | 4       | NULL |    3 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

ALL：將遍歷全表以找到匹配的行，性能最差蝗肪。

mysql> EXPLAIN SELECT * FROM robot;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | robot | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.6 possible_keys

possible_keys：表示在 MySQL 中通過哪些索引袜爪，能讓我們在表中找到想要的記錄，一旦查詢涉及到的某個字段上存在索引薛闪，則索引將被列出辛馆，但這個索引并不定一會是最終查詢數(shù)據(jù)時所被用到的索引。具體請參考上邊的例子豁延。

3.7 key

key：區(qū)別于 possible_keys昙篙，key 是查詢中實際使用到的索引，若沒有使用索引诱咏，顯示為 NULL苔可。具體請參考上邊的例子。

當(dāng) type 為 index_merge 時袋狞，可能會顯示多個索引焚辅。

3.8 key_len

key_len：表示查詢用到的索引長度（字節(jié)數(shù)），原則上長度越短越好苟鸯。

單列索引同蜻，那么需要將整個索引長度算進去；
多列索引早处，不是所有列都能用到湾蔓，需要計算查詢中實際用到的列。

注：key_len 只計算 where 條件中用到的索引長度砌梆，而排序和分組即便是用到了索引默责，也不會計算到 key_len 中。

3.9 ref

列出是通過常量（const）么库，還是某個表的某個字段（如果是 join）來過濾（通過 key）的傻丝。

3.10 rows

rows：以表的統(tǒng)計信息和索引使用情況，估算要找到我們所需的記錄诉儒，需要讀取的行數(shù)葡缰。

這是評估 SQL 性能的一個比較重要的數(shù)據(jù)，MySQL 需要掃描的行數(shù)，很直觀的顯示 SQL 性能的好壞泛释，一般情況下 rows 值越小越好滤愕。

3.11 filtered

filtered 這個是一個百分比的值，表里符合條件的記錄數(shù)的百分比怜校。簡單點說间影，這個字段表示存儲引擎返回的數(shù)據(jù)在經(jīng)過過濾后，剩下滿足條件的記錄數(shù)量的比例茄茁。

在 MySQL.5.7 版本以前想要顯示 filtered 需要使用 explain extended 命令魂贬。MySQL.5.7 后，默認(rèn) explain 直接顯示 partitions 和 filtered 的信息裙顽。

3.12 Extra

Extra ：不適合在其他列中顯示的信息付燥，Explain 中的很多額外的信息會在 Extra 字段顯示。

3.12.1 Using index

Using index：我們在相應(yīng)的 select 操作中使用了覆蓋索引愈犹，通俗一點講就是查詢的列被索引覆蓋键科，使用到覆蓋索引查詢速度會非常快漩怎，SQL 優(yōu)化中理想的狀態(tài)勋颖。

什么又是覆蓋索引?

一條 SQL 只需要通過索引就可以返回，我們所需要查詢的數(shù)據(jù)（一個或幾個字段）勋锤，而不必通過二級索引饭玲，查到主鍵之后再通過主鍵查詢整行數(shù)據(jù)（SELECT * ）。

id 為 user 表的主鍵

mysql> EXPLAIN SELECT id FROM user; 
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

注意：想要使用到覆蓋索引叁执，我們在 select 時只取出需要的字段咱枉，不可 SELECT *，而且該字段建了索引徒恋。

mysql> EXPLAIN SELECT * FROM user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

3.12.2 Using where

Using where：查詢時未找到可用的索引，進而通過 where 條件過濾獲取所需數(shù)據(jù)欢伏，但要注意的是并不是所有帶 where 語句的查詢都會顯示 Using where入挣。

下邊示例 name 并未用到索引，type 為 ALL硝拧，即 MySQL 通過全表掃描后再按 where 條件篩選數(shù)據(jù)径筏。

mysql> EXPLAIN SELECT name FROM user WHERE name = 'riemann';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

3.12.3 Using temporary

Using temporary：表示查詢后結(jié)果需要使用臨時表來存儲，一般在排序或者分組查詢時用到障陶。

mysql> EXPLAIN SELECT name FROM user WHERE id IN (1, 2) GROUP BY name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | user  | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    2 |   100.00 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------------+

3.12.4 Using filesort

Using filesort：表示無法利用索引完成的排序操作滋恬，也就是 ORDER BY 的字段沒有索引，通常這樣的 SQL 都是需要優(yōu)化的抱究。

mysql> EXPLAIN SELECT id FROM user ORDER BY name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

如果 ORDER BY 字段有索引就會用到覆蓋索引恢氯，相比執(zhí)行速度快很多。

mysql> EXPLAIN SELECT id FROM user ORDER BY id;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

3.12.5 Using join buffer

Using join buffer：在我們聯(lián)表查詢的時候，如果表的連接條件沒有用到索引勋拟，需要有一個連接緩沖區(qū)來存儲中間結(jié)果勋磕。

mysql> EXPLAIN SELECT u.name FROM user u, user_test t WHERE u.name = t.name;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_name      | idx_name | 83      | NULL |    1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | u     | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |    2 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+

3.12.6 Impossible where

Impossible where：表示在我們用不太正確的 where 語句，導(dǎo)致沒有符合條件的行敢靡。

mysql> EXPLAIN SELECT name FROM user WHERE 1=2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra            |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible WHERE |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+

3.12.7 No tables used

No tables used：我們的查詢語句中沒有 FROM 子句挂滓，或者有 FROM DUAL 子句。

mysql> EXPLAIN SELECT now();
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者

人面猴
序言：七十年代末啸胧，一起剝皮案震驚了整個濱河市赶站，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌纺念，老刑警劉巖贝椿，帶你破解...
沈念sama閱讀 218,755評論 6贊 507
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異柠辞，居然都是意外死亡团秽，警方通過查閱死者的電腦和手機，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 93,305評論 3贊 395
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門叭首，熙熙樓的掌柜王于貴愁眉苦臉地迎上來习勤，“玉大人，你說我怎么就攤上這事焙格⊥急希” “怎么了？”我有些...
開封第一講書人閱讀 165,138評論 0贊 355
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵眷唉，是天一觀的道長予颤。經(jīng)常有香客問我，道長冬阳，這世上最難降的妖魔是什么蛤虐？我笑而不...
開封第一講書人閱讀 58,791評論 1贊 295
?港島之戀（遺憾婚禮）
正文為了忘掉前任，我火速辦了婚禮肝陪，結(jié)果婚禮上驳庭，老公的妹妹穿的比我還像新娘。我一直安慰自己氯窍，他們只是感情好饲常，可當(dāng)我...
茶點故事閱讀 67,794評論 6贊 392
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布。她就那樣靜靜地躺著狼讨，像睡著了一般贝淤。火紅的嫁衣襯著肌膚如雪。梳的紋絲不亂的頭發(fā)上政供，一...
開封第一講書人閱讀 51,631評論 1贊 305
城市分裂傳說
那天播聪，我揣著相機與錄音朽基，去河邊找鬼。笑死犬耻，一個胖子當(dāng)著我的面吹牛踩晶，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播枕磁，決...
沈念sama閱讀 40,362評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼渡蜻，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了计济？” 一聲冷哼從身側(cè)響起茸苇，我...
開封第一講書人閱讀 39,264評論 0贊 276
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎沦寂，沒想到半個月后学密，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,724評論 1贊 315
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡传藏，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,900評論 3贊 336
?白月光啟示錄
正文我和宋清朗相戀三年腻暮，在試婚紗的時候發(fā)現(xiàn)自己被綠了。大學(xué)時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片毯侦。...
茶點故事閱讀 40,040評論 1贊 350
活死人
序言：一個原本活蹦亂跳的男人離奇死亡哭靖，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出侈离，到底是詐尸還是另有隱情试幽，我是刑警寧澤，帶...
沈念sama閱讀 35,742評論 5贊 346
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布卦碾，位于F島的核電站铺坞，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏洲胖。R本人自食惡果不足惜济榨，卻給世界環(huán)境...
茶點故事閱讀 41,364評論 3贊 330
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望绿映。院中可真熱鬧腿短，春花似錦、人聲如沸绘梦。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,944評論 0贊 22
一樁弒父案赴魁，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽卸奉。三九已至，卻和暖如春颖御，著一層夾襖步出監(jiān)牢的瞬間榄棵，已是汗流浹背凝颇。一陣腳步聲響...
開封第一講書人閱讀 33,060評論 1贊 270
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留疹鳄，地道東北人拧略。一個月前我還...
沈念sama閱讀 48,247評論 3贊 371
代替公主和親
正文我出身青樓，卻偏偏與公主長得像瘪弓，于是被迫代替她去往敵國和親垫蛆。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點故事閱讀 44,979評論 2贊 355