MySQL進階之路--EXPLAIN執(zhí)行計劃詳解

執(zhí)行計劃簡介

執(zhí)行計劃是指一條SQL語句在經(jīng)過MySQL查詢優(yōu)化器的優(yōu)化會后厢漩，具體的執(zhí)行方式拙寡。MySQL為我們提供了EXPLAIN語句举庶，來獲取執(zhí)行計劃的相關(guān)信息。需要注意的是炫狱，EXPLAIN語句并不會真的去執(zhí)行相關(guān)的語句，而是通過查詢優(yōu)化器對語句進行分析剔猿，找出最優(yōu)的查詢方案视译，并顯示對應(yīng)的信息。
執(zhí)行計劃支持SELECT, DELETE, INSERT, REPLACE以及 UPDATE語句归敬。對于SELECT語句酷含，EXPLAIN會生成擴展信息，這些信息可以通過緊隨EXPLAIN語句之后的SHOW WARNINGS語句顯示汪茧。

執(zhí)行計劃的輸出格式

列	json名稱	含義
id	select_id	每個`SELECT`語句都會對應(yīng)一個唯一的標識符id
select_type	None	`SELECT`關(guān)鍵字對應(yīng)的查詢類型
table	table_name	每行輸出的表名
partitions	partitions	匹配的分區(qū)
type	access_type	表的訪問方法
possible_keys	possible_keys	可能用到的索引
key	key	實際用到的索引
key_len	key_length	實際用到的索引長度
ref	ref	當使用索引等值查詢時椅亚，與索引作比較的列或常量
rows	rows	預(yù)計要讀取的行數(shù)
filtered	filtered	按表條件過濾后，留存的記錄數(shù)的百分比（過濾后的行數(shù)/過濾前行數(shù) ）
extra	None	附加信息

創(chuàng)建測試用表及并生成數(shù)據(jù)

創(chuàng)建測試用表
表結(jié)構(gòu)如下所示舱污，實際使用中會創(chuàng)建u1呀舔，u2兩張表，兩張表結(jié)構(gòu)一致扩灯，除id列外媚赖，其它字段隨機插入。

CREATE TABLE u1 (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100),
    card_no INT,
    school VARCHAR(100),
    city VARCHAR(100),
    district VARCHAR(100),
    street VARCHAR(100),
    hoppy VARCHAR(100),
    PRIMARY KEY (id),
    KEY idx_name (name),
    UNIQUE KEY idx_card_no (card_no),
    KEY idx_school (school),
    KEY idx_address(city, district, street)
) Engine=InnoDB CHARSET=utf8;

生成數(shù)據(jù)
先定義一個生成隨機數(shù)的函數(shù)rand_string:

DELIMITER $$
DROP FUNCTION IF EXISTS rand_string$$
CREATE  FUNCTION `rand_string`(num INT) RETURNS varchar(255) CHARSET UTF8
BEGIN
    DECLARE origin_str char(52) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i INT DEFAULT 0;
    WHILE i < num DO
        SET return_str = CONCAT(return_str, SUBSTRING(origin_str , FLOOR(1 + RAND()*52 ),1));
        SET i = i +1;
    END WHILE;
    RETURN return_str;
END $$
DELIMITER ;

創(chuàng)建存儲過程

DELIMITER $$
DROP PROCEDURE IF EXISTS gen_user_data$$
CREATE  PROCEDURE `gen_user_data`(num INT) 
BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= num DO
        INSERT INTO u1(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
        INSERT INTO u2(id, name, card_no, school, city, district, street, hoppy) VALUES(i, rand_string(1+FLOOR(RAND()*100)), 10000000+i, rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)), rand_string(1+FLOOR(RAND()*100)));
        SET i = i +1;
    END WHILE;
END $$
DELIMITER ;

調(diào)用存儲過程驴剔，生成數(shù)據(jù)

CALL gen_user_data(10000);

執(zhí)行計劃輸出各列詳解

id

查詢語句中每個SELECT關(guān)鍵字省古，都會有一個對應(yīng)的id。
最簡單的查詢?nèi)缦滤旧ナВ捎谥挥幸粋€SELECT關(guān)鍵字豺妓，所以只有id為1的記錄。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

在多表連接的情況下，有可能出現(xiàn)id相同的情況琳拭。比如下面這種情況训堆，雖然只有一個SELECT關(guān)鍵字，但是連接查詢了兩張表白嘁，由于每張表都有一行輸出記錄坑鱼，所以一共有兩行記錄；但是由于是同一個SELECT查詢出來的絮缅，所以id值相同鲁沥，都是1。這種情況下耕魄，出現(xiàn)在上面的表叫驅(qū)動表画恰，出現(xiàn)在下面的表叫被驅(qū)動表。下面的示例中u2是驅(qū)動表吸奴，u1是被驅(qū)動表允扇。

