【MySQL01】【 Explain 命令詳解】

文章目錄

一、前言
二、Explain 概覽
三、Explain 詳解
- 1. id
- 2. select_type
- 3. table
- 4. type
- 5. possible_keys
- 6. key
- 7. key_len
- 8. ref
- 9. rows
- 10. filtered
- 11. extra 列
四、補充
- 1. EXPLAIN 擴展
- - 1.1 Extend EXPLAIN
  - 1.2 JSON 格式的執行計劃
- 2. Intersection、Union、Sort-Union 索引合并
- - 2.1 Intersection
  - 2.2 Union
  - 2.3 Sort-Union
- 3. 基于塊的嵌套循環連接
五、參考內容

一、前言

最近在讀《MySQL 是怎樣運行的》一書，后續會隨機將書中部分內容記錄下來作為學習筆記，部分內容經過個人刪改，因此可能存在錯誤，如想詳細了解相關內容強烈推薦閱讀該書。

本文 Mysql 版本為 5.7.16，

涉及表結構來源兩處：

數據庫基于 Mysql 官方提供的 sakila 內容基于下載地址：https://dev.mysql.com/doc/index-other.html

如下：

CREATE TABLE `t1` (`id` int(11) NOT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,`c` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `idx_a` (`a`),KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE `t2` (`id` int(11) NOT NULL,`a` int(11) DEFAULT NULL,`b` int(11) DEFAULT NULL,`c` varchar(255) DEFAULT NULL,PRIMARY KEY (`id`),KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

二、Explain 概覽

使用EXPLAIN關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。分析你的查詢語句或是表結構的性能瓶頸。通過explain我們可以獲得以下信息：

表的讀取順序
數據讀取操作的操作類型
哪些索引可以使用
哪些索引被實際使用
表之間的引用
每張表有多少行被優化器查詢

使用方法：explain + sql語句。如下

在這里插入圖片描述
可以看到下面我們來分析每一列的內容

列名	描述
id	選擇標識符。數字越大，越先執行；數字相同，從上往下依次執行。需要注意在連接查詢（join ）中由于每個表都會對應一條記錄，因此會存在多條 ID 相同的記錄，此時出現在前面的表示驅動表，出現在后面的表示被驅動表。
select_type	表示查詢的類型
table	輸出結果集的表。EXPLAIN 語句輸出的每條記錄都對應著某個單表的訪問方法，table 即為該表的表名
partitions	匹配的分區
type	表示表的連接類型
possible_keys	表示查詢時，可能使用的索引
key	表示實際使用的索引
key_len	索引字段的長度
ref	列與索引的比較
rows	掃描出的行數(估算的行數)
filtered	按表條件過濾的行百分比
Extra	執行情況的描述和說明

三、Explain 詳解

1. id

選擇標識符。數字越大，越先執行；數字相同，從上往下依次執行。需要注意在連接查詢（join ）中由于每個表都會對應一條記錄，因此會存在多條 ID 相同的記錄，此時出現在前面的表示驅動表，出現在后面的表示被驅動表。

id 不同 : ID不同，數字越大，越先執行。 如下, 先執行SUBQUERY，再執行 PRIMARY 。

mysql> explain select * from film where film_id = (select max(film_id) from film);
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL                         |
|  2 | SUBQUERY    | NULL  | NULL       | NULL  | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Select tables optimized away |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set (0.08 sec)

id 相同 :按照從上到下的順序依次執行

mysql> explain select f.* from film f left join film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | fa    | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.f.film_id |    5 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
2 rows in set (0.06 sec)

id為null : 表示一個結果集，并不需要使用它來進行查詢。

UNION 會將多個查詢的結果集合合并起來并對結果集中的記錄去重，去重的實現是通過 MySQL 的內部臨時表，如下即使將 id 為 1 和 2 的查詢結果集合并起來去重，在內部創建了一個名為 <union1,2> 的零時表，id 為 null 說明這個臨時表是為了合并兩個查詢的結果集而創建的。額外的，由于 union all 不需要對結果集去重，他只是單純的將結果集合并后返回給用戶也就不需要臨時表，因此就沒有 id = NULL 的記錄。

-- 如下 UNION RESULT 作為一個合并結果集的操作的執行id為null。
mysql> explain select film_id from film union select film_id from film_actor;
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index     |
|    2 | UNION        | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index     |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL               | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------+
3 rows in set (0.03 sec)-- 額外的，由于 union all 不需要對結果集去重，他只是單純的將結果集合并后返回給用戶也就不需要臨時表，因此就沒有 id = NULL 的記錄。
mysql> explain select * from t1 union all select * from t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL  |
|  2 | UNION       | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set (0.04 sec)

需要注意的是：

在連接查詢（join ）中由于每個表都會對應一條記錄，因此會存在多條 ID 相同的記錄，此時出現在前面的表示驅動表，出現在后面的表示被驅動表。

-- 如下：t1在t2 之前，所以 t1 表將作為驅動表，t2 表將作為被驅動表
mysql> explain select * from t1 inner join t2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL                                  |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set (0.02 sec)

查詢優化器可能對涉及子查詢的查詢語句重寫，從而轉換為連接查詢，如果想知道查詢優化器是否對某個包含子查詢的語句進行了重寫，可以直接看執行計劃。如下：

-- 這一句由于包含 or id = 1 的部分，因此無法轉換為 連接查詢，所以存在 DEPENDENT SUBQUERY, id 列的值不同，說明存在執行優先級
mysql> explain select * from t1 where id in (select id from t2) or id = 1;
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | t1    | NULL       | ALL             | PRIMARY       | NULL    | NULL    | NULL |  100 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | unique_subquery | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using index |
+----+--------------------+-------+------------+-----------------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.03 sec)
-- 這一句可以直接轉換為連接查詢，所以id 列相同。
mysql> explain select * from t1 where id in (select id from t2);
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |  100 |   100.00 | NULL        |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | Using index |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------------+
2 rows in set (0.04 sec)

2. select_type

select_type 代表查詢的類型, 各種取值如下：

SIMPLE: 即簡單查詢，查詢語句中不包含 UNION 或者子查詢的查詢都算做 SIMPLE 類型。

mysql> explain select * from film;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.05 sec)mysql> explain select f.* from film f left JOIN film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | f     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | fa    | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.f.film_id |    5 |   100.00 | Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+------------------+------+----------+-------------+
2 rows in set (0.03 sec)

