MySQL 索引優化（Explain執行計劃）詳細講解

🤟致敬讀者

🟩感謝閱讀🟦笑口常開🟪生日快樂?早點睡覺

📘博主相關

🟧博主信息🟨博客首頁🟫專欄推薦🟥活動信息

文章目錄

MySQL 索引優化（Explain執行計劃）詳細講解
- 一、為什么需要 EXPLAIN？
- 二、如何使用 EXPLAIN？
- 三、EXPLAIN 輸出字段詳解（核心！面試重點！）
- 四、索引優化核心原則（結合 EXPLAIN）
- 五、面試常見問題及回答思路
- 六、實戰分析示例（模擬面試）
- 七、總結 & 面試準備要點

📃文章前言

🔷文章均為學習工作中整理的筆記。
🔶如有錯誤請指正，共同學習進步。

MySQL 索引優化（Explain執行計劃）詳細講解

在這里插入圖片描述

MySQL 的 EXPLAIN 執行計劃是理解和優化 SQL 查詢性能的核心工具，也是面試中數據庫相關崗位（尤其是中高級）幾乎必考的重點。掌握它不僅能讓你在面試中游刃有余，更能提升實際工作中的數據庫優化能力。

下面我將從基礎概念、EXPLAIN 輸出字段詳解、關鍵性能指標解讀、常見優化場景及面試要點等方面進行詳細講解：

一、為什么需要 EXPLAIN？

理解查詢行為： 數據庫是如何執行你的 SQL 語句的？它決定使用哪個索引（或者不用）？它如何連接表？它預估需要掃描多少行？
定位性能瓶頸： 查詢慢在哪里？是全表掃描？是文件排序？是臨時表過大？EXPLAIN 能提供關鍵線索。
驗證索引效果： 你創建的索引真的被用到了嗎？用到了哪個部分？是否是高效的使用方式？
優化查詢和索引： 基于 EXPLAIN 的分析結果，調整 SQL 寫法（如 WHERE 條件順序、JOIN 順序）、添加或修改索引、優化表結構等。

二、如何使用 EXPLAIN？

非常簡單，在你的 SELECT 語句前加上 EXPLAIN 或 EXPLAIN FORMAT=JSON (獲取更詳細的結構化信息) 即可：

EXPLAIN SELECT * FROM users WHERE name = '張三' AND age > 30 ORDER BY created_at DESC;

或者在 MySQL Workbench 等圖形化工具中，直接點擊“解釋”按鈕。

三、EXPLAIN 輸出字段詳解（核心！面試重點！）

EXPLAIN 的輸出是一個表格（或多行，每行代表查詢計劃中的一個操作），包含以下重要列：

id (查詢標識符)
- 含義： 標識 SELECT 子查詢的執行順序或屬于哪個大查詢。
- 解讀：
  - id 相同：執行順序從上到下（如普通的 JOIN）。
  - id 不同：如果是子查詢，id 值越大，優先級越高，越先執行（如 SELECT ... (SELECT ...)，內層 SELECT 的 id 更大）。
  - id 為 NULL：表示這是一個“結果行”，例如 UNION 的結果。
select_type (查詢類型)
- 含義： 說明查詢的類型。
- 常見類型及面試要點：
  - SIMPLE：最簡單的 SELECT，不包含子查詢或 UNION。
  - PRIMARY：查詢中最外層的 SELECT（包含子查詢時）。
  - SUBQUERY：在 SELECT 或 WHERE 列表中包含的子查詢。
  - DERIVED：在 FROM 列表中包含的子查詢（派生表）。面試點： DERIVED 通常意味著 MySQL 需要將子查詢的結果物化成一個臨時表，可能影響性能。優化方向：嘗試用 JOIN 重寫。
  - UNION： UNION 中的第二個或后續 SELECT。
  - UNION RESULT： UNION 操作的結果集。
  - DEPENDENT SUBQUERY / UNCACHEABLE SUBQUERY：依賴于外部查詢的子查詢（性能通常較差）。
table (訪問的表)
- 含義： 顯示這一步訪問的是哪個表。
- 解讀：
  - 表名：直接的表名。
  - <derivedN>：指向 id 為 N 的派生表結果。
  - <unionM,N>：指向 id 為 M 和 N 的 UNION 結果。
