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)
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)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/26506993/viewspace-2117237/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql語句分析工具explain使用說明MySqlAI
- MySQL 5.7 SELECT ... LOCK IN SHARE MODE|FOR UPDATE語句說明MySql
- MySQL 5.7 複製控制語句SET GLOBAL sql_slave_skip_counter說明MySql
- MySQL 的CASE WHEN 語句使用說明MySql
- MySQL 的 EXPLAIN 語句及用法MySqlAI
- MySQL中explain語句的使用MySqlAI
- Explain Extended檢視MySQL的優化AIMySql優化
- MySQL EXPLAIN命令的主要專案說明MySqlAI
- mysql5.7 General tablespace使用說明MySql
- MySQL 5.7 mysqlpump 備份工具說明MySql
- MySQL 5.5 INSERT ... ON DUPLICATE KEY UPDATE語句說明MySql
- MySQL 5.7 LIMIT語句介紹MySqlMIT
- MySQL 5.7 REPLACE語句的用法MySql
- MySQL 5.7 PREPARE、EXECUTE、DEALLOCATE語句介紹MySql
- MySQL 5.7 自增欄位相關引數說明MySql
- Mysql效能最佳化(三)--explain返回的結果說明MySqlAI
- 淺析MySQL語句優化中的explain引數MySql優化AI
- Mysql效能調優工具Explain結合語句講解MySqlAI
- ORACLE執行計劃 explain說明OracleAI
- mysql操作命令梳理(5)-執行sql語句查詢即mysql狀態說明MySql
- MySQL 5.7 的事務控制語句的介紹MySql
- 用EXPLAIN PLAN 分析SQL語句AISQL
- MySQL(十四)分析查詢語句Explain 七千字總結MySqlAI
- Oracle 執行計劃(Explain Plan) 說明OracleAI
- Oracle 執行計劃(Explain Plan) 說明OracleAI
- MySQL查詢語句過程和EXPLAIN語句的基本概念及其最佳化MySqlAI
- MySQL 5.7 新備份工具mysqlpump 使用說明 - 運維小結MySql運維
- MySQL 5.7 使用 SELECT ... INTO 語句匯出資料檔案MySql
- oracle 使用explain plan分析查詢語句OracleAI
- mysql 版本說明MySql
- 說說 Python 的 if 語句Python
- mysql語句MySql
- mysql 變數說明MySql變數
- MySQL引數說明MySql
- 用sql語句建立表的時候給列指定說明(轉)SQL
- MySQL replace語句MySql
- MySQL的語句MySql
- mySql常用語句MySql