PRIMARY: 對于包含 UNION、UNION ALL 或者子查詢的大查詢來說，他是由幾個小查詢組成的。

mysql> explain select film_id from film union all select film_id from film_actor;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
2 rows in set (0.03 sec)mysql> explain select film_id from film union all select film_id from film_actor where film_id > (select min(film_id) from film_actor);
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
| id | select_type | table      | partitions | type  | possible_keys  | key                | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL           | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index                  |
|  2 | UNION       | film_actor | NULL       | range | idx_fk_film_id | idx_fk_film_id     | 2       | NULL | 5452 |   100.00 | Using where; Using index     |
|  3 | SUBQUERY    | NULL       | NULL       | NULL  | NULL           | NULL               | NULL    | NULL | NULL | NULL     | Select tables optimized away |
+----+-------------+------------+------------+-------+----------------+--------------------+---------+------+------+----------+------------------------------+
3 rows in set (0.02 sec)

UNION：對于包含 UNION 或者 UNION ALL 的大查詢來說，它是由幾個小查詢組成的；其中除了最左邊的那個小查詢外，其余小查詢的 select_type 的值就是 UNUION。

mysql> explain select film_id from film union all select film_id from film_actor UNION all select film_id from film_text;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
|  3 | UNION       | film_text  | NULL       | index | NULL          | PRIMARY            | 2       | NULL | 1000 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
3 rows in set (0.04 sec)

UNION RESULT : MySQL 選擇用臨時表來完成 UNION 查詢的去重工作，針對該臨時表的查詢的 select_type 就是 UNION RESULT

mysql> explain select * from t1 union select * from t2;
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id   | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|    1 | PRIMARY      | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL            |
|    2 | UNION        | t2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set (0.06 sec)