partitions (匹配的分區)
- 含義： 如果表是分區表，顯示查詢將訪問哪些分區。非分區表為 NULL。
type (訪問/連接類型 - 極其重要！性能關鍵指標！ 面試必問！)
- 含義： 表示 MySQL 決定如何查找表中的行。從最好到最差（性能降序）大致如下：
  - system：表只有一行（系統表）。是 const 的特例。
  - const：通過主鍵（Primary Key）或唯一索引（Unique Index）進行等值查詢，最多只返回一行。速度極快。面試點： 這是最理想的情況之一。
  - eq_ref：在連接查詢中，對于來自前表的每一行，使用主鍵或唯一索引在當前表中進行等值匹配（只找到一行）。常見于 PRIMARY KEY 或 UNIQUE NOT NULL 索引的等值連接。性能非常好。
  - ref：使用非唯一索引進行等值查詢，可能返回多行。比 eq_ref 稍差，但仍然是高效的（如果返回行不多）。面試點： 最常用的高效訪問方式之一。
  - ref_or_null：類似 ref，但額外包含了對 NULL 值的搜索。
  - range： 范圍掃描。使用索引檢索給定范圍（BETWEEN, <, >, IN, LIKE 'prefix%' 等）的行。比全表掃描好，但需要關注掃描的行數（rows）。
  - index： 全索引掃描。掃描整個索引樹（比全表掃描 ALL 快，因為索引通常比表數據小）。面試點：
    - 當查詢所需列全部包含在索引中（覆蓋索引），且不需要回表時，性能可以接受（Extra 列會顯示 Using index）。
    - 否則，效率可能不高（需要排序或 Using filesort 時可能更糟）。
  - ALL： 全表掃描。沒有使用索引，或者索引失效。性能最差！ 面試點： 看到 ALL 必須警惕！通常意味著需要優化（加索引、修改查詢條件等）。在大表上尤其致命。
- 面試要點： 務必熟悉這個順序！能清晰解釋每種類型的含義和性能差異。面試官常問：“type 出現 ALL 意味著什么？如何優化？”，“ref 和 eq_ref 的區別是什么？”。
possible_keys (可能用到的索引)
- 含義： 顯示查詢可能使用哪些索引來查找行。基于 WHERE 子句、JOIN 條件等分析得出。
- 解讀： 這里列出的索引不一定真正被使用。實際使用的索引在 key 列中。如果為 NULL，表示沒有找到適用的索引。
key (實際使用的索引 - 重要！)
- 含義： 顯示 MySQL 實際決定使用的索引。如果為 NULL，表示沒有使用索引。
- 面試要點： 對比 possible_keys 和 key：
  - 如果 key 在 possible_keys 中：正常。
  - 如果 key 不在 possible_keys 中：可能使用了覆蓋索引（即使沒在 WHERE 中直接用到，但索引包含了 SELECT 需要的所有列），或者優化器基于成本選擇了不同的索引。
  - 如果 possible_keys 有值而 key 為 NULL：意味著雖然有可用索引，但優化器基于成本（如小表掃描更快）或索引選擇性差等原因決定不使用索引。需要分析原因！
key_len (使用的索引長度)
- 含義： 表示 MySQL 在索引中實際使用的字節數。可用于判斷索引是否被充分利用（是否使用了索引的全部列或部分列）。
- 解讀：
  - 計算規則：所有被使用的索引字段的長度之和。對于變長字段（如 VARCHAR），需要考慮字符集和是否允許 NULL（多1字節）。
  - 面試點： 如果 key_len 小于索引定義的長度，說明只使用了索引的最左前綴（復合索引的部分列）。這是理解最左前綴原則的關鍵指標！例如，索引 (a, b, c)，WHERE 條件只有 a=1 AND b=2，那么 key_len 就是 a 和 b 的長度，表明 c 沒有被用到。
ref (與索引比較的列或常量)
- 含義： 顯示使用 key 列指定的索引時，查找值所用到的列或常量。
- 常見值：
  - const：常量值（如 WHERE id = 10）。
  - func：使用函數的結果。
  - database_name.table_name.column_name：來自另一個表的列（常見于 JOIN）。
  - NULL：例如 index 掃描（全索引掃描）。
