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