SQL 效能：索引原理與查詢計畫

索引的本質：B-Tree

沒有索引的查詢：
┌─┬─┬─┬─┬─┬─┬─┬─┬─┬─┐
│1│2│3│4│5│6│7│8│9│10│  ← 一筆一筆掃描（Full Table Scan）
└─┴─┴─┴─┴─┴─┴─┴─┴─┴─┘
找 Id=7 → 掃描 7 次

有索引的查詢（B-Tree）：
        [5]
       /   \
    [2,4]  [7,9]
    / | \   / | \
  [1][3][4][6][8][10]
找 Id=7 → 只需 2 次比較（log₂N）

100 萬筆資料：
- Full Scan: 最多 1,000,000 次
- B-Tree Index: 最多 20 次（log₂ 1000000 ≈ 20）

EXPLAIN ANALYZE 怎麼看

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@gmail.com';

-- ❌ 壞的查詢計畫（沒有索引）
Seq Scan on users (cost=0.00..1500.00 rows=1 width=100)
  Filter: (email = 'test@gmail.com')
  Rows Removed by Filter: 99999
  Execution Time: 150.5 ms

-- ✅ 好的查詢計畫（有索引）
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=100)
  Index Cond: (email = 'test@gmail.com')
  Execution Time: 0.05 ms

關鍵字解讀

索引什麼時候失效？

-- ❌ 在索引欄位上用函式
WHERE UPPER(name) = 'MIKE'     → 索引失效
WHERE YEAR(created_at) = 2024  → 索引失效

-- ✅ 改寫避免函式
WHERE name = 'Mike'
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- ❌ LIKE 開頭用 %
WHERE name LIKE '%明'   → 索引失效
WHERE name LIKE '小%'   → ✅ 索引有效

-- ❌ 隱式型別轉換
WHERE phone = 912345678  → phone 是 VARCHAR，數字觸發轉換 → 索引失效
WHERE phone = '912345678' → ✅

正規化 vs 反正規化

正規化（減少重複）：
Users 表：id, name, city_id
Cities 表：id, city_name
→ 要 JOIN 才能取得城市名稱
→ 資料一致性好，但查詢要 JOIN

反正規化（接受重複）：
Users 表：id, name, city_name
→ 不需要 JOIN，查詢快
→ 但城市改名要改所有使用者的記錄

何時反正規化？
- 讀遠多於寫（報表、分析）
- JOIN 太多影響效能
- 資料很少變動