mysql> EXPLAIN SELECT * FROM u1 inner join u2 WHERE u1.name = u2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9899 |   100.00 | Using where |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u2.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

如果一條查詢語句中，出現(xiàn)多個SELECT關(guān)鍵字则奥，則id會依次遞增考润。
比如下面這條查詢語句，由于存在子查詢读处，整個查詢一共包含兩個SELECT關(guān)鍵字糊治，則id從1增加到2。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (select name from u2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref        | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE       | u1          | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL       | 9818 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 303     | zz.u1.name |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | u2          | NULL       | index  | idx_name      | idx_name   | 303     | NULL       | 9899 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
3 rows in set, 1 warning (0.01 sec)

另外档泽，需要注意的是俊戳，子查詢有可能被優(yōu)化為連接查詢，如下面這個查詢馆匿，雖然我們輸入的是子查詢抑胎，但從EXPLAIN的輸出結(jié)果看，兩行輸出的id相同渐北，均為1阿逃，可以看出，子查詢被轉(zhuǎn)化成了連接查詢赃蛛。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u2 WHERE u1.school = u2.school);
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
| id | select_type | table | partitions | type | possible_keys       | key        | key_len | ref          | rows | filtered | Extra                       |
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_name,idx_school | NULL       | NULL    | NULL         | 9818 |   100.00 | Using where                 |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_name,idx_school | idx_school | 303     | zz.u1.school |    1 |     4.55 | Using where; FirstMatch(u1) |
+----+-------------+-------+------------+------+---------------------+------------+---------+--------------+------+----------+-----------------------------+
2 rows in set, 2 warnings (0.00 sec)

對于包含UNION子句的查詢來說恃锉，除了會有多個SELECT關(guān)鍵字外，還會出現(xiàn)一種特殊情況呕臂，就像下面這條語句所展示的破托。

mysql> EXPLAIN SELECT * FROM u1 UNION SELECT * FROM u2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL            |
|  2 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.01 sec)

上面語句的輸出中包含了id為NULL的行，且table列顯示的是<union1,2>歧蒋，extra列顯示Using temporary土砂。<union1,2>表明州既，MySQL將id為1和id為2的兩個查詢結(jié)果合并起來；Using temporary表明萝映，MySQL為這個合并結(jié)果創(chuàng)建了一個臨時表吴叶，為什么要創(chuàng)建臨時表呢，主要是為了去重用的序臂。我們知道蚌卤，UNION查詢的結(jié)果是要去重的。如果不需要去重的話奥秆，還要不要創(chuàng)建臨時表呢逊彭，我們用UNION ALL語句來試一下：

mysql> EXPLAIN SELECT * FROM u1 UNION ALL SELECT * FROM u2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
|  2 | UNION       | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

可見，如果不需要去重的話吭练，就不會建立臨時表了诫龙。

table

執(zhí)行計劃的輸出結(jié)果中，每行都有對應(yīng)的表名鲫咽，表名除了正常的表之外，也可能是以下列出的值：

<unionM,N>: 本行引用了id為M和N的行的UNION結(jié)果谷异；
<derivedN>: 本行引用了id 為N的表所產(chǎn)生的的派生表結(jié)果分尸。派生表有可能產(chǎn)生自FROM語句中的子查詢。
<subqueryN>: 本行引用了id 為N的表所產(chǎn)生的的物化子查詢結(jié)果歹嘹。

關(guān)于 <unionM,N>和<subqueryN>的例子箩绍，我們在講解id屬性的時候已經(jīng)展示了，下面展示下 <derivedN>的情況：

mysql> explain SELECT * FROM (SELECT * FROM u1 UNION ALL SELECT * FROM u2) as t;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19717 |   100.00 | NULL  |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9818 |   100.00 | NULL  |
|  3 | UNION       | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9899 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

這個示例中尺上，table列為<derived2>材蛛，說明使用到了id為2的派生表。

select_type

根據(jù)每個SELECT所起到的作用怎抛，對其進行分類卑吭，并為其設(shè)置了select_type屬性，這個屬性分為以下幾種：