rows (預估需要掃描的行數 - 重要！性能指標！)
- 含義： MySQL 優化器預估執行該查詢操作需要掃描的行數（不是精確值，基于統計信息）。對于 InnoDB，這是一個估計值。
- 解讀： 這個值非常重要！它直接反映了查詢的成本。值越小越好。結合 type 看：
  - type=ALL 且 rows 很大：性能災難！
  - type=index 且 rows 很大：全索引掃描，也可能很慢，要看數據量和 Extra。
  - type=const/eq_ref/ref 且 rows=1：非常高效。
- 面試點： 理解 rows 是預估的，實際執行可能不同。關注其數量級（幾十、幾萬、幾百萬？）。
filtered (按條件過濾后剩余行的百分比 - MySQL 5.7+ 重要！)
- 含義： 表示存儲引擎返回的數據在服務層經過 WHERE 條件或其他過濾后，預估剩余行數的百分比。值在 0 到 100 之間。100 表示沒有過濾。
- 解讀： 對于單表查詢，意義不大（rows * filtered / 100 可以預估最終返回的行數）。對于 JOIN 查詢極其重要：
  - 它表示前一個表返回的行，在連接當前表后，預估能有多少比例的行滿足 JOIN 條件或單表 WHERE 條件。
  - 例如，前表 rows=1000, filtered=10%，則預估需要連接當前表 1000 * 10% = 100 次。filtered 越低，意味著連接次數越少，JOIN 效率可能越高。
- 面試點： 理解 filtered 在 JOIN 優化中的意義，特別是評估 JOIN 順序是否合理時。
Extra (額外信息 - 極其重要！包含關鍵性能線索！ 面試必問！)
- 含義： 包含 MySQL 解決查詢的額外細節信息。這里常常藏著性能問題的答案。
- 常見值及優化方向（面試高頻考點）：
  - Using index： 覆蓋索引（Covering Index）。查詢的列全部包含在使用的索引中，無需回表查詢數據行。這是非常理想的情況！ 面試點： 解釋什么是覆蓋索引及其優勢（減少I/O，提高速度）。
  - Using where：在存儲引擎返回行之后，服務層還需要應用 WHERE 條件進行過濾。說明索引可能沒有完全覆蓋 WHERE 條件，或者索引被部分使用。不一定壞，但結合 type 和 rows 看。
  - Using temporary： 使用了臨時表來保存中間結果。常見于 GROUP BY 或 ORDER BY 的列不屬于驅動表且沒有合適索引，或者 DISTINCT、UNION。通常需要優化！ 面試點： 為什么臨時表不好？如何避免（優化 GROUP BY/ORDER BY 的索引）？
  - Using filesort： 使用了文件排序。無法利用索引完成排序（ORDER BY, GROUP BY），需要在內存或磁盤進行額外排序。通常需要優化！ 面試點： filesort 為什么慢？如何避免（為 ORDER BY/GROUP BY 創建合適的索引）？注意：有時優化器會選擇 filesort 如果它認為更快（比如行數很少）。
  - Using join buffer (Block Nested Loop) / Using join buffer (Batched Key Access)：使用了連接緩沖區。常見于被驅動表（第二個表）沒有有效索引可用時，需要將驅動表的一部分行讀入 Join Buffer，然后批量與被驅動表匹配。效率較低！ 優化方向： 為被驅動表的連接字段添加索引。
  - Impossible WHERE： WHERE 子句永遠為 false，查不到任何行（如 WHERE 1=0）。
  - Select tables optimized away：使用某些聚合函數（如 MIN()/MAX()）訪問僅包含這些值的索引時，優化器可以直接從索引中取值，無需訪問表。
  - Distinct / Not exists：優化了 DISTINCT 或 NOT EXISTS 操作。
  - Range checked for each record (index map: N)： MySQL 沒有找到好的索引可用，但發現在前面的表返回行后，可能可以根據該行的值來使用某個索引進行范圍掃描（N 是位圖標識哪個索引可能被用）。效率不高。

四、索引優化核心原則（結合 EXPLAIN）

最左前綴原則 (Leftmost Prefixing):
- 對于復合索引 (col1, col2, col3)，索引可以被用于：
  - 只包含 col1 的查詢 (WHERE col1=val)
  - 包含 col1 和 col2 的查詢 (WHERE col1=val1 AND col2=val2)
  - 包含 col1, col2 和 col3 的查詢 (WHERE col1=val1 AND col2=val2 AND col3=val3)
- 不能跳過左邊列直接使用右邊列 (WHERE col2=val2 AND col3=val3 無法有效使用該索引)。
- EXPLAIN 體現： key_len 會顯示實際使用了索引的前幾列的長度。
- 面試必考！ 必須能清晰解釋并舉例。
避免索引失效（導致全表掃描 ALL 或低效 index）：
- 在索引列上做操作： 函數、計算、類型轉換（隱式或顯式）。如 WHERE YEAR(create_date) = 2023, WHERE amount * 2 > 100, WHERE id = '123' (id 是 INT)。
- 使用 != 或 <>： 通常會導致索引失效（除非覆蓋索引）。
- 使用 NOT IN, NOT LIKE： 通常會導致索引失效。
- OR 連接非索引列： 如果 OR 的條件中有一個列沒有索引，整個索引可能失效。
- LIKE 以通配符 % 開頭： LIKE '%keyword' 無法利用索引（LIKE 'keyword%' 可以）。
- 字符串不加單引號（隱式類型轉換）： 如 WHERE name = 123 (name 是 VARCHAR)。
- 復合索引未遵循最左前綴原則。
- 優化器認為全表掃描更快： 當表很小，或者索引選擇性極低時（如性別列）。
- EXPLAIN 體現： type=ALL 或 type=index + 高 rows + Using where。
覆蓋索引 (Covering Index)：
- 創建包含查詢所需所有列（SELECT, WHERE, ORDER BY, GROUP BY）的索引。
- 優勢： 避免回表（訪問數據行），極大提升速度。體現在 EXPLAIN 的 Extra: Using index。
- 權衡： 索引列越多，索引維護成本（插入/更新/刪除）越高，占用空間越大。需平衡查詢性能與寫入性能/空間開銷。
為排序和分組創建索引：
- ORDER BY 和 GROUP BY 子句的列順序同樣需要遵循最左前綴原則。
- 如果排序方向不一致 (ORDER BY col1 ASC, col2 DESC)，可能需要特殊索引或無法充分利用。
- EXPLAIN 體現： 避免 Extra: Using filesort 和 Using temporary。理想情況是 Using index 完成排序/分組。
JOIN 優化：
- 確保 ON/USING 子句中的列有索引： 通常在被驅動表（第二個及以后的表）的連接字段上建索引。體現在 type=ref/eq_ref。
- 小表驅動大表： 優化器通常會嘗試選擇小表作為驅動表（第一個表）。手動指定時可以用 STRAIGHT_JOIN（謹慎使用）。
- 關注 filtered 列： 高 filtered 值（接近100%）可能意味著 JOIN 條件選擇性差或索引不佳。
- 減少 Using join buffer： 通過為被驅動表添加有效索引。

五、面試常見問題及回答思路

EXPLAIN 是做什么用的？

答：EXPLAIN 是 MySQL 提供的用于分析 SQL 查詢執行計劃的工具。它顯示了 MySQL 優化器打算如何執行查詢，包括是否使用索引、使用哪個索引、表的連接順序和方式、預估掃描行數等關鍵信息。主要目的是幫助我們理解查詢行為、定位性能瓶頸、驗證索引效果和指導優化。
type 字段有哪些值？性能從好到壞排序是怎樣的？

