mysql 中如何使用explain

本篇文章為大家展示了 中如何使用explain，內容簡(jiǎn)明扼要并且容易理解，絕對能使你眼前一亮，通過(guò)這篇文章的詳細介紹希望你能有所收獲。

重點(diǎn)是第二種用法，需要深入的了解。

先看一個(gè)例子：

1. mysql | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra | 

2. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 

3. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100453 |       | 

4. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 

5. 1 row in set (0.03 sec) 

加上extended后之后：

1. mysql | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra | 

2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+ 

3. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100453 |   100.00 |       | 

4. +----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+ 

5. 1 row in set, 1 warning (0.00 sec) 

有必要解釋一下這個(gè)長(cháng)長(cháng)的表格里每一列的含義：

一.select_type的說(shuō)明

1.UNION:

當通過(guò)union來(lái)連接多個(gè)查詢(xún)結果時(shí)，第二個(gè)之后的select其select_type為UNION。

1. mysql | id | select_type  | table      | type  | possible_keys | key     | key_len | ref   | rows | Extra | 

2. +----+--------------+------------+-------+---------------+---------+---------+-------+------+-------+ 

3. |  1 | PRIMARY      | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 

4. |  2 | UNION        | t_order    | const | PRIMARY       | PRIMARY | 4       | const |    1 |       | 

5. | NULL | UNION RESULT |  3 rows in set (0.34 sec) 

2.DEPENDENT UNION與DEPENDENT SUBQUERY:

當union作為子查詢(xún)時(shí)，其中第二個(gè)union的select_type就是DEPENDENT UNION。
第一個(gè)子查詢(xún)的select_type則是DEPENDENT SUBQUERY。

1. mysql | id | select_type        | table      | type  | possible_keys | key     | key_len | ref   | rows   | Extra       | 

2. +----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+ 

3. |  1 | PRIMARY            | t_order    | ALL   | NULL          | NULL    | NULL    | NULL  | 100453 | Using where | 

4. |  2 | DEPENDENT SUBQUERY | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 

5. |  3 | DEPENDENT UNION    | t_order    | const | PRIMARY       | PRIMARY | 4       | const |      1 | Using index | 

6. | NULL | UNION RESULT       |  +----+--------------------+------------+-------+---------------+---------+---------+-------+--------+-------------+ 

7. 4 rows in set (0.03 sec) 

3.SUBQUERY:

子查詢(xún)中的第一個(gè)select其select_type為SUBQUERY。

1. mysql | id | select_type | table   | type  | possible_keys | key     | key_len | ref   | rows | Extra       | 

2. +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 

3. |  1 | PRIMARY     | t_order | const | PRIMARY       | PRIMARY | 4       | const |    1 |             | 

4. |  2 | SUBQUERY    | t_order | const | PRIMARY       | PRIMARY | 4       |       |    1 | Using index | 

5. +----+-------------+---------+-------+---------------+---------+---------+-------+------+-------------+ 

6. 2 rows in set (0.03 sec) 

4.DERIVED:

當子查詢(xún)是from子句時(shí)，其select_type為DERIVED。

1. mysql | id | select_type | table      | type   | possible_keys | key     | key_len | ref  | rows | Extra       | 

2. +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ 

3. |  1 | PRIMARY     |  +----+-------------+------------+--------+---------------+---------+---------+------+------+-------------+ 

4. 2 rows in set (0.03 sec) 

二.type的說(shuō)明

1.system，const

見(jiàn)上面4.DERIVED的例子。其中第一行的type就是為system，第二行是const，這兩種聯(lián)接類(lèi)型是最快的。

2.eq_ref

在t_order表中的order_id是主鍵，t_order_ext表中的order_id也是主鍵，該表可以認為是訂單表的補充信息表，他們的關(guān)系是1對1，在下面的例子中可以看到b表的連接類(lèi)型是eq_ref，這是極快的聯(lián)接類(lèi)型。

1. mysql | id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows | Extra       | 

2. +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ 

3. |  1 | SIMPLE      | b     | ALL    | order_id      | NULL    | NULL    | NULL            |    1 |             | 

4. |  1 | SIMPLE      | a     | eq_ref | PRIMARY       | PRIMARY | 4       | test.b.order_id |    1 | Using where | 

5. +----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------------+ 

6. 2 rows in set (0.00 sec) 

3.ref

下面的例子在上面的例子上略作了修改，加上了條件。此時(shí)b表的聯(lián)接類(lèi)型變成了ref。因為所有與a表中order_id=100的匹配記錄都將會(huì )從b表獲取。這是比較常見(jiàn)的聯(lián)接類(lèi)型。

1. mysql | id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows | Extra | 

2. +----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+ 

3. |  1 | SIMPLE      | a     | const | PRIMARY       | PRIMARY  | 4       | const |    1 |       | 

4. |  1 | SIMPLE      | b     | ref   | order_id      | order_id | 4       | const |    1 |       | 