類型	英文原文	描述
SIMPLE	Simple `SELECT`(not using `UNION` or subqueries)	沒有用到`UNION`或子查詢的簡單查詢
PRIMARY	Outermost `SELECT`	最外層的`SELECT`
UNION	Second or later `SELECT` statement in a `UNION`	`UNION`中的第二個或更后面的`SELECT`
DEPENDENT UNION	Second or later `SELECT` statement in a `UNION`, dependent on outer query	依賴于外部查詢的马绝，`UNION`中的第二個或更后面`SELECT`
UNION RESULT	Result of a `UNION`	`UNION`結(jié)果
SUBQUERY	First `SELECT` in subquery	子查詢中的第一個`SELECT`
DEPENDENT SUBQUERY	First `SELECT` in subquery, dependent on outer query	子查詢中的第一個`SELECT`豆赏，依賴于外部查詢
DERIVED	Derived table	派生表
MATERIALIZED	Materialized subquery	物化子查詢
UNCACHEABLE SUBQUERY	A subquery for which the result cannot be cached and must be re-evaluated for each row of the outer query	無法緩存結(jié)果的子查詢，針對外部查詢的每一行必須重新進行求值
UNCACHEABLE UNION	The second or later select in a `UNION` that belongs to an uncacheable subquery	`UNION` 中屬于不可緩存子查詢的第二個或更靠后的`SELECT`

翻譯的不好富稻，大家湊合看哈掷邦，下面我們來詳細講解一下。

SIMPLE
如果查詢中不包括UNION和子查詢椭赋，那么這個SELECT的類型就是SIMPLE抚岗。

比如下面所示的單表查詢：

mysql> EXPLAIN SELECT * FROM u1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

當然，連接查詢也屬于SIMPLE類型：

mysql> EXPLAIN SELECT * FROM u1 INNER JOIN u2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                                  |
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------------+
2 rows in set, 1 warning (0.00 sec)

PRIMARY
一條查詢語句中哪怔，可能會包含多個SELECT關(guān)鍵字宣蔚，最外層的SELECT向抢，其類型就是PRIMARY。

mysql> EXPLAIN SELECT * FROM u1 UNION SELECT * FROM u2;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL            |
|  2 | UNION        | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

從輸出可以看到件已，整個查詢由兩個SELECT組成笋额，最外層（最左側(cè)）的SELECT語句為SELECT * FROM u1，它的select_type為PRIMARY篷扩。

UNION
UNION語句中的第二個或更后的SELECT關(guān)鍵字兄猩，其類型為UNION。
比如上面那個UNION查詢鉴未，最外層的SELECT其類型為PRIMARY枢冤，第二個SELECT查詢SELECT * FROM u2其類型就是UNION。
DEPENDENT UNION
與UNION類似铜秆，都是指UNION語句中的第二個或更后的SELECT關(guān)鍵字淹真，不過此UNION查詢依賴于外部查詢。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u1 UNION  SELECT name FROM u2);
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
| id | select_type        | table      | partitions | type | possible_keys | key      | key_len | ref  | rows | filtered | Extra           |
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
|  1 | PRIMARY            | u1         | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | 9818 |   100.00 | Using where     |
|  2 | DEPENDENT SUBQUERY | u1         | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index     |
|  3 | DEPENDENT UNION    | u2         | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index     |
| NULL | UNION RESULT       | <union2,3> | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------------+------------+------------+------+---------------+----------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

UNION RESULT
MySQL選擇使用臨時表來完成UNION查詢的去重工作時连茧，其select_type就是UNION RESULT核蘸。上面的示例中就有，就不多說了啸驯。
SUBQUERY
如果子查詢沒有被轉(zhuǎn)換成連接查詢客扎，且是不相關(guān)子查詢，且查詢優(yōu)化器對該子查詢做了物化處理罚斗，那么該子查詢的第一個SELECT語句徙鱼，其select_type就是SUBQUERY。

mysql> EXPLAIN SELECT * FROM u1 WHERE name in (SELECT name FROM u2) or name = "abc";
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | u1    | NULL       | ALL   | idx_name      | NULL     | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | SUBQUERY    | u2    | NULL       | index | idx_name      | idx_name | 303     | NULL | 9899 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

$\color{red}{Tips}$ ：子查詢物化是指創(chuàng)建一張臨時表來保存子查詢結(jié)果针姿，以加快查詢速度和效率袱吆。MySQL在首次需要子查詢結(jié)果時創(chuàng)建臨時表，以后再次需要時距淫，則直接從臨時表讀取绞绒，不需要再去重復(fù)執(zhí)行子查詢，提高了查詢效率溉愁。物化時处铛，MySQL盡可能先在內(nèi)存中創(chuàng)建臨時表，如果表很大拐揭，則會在磁盤上創(chuàng)建臨時表撤蟆。

DEPENDENT SUBQUERY
如果子查詢沒有被轉(zhuǎn)換成連接查詢，且是相關(guān)子查詢堂污，那么該子查詢的第一個SELECT關(guān)鍵字代表的查詢語句家肯，其select_type就是DEPENDENT SUBQUERY。
需要注意的是盟猖，對于DEPENDENT SUBQUERY讨衣，子查詢針對其外部上下文中變量的每個不同值集重新求值一次换棚，也就是說DEPENDENT SUBQUERY會被執(zhí)行多次。