SUBQUERY : 如果包含子查詢的查詢語句不能轉換為對應的半連接形式，并且該子查詢是不相關子查詢，而且查詢優化器決定采用將該子查詢物化的方案來執行該子查詢時，該子查詢的第一個 SELECT 關鍵字代表的那個查詢就是 SUBQUERY。(需要注意：查詢條件是 SUBQUERY 的子查詢會被物化，所以該子查詢只需要執行一遍)

mysql> explain SELECT * FROM film_category WHERE film_id = (SELECT max(film_id) FROM film_actor);
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
| id | select_type | table         | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra                        |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
|  1 | PRIMARY     | film_category | NULL       | ref  | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | Using where                  |
|  2 | SUBQUERY    | NULL          | NULL       | NULL | NULL          | NULL    | NULL    | NULL  | NULL | NULL     | Select tables optimized away |
+----+-------------+---------------+------------+------+---------------+---------+---------+-------+------+----------+------------------------------+
2 rows in set (0.03 sec)mysql> explain SELECT (select 1 from film_category) from film;
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key                       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | film          | NULL       | index | NULL          | idx_fk_language_id        | 1       | NULL | 1000 |   100.00 | Using index |
|  2 | SUBQUERY    | film_category | NULL       | index | NULL          | fk_film_category_category | 1       | NULL | 1000 |   100.00 | Using index |
+----+-------------+---------------+------------+-------+---------------+---------------------------+---------+------+------+----------+-------------+
2 rows in set (0.05 sec)

DEPENDENT SUBQUERY : 如果包含子查詢的查詢語句不能夠轉為對應的半連接形式，并且該子查詢被查詢優化器轉換為相關子查詢的形式，則該子查詢第一個 SELECT 關鍵字代表的那個查詢就是 DEPENDENT SUBQUERY。需要注意 DEPENDENT SUBQUERY 的子查詢可能會被執行多次。

如下SQL，大查詢中包含一個子查詢，子查詢中又包含由 UNION 連接起來的兩個小查詢。從執行計劃可以看到，select id from t2 where a = '1' 是子查詢的第一個查詢，所以他的類型是 DEPENDENT SUBQUERY，而 select id from t1 where a = '1' 這個查詢就是 DEPENDENT UNION 類型。

mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1');
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|    1 | PRIMARY            | t1         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where     |
|    2 | DEPENDENT SUBQUERY | t2         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
|    3 | DEPENDENT UNION    | t1         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set (0.07 sec)

DEPENDENT UNION : 在包含 UNION 或者 UNION ALL的大查詢中，如果各個子查詢都依賴于外層查詢，除了最左邊的那個小查詢之外，其余小查詢就是 DEPENDENT UNION 。

mysql> explain select * from t1 where id in (select id from t2 where a = '1' union select id from t1 where a = '1');
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id   | select_type        | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra           |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
|    1 | PRIMARY            | t1         | NULL       | ALL    | NULL          | NULL    | NULL    | NULL |  100 |   100.00 | Using where     |
|    2 | DEPENDENT SUBQUERY | t2         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
|    3 | DEPENDENT UNION    | t1         | NULL       | eq_ref | PRIMARY,a     | PRIMARY | 4       | func |    1 |     5.00 | Using where     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | NULL | NULL     | Using temporary |
+------+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set (0.07 sec)

DERIVED : 在包含派生表的查詢中，如果是以物化派生表的方式執行查詢，則派生表對應的子查詢是 DERIVED。

mysql> explain SELECT ff.* FROM ( SELECT film_id FROM film UNION ALL SELECT film_id FROM film_actor ) ff;
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL               | NULL    | NULL | 6462 |   100.00 | NULL        |
|  2 | DERIVED     | film       | NULL       | index | NULL          | idx_fk_language_id | 1       | NULL | 1000 |   100.00 | Using index |
|  3 | UNION       | film_actor | NULL       | index | NULL          | idx_fk_film_id     | 2       | NULL | 5462 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------------------+---------+------+------+----------+-------------+
3 rows in set (0.02 sec)

MATERIALIZED : 當查詢優化器在執行包含子查詢的語句時，選擇將子查詢物化后與外層查詢進行連接查詢，該子查詢對應的類型即為 MATERIALIZED 。

