您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

高效 MySQL 查詢優化實戰：索引、排序、連接與子查詢深度解析

編輯：MySQL 資料庫相關知識

為何查詢優化至關重要？

查詢操作佔資料庫負載多數，SELECT 是成本最高的 SQL 語句。百萬級資料全表掃描耗時數十分鐘，優秀的查詢計畫可將耗時壓縮至分鐘級。許多開發者僅重視 UI 而忽略 SQL 效能，導致系統資源浪費嚴重。本文結合理論與實戰，系統闡述優化核心策略。

核心認知：優化器非萬能

查詢優化器（基於成本模型）依賴使用者提交的 SQL 生成執行計畫。糟糕的 SQL 經過優化器仍難變高效，因此撰寫高品質 SQL 是效能基石。優化流程：詞法/語法檢查 → 代數優化 → 存取路徑優化 → 預編譯 → 執行 → 回傳結果。

七大優化實戰策略

1. 合理使用索引

經常 JOIN 但非外鍵的欄位建索引；非頻繁連接欄位交由優化器自動處理。
GROUP BY/ORDER BY 高頻欄位建索引。
條件表達式常用且基數高（唯一值多）的欄位建索引；低基數欄位（如性別）建索引反而拖慢寫入。
多欄位排序可建複合索引（順序需匹配查詢）。
定期用工具（如 Informix tbcheck）檢查索引完整性；大量更新後重建索引提速。

2. 避免或簡化排序

優化器若能利用索引順序直接輸出，可省略排序。導致排序的常見原因：

索引未覆蓋排序欄位。
GROUP BY/ORDER BY 欄位順序與索引不符。
排序欄位來自不同表。

對策：正確建索引、適度反正規化合併表、縮小排序範圍。

3. 消除大表順序掃描

巢狀查詢若每層順序掃描 1000 行，三層即掃描 10 億行。核心解法：為連接欄位建索引（如學生表與選課表皆於 學號 建索引）。

特殊 WHERE 條件強制順序掃描時，改用 UNION 分拆查詢：

-- 原：強制全表掃描
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008;

-- 優化：利用索引路徑
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;

4. 避免相關子查詢

主查詢欄位在子查詢 WHERE 中出現，導致主查詢每行皆觸發子查詢重新執行。嵌套層次越深效能越差。對策：盡量改寫為 JOIN 或 EXISTS；若不可避免，在子查詢中儘早過濾掉大量不符合行。

5. 避免困難正則表達式

LIKE '98___' 即使有索引也會全表掃描 → 改為 zipcode > '98000' 利用索引範圍掃描。
避免非開頭子串比對（如 zipcode[2,3] > '80'），同樣導致索引失效。

6. 善用臨時表加速複雜查詢

將常用子集排序後存入臨時表，避免重複排序、簡化優化器工作：

-- 建立臨時表
SELECT cust.name, rcvbles.balance, ...
INTO TEMP cust_with_balance
FROM cust, rcvbles
WHERE cust.customer_id = rcvbles.customer_id
  AND rcvbles.balance > 0
ORDER BY cust.name;

-- 後續查詢直接針對臨時表
SELECT * FROM cust_with_balance WHERE postcode > '98000';

注意：臨時表不自動同步主表變更，高頻寫入場景需謹慎使用。