mysql> EXPLAIN SELECT * FROM u1 WHERE name IN (SELECT name FROM u2 where u2.name = u1.name) OR name="abc";
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
| id | select_type        | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra                    |
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
|  1 | PRIMARY            | u1    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9818 |   100.00 | Using where              |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u1.name |    1 |   100.00 | Using where; Using index |
+----+--------------------+-------+------------+------+---------------+----------+---------+------------+------+----------+--------------------------+
2 rows in set, 2 warnings (0.01 sec)

DERIVED
派生表一般存在于FROM子句中反镇。
對于采用物化的方式執(zhí)行的包含派生表的查詢固蚤，該派生表對應(yīng)的子查詢的select_type就是DERIVED，比方說下邊這個查詢：

mysql> EXPLAIN SELECT * FROM (SELECT name, school FROM u1 GROUP BY name, school) AS t;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                            |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
2 rows in set, 1 warning (0.00 sec)

又或者下面這條語句：

mysql> EXPLAIN SELECT * FROM (SELECT * FROM u1 UNION ALL SELECT * FROM u2) as t;
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 19717 |   100.00 | NULL  |
|  2 | DERIVED     | u1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9818 |   100.00 | NULL  |
|  3 | UNION       | u2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  9899 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

MATERIALIZED
當執(zhí)行子查詢語句時歹茶，查詢優(yōu)化器先將子查詢物化后再與外層查詢進行連接查詢時夕玩，其select_type為MATERIALIZED：

mysql> EXPLAIN SELECT * FROM u1 WHERE name IN (SELECT name from u2);
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
| id | select_type  | table       | partitions | type   | possible_keys | key        | key_len | ref        | rows | filtered | Extra       |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
|  1 | SIMPLE       | u1          | NULL       | ALL    | idx_name      | NULL       | NULL    | NULL       | 9818 |   100.00 | Using where |
|  1 | SIMPLE       | <subquery2> | NULL       | eq_ref | <auto_key>    | <auto_key> | 303     | zz.u1.name |    1 |   100.00 | NULL        |
|  2 | MATERIALIZED | u2          | NULL       | index  | idx_name      | idx_name   | 303     | NULL       | 9899 |   100.00 | Using index |
+----+--------------+-------------+------------+--------+---------------+------------+---------+------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

從執(zhí)行計劃可以看出，有兩行id為1的記錄惊豺，說明優(yōu)化器將子查詢轉(zhuǎn)換成了連接查詢燎孟，其id為2的行的select_type為MATERIALIZED，說明是先將子查詢進行物化尸昧，然后再轉(zhuǎn)換成了連接查詢揩页。

UNCACHEABLE SUBQUERY
略過。
UNCACHEABLE UNION
略過烹俗。

partitions

查詢記錄中匹配的分區(qū)爆侣。對于非分區(qū)表，該值為NULL幢妄。

type

EXPLAIN執(zhí)行計劃中的每一行都代表著對一張表的查詢累提，而type列則表示該表的訪問方法。訪問方法按從好到壞磁浇，依次為system、const朽褪，eq_ref置吓，ref，fulltext缔赠，ref_or_null衍锚，index_merge，unique_subquery嗤堰，index_subquery戴质，range，index踢匣，ALL告匠，下面分別來介紹一下。

system
如果表使用的引擎對于表行數(shù)統(tǒng)計是精確的（如：MyISAM）离唬，且表中只有一行記錄的情況下后专，訪問方法是system。

先看看存儲引擎為InnoDB的表訪問方法：

mysql> CREATE TABLE single_row (id INT NOT NULL) engine=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO single_row VALUES(1);
Query OK, 1 row affected (0.01 sec)

mysql> EXPLAIN SELECT * FROM single_row;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | single_row | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到输莺，對于InnoDB 引擎戚哎，type類型為ALL裸诽，換成MyISAM 引擎看看：

mysql> ALTER TABLE single_row engine=MyISAM;
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT * FROM single_row;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | single_row | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

可以看到，改成MyISAM 引擎后型凳，type類型為system丈冬。

const
當使用主鍵或唯一索引與常量作等值比較時（如果主鍵或唯一索引是聯(lián)合索引的話，則聯(lián)合索引的每一部分都必須是和常量做等值比較）甘畅，就會使用 const埂蕊，例如：

mysql> EXPLAIN SELECT * FROM u1 WHERE id=1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