答：type 表示訪問表的方式，性能從最優到最差大致是：system > const > eq_ref > ref > ref_or_null > range > index > ALL。const/eq_ref/ref/range 通常表示使用了索引且高效；index 是全索引掃描，有時可接受（覆蓋索引時）；ALL 是全表掃描，性能最差，必須優化。
看到 type=ALL 怎么優化？
答：ALL 表示全表掃描，是性能瓶頸的信號。優化方向包括：
- 檢查 WHERE 條件涉及的列，嘗試添加合適的索引（單列或復合索引，注意最左前綴）。
- 檢查查詢條件是否導致索引失效（如函數操作、類型轉換、LIKE '%xx'、違反最左前綴）。
- 如果表很小，ALL 可能比用索引快，但需確認表大小。
- 分析是否真的需要所有列，避免 SELECT *，考慮覆蓋索引。
Extra 列里 Using filesort 和 Using temporary 是什么意思？怎么優化？
答：
- Using filesort：表示 MySQL 無法利用索引完成排序（ORDER BY/GROUP BY），需要在內存或磁盤進行額外排序。優化： 為排序/分組的字段創建合適的索引（順序遵循最左前綴）。
- Using temporary：表示 MySQL 創建了臨時表來處理查詢（常見于 GROUP BY, DISTINCT, UNION）。優化： 嘗試優化 GROUP BY 的列使其有索引；避免不必要的 DISTINCT/排序；確保 UNION 子句有索引。臨時表消耗內存/磁盤，影響性能。
什么是覆蓋索引？EXPLAIN 如何體現？有什么好處？
答：
- 覆蓋索引： 指一個索引包含了查詢所需的所有列（SELECT, WHERE, ORDER BY, GROUP BY 涉及的列）。
- EXPLAIN 體現： Extra 列會顯示 Using index。
- 好處： 最大的優勢是避免回表（無需訪問數據行）。直接從索引中獲取數據，減少 I/O 操作，顯著提高查詢速度。是優化查詢的高效手段之一。
什么是索引的最左前綴原則？EXPLAIN 如何驗證？
答：
- 最左前綴原則： 對于復合索引 (col1, col2, col3)，查詢條件必須包含索引定義中的最左邊的連續列才能有效利用該索引。例如，條件 col1=val 或 col1=val1 AND col2=val2 可以利用索引；但 col2=val2 或 col2=val2 AND col3=val3 無法有效利用（除非跳過掃描，但效率不高）。
- EXPLAIN 驗證： 主要看 key_len 列。key_len 的值代表了實際使用到的索引列的總字節數。如果 key_len 小于整個復合索引的長度，說明只使用了索引的一部分（最左前綴）。
possible_keys 有值，key 為 NULL 是什么原因？
答：這表示優化器分析后認為有可用的索引 (possible_keys)，但最終決定不使用任何索引 (key=NULL)。常見原因：
- 表很小： 全表掃描比走索引更快（如只有幾行）。
- 索引選擇性差： 索引列的值重復率極高（如狀態標志位），走索引后仍需回表掃描大量行，不如直接全表掃。
- 覆蓋索引不成立且需要回表： 雖然 WHERE 條件有索引，但 SELECT 的列不在索引中，需要大量回表操作，優化器計算成本后認為全表掃描更優。
- 查詢使用了 FORCE INDEX 之外的提示但優化器未采納。
  需要結合 rows 預估值和表大小具體分析。
filtered 字段有什么用？

答：filtered (MySQL 5.7+) 表示存儲引擎返回的行，在服務層經過 WHERE 條件過濾后，預估剩余行的百分比。對于單表查詢： 可以用來預估最終結果行數 (rows * filtered / 100)。對于 JOIN 查詢（更重要）： 它表示前一個表的每一行，在連接當前表時，預估有多少比例的行能滿足連接條件或單表 WHERE 條件。filtered 值越低（接近0），意味著前表行連接當前表的次數越少，JOIN 效率可能越高。優化 JOIN 順序時，驅動表（第一個表）應盡量選擇 filtered 小的。

六、實戰分析示例（模擬面試）

查詢：

EXPLAIN SELECT o.order_id, c.customer_name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-01-31'
AND c.country = 'China'
ORDER BY o.order_date DESC;

假設 EXPLAIN 結果關鍵部分（簡化）：

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	o	range	idx_date	idx_date	4	NULL	1000	100.00	Using where; Using filesort
1	SIMPLE	c	eq_ref	PRIMARY	PRIMARY	4	db.o.customer_id	1	10.00	Using where
1	SIMPLE	oi	ref	idx_order	idx_order	4	db.o.order_id	5	100.00	NULL
1	SIMPLE	p	eq_ref	PRIMARY	PRIMARY	4	db.oi.product_id	1	100.00	NULL