mysql> explain select * from t1 where b in (select b from t2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref       | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
|  1 | SIMPLE       | t1          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      |  100 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 5       | demo.t1.b |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | t2          | NULL       | ALL    | NULL          | NULL       | NULL    | NULL      | 1000 |   100.00 | NULL        |
+----+--------------+-------------+------------+--------+---------------+------------+---------+-----------+------+----------+-------------+
3 rows in set (0.06 sec)

3. table

顯式的查詢顯式查詢的表名，如果查詢使用了別名，這里顯示的則是別名。如果不涉及對數據庫的操作，那么這里顯示的就是null 。如果顯示為 <derived N > 則表明這個是臨時表，N為執行計劃id，表示這個結果來源于這個查詢自身，如果顯示為 <union M, N>，其也是一個臨時表，表示這個結果來自于 union 查詢的 id為 M，N的結果集。

不涉及表操作：

mysql> explain select sleep(1);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.06 sec)

4. type

type列很重要，是用來說明表與表之間是如何進行關聯操作的，有沒有使用索引。MySQL中“關聯”一詞比一般意義上的要寬泛，MySQL認為任何一次查詢都是一次“關聯”，并不僅僅是一個查詢需要兩張表才叫關聯，所以也可以理解MySQL是如何訪問表的。主要有下面幾種類別。

type 依次性能從好到差： system，const，eq_ref, ref, ref_or_null, index_merge, unique_subquery, index_subquery, range,index,all。除了all之外，其他type都能用到索引，除了 index_merge 之外，其他的type只可以用到一個索引，一般來說，好的 sql查詢至少達到 range 級別，最好能達到 ref

system : 當表中只有一條記錄并且該表使用的存儲引擎（如 MyISAM、MEMORY）的統計數據是精確的，那么該表的訪問方法就是 system。如果是 Innodb 引擎表，type 在這個情況下通常都是 all 或者 index。

const : 當根據主鍵或者唯一二級索引列與常數進行等值匹配的時候，對單表的訪問方法就是 const。即查詢是通過主鍵或唯一二級索引來與常量做比較，最終結果只能有一條記錄。

mysql> explain select * from film where film_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set (0.01 sec)

eq_ref : 執行連接查詢時，如果被驅動表是通過主鍵或者不允許存儲 NULL 值的唯一二級索引列等值匹配的方式進行訪問的（如果該主鍵或者不允許存儲 NULL 值的唯一二級索引是聯合索引，則所有的索引列都必須進行等值比較），則對該被驅動表的訪問方法就是 eq_ref。即查詢是通過主鍵或唯一二級索引來來作為查詢條件，最終結果只能有一條記錄。

如下，t1 將作為驅動表，t2 作為被驅動表。在訪問 t2 表時，可以通過主鍵等值匹配來訪問：

mysql> explain select * from t1 inner join t2 on t1.id = t2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL       |  100 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set (0.07 sec)

ref : 當通過普通索引列與常量進行等值匹配的方式來查詢某個表時，對該表的訪問方法就可能是 ref。另外，如果是執行連接查詢，被驅動表中的某個普通的二級索引列與驅動表中的某個列進行等值匹配，那么對被驅動表也可能使用 ref。

相較于 eq_ref。 ref 不要求記錄唯一性，也就是不要求索引是主鍵或者唯一索引。ref在連接查詢時，只要求是索引即可，返回記錄也可出現多條。總之，返回數據可能不唯一的等值查找就可能出現。

mysql> explain select * from film f left join film_actor fa on fa.film_id = f.film_id;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | fa    | NULL       | ALL  | NULL           | NULL           | NULL    | NULL              | 1000 |   100.00 | NULL  |
|  1 | SIMPLE      | f     | NULL       | ref  | idx_fk_film_id | idx_fk_film_id | 2       | sakila.fa.film_id |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
2 rows in set (0.06 sec)mysql> explain select * from film where title = 'film1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | ref  | idx_title     | idx_title | 514     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set (0.07 sec)

fulltext : 全文索引檢索，全文檢索的優先級很高，若全文索引和普通索引同時存在，mysql不管代價，優先選擇全文索引。

ref_or_null : 當對普通二級索引列進行等值匹配且該索引列的值也可以是 NULL 值時，對該表的訪問方法就可能是 ref_or_null。簡單來說類似ref，但是可以搜索值為NULL的行。

