MySQL Explain详解（二）
2008-8-18 选择字号：
大 |
中 |
小
正在加载数据...
(3).union
union中的第二个或后面的select语句.例如
mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | union | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|NULL | union RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |
(4).DEPENDENT union
union中的第二个或后面的select语句，取决于外面的查询
mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3) ;
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | Using index |
| 3 | DEPENDENT union | t3 | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4 | func | 1 | Using where; Using index |
|NULL | union RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ |
(5).union RESULT
union的结果。
mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | union | t3 | ALL | NULL | NULL | NULL | NULL | 1000 | |
|NULL | union RESULT | | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ |
(6).SUBQUERY
子查询中的第一个select.
mysql> explain select * from t3 where id = (select id from t3 where id=3952602 ) ;
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | const | 1 | |
| 2 | SUBQUERY | t3 | const | PRIMARY,idx_t3_id | PRIMARY | 4 | | 1 | Using index |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+ |
MySQL Explain 详解
MySQL Explain详解（一）
MySQL Explain详解（二）
MySQL Explain详解（三）
MySQL Explain详解（四）
MySQL Explain详解（五）
MySQL Explain详解（六）
原文出处：http://space.itpub.net/7364032/viewspace-421320