SQL 優化原則與方法十大守則

核心優化原則

1. 避免 WHERE 子句使用 IS NULL / IS NOT NULL

   此類條件無法使用索引，導致全表掃描。建議：給欄位設定預設值（如 0、空字串），查詢改用 = 0 或 = ''。

2. 避免 LIKE 模糊查詢以萬用字元開頭（%keyword）

   WHERE name LIKE '%abc%' 或 '%abc' 皆無法用索引。若業務允許，改用全文檢索或 LIKE 'abc%'（前綴匹配可用索引）。

3. 減少 NOT、<>、!= 使用

   否定條件難以走索引。改用正向邏輯：WHERE status <> 1 → WHERE status IN (0,2,3) 或 WHERE status < 1 OR status > 1。

4. 用 EXISTS 替代 IN（子查詢場景）

   EXISTS 可在找到第一筆匹配時停止，IN 需掃描完整子查詢結果集。大數據量下 EXISTS 效能通常優於 IN。

5. ORDER BY 避免使用非索引欄位

   排序欄位無索引會觸發 Filesort（MySQL）或 Sort 運算子（SQL Server），消耗 CPU/記憶體。建立複合索引覆蓋 WHERE + ORDER BY 欄位。

6. 避免 WHERE 子句中對欄位進行運算/函數/串接

   WHERE YEAR(create_time) = 2023、WHERE name || '' || last_name = 'JohnDoe' 皆導致索引失效。改寫為 WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'。

7. 善用執行計畫分析

   MySQL: EXPLAIN SELECT ...；SQL Server: Ctrl+L / Ctrl+M；Oracle: EXPLAIN PLAN FOR ...。重點關注：type=ALL（全表掃描）、key=NULL（未用索引）、rows 預估掃描行數。

8. 多表連接：視結果集大小選擇子查詢或 JOIN

   小表驅動大表時 JOIN 效能佳；大表驅動小表或存在複雜過濾時，EXISTS/IN 子查詢可能更快。實測為準。

9. SQL 使用參數化查詢（佔位符）

   WHERE id = ? 而非 WHERE id = 123。優點：執行計畫重用、防 SQL 注入、減少硬解析（Oracle/PostgreSQL/SQL Server）。

10. 適度建立索引，避免過度索引

    索引能加速查詢，但會拖慢 INSERT/UPDATE/DELETE（需維護 B+ 樹）。單表索引建議 ≤ 5 個；優先建立高選擇性（基數高）欄位索引；複合索引遵循「最左前綴」原則。

補充：索引選擇性快速評估

SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;
-- selectivity 越接近 1，索引效果越好；< 0.1 通常不建議建立普通索引

小結

SQL 優化無銀彈，核心在於：讓索引生效、減少掃描行數、避免不必要的排序/運算。養成「寫 SQL → 跑 EXPLAIN → 調整索引/改寫」的閉環習慣，才是長久之計。