mysql> explain select * from t1 where a = 'a' or a is null;
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref_or_null | a             | a   | 5       | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+-----+---------+-------+------+----------+-----------------------+
1 row in set (0.06 sec)

index_merge : 一般清理下只會為單個索引生成掃描區間，但是 InnoDB 存在Intersection (交集)索引合并、Union （并集）索引合并、Sort-Union 索引合并三種索引合并的方式來執行查詢。(關于這三種索引合并方式，本文末有補充)。

如下，type 為 index_mege，即MySQL 打算使用索引合并的方式來執行對 t1 表的查詢。

mysql> explain select * from t1 where a = 'a' or b = 'b';
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key         | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index_merge | idx_a,idx_b   | idx_a,idx_b | 5,5     | NULL |    2 |   100.00 | Using union(idx_a,idx_b); Using where |
+----+-------------+-------+------------+-------------+---------------+-------------+---------+------+------+----------+---------------------------------------+
1 row in set (0.06 sec)

unique_subquery : 類似于兩表連接中被驅動表的 eq_ref 訪問方法， unique_subquery 針對的事一些包含 in 子查詢的查詢語句。如果查詢優化器決定將 in 子查詢轉換為 exists 子查詢，而且子查詢在轉換之后可以使用主鍵或者不允許存儲 NULL 值的唯一二級索引進行等值匹配，那么該子查詢執行計劃的 type 列的值就是 unique_subquery 。

mysql> explain select * from t1 where a in (select id from t2 where t1.id = t2.id) or b = 'a';
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra                    |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
|  1 | PRIMARY            | t1    | NULL       | ALL    | idx_b         | NULL    | NULL    | NULL       |  100 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+--------------------------+
2 rows in set (0.06 sec)

index_subquery : 與 unique_subquery 類似，只不過在訪問子查詢中表時使用的是普通索引。

range : 如果使用索引獲取某些單點掃描區間的記錄，那么就可能使用到 range 訪問方法，即索引范圍掃描，常見于使用 >， <, is null, between, in, like 等范圍運算符查詢中。

mysql> explain select * from t1 where id > 0;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |  100 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set (0.06 sec)

index : 當可以使用索引覆蓋，但需要掃描全部的索引時，該表的訪問方法就是 index。額外的，當我們需要執行全表掃描，并且需求對主鍵進行排序時，此時的 type 也是 index。

mysql> explain select id from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a | 5       | NULL |  100 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

all : 全表掃描數據文件，然后再在server層進行過濾返回符合要求的記錄。效率最差

mysql> explain select * from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.04 sec)

5. possible_keys

表示在某個查詢語句中，對某個表執行單表查詢時可能用到的索引有哪些，這里可能出現多個的。如下：對于 t1 表字段a 和 b 都是普通索引，如下語句在執行前就可能會使用 idx_a 或 idx_b 為索引。最終通過查詢優化器計算后認為執行全表掃描成本更低（因為表里就一條記錄）

mysql> explain select * from t1 where a > 'a' and b > 'b';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a,idx_b   | NULL | NULL    | NULL |  100 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

6. key

表示在某個查詢語句中，對某個表執行單表查詢時真正用到的索引有哪些。之所以出現 possible_keys 與 key 的不同是因為MySQL 經過查詢優化器計算不同所以的使用成本后，會選擇成本較少的索引使用。需要注意的是：當select_type 為 index_merge 時，這里可能出現兩個以上的索引，其他的 select_type 這里只會出現一個

7. key_len

用于處理查詢的索引長度，如果是單列索引，那就是整個索引長度，如果是多列索引，那么查詢不一定都能使用到所有的列，具體使用到了多少列的索引，這里就會計算出去，沒有使用到的列不會計算進去。留意下整個列的值，算一下你的多列索引使用長度就知道有沒有使用到所有的列。另外 key_len 只計算where 條件用到的索引長度，而排序和分組就算用到了索引，也不會計算到 key_len 中。

具體的長度計算邏輯在《MySQL是怎樣運行的》中有具體示例，如有需要可以看書。

8. ref

