mysql 5.7 explain_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`,(`fire`.`t1`.`a`,`fire`.`t1`.`a` in ( (/* select#2 */ select `fire`.`t2`.`a` from `fire`.`t2` where 1 having 1 ), (`fire`.`t1`.`a` in on 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`,(`fire`.`t1`.`a`,`fire`.`t1`.`a` in ( (/* select#2 */ select `fire`.`t2`.`a` from `fire`.`t2` where 1 having 1 ), (`fire`.`t1`.`a` in on 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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2117237/，如需转载，请注明出处，否则将追究法律责任。