EXPLAIN 是 SQL 性能優(yōu)化的關(guān)鍵工具，它展示了 MySQL 如何執(zhí)行一條 SQL 語句。通過分析它的結(jié)果，你可以找出查詢的瓶頸并進行優(yōu)化。

下面我將對 EXPLAIN 進行詳細解析。

一、什么是 EXPLAIN？

EXPLAIN 關(guān)鍵字可以放在 SELECT、DELETE、INSERT、REPLACE 或 UPDATE 語句之前，MySQL 會返回該語句的執(zhí)行計劃，而不是真正執(zhí)行它。

執(zhí)行計劃揭示了 MySQL 優(yōu)化器決定如何訪問表、使用哪些索引、表之間的連接方式等信息。

基本語法：

EXPLAIN your_sql_statement;
-- 例如
EXPLAIN SELECT * FROM users WHERE name = 'John';

在 MySQL 8.0 之后，推薦使用 EXPLAIN FORMAT=TRADITIONAL（默認(rèn)格式），或者更詳細的 EXPLAIN ANALYZE（MySQL 8.0.18+ 引入，會實際執(zhí)行查詢并給出更精確的分析）。

二、EXPLAIN 輸出列詳解

執(zhí)行 EXPLAIN 后，你會得到一個包含多列的結(jié)果集。以下是這些列的含義，其中前幾列（type, key, rows, Extra）最為重要。

三、核心列深度解析

1.type（訪問類型）

性能從最優(yōu)到最差排序如下：

system > const > eq_ref > ref > range > index > ALL

system：表只有一行記錄（等于系統(tǒng)表），是 const 類型的特例。

const：通過索引一次就找到了，用于比較 主鍵索引 或 唯一索引 與常數(shù)值。速度極快。

EXPLAIN SELECT * FROM users WHERE id = 1;
-- `id` 是主鍵

eq_ref：在連接查詢時，使用 主鍵 或 唯一非空索引 進行關(guān)聯(lián)。對于來自前表的每一行，從本表中只返回一行。這是除了 system 和 const 之外最好的連接類型。

EXPLAIN SELECT * FROM users u 
JOIN orders o ON u.id = o.user_id;
-- `o.user_id` 是 `users` 表的主鍵 `id` 的外鍵，并且是唯一索引

ref：使用 非唯一性索引 進行掃描，返回匹配某個單獨值的所有行。

EXPLAIN SELECT * FROM users WHERE name = 'John';
-- `name` 字段上有一個普通索引（非唯一）

range：只檢索給定范圍的行，使用一個索引來選擇行。關(guān)鍵運算符是 BETWEEN、>、<、IN 等。

EXPLAIN SELECT * FROM users WHERE id > 10;
EXPLAIN SELECT * FROM users WHERE id IN (1, 2, 3);

index：全索引掃描。遍歷整個索引樹來查找數(shù)據(jù)，比 ALL 快一點，因為索引文件通常比數(shù)據(jù)文件小。

EXPLAIN SELECT id FROM users;
-- 查詢的列 `id` 正好是索引的一部分，直接從索引中讀取，無需回表

ALL：全表掃描。性能最差，意味著 MySQL 會遍歷整張表來找到匹配的行。必須優(yōu)化。

2.rows（預(yù)估行數(shù)）

這不是查詢結(jié)果的行數(shù)，而是 MySQL 為了找到目標(biāo)記錄，預(yù)估需要掃描多少行。這是一個基于統(tǒng)計信息的預(yù)估值。這個值越小越好，說明查詢效率高。

3.Extra（額外信息）

這里包含大量細節(jié)，是判斷查詢質(zhì)量的另一個關(guān)鍵。

• Using index：覆蓋索引。查詢的列都包含在索引中，無需回表查詢數(shù)據(jù)行。性能極佳。

-- 假設(shè)在 `name` 和 `age` 上有一個復(fù)合索引 (name, age)
EXPLAIN SELECT name, age FROM users WHERE name = 'John';

• Using where：表示在存儲引擎返回行后，MySQL 服務(wù)器層再次進行了過濾。如果 type 是 ALL 或 index，并且出現(xiàn) Using where，通常意味著性能不佳。
• Using temporary：MySQL 需要創(chuàng)建一張臨時表來處理查詢。常見于 GROUP BY 和 ORDER BY 的子句涉及不同列時。需要優(yōu)化。
• Using filesort：MySQL 無法使用索引對結(jié)果進行排序，需要額外的排序步驟。如果數(shù)據(jù)量大，會非常消耗資源。需要優(yōu)化。
• Using join buffer：表示連接查詢時，沒有使用索引，需要用到連接緩沖區(qū)。通常意味著連接字段上沒有索引。

四、實戰(zhàn)分析示例

假設(shè)我們有兩張表：

• users 表
  • id (主鍵)
  • name (有普通索引)
• email
  • orders 表
  • id (主鍵)
  • user_id (外鍵，關(guān)聯(lián) users.id，有索引)
• amount

查詢1：簡單的等值查詢

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

可能的結(jié)果分析：

• type: ref (使用了非唯一索引)
• key: name (實際使用了 name 索引)
• rows: 1 (預(yù)估掃描1行)
• Extra: (空，或者 Using index 如果查詢的列都被索引覆蓋)

結(jié)論： 這是一個高效的查詢。

查詢2：連接查詢

EXPLAIN SELECT u.name, o.amount 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.name = 'Bob';

可能的結(jié)果分析（對于 orders 表這一行）：

• type: ref (使用 user_id 索引來關(guān)聯(lián))
• key: user_id
• rows: 5 (預(yù)估每個用戶平均有5個訂單)
• Extra: (空)

結(jié)論： 連接效率很高，因為雙方都使用了索引。

查詢3：性能不佳的查詢

EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com' ORDER BY name;

可能的結(jié)果分析：

• type: ALL (全表掃描，因為 email 字段沒有索引)
• key: NULL
• rows: 10000 (表中有1萬行數(shù)據(jù))
• Extra: Using filesort (在內(nèi)存或磁盤上進行排序)

結(jié)論： 這是一個災(zāi)難性的查詢。它進行了全表掃描，并且還有一個昂貴的文件排序。
優(yōu)化建議：

• 為 email 字段添加索引。
• 如果經(jīng)常需要按 name 排序，可以考慮建立 (email, name) 的復(fù)合索引，這樣可以利用索引來查找和排序。

五、進階工具：EXPLAIN ANALYZE (MySQL 8.0.18+)

EXPLAIN ANALYZE 會實際執(zhí)行查詢，并提供一個更詳細的、包含實際執(zhí)行時間的分析報告。

EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'John';

輸出格式類似于：

-> Index lookup on users using idx_name (name='John')  (cost=0.35 rows=1) (actual time=0.025..0.027 rows=1 loops=1)

它提供了：

• 實際執(zhí)行時間 (actual time)。
• 實際返回行數(shù) (rows)。
• 執(zhí)行循環(huán)次數(shù) (loops)。
• 預(yù)估成本 (cost)。

這比傳統(tǒng)的 EXPLAIN 提供了更精確的性能視圖。

總結(jié)

熟練掌握 EXPLAIN 是每個后端開發(fā)者和 DBA 的必備技能，它能幫助你從“猜測”優(yōu)化變?yōu)?ldquo;數(shù)據(jù)驅(qū)動”的優(yōu)化。

到此這篇關(guān)于MySQL EXPLAIN 詳解與優(yōu)化指南的文章就介紹到這了,更多相關(guān)mysql explain內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論