當訪問方法時 const、eq_ref、ref、ref_or_null、unique_subquery、index_subquery 中的其中一個時，ref 列展示的就是與索引列進行等值匹配的東西，比如只是一常數或者某一列

-- 如下查詢：以 t1 表作為驅動表，查詢條件是 const 級別， 以 t2 表作為被驅動表，對應的 ref列是 demo.t1.id
mysql> explain select * from t1 inner join t2 on t1.id = t2.id where t1.a = 'a';
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref        | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref    | PRIMARY,idx_a | idx_a   | 5       | const      |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | t2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | demo.t1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------+------+----------+-------+
2 rows in set (0.05 sec)

需要注意的是有時與索引列做匹配的是一個函數，此時 ref 列展示的是 func，如下：

mysql> explain select * from t1 inner join t2 on t1.id = upper(t2.id);
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | t1    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | func |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------+------+----------+-------------+
2 rows in set (0.05 sec)

9. rows

根據表統計信息及索引選用情況，大致估算出找到所需的記錄所需讀取的行數。

10. filtered

5.7 版本之前，使用 explain extended 命令才會出現此字段，5.7 版本之后默認有此字段，這個字段表示存儲引擎返回的數據在 server 層過濾后，剩下多少滿足查詢的記錄數量的比例，這里是百分比，并非具體記錄數。在單表查詢中這一列沒有什么意義，更應該在連接查詢中關注驅動表對應的執行計劃的 filtered 值。

11. extra 列

No table used : 不帶 from字句的查詢或者 from dual 查詢, 如下：

mysql> explain select 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.05 sec)

Impossible WHERE : 查詢語句的 where 字句永遠為 false，如下：

mysql> explain select MAX(id) from t1 where 1 != 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
| 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 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------------+
1 row in set (0.05 sec)

No matching min/max row : 當查詢列表有 MIN 或者 MAX 函數，但是并沒有記錄符合 WHERE 字句中的搜索條件，如下：

mysql> explain select MAX(id) from t1 where a = 'asda';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
| 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 matching min/max row |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------------+
1 row in set (0.04 sec)

Using index : 不需要回表，直接通過索引查詢 (覆蓋索引) ，如下：

mysql> explain select a from t1;
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | idx_a | 5       | NULL |  100 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------+---------+------+------+----------+-------------+
1 row in set (0.05 sec)

Using index condition : 有些搜索條件中雖然出現了索引列，但是不能充當邊界條件來形成掃描區間，即不能用來減少需要掃描的記錄數量，將會提示該額外信息; 出現了該信息提示，也可以說明該查詢使用了索引下推（Index Condition Pushdown ICP）。如下：

mysql> explain select * from t1 where a > '1';
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
| id | select_type | table    | partitions | type  | possible_keys | key | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t1		  | NULL       | range | a             | a   | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+----------+------------+-------+---------------+-----+---------+------+------+----------+-----------------------+
1 row in set (0.05 sec)

Using where : 當某個查詢條件需要在 server 層進行判斷時，會提示該信息。如下：這里雖然 a 列是索引，但是MySQL 判斷全表掃描效率更高，因此沒有使用索引（key 為 null）；全表掃描后將數據返回給 Server 層，在 Server 層過濾 a > ‘a’ 并且 c = ‘c’ 的條件.

mysql> explain select * from t1 where a > 'a' and c = 'c';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | idx_a         | NULL | NULL    | NULL |  100 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.04 sec)

Using join buffer(Block Nested Loop) : 在連接查詢的執行過程中，當被驅動表不能有效利用索引加快訪問速度時，MySQL一般會為其分配一塊名為連接緩沖區（Join Buffer）的內存卡來加快查詢速度；也就是使用基于塊的嵌套循環算法來執行連接查詢。如下：

sql> explain select * from t1 inner join t2 on t1.c = t2.c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | NULL                                               |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.06 sec)

在 Extra 中顯示了兩個提示：

Using join buffer (Block Nested Loop) : 因為對于 t2 表的訪問不能有效利用索引，所以只能退而求其次，使用 Join Buffer 來減少 t2 表的訪問次數，提高性能。
Using where : 查詢語句中的 t2 on t1.c = t2.c條件，因此 t1 表是驅動表，t2 表是被驅動表，所以在訪問 t2 表的時候 t1.c 的值已經確定下來了，所以提示了 Using where 信息。

