|NO.Z.00148|——————————|BigDataEnd|——|Java&MySQL.高级.V20|——|MySQL.v21|EXPLAIN_select_type介绍|

一、EXPLAIN_select_type介绍

### --- select_type和table介绍
~~~     查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询simple : 
~~~     简单的select查询，查询中不包含子查询或者UNION

EXPLAIN SELECT * FROM L1;
mysql> EXPLAIN SELECT * FROM L1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | L1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+

~~~     # primary : 查询中若包含任何复杂的子部分，最外层查询被标记

EXPLAIN SELECT * FROM L2 WHERE id = (
    SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
'yanqiu03'));

mysql> EXPLAIN SELECT * FROM L2 WHERE id = (
    ->     SELECT id FROM L1 WHERE id = (SELECT L3.id FROM L3 WHERE L3.title =
    -> 'yanqiu03'));
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+
|  1 | PRIMARY     | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  2 | SUBQUERY    | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | no matching row in const table |
|  3 | SUBQUERY    | L3    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where                    |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+

~~~     # subquery : 在select或where列表中包含了子查询

EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title = 'yanqi03' );

mysql> EXPLAIN SELECT * FROM L2 WHERE L2.id = (SELECT id FROM L3 WHERE L3.title = 'yanqi03' );
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | L2    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL        |
|  2 | SUBQUERY    | L3    | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+

——>        derived : 在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
——>        union : 如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
——>        union result : UNION 的结果

EXPLAIN SELECT * FROM L2
UNION
SELECT * FROM L3;

mysql> EXPLAIN SELECT * FROM L2
    -> UNION
    -> SELECT * FROM L3;
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | PRIMARY      | L2         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
|  2 | UNION        | L3         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+---------------+------+---------+------+------+----------+-----------------+

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---