msyql中Explain的用法详解

Explain在mysql的作用我想大家都明白,显示了mysql如何使用索引来处理select语句以及连接表,可以帮助选择更好的索引和写出更优化的查询语句.

一.语法:explain < table_name >

例如:explain select * from t3 where id=3952602;

二.explain输出解释,代码如下:

1. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ 
2. | id | select_type | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra | 
3. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------+ 

1.id

我的理解是SQL执行的顺利的标识,SQL从大到小的执行.例如:

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

很显然这条SQL是从里向外的执行,就是从id=3 向上执行.

2.select_type

就是select类型,可以有以下几种:

(1) SIMPLE

简单SELECT(不使用UNION或子查询等) 例如:

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

(2).PRIMARY

我的理解是最外层的select.例如:

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

(3).UNION

UNION中的第二个或后面的SELECT语句.例如

1. mysql> explain select * from t3 where id=3952602 union all select * from t3 ; 
2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 
3. | id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra | 
4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 
5. |  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       | 
6. |  2 | UNION        | t3         | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       | 
7. |NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       | 
8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+  

(4).DEPENDENT UNION

UNION中的第二个或后面的SELECT语句,取决于外面的查询,代码如下:

1. mysql> explain select * from t3 where id in (select id from t3 where id=3952602 union all select id from t3)  ; 
2. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ 
3. | id | select_type        | table      | type   | possible_keys     | key     | key_len | ref   | rows | Extra                    | 
4. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ 
5. |  1 | PRIMARY            | t3         | ALL    | NULL              | NULL    | NULL    | NULL  | 1000 | Using where              | 
6. |  2 | DEPENDENT SUBQUERY | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 | Using index              | 
7. |  3 | DEPENDENT UNION    | t3         | eq_ref | PRIMARY,idx_t3_id | PRIMARY | 4       | func  |    1 | Using where; Using index | 
8. |NULL | UNION RESULT       | <union2,3> | ALL    | NULL              | NULL    | NULL    | NULL  | NULL |                          | 
9. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ 

(4).UNION RESULT

UNION的结果,代码如下:

1. mysql> explain select * from t3 where id=3952602 union all select * from t3 ; 
2. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 
3. | id | select_type  | table      | type  | possible_keys     | key     | key_len | ref   | rows | Extra | 
4. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 
5. |  1 | PRIMARY      | t3         | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 |       | 
6. |  2 | UNION        | t3         | ALL   | NULL              | NULL    | NULL    | NULL  | 1000 |       | 
7. |NULL | UNION RESULT | <union1,2> | ALL   | NULL              | NULL    | NULL    | NULL  | NULL |       | 
8. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 

(5).SUBQUERY

子查询中的第一个SELECT,代码如下: