MySQL 優化與 EXPLAIN：查詢效能優化方法
簡介
在實務開發中，資料庫往往是系統的瓶頸所在。即使程式碼寫得再好，若 SQL 查詢效率低落，整個應用的回應時間仍會被拖慢。MySQL 作為最常見的開源關聯式資料庫，提供了多樣的優化工具與技巧，讓開發者能在不改變資料模型的前提下，大幅提升查詢效能。
本篇文章將聚焦於 MySQL 查詢效能優化，從基本概念、實作範例、常見陷阱到最佳實踐，一步步帶領讀者掌握 EXPLAIN 的使用方法與其他關鍵優化手段，幫助你在日常開發與維運中快速定位與解決效能問題。
核心概念
1. 什麼是 EXPLAIN？
EXPLAIN（或 EXPLAIN ANALYZE）是 MySQL 用來顯示 查詢執行計畫 的指令。它告訴我們：
| 欄位 | 說明 |
|---|---|
id |
執行順序的唯一標識，值越大表示越晚執行 |
select_type |
查詢類型（簡單查詢、子查詢、派生表等） |
table |
被存取的資料表名稱 |
type |
連接方式（ALL、index、range、ref、eq_ref、const） |
possible_keys |
可能使用的索引 |
key |
真正使用的索引 |
rows |
估計需要讀取的行數 |
Extra |
其他資訊（如 Using where、Using filesort、Using temporary） |
透過觀察 type、rows 與 Extra，我們能快速判斷 是否有索引失效、是否產生排序或臨時表，進而決定優化方向。
小技巧：在 MySQL 8.0 以上，使用
EXPLAIN FORMAT=JSON能得到更詳細的階層化結構，方便程式化分析。
EXPLAIN FORMAT=JSON
SELECT *
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';
2. 為什麼索引是關鍵？
索引是資料庫的 查找表，正確的索引能把原本需要全表掃描 (ALL) 的查詢，變成 索引掃描 (range、ref、eq_ref），將 rows 從數十萬降到幾十或更少。建立索引時需要注意：
- 選擇性（Selectivity）高的欄位更適合作為索引。
- 複合索引的欄位順序必須符合查詢條件的使用順序（左前綴原則）。
- 避免過度索引：過多的索引會拖慢
INSERT、UPDATE、DELETE，且佔用磁碟空間。
3. 常見的效能瓶頸類型
| 類型 | 典型症狀 | 可能原因 |
|---|---|---|
全表掃描 (type=ALL) |
rows 數量極大、查詢慢 |
缺少適當索引、條件使用函式 |
| 排序 (filesort) | Extra 出現 Using filesort |
ORDER BY 欄位未被索引、使用 GROUP BY |
| 臨時表 | Extra 出現 Using temporary |
GROUP BY、DISTINCT、UNION 未使用索引 |
| 鎖爭用 | 查詢卡住、延遲 | 大量更新同一筆資料、缺乏行級鎖 |
程式碼範例
以下示範 5 個實務中常見的優化案例，從 問題診斷 到 解決方案，每段程式碼皆附上說明。
範例 1：避免全表掃描 → 建立單欄位索引
-- 問題查詢：找出 2024 年 1 月以後的訂單
SELECT *
FROM orders
WHERE order_date >= '2024-01-01';
診斷
EXPLAIN SELECT *
FROM orders
WHERE order_date >= '2024-01-01';
type 為 ALL，rows 可能是整張表的行數。
解決方案
-- 為 order_date 建立索引
CREATE INDEX idx_orders_order_date ON orders(order_date);
重新執行 EXPLAIN，type 會變成 range，rows 大幅下降。
範例 2：使用複合索引支援多條件查詢
-- 問題查詢：找出特定客戶在特定日期範圍的訂單
SELECT *
FROM orders
WHERE customer_id = 12345
AND order_date BETWEEN '2024-01-01' AND '2024-01-31';
診斷
possible_keys 只顯示 customer_id，order_date 仍需過濾，導致 rows 較多。
解決方案
-- 建立左前綴符合的複合索引
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
此索引同時涵蓋兩個條件，EXPLAIN 會顯示 type=range，rows 只剩下該客戶當月的訂單。
範例 3：避免函式導致索引失效
-- 問題查詢：使用 YEAR() 函式篩選年份
SELECT *
FROM orders
WHERE YEAR(order_date) = 2024;
診斷
type 為 ALL，因為 YEAR(order_date) 使索引無法使用。
解決方案
-- 改寫為範圍條件
SELECT *
FROM orders
WHERE order_date >= '2024-01-01'
AND order_date < '2025-01-01';
此寫法可直接利用 order_date 索引（若已建立），EXPLAIN 會顯示 type=range。
範例 4：利用覆蓋索引（Covering Index）減少回表
-- 問題查詢：僅取得訂單編號與金額
SELECT order_id, total_amount
FROM orders
WHERE status = 'completed';
診斷
即使 status 有索引，仍需回表取得 order_id、total_amount。
解決方案
-- 建立覆蓋索引，將需要的欄位全部放入索引
CREATE INDEX idx_orders_status_cover ON orders(status, order_id, total_amount);
此時 EXPLAIN 會出現 Using index，表示只在索引內完成查詢，IO 負載大幅降低。
範例 5：排除不必要的 SELECT *，減少資料傳輸
-- 問題查詢：取出所有欄位，實際只需要三個欄位
SELECT *
FROM customers
WHERE country = 'TW';
診斷
即使使用索引，仍會回傳整筆資料，造成 網路與記憶體浪費。
改寫
SELECT customer_id, name, email
FROM customers
WHERE country = 'TW';
此寫法讓 MySQL 僅讀取必要欄位，效能提升明顯，尤其在欄位很多的大表上更為顯著。
常見陷阱與最佳實踐
| 陷阱 | 說明 | 最佳實踐 |
|---|---|---|
過度使用 SELECT * |
會導致不必要的 I/O 與網路傳輸 | 只選取需要的欄位 |
| 忘記更新統計資訊 | 索引選擇依賴統計資訊，過時會誤判 | 定期執行 ANALYZE TABLE 或使用自動統計 |
| 在條件中使用函式 | 使索引失效，例如 WHERE DATE(col) = ... |
改寫為範圍條件或使用 generated column + 索引 |
忽視 EXPLAIN 的 Extra |
Using filesort、Using temporary 代表潛在瓶頸 |
盡量讓 ORDER BY、GROUP BY 使用索引 |
| 複合索引欄位順序錯誤 | 左前綴原則未遵守，導致只能使用部分索引 | 依照最常見的查詢條件排列欄位順序 |
| 過度索引 | 每筆寫入都要更新所有索引，影響寫入效能 | 僅保留必要的索引，使用 SHOW INDEX 定期審查 |
其他實用技巧
- 分頁查詢：使用
LIMIT offset, row_count時，offset越大越慢。可改用「鍵值分頁」 (WHERE id > last_id)。 - 批次寫入：大量
INSERT時，關閉自動提交 (SET autocommit=0) 或使用INSERT ... VALUES (...),(...),...批次插入。 - 使用
EXISTS取代IN：在子查詢結果集較大時，EXISTS效能較佳。 - 適度使用
STRAIGHT_JOIN：強制 MySQL 按寫入順序連接，避免不理想的 join 順序。
實際應用場景
案例 1：電商平台的訂單搜尋
需求：每日報表需要統計 上月、特定會員、已付款 的訂單金額。原始查詢因 WHERE YEAR(order_date)=2024 AND MONTH(order_date)=3 耗時 12 秒。
解決步驟：
- 改寫為範圍條件：
order_date BETWEEN '2024-03-01' AND '2024-03-31'。 - 建立複合索引：
CREATE INDEX idx_orders_status_date ON orders(status, order_date); - 使用覆蓋索引：把
total_amount加入索引，使報表只走索引。
結果：查詢時間從 12 秒降至 0.3 秒，系統 CPU 使用率下降 80%。
案例 2：社群平台的好友推薦
需求：每秒需要從 百萬筆使用者 中挑選符合條件的 10 位好友。原始 SELECT * FROM users WHERE age BETWEEN 20 AND 30 AND city='Taipei' ORDER BY last_active DESC LIMIT 10; 產生 Using filesort。
解決步驟：
- 建立複合索引：
CREATE INDEX idx_users_age_city_active ON users(age, city, last_active DESC); - 改寫為僅取必要欄位：
SELECT user_id, name, avatar FROM users ... - 使用
FORCE INDEX（若 optimizer 未自動選擇）。
結果：EXPLAIN 顯示 type = range、Extra 無 filesort，查詢時間從 850 ms 降至 45 ms。
總結
- EXPLAIN 是診斷 MySQL 查詢效能的第一把鑰匙，透過
type、rows、Extra能快速定位問題。 - 索引 是提升查詢速度的核心資產，必須根據 選擇性、查詢模式 與 左前綴原則 來設計。
- 避免 函式、SELECT *、過度索引 等常見陷阱，並善用 覆蓋索引、範圍條件、鍵值分頁 等技巧。
- 針對實務需求，先分析查詢計畫 → 調整索引或改寫 SQL → 再次驗證，形成迭代式的優化流程。
掌握上述概念與實作範例，你將能在日常開發與維運中，快速找出效能瓶頸，並透過合理的索引與查詢重構，將 MySQL 的查詢速度提升至 秒級甚至毫秒級，為使用者提供更流暢的體驗。祝你在 MySQL 優化的道路上越走越順！