mysql 中如何使用explain

136次閱讀

沒有評論

共計 14675 個字符，預計需要花費 37 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

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

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

先看一個例子：

+—-+————-+———+——+—————+——+———+——+——–+——-+

+—-+————-+———+——+—————+——+———+——+——–+——-+

1 row in set (0.03 sec)

加上 extended 后之后：

+—-+————-+———+——+—————+——+———+——+——–+———-+——-+

+—-+————-+———+——+—————+——+———+——+——–+———-+——-+

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 的結果

SUBQUERY: 子查詢中的第一個 SELECT

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

DERIVED: 導出表的 SELECT(FROM 子句的子查詢)

table

輸出的行所引用的表

type

聯接類型。下面給出各種聯接類型, 按照從最佳類型到最壞類型進行排序:

system: 表僅有一行 (= 系統表)。這是 const 聯接類型的一個特例。

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

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

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

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

index_merge: 該聯接類型表示使用了索引合并優化方法。

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

index_subquery: 該聯接類型類似于 unique_subquery。可以替換 IN 子查詢, 但只適合下列形式的子查詢中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)

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

index: 該聯接類型與 ALL 相同, 除了只有索引樹被掃描。這通常比 ALL 快, 因為索引文件通常比數據文件小。

ALL: 對于每個來自于先前的表的行組合, 進行完整的表掃描。

possible_keys

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

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

該列包含 MySQL 解決查詢的詳細信息

Distinct:MySQL 發現第 1 個匹配行后, 停止為當前的行組合搜索更多的行。

Not exists:MySQL 能夠對查詢進行 LEFT JOIN 優化, 發現 1 個匹配 LEFT JOIN 標準的行后, 不再為前面的的行組合在該表內檢查更多的行。

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

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

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

Using temporary: 為了解決查詢,MySQL 需要創建一個臨時表來容納結果。

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

Using sort_union(…), Using union(…), Using intersect(…): 這些函數說明如何為 index_merge 聯接類型合并索引掃描。

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

一.select_type 的說明

1.UNION:

當通過 union 來連接多個查詢結果時，第二個之后的 select 其 select_type 為 UNION。

+—-+————–+————+——-+—————+———+———+——-+——+——-+

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

2.DEPENDENT UNION 與 DEPENDENT SUBQUERY:

當 union 作為子查詢時，其中第二個 union 的 select_type 就是 DEPENDENT UNION。
第一個子查詢的 select_type 則是 DEPENDENT SUBQUERY。

+—-+——————–+————+——-+—————+———+———+——-+——–+————-+

| NULL | UNION RESULT |
+—-+——————–+————+——-+—————+———+———+——-+——–+————-+

4 rows in set (0.03 sec)

3.SUBQUERY:

子查詢中的第一個 select 其 select_type 為 SUBQUERY。

+—-+————-+———+——-+—————+———+———+——-+——+————-+

+—-+————-+———+——-+—————+———+———+——-+——+————-+

2 rows in set (0.03 sec)

4.DERIVED:

當子查詢是 from 子句時，其 select_type 為 DERIVED。

+—-+————-+————+——–+—————+———+———+——+——+————-+

| 1 | PRIMARY |
+—-+————-+————+——–+—————+———+———+——+——+————-+

2 rows in set (0.03 sec)

二.type 的說明

1.system，const

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

2.eq_ref

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

+—-+————-+——-+——–+—————+———+———+—————–+——+————-+

| 1 | SIMPLE | b | ALL | order_id | NULL | NULL | NULL | 1 | |

+—-+————-+——-+——–+—————+———+———+—————–+——+————-+

2 rows in set (0.00 sec)

3.ref

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

+—-+————-+——-+——-+—————+———-+———+——-+——+——-+

| 1 | SIMPLE | a | const | PRIMARY | PRIMARY | 4 | const | 1 | |

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

+—-+————-+——-+——-+—————+———-+———+——-+——+——-+

2 rows in set (0.00 sec)

4.ref_or_null

user_id 字段是一個可以為空的字段，并對該字段創建了一個索引。在下面的查詢中可以看到聯接類型為 ref_or_null，這是 mysql 為含有 null 的字段專門做的處理。在我們的表設計中應當盡量避免索引字段為 NULL，因為這會額外的耗費 mysql 的處理時間來做優化。

