本文 AI 產出，尚未審核
MySQL 優化與 EXPLAIN — 使用 EXPLAIN 分析 SQL
簡介
在日常開發中，我們常會發現某些查詢在資料量稍微增長就變得非常慢，這時候最直接、最有效的工具就是 EXPLAIN。
EXPLAIN 能夠把 MySQL 執行器的執行計畫以表格形式呈現，讓我們看得到每一步是如何存取資料的，進而找出索引缺失、全表掃描或不必要的排序等效能瓶頸。
掌握 EXPLAIN 的使用方法，不只可以 快速定位問題，更能在寫 SQL 時就養成良好的優化思維，避免日後因為慢查詢而浪費大量除錯時間。本文將從概念講起，搭配實作範例，說明如何解讀 EXPLAIN 輸出，並提供常見陷阱與最佳實踐，幫助初學者到中階開發者在實務上即時提升 MySQL 效能。
核心概念
1. EXPLAIN 的基本語法
EXPLAIN [FORMAT=TRADITIONAL|JSON] SELECT ...
- FORMAT=TRADITIONAL（預設）以文字表格呈現，欄位包括
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。 - FORMAT=JSON 會回傳 JSON 物件，適合程式化解析或在 GUI 工具中直接觀看。
Tip：在 MySQL 8.0 之後，
EXPLAIN ANALYZE會實際執行查詢並回傳真實執行時間，對於微調非常有幫助。
2. 重要欄位說明
| 欄位 | 說明 |
|---|---|
| id | 查詢的執行順序，數值越大表示越晚執行。 |
| select_type | 查詢類型，如 SIMPLE、PRIMARY、DERIVED（子查詢）等。 |
| type | 連接方式（存取方式），從好到壞依序為 system > const > eq_ref > ref > range > index > ALL。ALL 表示全表掃描，需要特別注意。 |
| possible_keys | 該步驟可能使用的索引清單。 |
| key | 實際使用的索引，若為 NULL 表示未使用索引。 |
| rows | 估算要讀取的資料列數，數值越大代表成本越高。 |
| Extra | 其他資訊，如 Using where、Using index、Using temporary、Using filesort 等。 |
3. 讀取執行計畫的流程
- 先看
type：如果出現ALL或index，代表可能有全表掃描或只走索引而未回表。 - 檢查
key是否為期望的索引：若key為NULL，代表沒有使用索引，需要檢查條件式或索引建立方式。 - 觀察
rows：估算值遠大於實際資料量時，表示統計資訊可能過時，需要ANALYZE TABLE。 - 分析
Extra：Using filesort、Using temporary常是效能殺手，應盡量避免。
程式碼範例
以下示範 5 個常見情境，說明如何使用 EXPLAIN 觀察與優化查詢。
範例 1：簡單的單表查詢
-- 建立測試表與資料
CREATE TABLE orders (
order_id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
status VARCHAR(20),
created_at DATETIME,
INDEX idx_user_status (user_id, status)
);
INSERT INTO orders (order_id, user_id, status, created_at)
SELECT
seq,
FLOOR(RAND()*1000)+1,
IF(seq%5=0,'CANCELLED','COMPLETED'),
NOW() - INTERVAL FLOOR(RAND()*365) DAY
FROM
(SELECT @row:=@row+1 AS seq FROM information_schema.columns, (SELECT @row:=0) r LIMIT 100000) t;
查詢與 EXPLAIN：
EXPLAIN SELECT * FROM orders WHERE user_id = 123 AND status = 'COMPLETED';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ref | idx_user_status | idx_user_status | 12 | const,const | 12 | Using where |
說明：type 為 ref，使用了 idx_user_status 複合索引，rows 只需讀 12 筆，效能相當好。
範例 2：缺少索引的全表掃描
EXPLAIN SELECT * FROM orders WHERE status = 'CANCELLED';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ALL | NULL | NULL | NULL | NULL | 100000 | Using where |
問題：type 為 ALL（全表掃描），key 為 NULL，顯示未使用任何索引。
解法：在 status 欄位上建立單欄索引或加入複合索引：
ALTER TABLE orders ADD INDEX idx_status (status);
範例 3：使用 LIKE 前綴通配符
EXPLAIN SELECT * FROM orders WHERE status LIKE 'COM%';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | orders | ref | idx_status | idx_status | 7 | const | 80000 | Using where |
即使使用了 LIKE，只要 前綴 是固定字串，就能走索引。若使用 %COM（前綴為 %），則會退化為全表掃描。
範例 4：JOIN 與索引選擇
EXPLAIN
SELECT o.order_id, u.username
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.created_at >= '2024-01-01';
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | u | ref | PRIMARY | PRIMARY | 8 | const | 5000 | |
| 1 | SIMPLE | o | range | idx_created_at, idx_user_status | idx_created_at | 5 | const | 20000 | Using where; Using index |
說明：orders 先使用 idx_created_at 篩選最近的資料，再透過 user_id 連接 users。若 users.user_id 沒有索引，type 會變成 ALL，效能會大幅下降。
範例 5：EXPLAIN ANALYZE 取得實際執行時間（MySQL 8.0+）
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id IN (SELECT user_id FROM users WHERE active = 1)
AND status = 'COMPLETED';
{
"query_block": {
"select_id": 1,
"cost_info": {"query_cost": "0.12"},
"used_columns": ["orders.order_id","orders.user_id","orders.status"],
"message": "Rows examined: 1200 (actual time: 0.001 sec)",
"child_blocks": [
{
"select_id": 2,
"table": {
"table_name": "orders",
"access_type": "ref",
"key_used": "idx_user_status",
"rows_examined_per_scan": 1200,
"rows_produced_per_join": 1200
}
},
{
"select_id": 3,
"table": {
"table_name": "users",
"access_type": "ref",
"key_used": "PRIMARY",
"rows_examined_per_scan": 500,
"rows_produced_per_join": 500
}
}
]
}
}
透過 EXPLAIN ANALYZE，我們可以直接看到 實際執行時間 與 掃描列數，若與 rows 預估差距太大，代表統計資訊失真，需要 ANALYZE TABLE。
常見陷阱與最佳實踐
| 陷阱 | 為什麼會發生 | 解決方案 |
|---|---|---|
使用 SELECT * |
可能導致 MySQL 必須讀取整個行，且無法僅使用覆蓋索引（covering index）。 | 只挑選必要欄位，若能全部由索引提供，將出現 Using index（純索引查詢）。 |
| WHERE 子句裡的函式 | 如 WHERE DATE(created_at) = '2024-01-01' 會讓索引失效。 |
使用範圍條件：WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。 |
| 不適當的資料類型 | 文字與數字混用、VARCHAR 長度過長會影響索引大小。 | 選擇正確的資料型別，如 INT、BIGINT、CHAR(2)，並盡量避免在索引欄位上使用 TEXT。 |
過度使用 OR |
OR 會導致 MySQL 可能無法使用索引，改為 UNION ALL 或分開查詢。 |
改寫為 UNION 或使用 index merge（type = index_merge）時確認索引覆蓋。 |
| 統計資訊過時 | rows 估算不準確，導致錯誤的執行計畫。 |
定期執行 ANALYZE TABLE、OPTIMIZE TABLE，或在大量資料變動後手動更新。 |
忘記 EXPLAIN 的 FORMAT=JSON |
文字表格在複雜查詢時難以閱讀。 | 使用 EXPLAIN FORMAT=JSON 或 EXPLAIN ANALYZE 取得更清晰的結構。 |
最佳實踐小結：
- 先寫索引：在設計資料表時，根據常用的查詢條件提前規劃單欄與複合索引。
- 使用
EXPLAIN逐步驗證：每次調整索引或改寫 SQL 後，都以EXPLAIN確認執行計畫。 - 避免不必要的排序：
ORDER BY若無索引支援，會產生Using filesort。可透過在排序欄位上加索引或改寫查詢來減少。 - 利用覆蓋索引：只取索引欄位時會產生
Using index，不需要回表，效能最佳。 - 保持統計資訊新鮮：資料量變動大時，務必
ANALYZE TABLE，讓 Optimizer 能正確預估成本。
實際應用場景
1. 電商平台的訂單查詢
需求：每日產生上千筆訂單，後台管理介面需要即時顯示「最近 7 天內、狀態為已付款的訂單」列表。
做法：
- 建立複合索引
idx_status_created (status, created_at)。 - 使用
EXPLAIN確認type為range，rows估算在 10,000 以內。 - 若仍出現
Using filesort，改為ORDER BY created_at DESC前加上idx_status_created的 倒排（status, created_at DESC）。
EXPLAIN SELECT order_id, user_id, total
FROM orders
WHERE status = 'PAID'
AND created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)
ORDER BY created_at DESC;
2. 社交網站的好友建議
需求：根據使用者最近的互動紀錄，從 500 萬筆 friend_actions 表中找出符合條件的前 20 位候選人。
挑戰：資料量龐大且查詢包含多個條件（action_type, action_time）。
解法：
- 為
friend_actions建立 分割索引（action_type, user_id, action_time），並使用 分區（PARTITION BY RANGE (action_time)）降低單分區掃描量。 - 使用
EXPLAIN ANALYZE確認每個分區的rows估算在 5,000 以內。 - 若仍有
Using temporary，考慮將LIMIT 20前的排序改為ORDER BY action_time DESC並加上索引支援。
EXPLAIN ANALYZE
SELECT friend_id, COUNT(*) AS cnt
FROM friend_actions
WHERE user_id = 98765
AND action_type = 'LIKE'
AND action_time > NOW() - INTERVAL 30 DAY
GROUP BY friend_id
ORDER BY cnt DESC
LIMIT 20;
總結
- EXPLAIN 是 MySQL 性能調校的第一把鑰匙，能讓我們快速看出索引是否被使用、是否有全表掃描、排序或臨時表等潛在瓶頸。
- 透過
type、key、rows、Extra四大指標，我們可以一步步定位問題，然後以 建立適當索引、調整查詢條件、更新統計資訊 等方式優化。 - 常見的陷阱（如
SELECT *、函式在 WHERE、過時統計）只要在開發階段養成 每次改寫 SQL 都跑一次 EXPLAIN 的好習慣，就能避免。 - 在實務上，無論是電商訂單、社交平台的推薦系統，甚至是日誌分析，都能藉由 EXPLAIN 的即時回饋，讓資料庫在面對大規模資料時仍保持高效能。
最重要的訊息：優化不是一次性的工作，而是持續的迭代。只要把 EXPLAIN 當成日常開發的檢查清單，你的 MySQL 查詢將會變得更快、更可靠。祝你寫出高效能的 SQL！