5. +----+-------------+-------+-------+---------------+----------+---------+-------+------+-------+ 

6. 2 rows in set (0.00 sec) 

4.ref_or_null

user_id字段是一個(gè)可以為空的字段，并對該字段創(chuàng )建了一個(gè)索引。在下面的查詢(xún)中可以看到聯(lián)接類(lèi)型為ref_or_null，這是mysql為含有null的字段專(zhuān)門(mén)做的處理。在我們的表設計中應當盡量避免索引字段為NULL，因為這會(huì )額外的耗費mysql的處理時(shí)間來(lái)做優(yōu)化。

1. mysql | id | select_type | table   | type        | possible_keys | key     | key_len | ref   | rows  | Extra       | 

2. +----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+ 

3. |  1 | SIMPLE      | t_order | ref_or_null | user_id       | user_id | 5       | const | 50325 | Using where | 

4. +----+-------------+---------+-------------+---------------+---------+---------+-------+-------+-------------+ 

5. 1 row in set (0.00 sec) 

5.index_merge

經(jīng)常出現在使用一張表中的多個(gè)索引時(shí)。mysql會(huì )將多個(gè)索引合并在一起，如下例:

1. mysql | id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                     | 

2. +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+ 

3. |  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | PRIMARY,user_id | 4,5     | NULL |    2 | Using union(PRIMARY,user_id); Using where | 

4. +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+-------------------------------------------+ 

5. 1 row in set (0.09 sec) 

6.unique_subquery

該聯(lián)接類(lèi)型用于替換value IN (SELECT primary_key FROM single_table WHERE some_expr)這樣的子查詢(xún)的ref。注意ref列，其中第二行顯示的是func，表明unique_subquery是一個(gè)函數，而不是一個(gè)普通的ref。

1. mysql | id | select_type        | table   | type            | possible_keys   | key     | key_len | ref  | rows   | Extra       | 

2. +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+ 

3. |  1 | PRIMARY            | t_order | ALL             | NULL            | NULL    | NULL    | NULL | 100649 | Using where | 

4. |  2 | DEPENDENT SUBQUERY | t_order | unique_subquery | PRIMARY,user_id | PRIMARY | 4       | func |      1 | Using where | 

5. +----+--------------------+---------+-----------------+-----------------+---------+---------+------+--------+-------------+ 

6. 2 rows in set (0.00 sec) 

7.index_subquery

該聯(lián)接類(lèi)型與上面的太像了，唯一的差別就是子查詢(xún)查的不是主鍵而是非唯一索引。

1. mysql | id | select_type        | table   | type           | possible_keys   | key     | key_len | ref  | rows   | Extra                    | 

2. +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+ 

3. |  1 | PRIMARY            | t_order | ALL            | NULL            | NULL    | NULL    | NULL | 100649 | Using where              | 

4. |  2 | DEPENDENT SUBQUERY | t_order | index_subquery | PRIMARY,user_id | user_id | 5       | func |  50324 | Using index; Using where | 

5. +----+--------------------+---------+----------------+-----------------+---------+---------+------+--------+--------------------------+ 

6. 2 rows in set (0.00 sec) 

8.range

按指定的范圍進(jìn)行檢索，很常見(jiàn)。

1. mysql | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra       | 

2. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 

3. |  1 | SIMPLE      | t_order | range | user_id       | user_id | 5       | NULL |    3 | Using where | 

4. +----+-------------+---------+-------+---------------+---------+---------+------+------+-------------+ 

5. 1 row in set (0.00 sec) 

9.index

在進(jìn)行統計時(shí)非常常見(jiàn)，此聯(lián)接類(lèi)型實(shí)際上會(huì )掃描索引樹(shù)，僅比ALL快些。

1. mysql | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows   | Extra       | 

2. +----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+ 

3. |  1 | SIMPLE      | t_order | index | NULL          | user_id | 5       | NULL | 100649 | Using index | 

4. +----+-------------+---------+-------+---------------+---------+---------+------+--------+-------------+ 

5. 1 row in set (0.00 sec) 

10.ALL

完整的掃描全表，最慢的聯(lián)接類(lèi)型，盡可能的避免。

1. mysql | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra | 

2. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 

3. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100649 |       | 

4. +----+-------------+---------+------+---------------+------+---------+------+--------+-------+ 

5. 1 row in set (0.00 sec) 

三.extra的說(shuō)明

1.Distinct

MySQL發(fā)現第1個(gè)匹配行后,停止為當前的行組合搜索更多的行。對于此項沒(méi)有找到合適的例子，求指點(diǎn)。

2.Not exists

因為b表中的order_id是主鍵，不可能為NULL，所以mysql在用a表的order_id掃描t_order表，并查找b表的行時(shí)，如果在b表發(fā)現一個(gè)匹配的行就不再繼續掃描b了，因為b表中的order_id字段不可能為NULL。這樣避免了對b表的多次掃描。

1. mysql | id | select_type | table | type  | possible_keys | key          | key_len | ref             | rows   | Extra                                | 