eq_ref
對于連接查詢來說，如果被驅(qū)動表是使用主鍵或者非空唯一索引與常量進行等值比較（如果主鍵或非空唯一索引是聯(lián)合索引橄浓，則聯(lián)合索引的每一列都要做等值比較）的方法進行訪問的粒梦，那么該驅(qū)動表訪問方法為eq_ref。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.id=u2.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL     | 9818 |   100.00 | NULL  |
|  1 | SIMPLE      | u2    | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | zz.u1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

需要注意的是荸实，如果唯一索引允許為NULL匀们，那么訪問方法就不是eq_ref，請看如下示例：

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.card_no=u2.card_no;
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref           | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_card_no   | NULL        | NULL    | NULL          | 9818 |   100.00 | Using where |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_card_no   | idx_card_no | 5       | zz.u1.card_no |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-------------+---------+---------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

可以看到准给，在唯一索引允許為NULL時泄朴，訪問方法為ref。

ref
當通過普通二級索引列與常量進行等值匹配來查詢時露氮，其訪問方法有可能是ref祖灰，ref可用于使用=或<=>運算符進行比較的索引列。在以下示例中畔规，MySQL可以使用ref訪問方法來處理查詢：

mysql> EXPLAIN SELECT * FROM u1 WHERE name="abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM u1 WHERE name <=> "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

fulltext
連接使用了fulltext索引辜窑。
ref_or_null
這種連接類型類似于ref，但是MySQL會額外搜索包含NULL值的行娩践。在以下示例中陪腌，MySQL可以使用ref_or_null的訪問方法來查詢：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" OR name IS NULL;
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type        | possible_keys | key      | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref_or_null | idx_name      | idx_name | 303     | const |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------------+---------------+----------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index_merge
查詢優(yōu)化器選擇使用索引合并來優(yōu)化查詢。索引合并分為Intersection莫绣、Union畴蒲、Sort-Union三種。在使用索引合并的情況下对室，輸出行中的key列包含使用的索引列表模燥，而key_len包含使用的索引的長度（按長度最長的情況計算）列表。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" OR school = "abc";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    2 |   100.00 | Using union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

unique_subquery
類似于兩表連接時掩宜，被驅(qū)動表的eq_ref方法蔫骂；unique_subquery被用于IN子查詢中，如果IN子查詢中能夠用主鍵索引做等值匹配進行查詢的話锭亏，那么該子查詢的訪問方法為unique_subquery纠吴。

mysql> EXPLAIN SELECT * FROM u1 WHERE id IN (SELECT id FROM u2 WHERE u1.card_no = u2.card_no) OR card_no = 1000;
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type            | possible_keys       | key     | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | u1    | NULL       | ALL             | idx_card_no         | NULL    | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | unique_subquery | PRIMARY,idx_card_no | PRIMARY | 4       | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+-----------------+---------------------+---------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

可以看到，對u2表的查詢使用的是PRIMARY主鍵索引慧瘤，其type列為unique_subquery戴已。

index_subquery
類似于unique_subquery固该，只不過在子查詢中用到的是非唯一索引：

mysql> EXPLAIN SELECT * FROM u1 WHERE school IN (SELECT school FROM u2 WHERE u1.name = u2.name) OR hoppy = "abc";
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
| id | select_type        | table | partitions | type           | possible_keys       | key        | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | u1    | NULL       | ALL            | NULL                | NULL       | NULL    | NULL | 9818 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | u2    | NULL       | index_subquery | idx_name,idx_school | idx_school | 303     | func |    1 |    10.00 | Using where |
+----+--------------------+-------+------------+----------------+---------------------+------------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

range
當使用索引進行范圍查詢時，其訪問類型為range糖儡。

mysql> EXPLAIN SELECT * FROM u1 WHERE id > 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL | 4909 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> EXPLAIN SELECT * FROM u1 WHERE name LIKE "abc%";
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | range | idx_name      | idx_name | 303     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

index
如果查詢中用的是覆蓋索引伐坏，索引中的數(shù)據(jù)就可滿足查詢需要，此時的訪問方法為index握联。

mysql> EXPLAIN SELECT street FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

ALL
當使用全表掃描的方式進行查詢時桦沉，訪問方法為ALL。通常金闽，可以通過添加索引來避免ALL訪問方法纯露。

mysql> EXPLAIN SELECT * FROM u1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

possible_keys

possible_keys列表示MySQL執(zhí)行查詢時可能用到的索引。如果這一列為NULL代芜，則沒有使用到索引埠褪；這種情況下，需要檢查WHERE語句中所使用的的列挤庇，看是否可以通過給這些列中某個或多個添加索引的方法來提高查詢性能钞速。

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" AND school = "abc";
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys       | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name,idx_school | idx_name | 303     | const |    1 |     5.00 | Using where |
+----+-------------+-------+------------+------+---------------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以看到，possible_keys列顯示了兩種可用的索引idx_name和idx_school嫡秕，從key列可以看到渴语，實際使用的是idx_name索引。

