MySQL 高效查詢語句設計七大原則

2. 避免或簡化排序

• 利用索引自然有序避免 filesort。
• 索引缺失排序列、GROUP BY/ORDER BY 順序與索引不一致、跨表排序會觸發排序。
• 不可避免時縮小排序範圍（如先 WHERE 過濾再排序）。

3. 消除大表順序掃描

• 嵌套查詢中若無索引，三層嵌套各 1000 行即產生 10 億次掃描。
• 核心對策：給 JOIN 列建索引。
• 利用 UNION 將離散條件拆分，讓優化器走索引：

-- 強制全表掃描
SELECT * FROM orders WHERE (customer_num=104 AND order_num>1001) OR order_num=1008;
-- 改為 UNION，各分支走索引
SELECT * FROM orders WHERE customer_num=104 AND order_num>1001
UNION
SELECT * FROM orders WHERE order_num=1008;

4. 避免相關子查詢

外層列值變化導致內層重複執行，嵌套層數越多性能指數級下降。能用 JOIN 替換就不用子查詢；必須用時在子查詢中儘早過濾數據。

5. 避免困難正則表達式

• LIKE '%abc%'、MATCHES 導致全表掃描，即使有索引也失效。
• 改用範圍查詢：zipcode > '98000' 可走索引。
• 避免非前綴子串：zipcode[2,3] > '80' 不走索引。

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

將中間結果集物質化到臨時表，避免重複排序、簡化優化器工作：

-- 原查詢多次執行
SELECT ... FROM cust, rcvbles WHERE ... ORDER BY cust.name;

-- 建臨時表
SELECT ... INTO TEMP cust_with_balance FROM cust, rcvbles WHERE ... ORDER BY cust.name;
-- 後續查詢臨時表
SELECT * FROM cust_with_balance WHERE postcode > '98000';

注意：臨時表不反映主表實時變更，高併發寫入場景慎用。

7. 用排序替代非順序 I/O

非順序磁盤讀取（磁頭跳轉）最慢。某些場景下，先按需排序再順序讀取，反而比隨機訪問快。

總結

索引是核心，但索引不是越多越好；查詢重構（UNION、臨時表、避免子查詢/正則）往往比加索引收益更大。持續 EXPLAIN 分析執行計劃，是 SQL 優化的不二法門。