MySQL 查詢優化實戰案例：從全表掃描到索引覆蓋的效能躍升

前言

資料庫系統是管理資訊系統的核心，查詢操作佔比最高，而 SELECT 是成本最高的 SQL 語句。當資料量達百萬/千萬筆，全表掃描需數十分鐘甚至數小時；優秀的查詢計畫可將時間壓縮至分鐘級。本文結合理論與實例，詳細剖析查詢優化關鍵技術。

一、誤區澄清：優化不只是 DBMS 的事

許多程式設計師認為優化是 DBMS 內部工作，實則使用者撰寫的 SQL 是優化基礎。DBMS 流程：語法檢查 → 查詢優化器（代數優化 + 存取路徑優化） → 預編譯 → 執行計畫。基於成本的優化器依賴系統統計資訊，**糟糕的 SQL 經過優化器仍難以高效**。

二、核心優化策略

1. 合理使用索引

索引是提升查詢效率的核心資料結構（多採 ISAM/B+ 樹）。使用原則：

• 頻繁 JOIN 但非外鍵的欄位 建立索引；非頻繁 JOIN 讓優化器自動處理。
• 頻繁 ORDER BY / GROUP BY 的欄位 建立索引。
• WHERE 條件常用、且基數高（唯一值多）的欄位 建索引；基數低（如性別）勿建索引，反而拖慢寫入。
• 多欄位排序/分組 使用 複合索引。
• 維護： 定期用工具檢查索引完整性；大量更新後 重建索引 可提速。

2. 避免或簡化排序

大表重複排序代價極大。優化器利用索引順序可省略排序。影響因素：

• 索引缺少排序欄位
• GROUP BY/ORDER BY 欄位順序與索引順序不一致
• 排序欄位來自不同表

對策：正確建立索引、合理合併表（犧牲正規化換效能）、縮小排序範圍。

3. 消除大表順序存取

巢狀查詢中順序存取會導致指數級掃描（三層各 1000 行 → 10 億行）。核心解法：**JOIN 欄位建立索引**。

範例：學生表 + 選課表，在 學號 建索引後 JOIN 瞬間完成。

4. 善用 UNION 避免強制順序掃描

某些 WHERE 寫法會迫使優化器放棄索引：

SELECT * FROM orders 
WHERE (customer_num=104 AND order_num>1001) OR order_num=1008

雖有索引，但 OR 跨欄位導致全表掃描。改寫為：

SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008

各子查詢可獨立使用索引，效能大幅提升。

三、實戰案例分析

場景： 銀行帳戶表 500 萬筆，需查詢某客戶近一年交易明細並按金額排序。

關鍵動作：

1. 建立複合索引 CREATE INDEX idx_cust_date_amt ON trans(cust_id, txn_date, amount)
2. 查詢改為 WHERE cust_id=? AND txn_date>=? ORDER BY amount（索引順序匹配）
3. EXPLAIN 確認 type=ref、Using index、Using filesort 消失

四、持續優化清單

• 定期 ANALYZE TABLE 更新統計資訊
• 監控 Slow Query Log 找出問題 SQL
• EXPLAIN 分析執行計畫，關注 type、key、rows、Extra
• 避免 SELECT *，只取需要欄位
• 大量寫入時暫停索引，事後重建

小結

查詢優化是「索引設計 + SQL 改寫 + 架構調整」的系統工程。沒有萬能銀彈，只有持續的測量、分析、調整。將優化意識融入開發流程，才能在資料爆炸期保持系統穩定高效。