MySQL 5.7 EXPLAIN EXTENDED语句说明
来源:互联网 发布:mac air使用教程视频 编辑:程序博客网 时间:2024/06/05 16:15
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)
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)
阅读全文
0 0
- MySQL 5.7 EXPLAIN EXTENDED语句说明
- MYSQL EXPLAIN语句的extended 选项学习体会
- MYSQL EXPLAIN语句的extended 选项学习体会
- mysql中explain说明
- mysql explain字段说明
- MYSQL explain说明
- mysql explain 说明
- mysql explain详细说明
- mysql explain说明
- explain extended 获取mysql优化后的sql
- MYSQL中EXPLAIN的说明
- MYSQL中EXPLAIN的说明
- MySQL的explain详细说明
- MYSQL中EXPLAIN的说明
- MYSQL中EXPLAIN的说明
- MYSQL中EXPLAIN的说明
- MYSQL中EXPLAIN的说明
- mysql之explain用法说明
- windows部署项目到服务器,文件名中文乱码
- Java 类的生命周期
- mybatis入门实战之CRUD
- python爬取糗事百科
- #支持向量机(SVM)与其理论发展(1)
- MySQL 5.7 EXPLAIN EXTENDED语句说明
- 次小生成树
- debug有感
- Python网络爬虫1
- vue.js 2.0实现的简单分页
- React实现按钮控制倒计时
- 基于vue2.0的一个分页组件
- dns-prefetch对网站速度能提升有多少?详解dns-prefetch
- Hibernate的入门