MySQL 查詢優化實戰技巧：索引、排序與存取路徑

查詢優化的重要性

資料庫系統是管理資訊系統核心，OLTP 與 OLAP 是銀行、企業、政府關鍵應用。查詢操作佔比最高，SELECT 為 SQL 中代價最大陳述式。百萬級資料全表掃描需數十分鐘，優良查詢策略可降至數分鐘，優化效益顯著。

許多開發者只重視介面華麗，忽略查詢效率，導致系統低效、資源浪費。本文結合實務案例與理論，闡述查詢優化實戰運用。

誤區與原理

誤區：以為優化是 DBMS 責任，與 SQL 撰寫無關。事實：良好查詢計畫可提升數十倍效能。DBMS 處理流程：語法檢查 → 查詢最佳化器（代數優化 + 存取路徑優化） → 預編譯模組產生查詢規劃 → 執行 → 回傳結果。主流資料庫（Oracle、Sybase 等）採用基於成本的優化，但用戶提交的 SQL 為優化基礎，原本糟糕的查詢難經由系統優化變高效。

三大核心優化策略

1. 合理使用索引

索引為提升查詢效率的關鍵資料結構（多採 ISAM 架構）。使用原則：

• 經常 JOIN 但非外鍵的欄位建索引；不常連接的欄位讓最佳化器自動處理。
• GROUP BY、ORDER BY 高頻欄位建索引。
• 條件表達式中高基數（Distinct 值多）欄位建索引；低基數（如性別僅「男/女」）勿建索引，反而嚴重拖慢更新速度。
• 多欄位排序需求時建立複合索引。
• 善用系統工具（如 Informix tbcheck）檢查索引完整性；大量更新後刪除重建索引可提速。

2. 避免或簡化排序

大型表重複排序代價高。最佳化器若能利用索引順序輸出則省略排序。影響因素：

• 索引未包含待排序欄位
• GROUP BY/ORDER BY 欄位順序與索引不符
• 排序欄位來自不同表

對策：正確增建索引、合理合併表（犧牲正規化換效率）、不可避免時縮小排序範圍。

3. 消除大型表循序存取

巢狀查詢中循序存取可能造成災難性效能：3 層巢狀各 1000 行即產生 10 億行掃描。核心解法：對連接欄位建索引。例：學生表與選課表以「學號」連接，必須在學號欄位建索引。

亦可用 UNION 避免循序存取。某些 WHERE 子句形式會強迫最佳化器採用循序存取，需特別注意改寫。