Using intersect(…)、Using union(…)、Using sort_union(…) : 說明查詢使用了 Intersect、Union 或 Sort-Union 索引合并。

Zero limit ：當 Limit 參數為0時，表示不打算從表中讀取出任何記錄。

mysql> explain select * from t1 limit 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
| 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     | Zero limit |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+------------+
1 row in set (0.04 sec)

Using filesort : 在有些情況下，對結果集總的記錄進行排序時，是可以使用到索引的。但是很多情況下排序操作是無法使用到索引的，只能在內存或磁盤中進行排序，而這種在內存或磁盤中進行排序的方式稱為文件排序（filesort）。如果某個查詢使用到了文件排序，則會顯示該提示，如下：

mysql> explain select * from t1 order by c;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set (0.04 sec)

Using temporary ：在許多查詢的執行過程中，MySQL會借助臨時表來完成一些去重、排序等功能。如果不能有效利用索引來完成查詢，MySQL很有可能通過建立內部臨時表執行查詢。如果查詢中使用了內部臨時表則會展示該信息。

mysql> explain select distinct(c) from t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  100 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set (0.04 sec)

Loosescan : 在將 IN 子查詢轉為半連接時，如果采用的是 LooseScan 執行策略，則驅動表的 Extra 列會顯示該提示。
Firstmatch ：在將 IN 子查詢轉為半連接時，如果采用的是 FirstMatch 執行策略，則被驅動表的 Extra 列會顯示該提示。

NULL : 查詢的列未被索引覆蓋，并且where 篩選條件是索引的前導列，意味著用到了索引，但是部分字段未被索引覆蓋，必須通過 “回表” 來實現，不是純粹地用到了索引，也不是完全沒用到索引。（需要回表操作完成的查詢）,如下：

mysql> explain select * from t1 where a = 'a';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

四、補充

1. EXPLAIN 擴展

1.1 Extend EXPLAIN

在使用 Explain 語句查看 SQL 的執行計劃后，緊接著還可以使用 show warnings 語句來查看與這個查詢計劃有關的擴展信息, 如下：

mysql> explain select * from t1 where a = 'a' and c = 'c' ;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | idx_a         | idx_a | 5       | const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set (0.04 sec)mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                          |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `demo`.`t1`.`id` AS `id`,`demo`.`t1`.`a` AS `a`,`demo`.`t1`.`b` AS `b`,`demo`.`t1`.`c` AS `c` from `demo`.`t1` where ((`demo`.`t1`.`a` = 'a') and (`demo`.`t1`.`c` = 'c')) |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

show warnings 展示了三個字段：Level、Code、 Message。其中當 Code =1003 時，Message 字段展示的信息類似于查詢優化器將查詢語句重寫后的語句。

1.2 JSON 格式的執行計劃

可以在 Explain 命令后追加 format=json 命令以得到一個 JSON 格式的執行計劃，里面除了常規的信息外海包含該計劃花費的成本。

