MySQL 5.7 EXPLAIN EXTENDED语句说明

千家信息网最后更新 2025年01月20日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)