温馨提示×

温馨提示×

您好,登录后才能下订单哦!

密码登录×
登录注册×
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》

MySQL 5.7 EXPLAIN EXTENDED语句说明

发布时间:2020-08-12 05:53:27 阅读:311 作者:feelpurple 栏目:MySQL数据库
亿速云mysql数据库,读写分离,安全稳定,弹性扩容,低至0.3元/天!! 点击查看>>
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)

亿速云「云数据库 MySQL」免部署即开即用,比自行安装部署数据库高出1倍以上的性能,双节点冗余防止单节点故障,数据自动定期备份随时恢复。点击查看>>

向AI问一下细节

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

原文链接:http://blog.itpub.net/26506993/viewspace-2117237/

AI

开发者交流群×