MySQL EXPLAIN 用法與執行計畫解讀全攻略

By 佐川 直弘 | Naohiro Sagawa (運営者)

※記事内に広告を含みます。Amazonアソシエイトとして適格販売により収入を得ています。　

1. MySQL EXPLAIN 概觀

MySQL 的 EXPLAIN 指令是分析查詢執行計畫並提供最佳化提示的重要工具。特別是在大型資料庫環境中，查詢的效率對整體效能影響甚鉅。

什麼是 EXPLAIN？

EXPLAIN 能將 MySQL 如何執行查詢視覺化。透過它，您可以獲得有關查詢執行方式的詳細資訊，例如索引的使用情況、是否進行全表掃描，以及結合（JOIN）的順序等。

EXPLAIN 的重要性

查詢最佳化是提升資料庫效能不可或缺的一環。利用 EXPLAIN，可以找出效能瓶頸所在，並編寫出更有效率的查詢。這有助於加快資料擷取速度，並更有效地利用伺服器資源。

2. MySQL EXPLAIN 的基本用法

這裡將說明 EXPLAIN 指令的基本用法及其輸出內容的解讀方法。

EXPLAIN 的基本用法

EXPLAIN 用於放在欲分析的查詢前面。例如：

EXPLAIN SELECT * FROM users WHERE age > 30;

這個指令會顯示查詢的執行計畫，您可以查看索引的使用情況和是否進行全表掃描。

解讀 EXPLAIN 的輸出內容

輸出結果包含以下欄位：

 
• id: 指派給查詢各部分的識別碼

 

• select_type: 查詢的類型（簡單查詢、子查詢等）

 

• table: 使用的資料表名稱

 

• type: 資料表的存取方法（ALL、index、range 等）

 

• possible_keys: 查詢可利用的索引

 

• key: 實際使用的索引

 

• rows: 預計掃描的行數

 

• Extra: 其他資訊（Using index、Using temporary 等）

利用這些資訊，可以評估查詢的執行效率，找出可最佳化的空間。

3. 使用 EXPLAIN 最佳化查詢

這裡說明如何使用 EXPLAIN 來最佳化查詢。

適當利用索引

索引對於提升查詢效能至關重要。使用 EXPLAIN 確認查詢是否適當利用索引。

EXPLAIN SELECT * FROM orders USE INDEX (order_date_idx) WHERE order_date > '2024-01-01';

從這個結果可以判斷索引是否有效使用，或者是否需要新增索引。

最小化行掃描

EXPLAIN 的 rows 欄位顯示查詢預計掃描的行數。掃描的行數越多，效能越差，因此設定適當的索引來最小化行數非常重要。

4. EXPLAIN 的進階功能

EXPLAIN 具有更詳細分析查詢執行計畫的進階功能。

選擇輸出格式

EXPLAIN 提供以下格式的輸出：

 
• Traditional: 預設的表格格式

 

• JSON: 包含詳細資訊的 JSON 格式（MySQL 5.7 及以上版本）

 

• Tree: 以樹狀結構顯示查詢的執行結構（MySQL 8.0.16 及以上版本）

例如，指定 JSON 格式輸出如下：

EXPLAIN FORMAT = JSON SELECT * FROM users WHERE age > 30;

透過這種方式，可以深入分析查詢執行計畫的詳細內容。

即時查詢分析

使用 EXPLAIN FOR CONNECTION 可以即時取得目前正在執行的查詢的執行計畫。這讓您能夠即時評估特定查詢對資料庫造成的負載。

5. 實際使用範例

這裡將介紹使用 EXPLAIN 進行查詢最佳化的具體範例。

分析簡單查詢

首先，對簡單的查詢應用 EXPLAIN。

EXPLAIN SELECT * FROM employees WHERE department = 'Sales';

從這個結果確認索引是否被適當利用，或者是否進行了全表掃描。

最佳化複雜查詢

分析結合多個資料表的查詢的執行計畫。

EXPLAIN SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.department_id = d.id WHERE e.salary > 50000;

從這個輸出判斷結合的順序和索引的利用是否最佳。

執行計畫的視覺化

以樹狀結構視覺化查詢的執行計畫。

EXPLAIN FORMAT = tree SELECT * FROM employees WHERE department = 'Sales';

樹狀結構的視覺化分析對於複雜查詢的最佳化非常有幫助。

6. EXPLAIN 的最佳實務

介紹一些有效使用 EXPLAIN 的最佳實務。

查詢的重複執行

查詢的執行速度會受到快取狀態的影響，因此在使用 EXPLAIN 時，重複執行查詢幾次，並評估快取暖機後的效能。

與 SHOW STATUS 併用

使用 SHOW STATUS 指令查看查詢執行後的狀態，可以獲得更多詳細資訊，例如實際讀取的行數和索引的使用情況等。

7. 常見問題與誤解

說明使用 EXPLAIN 時應注意的事項和常見的誤解。

EXPLAIN 的估計值與實際差異

EXPLAIN 的輸出是基於 MySQL 最佳化器（Optimizer）的估計，因此可能與實際的查詢執行結果不同。不要過分依賴估計值，確認實際效能非常重要。

對索引的過度依賴及其影響

索引對提升查詢效率有效，但並非所有情況都適用。索引數量過多會增加資料插入和更新時的負擔。此外，如果索引的使用不當，MySQL 也可能忽略索引而選擇全表掃描。

8. 總結

本文說明了如何使用 MySQL 的 EXPLAIN 指令來分析和最佳化查詢。

重要重點總結

 
• 基本用法: 使用 EXPLAIN 查看查詢的執行計畫，評估索引的使用情況和資料表的存取方法。

 

• 進階功能: 利用 JSON 或 Tree 格式進行更詳細的執行計畫分析。透過即時查詢分析，評估執行中查詢的負載。

 

• 最佳實務: 考量快取的影響，重複執行查詢數次以評估穩定的執行時間。同時使用 SHOW STATUS 分析查詢的實際執行結果，有助於最佳化。

查詢最佳化的後續步驟

根據 EXPLAIN 的結果持續最佳化查詢，目標是提升整體資料庫效能。具體可以包括新增或修改索引、改善查詢結構、調整資料表設計等。

最後

EXPLAIN 指令是資料庫查詢最佳化中最基本且強大的工具。適當運用它可以提升查詢效率，最佳化整體資料庫效能。請參考本文介紹的內容，應用於日常的資料庫管理和查詢最佳化工作中。查詢最佳化是一個持續的過程，需要根據資料庫規模和使用情況的變化進行調整。善用 EXPLAIN，朝著高效能的資料庫運作邁進吧。