2. +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+ 

3. |  1 | SIMPLE      | a     | index | NULL          | express_type | 1       | NULL            | 100395 | Using index                          | 

4. |  1 | SIMPLE      | b     | ref   | order_id      | order_id     | 4       | test.a.order_id |      1 | Using where; Using index; Not exists | 

5. +----+-------------+-------+-------+---------------+--------------+---------+-----------------+--------+--------------------------------------+ 

6. 2 rows in set (0.01 sec) 

3.Range checked for each record

這種情況是mysql沒(méi)有發(fā)現好的索引可用，速度比沒(méi)有索引要快得多。

1. mysql | id | select_type | table | type  | possible_keys        | key          | key_len | ref  | rows | Extra                                          | 

2. +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+ 

3. |  1 | SIMPLE      | t     | range | PRIMARY,express_type | express_type | 1       | NULL |    1 | Using where                                    | 

4. |  1 | SIMPLE      | s     | ALL   | order_id             | NULL         | NULL    | NULL |    1 | Range checked for each record (index map: 0x1) | 

5. +----+-------------+-------+-------+----------------------+--------------+---------+------+------+------------------------------------------------+ 

6. 2 rows in set (0.00 sec)

4.Using filesort

在有排序子句的情況下很常見(jiàn)的一種情況。此時(shí)mysql會(huì )根據聯(lián)接類(lèi)型瀏覽所有符合條件的記錄，并保存排序關(guān)鍵字和行指針，然后排序關(guān)鍵字并按順序檢索行。

1. mysql | id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows   | Extra          | 

2. +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+ 

3. |  1 | SIMPLE      | t_order | ALL  | NULL          | NULL | NULL    | NULL | 100395 | Using filesort | 

4. +----+-------------+---------+------+---------------+------+---------+------+--------+----------------+ 

5. 1 row in set (0.00 sec) 

5.Using index

這是性能很高的一種情況。當查詢(xún)所需的數據可以直接從索引樹(shù)中檢索到時(shí)，就會(huì )出現。上面的例子中有很多這樣的例子，不再多舉例了。

6.Using temporary

發(fā)生這種情況一般都是需要進(jìn)行優(yōu)化的。mysql需要創(chuàng )建一張臨時(shí)表用來(lái)處理此類(lèi)查詢(xún)。

1. mysql | id | select_type | table | type | possible_keys | key      | key_len | ref             | rows   | Extra                           | 

2. +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+ 

3. |  1 | SIMPLE      | a     | ALL  | NULL          | NULL     | NULL    | NULL            | 100395 | Using temporary; Using filesort | 

4. |  1 | SIMPLE      | b     | ref  | order_id      | order_id | 4       | test.a.order_id |      1 |                                 | 

5. +----+-------------+-------+------+---------------+----------+---------+-----------------+--------+---------------------------------+ 

6. 2 rows in set (0.00 sec) 

7.Using where

當有where子句時(shí)，extra都會(huì )有說(shuō)明。

8.Using sort_union(...)/Using union(...)/Using intersect(...)

下面的例子中user_id是一個(gè)檢索范圍，此時(shí)mysql會(huì )使用sort_union函數來(lái)進(jìn)行索引的合并。而當user_id是一個(gè)固定值時(shí)，請參看上面type說(shuō)明5.index_merge的例子，此時(shí)會(huì )使用union函數進(jìn)行索引合并。

1. mysql | id | select_type | table   | type        | possible_keys   | key             | key_len | ref  | rows | Extra                                          | 

2. +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+ 

3. |  1 | SIMPLE      | t_order | index_merge | PRIMARY,user_id | user_id,PRIMARY | 5,4     | NULL |    2 | Using sort_union(user_id,PRIMARY); Using where | 

4. +----+-------------+---------+-------------+-----------------+-----------------+---------+------+------+------------------------------------------------+ 

5. 1 row in set (0.00 sec) 

對于Using intersect的例子可以參看下例，user_id與express_type發(fā)生了索引交叉合并。

1. mysql | id | select_type | table   | type        | possible_keys        | key                  | key_len | ref  | rows | Extra                                              | 

2. +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+ 

3. |  1 | SIMPLE      | t_order | index_merge | user_id,express_type | user_id,express_type | 5,1     | NULL |    1 | Using intersect(user_id,express_type); Using where | 

4. +----+-------------+---------+-------------+----------------------+----------------------+---------+------+------+----------------------------------------------------+ 

5. 1 row in set (0.00 sec) 

9.Using index for group-by

表明可以在索引中找到分組所需的所有數據，不需要查詢(xún)實(shí)際的表。

1. mysql | id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows | Extra                    | 

2. +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 

3. |  1 | SIMPLE      | t_order | range | NULL          | user_id | 5       | NULL |    3 | Using index for group-by | 

4. +----+-------------+---------+-------+---------------+---------+---------+------+------+--------------------------+ 

5. 1 row in set (0.00 sec)