mysql explain 用法详解

mysql explain 可以用来分析你要查询的sql语句的各项参数值,下面我们有详细的实例有需要的可以看看,代码如下:

EXPLAIN table == DESC table == SHOW COLUMNS FORM table

EXPLAIN [EXTENDED|PARTITIONS] SELECT...  --显示该语句将使用哪一个索引以及何时进行多表查询与使用到的表顺序,代码如下:

1. mysql> EXPLAIN SELECT * FROM BOOKS WHERE BOOK_ID=1; 
2.  
3. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
4.  
5. | id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra | 
6.  
7. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
8.  
9. |  1 | SIMPLE      | BOOKS | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 
10.  
11. +----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+ 
12.  
13. 1 row in set (0.00 sec) 

POSSIBLE_KEYS字段列举出了用于查找数据的索引,而KEY字段指示我们实际上用到了XX索引,如果POSSIBLE_KEYS字段的值显示NULL,那么说明没有用到索引.

SELECT_TYPE

SIMPLE   指示简单SELECT语句，没有子查询或者UNION

PRIMARY   当使用子查询时，这是主要的SELECT语句

UNION   当使用子查询时，这是主要的SELECT语句

DEPENDENT UNION  当使用UNION时，这并不是第一个SELECT语句，取决于主查询

UNION RESULT  UINON查询

SUBQUERY  子查询中的第一个SELECT语句

DEPENDENT SUBQUERY 子查询中的第一个SELECT语句,取决于主查询

DERIVED   来自于子查询的表

UNCACHEABLE SUBQUERY 指示子查询中的结果不能缓存,因此必须对主查询中的每一行重新评价.

UNCACHEABLE UNION 指示子查询的UNION中，结果不能缓存，因此必须对主查询中的每一行重新评价.

这是在官网上的说明,代码如下:

1. EXPLAIN Syntax 
2. EXPLAIN [EXTENDED] SELECT select_options 
3. Or:  
4. EXPLAIN tbl_name 

The EXPLAIN statement can be used either as a way to obtain information about how MySQL executes a statement, or as a synonym for DESCRIBE: 

When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the statement, including information about how tables are joined and in which order. EXPLAIN EXTENDED can be used to provide additional information.

例如如下代码:

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. +----+--------------------+------------+--------+-------------------+---------+---------+-------+------+--------------------------+ 

(5).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. +----+--------------+------------+-------+-------------------+---------+---------+-------+------+-------+ 

(6).SUBQUERY,子查询中的第一个SELECT,代码如下:

1. mysql> explain select * from t3 where id = (select id from t3 where id=3952602 )  ; 
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 | SUBQUERY    | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       |       |    1 | Using index |  --Vevb.com 
7. +----+-------------+-------+-------+-------------------+---------+---------+-------+------+-------------+ 

(7).DEPENDENT SUBQUERY,子查询中的第一个SELECT,取决于外面的查询,代码如下:

1. mysql> explain select id from t3 where id in (select id from t3 where id=3952602 )  ; 
2. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+ 
3. | id | select_type        | table | type  | possible_keys     | key     | key_len | ref   | rows | Extra                    | 
4. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+ 
5. |  1 | PRIMARY            | t3    | index | NULL              | PRIMARY | 4       | NULL  | 1000 | Using where; Using index | 
6. |  2 | DEPENDENT SUBQUERY | t3    | const | PRIMARY,idx_t3_id | PRIMARY | 4       | const |    1 | Using index              | 
7. +----+--------------------+-------+-------+-------------------+---------+---------+-------+------+--------------------------+ 

(8).DERIVED,派生表的SELECT(FROM子句的子查询),代码如下:

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. +----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+