需要注意的一點是昆咽，當type為index時驾凶，會出現(xiàn)possible_keys為NULL而key列為實際使用到的索引的情況，如下所示：

mysql> EXPLAIN SELECT city FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

key
key列表示MySQL實際使用到的索引掷酗。
當使用覆蓋索引時狭郑，key有可能會命中possible_keys中不存在的索引。
key_len
key_len列表示MySQL實際使用的索引的最大長度汇在；當使用到聯(lián)合索引時，有可能是多個列的長度和脏答。
索引長度由三個部分組成：
1糕殉、索引本身字節(jié)數(shù)
對于固定長度類型的索引列來說，就是它實際占用的存儲空間的長度殖告，比如說int類型占用4個字節(jié)阿蝶；對于可變長度類型來說，是它實際占用的存儲空間的最大長度黄绩，比如說varchar(100)類型羡洁，如果編碼類型為utf8，那么該長度為100*3=300個字節(jié)爽丹，如果編碼類型為utf8mb4筑煮，那么該長度為100*4=400個字節(jié)
2辛蚊、如果該索引列允許為NULL值，則key_len再加1個字節(jié)真仲。
3袋马、如果是變長字段，則key_len再加2個字節(jié)秸应。

示例如下：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

由于name列為varchar(100)類型虑凛，編碼格式為utf8并且允許為NULL，則計算公式如下：

100 * 3 + 1 + 2 = 303

再來看一下固定長度索引的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

上例中使用到了主鍵索引软啼，int類型且不允許為NULL桑谍，索引key_len列顯示為4。

ref
ref列顯示的是祸挪，通過索引列做等值比較查詢時（也就是在訪問方法是const锣披、eq_ref、ref匕积、ref_or_null盈罐、unique_subquery、index_subquery其中之一時）闪唆，哪些列或常量被用來與key列中的索引做比較盅粪。
示例如下：

mysql> EXPLAIN SELECT * FROM u1 WHERE id = 1000;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | u1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE  u1.name = u2.name;
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9899 |   100.00 | Using where |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u2.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

如果顯示為func，則表示使用到的值是某個函數(shù)的結(jié)果悄蕾。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE  u1.name = SUBSTRING(u2.name, 1, 10);
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL     | NULL    | NULL | 9899 |   100.00 | NULL                  |
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | func |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+----------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

rows
rows列表示MySQL認為執(zhí)行查詢時所需要檢查的行數(shù)票顾。對于InnoDB表，這個值為估計值帆调，并不完全準確奠骄。
filtered
filtered列表示被查詢條件過濾后的留存百分比。最大值為100番刊，意味著實際并沒有過濾掉任何行含鳞。此值從100逐漸降低時，表示被過濾掉的行數(shù)越來越多芹务。
rows列表示MySQL要檢查的行數(shù)的估計值蝉绷，rows × filtered表示過濾后，要和下張表進行join的行數(shù)枣抱。

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.name = u2.name AND u1.hoppy = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | idx_name      | NULL     | NULL    | NULL       | 9818 |    10.00 | Using where |
|  1 | SIMPLE      | u2    | NULL       | ref  | idx_name      | idx_name | 303     | zz.u1.name |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+----------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

從執(zhí)行計劃可以看出熔吗，u1為驅(qū)動表，u2為被驅(qū)動表佳晶。驅(qū)動表預(yù)計掃描9818行記錄桅狠，有10%的記錄會被保留下來，也就是說驅(qū)動表大概有9818 * 10% = 981.8條記錄被用來u2表做連接，也就是說被驅(qū)動表將會被執(zhí)行約982次中跌。

Extra

這列包含了MySQL解析查詢的額外信息咨堤，通過這些信息，可以更準確的理解MySQL到底是如何執(zhí)行查詢的晒他。
Extra列信息比較多吱型，我們挑一些常用的來講解一下。

Using index
在使用了覆蓋索引時陨仅，Extra列會顯示Using index信息津滞。
如下所示：

mysql> EXPLAIN SELECT city, street FROM u1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | index | NULL          | idx_address | 909     | NULL | 9818 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using index condition
索引條件下推的詳細內(nèi)容我在單獨的一篇文章里介紹過了，不熟悉的小伙伴可以看這里：一文讀懂什么是MySQL索引下推（ICP）

如果查詢優(yōu)化器選擇使用索引條件下推這個特性灼伤，在Extra列中將會顯示Using index condition触徐，比如下面的查詢：

mysql> EXPLAIN SELECT * FROM u1 WHERE city = "abc" AND district LIKE "%abc";
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_address   | idx_address | 303     | const |    1 |    11.11 | Using index condition |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