面試官：分析一下這個查詢的執行計劃和潛在性能問題？

回答思路：

驅動表： 第一個表是 o (orders)。優化器選擇它作為驅動表，因為 WHERE o.order_date BETWEEN ... 使用了范圍查詢 (type=range)，預估掃描 rows=1000 行。
orders 表 (o):
- type=range：使用了索引 idx_date 進行范圍掃描（好）。
- Extra=Using where; Using filesort：Using where 可能表示索引未完全覆蓋 WHERE 條件（比如 order_date 在索引中，但可能還有其他條件？這里只有日期）。Using filesort 是問題！ 因為最終的 ORDER BY o.order_date DESC 無法利用 idx_date 的排序（因為是范圍掃描后結果集，且可能需要與其他表連接后再排序），需要額外文件排序。優化建議： 嘗試調整索引或查詢，看是否能利用索引消除排序。
customers 表 (c):
- type=eq_ref：使用主鍵連接，對于驅動表 o 的每一行 customer_id，精確查找一行。效率很高。
- filtered=10.00%：表示連接后，只有大約 10% 的行滿足 c.country='China' 條件。這個選擇性還不錯，意味著驅動表 o 的 1000 行，大約只需要連接 c 表 100 次 (1000 * 10%)。
order_items 表 (oi):
- type=ref：使用非唯一索引 idx_order (可能在 order_id 上) 進行連接。預估每個訂單有 5 個訂單項 (rows=5)。
products 表 (p):
- type=eq_ref：使用主鍵連接，效率很高。
主要性能風險點：
- o 表的文件排序 (Using filesort)： 如果滿足日期條件的訂單很多（rows=1000 是預估，實際可能更多），文件排序可能成為瓶頸，尤其是在內存不足需落盤時。
- 連接行數放大： 驅動表 o 掃描 1000 行 -> 連接 c 約 100 次 -> 連接 oi 約 100 * 5 = 500 次 -> 連接 p 500 次。最終需要處理的行數可能達到 500 行（結果集），但中間過程需要處理連接。
優化建議：
- 解決排序：
  - 如果 o.order_date 是驅動條件且需要排序，考慮能否讓 idx_date 包含排序方向（但范圍查詢本身可能破壞順序）。
  - 嘗試創建一個包含 (order_date, customer_id) 的索引，order_date 用于范圍查找和排序，customer_id 用于連接 c 表。看 EXPLAIN 是否能消除 filesort (Extra 去掉 Using filesort) 并保持高效連接。
- 覆蓋索引檢查： 檢查 SELECT 的列是否被現有索引覆蓋，減少回表（當前計劃中沒有 Using index，說明有回表）。
- c.country 過濾： filtered=10% 不錯，但如果在 customers 表上有 (country, customer_id) 的索引，理論上可以讓 eq_ref 連接更順暢（雖然主鍵連接已最優），或者如果 country 過濾性更強，考慮調整 JOIN 順序（但優化器通常已經做了最優選擇，除非用 STRAIGHT_JOIN 強制）。

七、總結 & 面試準備要點

理解每個 EXPLAIN 字段的含義： 特別是 type, key, rows, filtered, Extra。
掌握性能關鍵指標： type=ALL/index 差，type=const/eq_ref/ref/range 好；rows 大差小好；Extra 中的 Using filesort, Using temporary, Using join buffer 通常是警告信號；Using index 是好的信號。
深刻理解索引原理： 最左前綴原則（看 key_len）、覆蓋索引（看 Using index）、索引失效場景。
理解 JOIN 優化： 驅動表選擇、被驅動表索引 (ref/eq_ref)、filtered 的意義。
能解讀 EXPLAIN 輸出： 按 id 順序理解執行步驟，分析每個步驟的訪問方式和潛在問題。
能提出優化建議： 根據 EXPLAIN 結果，說出可能的優化方向（加索引、改索引、改寫查詢、調整 JOIN 順序等）。
動手實踐： 在自己環境或在線 SQL 平臺多寫 SQL，多用 EXPLAIN 分析不同寫法和索引的影響，加深理解。