mysql> explain format=json select * from t1 where a = 'a' and c = 'c';
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| {"query_block": {"select_id": 1, # 整個查詢語句只有一個 Select 關鍵字，該關鍵字對應的id號為 1"cost_info": {"query_cost": "1.20" # 整個查詢語句預計執行成本},"table": {"table_name": "t1","access_type": "ref","possible_keys": ["idx_a"],"key": "idx_a","used_key_parts": ["a"],"key_length": "5","ref": ["const"],"rows_examined_per_scan": 1,"rows_produced_per_join": 0,"filtered": "10.00","cost_info": {"read_cost": "1.00","eval_cost": "0.02","prefix_cost": "1.20","data_read_per_join": "78"},# 執行查詢中設計的列"used_columns": [  "id","a","b","c"],# 針對單表的查詢條件"attached_condition": "(`demo`.`t1`.`c` = 'c')"}}
} |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

2. Intersection、Union、Sort-Union 索引合并

一般情況下 MySQL 只會為單個索引生成掃描空間，但是特殊情況下MySQL 也會為多個索引生成掃描區間。

2.1 Intersection

select  from  t1 where k1='a' and k2 = 'b'

其中 k1，k2 都是索引，此處存在三種方案：

掃描 k1 輔助索引，回表查詢 k2 = ‘b’ 的記錄
掃描 k2 輔助索引，回表查詢 k1 = 'a’的記錄
同時掃描 k1 和 k2 的輔助索引，然后從二者的操作結果中找出 id 列值相同的記錄。此方案，即為 Intersection (交集)索引合并

如果要使用 Intersection (交集)索引合并，并且每個使用到的索引都是二級索引，那么則要求從每個索引中獲取到的二級索引記錄都是按照主鍵值排序的。比如上面的sql，查詢 k1 = ‘a’ 的輔助索引記錄，因為其輔助索引列都是 a，又因為在索引列相同時按照主鍵排序，所以這里查詢出來的二級索引記錄是按照主鍵排序的，k2 = ‘b’ 同理，因此上述SQL 可以使用 Intersection索引合并。

為什么需要主鍵有序？

從兩個有序集合中取交集更為容易
如果獲取到的id 值是有序的，則回表時就不是隨機 IO了（相當于實現了MMR），從而提高效率。

2.2 Union

基本與 Intersection (交集)索引合并類似，只不過這里取的是并集。

如：select from t1 where k1='a' or k2 = 'b'

2.3 Sort-Union

Union 需要保證掃描出來的二級索引的主鍵值是有序的，而 Sort-Union 則會將從各個索引中掃描到的記錄的主鍵值進行排序，在按照 Union 索引合并的方式執行查詢。相較于 Union， Sort-Union 多了一步將查詢出來的記錄按照主鍵排序的過程。

需要注意的是并不存在 Sort-Intersection 索引合并，在《MySQL是怎樣運行的》一書中說是 Sort-Intersection 索引合并查詢出的記錄太多，導致回表代價太大。

3. 基于塊的嵌套循環連接

在連接查詢過程中，分為驅動表和被驅動表，驅動表只需要訪問一次，被驅動表可能需要訪問多次。兩表連接查詢的過程并不是將所有滿足條件的驅動表記錄先查詢出來放到一個地方然后再去被驅動表中查詢（因為可能驅動表記錄太大，無法存儲），而是每獲取到一條驅動表記錄，就立即到被驅動表中尋找匹配的記錄，如果得到了匹配的記錄，就把組合后的記錄發送給客戶端，然后再到驅動表中獲取下一條記錄，這個過程將重復進行。但上述這種方案需要頻繁訪問被驅動表，因此InnoDB 存在一個 Join Buffer (連接緩沖區) 的概念，Join Buffer 在執行連接查詢前申請的一塊大小固定的內存。先把若干條驅動表結果集中的記錄記錄在 Join Buffer 中，然后開始掃描被驅動表，每一條被動表的記錄一次性與 Join Buffer 中的多條驅動表記錄進行匹配。由于匹配的過程是在內存中完成的，因此可以顯著減少被驅動表的 IO 代價。此為基于塊的嵌套循環連接（Block Nested-Loop Join）算法。

需要注意的是 : Join Buffer 中并不會存放驅動表記錄的所有列，只有查詢列表中的列和過濾條件中的列才會被放到 Join Buffer 中。

Join Buffer 示意圖
在這里插入圖片描述

五、參考內容

書籍：《MySQL是怎樣運行的——從根兒上理解MySQL》
全技術棧企業級性能調優萬花筒
一張圖徹底搞懂MySQL的 explain
MySQL優化—工欲善其事，必先利其器之EXPLAIN*
https://coding.imooc.com/class/442.html
https://segmentfault.com/a/1190000021458117?utm_source=tag-newest
https://www.cnblogs.com/tufujie/p/9413852.html
https://blog.csdn.net/qq_27399407/article/details/93741225
https://smartan123.github.io/book/?file=home-%E9%A6%96%E9%A1%B5
https://www.cnblogs.com/zhanjindong/archive/2013/11/23/3439042.html
如有侵擾，聯系刪除。內容僅用于自我記錄學習使用。如有錯誤，歡迎指正