溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

mysql 中如何使用explain

發(fā)布時間：2021-07-12 10:36:09 來源：億速云 閱讀：132 作者：Leah 欄目：MySQL數(shù)據(jù)庫

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

重點是第二種用法，需要深入的了解。

先看一個例子：

  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) 

有必要解釋一下這個長長的表格里每一列的含義：

id SELECT識別符。這是SELECT的查詢序列號
select_type

SELECT類型,可以為以下任何一種:

  • SIMPLE:簡單SELECT(不使用UNION或子查詢)

  • PRIMARY:最外面的SELECT

  • UNION:UNION中的第二個或后面的SELECT語句

  • DEPENDENT UNION:UNION中的第二個或后面的SELECT語句,取決于外面的查詢

  • UNION RESULT:UNION 的結(jié)果

  • SUBQUERY:子查詢中的第一個SELECT

  • DEPENDENT SUBQUERY:子查詢中的第一個SELECT,取決于外面的查詢

  • DERIVED:導(dǎo)出表的SELECT(FROM子句的子查詢)

table

輸出的行所引用的表

type

聯(lián)接類型。下面給出各種聯(lián)接類型,按照從最佳類型到最壞類型進(jìn)行排序:

  • system:表僅有一行(=系統(tǒng)表)。這是const聯(lián)接類型的一個特例。

  • const:表最多有一個匹配行,它將在查詢開始時被讀取。因為僅有一行,在這行的列值可被優(yōu)化器剩余部分認(rèn)為是常數(shù)。const表很快,因為它們只讀取一次!

  • eq_ref:對于每個來自于前面的表的行組合,從該表中讀取一行。這可能是最好的聯(lián)接類型,除了const類型。

  • ref:對于每個來自于前面的表的行組合,所有有匹配索引值的行將從這張表中讀取。

  • ref_or_null:該聯(lián)接類型如同ref,但是添加了MySQL可以專門搜索包含NULL值的行。

  • index_merge:該聯(lián)接類型表示使用了索引合并優(yōu)化方法。

  • unique_subquery:該類型替換了下面形式的IN子查詢的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) unique_subquery是一個索引查找函數(shù),可以完全替換子查詢,效率更高。

  • index_subquery:該聯(lián)接類型類似于unique_subquery?？梢蕴鎿QIN子查詢,但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

  • range:只檢索給定范圍的行,使用一個索引來選擇行。

  • index:該聯(lián)接類型與ALL相同,除了只有索引樹被掃描。這通常比ALL快,因為索引文件通常比數(shù)據(jù)文件小。

  • ALL:對于每個來自于先前的表的行組合,進(jìn)行完整的表掃描。

possible_keys

指出MySQL能使用哪個索引在該表中找到行

key 顯示MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL。
key_len 顯示MySQL決定使用的鍵長度。如果鍵是NULL,則長度為NULL。
ref 顯示使用哪個列或常數(shù)與key一起從表中選擇行。
rows 顯示MySQL認(rèn)為它執(zhí)行查詢時必須檢查的行數(shù)。多行之間的數(shù)據(jù)相乘可以估算要處理的行數(shù)。
filtered 顯示了通過條件過濾出的行數(shù)的百分比估計值。
Extra

該列包含MySQL解決查詢的詳細(xì)信息

  • Distinct:MySQL發(fā)現(xiàn)第1個匹配行后,停止為當(dāng)前的行組合搜索更多的行。

  • Not exists:MySQL能夠?qū)Σ樵冞M(jìn)行LEFT JOIN優(yōu)化,發(fā)現(xiàn)1個匹配LEFT JOIN標(biāo)準(zhǔn)的行后,不再為前面的的行組合在該表內(nèi)檢查更多的行。

  • range checked for each record (index map: #):MySQL沒有發(fā)現(xiàn)好的可以使用的索引,但發(fā)現(xiàn)如果來自前面的表的列值已知,可能部分索引可以使用。

  • Using filesort:MySQL需要額外的一次傳遞,以找出如何按排序順序檢索行。

  • Using index:從只使用索引樹中的信息而不需要進(jìn)一步搜索讀取實際的行來檢索表中的列信息。

  • Using temporary:為了解決查詢,MySQL需要創(chuàng)建一個臨時表來容納結(jié)果。

  • Using where:WHERE 子句用于限制哪一個行匹配下一個表或發(fā)送到客戶。

  • Using sort_union(...), Using union(...), Using intersect(...):這些函數(shù)說明如何為index_merge聯(lián)接類型合并索引掃描。

  • Using index for group-by:類似于訪問表的Using index方式,Using index for group-by表示MySQL發(fā)現(xiàn)了一個索引,可以用來查 詢GROUP BY或DISTINCT查詢的所有列,而不要額外搜索硬盤訪問實際的表。

一.select_type的說明

1.UNION:

當(dāng)通過union來連接多個查詢結(jié)果時，第二個之后的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:

當(dāng)union作為子查詢時，其中第二個union的select_type就是DEPENDENT UNION。
第一個子查詢的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:

子查詢中的第一個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:

當(dāng)子查詢是from子句時，其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的說明

1.system，const

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

2.eq_ref

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

  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

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

  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字段是一個可以為空的字段，并對該字段創(chuàng)建了一個索引。在下面的查詢中可以看到聯(lián)接類型為ref_or_null，這是mysql為含有null的字段專門做的處理。在我們的表設(shè)計中應(yīng)當(dāng)盡量避免索引字段為NULL，因為這會額外的耗費mysql的處理時間來做優(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)常出現(xiàn)在使用一張表中的多個索引時。mysql會將多個索引合并在一起，如下例:

  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)接類型用于替換value IN (SELECT primary_key FROM single_table WHERE some_expr)這樣的子查詢的ref。注意ref列，其中第二行顯示的是func，表明unique_subquery是一個函數(shù)，而不是一個普通的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)接類型與上面的太像了，唯一的差別就是子查詢查的不是主鍵而是非唯一索引。

  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)行檢索，很常見。

  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)行統(tǒng)計時非常常見，此聯(lián)接類型實際上會掃描索引樹，僅比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)接類型，盡可能的避免。

  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的說明

1.Distinct

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

2.Not exists

因為b表中的order_id是主鍵，不可能為NULL，所以mysql在用a表的order_id掃描t_order表，并查找b表的行時，如果在b表發(fā)現(xiàn)一個匹配的行就不再繼續(xù)掃描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沒有發(fā)現(xiàn)好的索引可用，速度比沒有索引要快得多。

  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

在有排序子句的情況下很常見的一種情況。此時mysql會根據(jù)聯(lián)接類型瀏覽所有符合條件的記錄，并保存排序關(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

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

6.Using temporary

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

  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

當(dāng)有where子句時，extra都會有說明。

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

下面的例子中user_id是一個檢索范圍，此時mysql會使用sort_union函數(shù)來進(jìn)行索引的合并。而當(dāng)user_id是一個固定值時，請參看上面type說明5.index_merge的例子，此時會使用union函數(shù)進(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

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

  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) 

上述內(nèi)容就是mysql 中如何使用explain，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。

向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手