撰寫高效能 MySQL SQL 語法：8 大 WHERE 子句優化陷阱與對策

前言

系統上線初期資料量小，SQL 寫法差異不明顯；隨資料增長，劣質 SQL 與優質 SQL 速度可差上百倍。優化器依索引決定存取路徑，不當寫法會導致全表掃描。

八大常見陷阱

1. IS NULL / IS NOT NULL

• 含 NULL 的欄位不會被納入索引（複合索引同理）。
• WHERE col IS NULL 無法使用索引，改用預設值（如 0、空字串）+ 索引。

2. 欄位聯接運算

WHERE first_name || ' ' || last_name = 'Bill Clinton' 導致 last_name 索引失效。
改寫：WHERE first_name = 'Bill' AND last_name = 'Clinton'。
變數情況：應用層拆分後再查詢，勿在 SQL 端對索引欄做函數運算。

3. LIKE 開頭萬用字元

LIKE '%clinton%' 無法用索引；LIKE 'clinton%' 可用。
若業務必須前置萬用字元，考慮全文索引或外部搜尋引擎。

4. ORDER BY 非索引欄或運算式

ORDER BY col1 + col2、ORDER BY UPPER(name) 會觸發檔案排序。
解決：建立對應索引、或改寫為索引欄排序。

5. NOT 邏輯與 <> 運算子

WHERE NOT salary > 3000 → WHERE salary <= 3000（可用索引）。
<> 同屬 NOT 類，盡量改寫為範圍查詢。

6. IN vs EXISTS

• IN：先執行子查詢建臨時表，再掃描主查詢。
• EXISTS：掃描主查詢，逐筆檢查子查詢，找到即停止，通常更快。
• 建議：NOT EXISTS 替代 NOT IN。

7. 隱含型別轉換

WHERE varchar_col = 123 導致索引失效，嚴禁數值與字串隱含轉換。

8. 複合索引最左前綴原則

索引 (a,b,c)，查詢需從 a 開始，跳過 a 直接查 b 會失效。

優化檢查清單

1. EXPLAIN 每條核心 SQL，確認 type 非 ALL。
2. 對 WHERE、JOIN、ORDER BY 欄位建立適當索引。
3. 避免對索引欄使用函數、運算、型別轉換。
4. 大表分頁改用游標/延遲關聯法。
5. 定期 ANALYZE TABLE 更新統計資訊。