explain 用法详解

explain < table_name >
例如：

explain select * from t3 where id=3952602;
二、explain输出解释
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
1 id
SQL执行的顺利的标识，SQL从大到小的执行。

例如:

mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
很显然这条SQL是从里向外的执行，就是从id=3 向上执行。

2 select_type
就是select类型，可以有以下几种

（1）SIMPLE

简单SELECT（不使用UNION或子查询等）。

例如：

mysql> explain select * from t3 where id=3952602;
 +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
 | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra |
 +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
 |  1 | SIMPLE      | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       |
 +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+
（2）PRIMARY

就是最外层的select。

例如:

mysql> explain select * from (select * from t3 where id=3952602) a ;
 +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
 | id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
 +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
 |  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
 |  2 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
 +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
（3）UNION

UNION中的第二个或后面的SELECT语句。

例如

mysql> explain select * from t3 where id=3952602 union all select * from t3 ;
 +----+--------------+------------+-------+-------------------+---------+---------+