Using where
如果查詢沒有用到索引（也就是全表掃描），那么當查詢語句的WHERE條件中有針對該表的列搜索時狐赡，Extra列會顯示Using where撞鹉。請看下面的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE hoppy = 'abc';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果查詢使用到了索引，那么當WHERE條件中包含該索引之外的列時颖侄，Extra列會顯示Using where鸟雏。請看下面的示例：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "abc" AND hoppy = "abc";
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | u1    | NULL       | ref  | idx_name      | idx_name | 303     | const |    1 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Using join buffer (Block Nested Loop)
在連接查詢執(zhí)行過程中，如果被驅(qū)動表不能有效的利用索引加快查詢速度览祖，MySQL一般會為其分配一塊名叫join buffer的內(nèi)存塊來加快查詢速度孝鹊，也就是基于塊的嵌套循環(huán)算法，比如下面這個查詢：

mysql> EXPLAIN SELECT * FROM u1, u2 WHERE u1.hoppy = u2.hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9899 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

Using sort_union(...), Using union(...), Using intersect(...)
如果執(zhí)行計劃的Extra列出現(xiàn)了Using intersect(...)提示展蒂，說明準備使用Intersect索引合并的方式執(zhí)行查詢又活，括號中的...表示需要進行索引合并的索引名稱；如果出現(xiàn)了Using union(...)提示锰悼，說明準備使用Union索引合并的方式執(zhí)行查詢柳骄；出現(xiàn)了Using sort_union(...)提示，說明準備使用Sort-Union索引合并的方式執(zhí)行查詢箕般。

比如這個查詢的執(zhí)行計劃耐薯，使用到了Intersect索引合并：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "a" AND school = "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                             |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    1 |   100.00 | Using intersect(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+---------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

再比如下面這個查詢的執(zhí)行計劃，使用到了Union索引合并：

mysql> EXPLAIN SELECT * FROM u1 WHERE name = "a" OR school = "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |    8 |   100.00 | Using union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

下面這個查詢語句丝里，使用到了Sort-Union索引合并:

mysql> EXPLAIN SELECT * FROM u1 WHERE name < "a" OR school < "b";
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys       | key                 | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u1    | NULL       | index_merge | idx_name,idx_school | idx_name,idx_school | 303,303 | NULL |  395 |   100.00 | Using sort_union(idx_name,idx_school); Using where |
+----+-------------+-------+------------+-------------+---------------------+---------------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

Using filesort
有一些情況下對結(jié)果集中的記錄進行排序時可以使用到索引可柿，但是在很多情況下，無法使用到索引丙者，只能在內(nèi)存（數(shù)據(jù)較少）或磁盤（數(shù)據(jù)較大）中進行排序。這種在內(nèi)存中或者磁盤上進行排序的方式統(tǒng)稱為文件排序（英文名：filesort）营密。如果某個查詢需要使用文件排序的方式執(zhí)行查詢械媒，就會在執(zhí)行計劃的Extra列中顯示Using filesort提示，比如下面這條語句：

mysql> EXPLAIN SELECT * FROM u1  ORDER BY hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

$\color{red}{Tips}$ ：通常來講，出現(xiàn)文件排序會使查詢性能變差纷捞，應(yīng)該盡量避免痢虹，盡量將文件排序提升為索引排序。

Using temporary
在許多查詢的執(zhí)行過程中主儡，MySQL可能會借助臨時表來完成一些功能奖唯，比如去重、排序之類的糜值。比如我們在執(zhí)行許多包含DISTINCT丰捷、GROUP BY、UNION等子句的查詢過程中寂汇，如果不能有效利用索引來完成查詢病往，MySQL很有可能尋求通過建立內(nèi)部的臨時表來執(zhí)行查詢。如果查詢中使用到了內(nèi)部的臨時表骄瓣，在執(zhí)行計劃的Extra列將會顯示Using temporary提示停巷，比如下面的語句：

mysql> EXPLAIN SELECT  hoppy, count(*) as cnt  FROM u1  GROUP BY hoppy;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

可以看到，上述執(zhí)行計劃的Extra列不僅僅包含Using temporary提示榕栏，還包含Using filesort提示畔勤，可是我們的查詢語句中明明沒有寫ORDER BY子句呀？這是因為MySQL會在包含GROUP BY子句的查詢中默認添加上ORDER BY子句扒磁。如果我們并不想為包含GROUP BY子句的查詢進行排序庆揪，需要我們顯式的寫上ORDER BY NULL，就像這樣：

mysql> EXPLAIN SELECT  hoppy, count(*) as cnt  FROM u1  GROUP BY hoppy ORDER BY NULL;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | u1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9818 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

