Explain在mysql的作用我想大家都明白,顯示了mysql如何使用索引來處理select語句以及連接表,可以幫助選擇更好的索引和寫出更優(yōu)化的查詢語句.

一.語法: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執(zhí)行的順利的標(biāo)識(shí),SQL從大到小的執(zhí)行.例如:

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是從里向外的執(zhí)行,就是從id=3 向上執(zhí)行.

2.select_type

就是select類型,可以有以下幾種:

(1) SIMPLE

簡(jiǎn)單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中的第二個(gè)或后面的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中的第二個(gè)或后面的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的結(jié)果,代碼如下:

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

子查詢中的第一個(gè)SELECT,代碼如下: