MySQL 5.7 EXPLAIN EXTENDED語句說明

EXPLAIN EXTENDED相比EXPLAIN命令，會額外顯示一個filtered字段。這個字段會指示出表的條件所過濾的表中行數的百分比。除此之外，在執行EXPLAIN EXTENDED命令之后，使用SHOW WARNINGS語句可以查看額外的語句信息。在MySQL 5.7.3，EXPLAIN命令會自動帶EXTENDED參數。

mysql> EXPLAIN EXTENDED SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1681
Message: 'EXTENDED' is deprecated and will be removed in a future release.
*************************** 2. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `fire`.`t1`.`a` AS `a`,<in_optimizer>(`fire`.`t1`.`a`,`fire`.`t1`.`a` in ( <materialize> (/* select#2 */ select `fire`.`t2`.`a` from `fire`.`t2` where 1 having 1 ), <primary_index_lookup>(`fire`.`t1`.`a` in <temporary table> on <auto_key> where ((`fire`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a IN (SELECT t2.a FROM t2)` from `fire`.`t1`
2 rows in set (0.00 sec)

mysql> EXPLAIN SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
|  2 | SUBQUERY    | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `fire`.`t1`.`a` AS `a`,<in_optimizer>(`fire`.`t1`.`a`,`fire`.`t1`.`a` in ( <materialize> (/* select#2 */ select `fire`.`t2`.`a` from `fire`.`t2` where 1 having 1 ), <primary_index_lookup>(`fire`.`t1`.`a` in <temporary table> on <auto_key> where ((`fire`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a IN (SELECT t2.a FROM t2)` from `fire`.`t1`
1 row in set (0.00 sec)