可見渗磅，Extra列里沒有出現(xiàn)Using filesort提示了嚷硫，這就節(jié)約了排序成本，提升了查詢效率始鱼。

$\color{red}{Tips}$ ：與Using filesort類似仔掸，Extra列出現(xiàn)Using temporary也不是什么好事，應(yīng)該盡量避免医清。

參考與感謝：

1起暮、《MySQL 是怎樣運行的：從根兒上理解 MySQL》
2、MySQL官方手冊

最后編輯于：2021.03.22 21:58:17

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

人面猴
序言：七十年代末会烙，一起剝皮案震驚了整個濱河市负懦，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌柏腻，老刑警劉巖纸厉，帶你破解...
沈念sama閱讀 216,843評論 6贊 502
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異五嫂，居然都是意外死亡颗品，警方通過查閱死者的電腦和手機肯尺，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 92,538評論 3贊 392
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進店門，熙熙樓的掌柜王于貴愁眉苦臉地迎上來躯枢，“玉大人则吟，你說我怎么就攤上這事〕澹” “怎么了氓仲？”我有些...
開封第一講書人閱讀 163,187評論 0贊 353
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長得糜。經(jīng)常有香客問我敬扛，道長，這世上最難降的妖魔是什么掀亩？我笑而不...
開封第一講書人閱讀 58,264評論 1贊 292
?港島之戀（遺憾婚禮）
正文為了忘掉前任舔哪，我火速辦了婚禮，結(jié)果婚禮上槽棍，老公的妹妹穿的比我還像新娘捉蚤。我一直安慰自己，他們只是感情好炼七，可當我...
茶點故事閱讀 67,289評論 6贊 390
惡毒庶女頂嫁案：這布局不是一般人想出來的
文/花漫我一把揭開白布缆巧。她就那樣靜靜地躺著，像睡著了一般豌拙。火紅的嫁衣襯著肌膚如雪陕悬。梳的紋絲不亂的頭發(fā)上，一...
開封第一講書人閱讀 51,231評論 1贊 299
城市分裂傳說
那天按傅，我揣著相機與錄音捉超，去河邊找鬼。笑死唯绍，一個胖子當著我的面吹牛拼岳，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播况芒，決...
沈念sama閱讀 40,116評論 3贊 418
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開眼惜纸，長吁一口氣：“原來是場噩夢啊……” “哼！你這毒婦竟也來了绝骚？” 一聲冷哼從身側(cè)響起耐版，我...
開封第一講書人閱讀 38,945評論 0贊 275
萬榮殺人案實錄
序言：老撾萬榮一對情侶失蹤，失蹤者是張志新（化名）和其女友劉穎压汪，沒想到半個月后粪牲，有當?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 45,367評論 1贊 313
?護林員之死
正文獨居荒郊野嶺守林人離奇死亡止剖，尸身上長有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點故事閱讀 37,581評論 2贊 333
?白月光啟示錄
正文我和宋清朗相戀三年腺阳，在試婚紗的時候發(fā)現(xiàn)自己被綠了湿滓。大學時的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
茶點故事閱讀 39,754評論 1贊 348
活死人
序言：一個原本活蹦亂跳的男人離奇死亡舌狗，死狀恐怖，靈堂內(nèi)的尸體忽然破棺而出扔水，到底是詐尸還是另有隱情痛侍，我是刑警寧澤，帶...
沈念sama閱讀 35,458評論 5贊 344
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布魔市，位于F島的核電站主届，受9級特大地震影響，放射性物質(zhì)發(fā)生泄漏待德。R本人自食惡果不足惜君丁，卻給世界環(huán)境...
茶點故事閱讀 41,068評論 3贊 327
男人毒藥：我在死后第九天來索命
文/蒙蒙一、第九天我趴在偏房一處隱蔽的房頂上張望将宪。院中可真熱鬧绘闷，春花似錦、人聲如沸较坛。這莊子的主人今日做“春日...
開封第一講書人閱讀 31,692評論 0贊 22
一樁弒父案，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽丑勤。三九已至华嘹，卻和暖如春，著一層夾襖步出監(jiān)牢的瞬間法竞，已是汗流浹背耙厚。一陣腳步聲響...
開封第一講書人閱讀 32,842評論 1贊 269
情欲美人皮
我被黑心中介騙來泰國打工，沒想到剛下飛機就差點兒被人妖公主榨干…… 1. 我叫王不留岔霸，地道東北人薛躬。一個月前我還...
沈念sama閱讀 47,797評論 2贊 369
代替公主和親
正文我出身青樓，卻偏偏與公主長得像秉剑，于是被迫代替她去往敵國和親泛豪。傳聞我的和親對象是個殘疾皇子，可洞房花燭夜當晚...
茶點故事閱讀 44,654評論 2贊 354