+—-+————-+———+————-+—————+———+———+——-+——-+————-+

+—-+————-+———+————-+—————+———+———+——-+——-+————-+

1 row in set (0.00 sec)

5.index_merge

經常出現在使用一張表中的多個索引時。mysql 會將多個索引合并在一起，如下例:

+—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+

+—-+————-+———+————-+—————–+—————–+———+——+——+——————————————-+

1 row in set (0.09 sec)

6.unique_subquery

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

+—-+——————–+———+—————–+—————–+———+———+——+——–+————-+

+—-+——————–+———+—————–+—————–+———+———+——+——–+————-+

2 rows in set (0.00 sec)

7.index_subquery

該聯接類型與上面的太像了，唯一的差別就是子查詢查的不是主鍵而是非唯一索引。

+—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+

+—-+——————–+———+—————-+—————–+———+———+——+——–+————————–+

2 rows in set (0.00 sec)

8.range

按指定的范圍進行檢索，很常見。

+—-+————-+———+——-+—————+———+———+——+——+————-+

+—-+————-+———+——-+—————+———+———+——+——+————-+

1 row in set (0.00 sec)

9.index

在進行統計時非常常見，此聯接類型實際上會掃描索引樹，僅比 ALL 快些。

+—-+————-+———+——-+—————+———+———+——+——–+————-+

+—-+————-+———+——-+—————+———+———+——+——–+————-+

1 row in set (0.00 sec)

10.ALL

完整的掃描全表，最慢的聯接類型，盡可能的避免。

+—-+————-+———+——+—————+——+———+——+——–+——-+

+—-+————-+———+——+—————+——+———+——+——–+——-+

1 row in set (0.00 sec)

三.extra 的說明

1.Distinct

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

2.Not exists

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

+—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+

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

+—-+————-+——-+——-+—————+————–+———+—————–+——–+————————————–+

2 rows in set (0.01 sec)

3.Range checked for each record

這種情況是 mysql 沒有發現好的索引可用，速度比沒有索引要快得多。

+—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+

+—-+————-+——-+——-+———————-+————–+———+——+——+————————————————+

2 rows in set (0.00 sec)

4.Using filesort

在有排序子句的情況下很常見的一種情況。此時 mysql 會根據聯接類型瀏覽所有符合條件的記錄，并保存排序關鍵字和行指針，然后排序關鍵字并按順序檢索行。

+—-+————-+———+——+—————+——+———+——+——–+—————-+

+—-+————-+———+——+—————+——+———+——+——–+—————-+

1 row in set (0.00 sec)

5.Using index

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

6.Using temporary

發生這種情況一般都是需要進行優化的。mysql 需要創建一張臨時表用來處理此類查詢。

+—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+

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

+—-+————-+——-+——+—————+———-+———+—————–+——–+———————————+

2 rows in set (0.00 sec)

7.Using where

當有 where 子句時，extra 都會有說明。

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

下面的例子中 user_id 是一個檢索范圍，此時 mysql 會使用 sort_union 函數來進行索引的合并。而當 user_id 是一個固定值時，請參看上面 type 說明 5.index_merge 的例子，此時會使用 union 函數進行索引合并。

+—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+

+—-+————-+———+————-+—————–+—————–+———+——+——+————————————————+

1 row in set (0.00 sec)

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

+—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+

+—-+————-+———+————-+———————-+———————-+———+——+——+—————————————————-+

1 row in set (0.00 sec)

9.Using index for group-by

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

+—-+————-+———+——-+—————+———+———+——+——+————————–+

+—-+————-+———+——-+—————+———+———+——+——+————————–+

1 row in set (0.00 sec)

上述內容就是 mysql 中如何使用 explain，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關注丸趣 TV 行業資訊頻道。

向 AI 問一下細節

正文完

發表至：數據庫

2023-12-04

轉載說明：除特殊說明外本站除技術相關以外文章皆由網絡搜集發布，轉載請注明出處。

如何快速完成mysql數據遷移

久久精品人人爽,华人av在线,亚洲性视频网站,欧美专区一二三

mysql 中如何使用explain