# DB Index 原理筆記
> 建議存放路徑：`~/devlog/2026-09/db-index-basics.md`
---
## 1. Index 到底是什麼
想像一本沒有目錄的字典，你要找「桌」這個字，只能從第一頁翻到最後一頁——這就是**Full Table Scan**：沒有 index 時，資料庫只能把整張表從頭掃到尾。
Index 就是幫這本字典加上目錄：一份**額外的、排序過的資料結構**（多數資料庫用 B-tree），裡面記著「某個值 → 在哪裡可以找到對應的資料列」。查詢時資料庫先在這個排序好的目錄裡做二分查找，快速定位到位置，再回原表撈出完整的一列資料。
代價是什麼？這份目錄要額外占空間，而且每次表格新增/修改資料，目錄也要跟著更新——所以 index 是用**空間 + 寫入成本**，換**查詢時間**。
---
## 2. Index 什麼時候真的有效？
不是加了 index 就一定變快，這裡有三個門檻：
**門檻 1：這個欄位的值夠「分散」嗎？（Selectivity）**
假設一張表有 100 萬筆訂單：
- 用「訂單編號」查（幾乎每筆都不同）→ index 一秒內定位到那一筆，非常划算。
- 用「訂單狀態」查，而狀態只有「處理中/已完成/已取消」3 種 → 就算走 index，光是「處理中」可能就命中 30 萬筆，還要回表撈 30 萬次，這時候資料庫可能反而覺得「乾脆整表掃一遍比較快」，直接放棄這支 index。
判斷方式：distinct 值數量 / 總列數，越接近 1 越適合當 index 的 leading column。
**門檻 2：查詢條件有沒有對上 index 的「起始欄位」**
這是最容易誤解的地方，第 3、4 節整個都在講這件事。
**門檻 3：統計資訊夠新嗎**
資料庫的優化器（Oracle/PostgreSQL/MySQL 等都是 Cost-Based Optimizer）要靠「這欄位大概有幾種值、表大概多少列」這類統計資訊來估算成本、決定要不要走 index。如果資料表剛匯入大量新資料，統計資訊還沒更新，優化器可能還在用舊的印象做判斷，結果選錯路——明明該走 index 卻選了 full scan，或反過來。
→ 定期跑統計資訊更新指令（Oracle `DBMS_STATS`、PostgreSQL `ANALYZE`、MySQL `ANALYZE TABLE`）。
---
## 3. 複合索引：把它想成一本「多層排序」的電話簿
假設有這支 index：
```
INDEX (colA, colB, colC, colD)
```
想像一本電話簿：先按姓氏排（colA），姓氏相同的人再按名字排（colB），名字也相同的再按地址排（colC）……以此類推。
現在問題來了：如果你只知道某人的「名字」（colB），要在這本電話簿裡找他，你能用「二分查找」嗎？不行——因為整本電話簿是先按姓氏分區塊的，同名字的人可能散落在電話簿的各個角落（因為姓氏都不同）。你唯一能做的就是從頭翻到尾，一個個看名字對不對——這其實就是退化成 full scan 了。
但如果你知道「姓氏」（colA），就可以直接跳到那個姓氏的區塊，範圍瞬間縮小。如果你連姓氏跟名字都知道（colA + colB），範圍還能再縮小一層。
這就是**最左前綴原則（Leftmost Prefix）**：index 只能從最左邊的欄位開始，連續往右用，才能一層層縮小範圍。
| 查詢條件 | 能不能用上這支 index |
|---|---|
| `colA = ?` | ✅ 可以，鎖定一個區塊 |
| `colA = ? AND colB = ?` | ✅ 可以，範圍更小 |
| `colA = ? AND colB = ? AND colC = ?` | ✅ 三層都鎖定，非常精準 |
| `colB = ?`（沒給 colA） | ❌ 像前面電話簿的例子，等於整本翻 |
| `colA = ? AND colC = ?`（跳過 colB） | ⚠️ 部分有效，見第 4 節 |
---
## 4. 查詢條件裡少一個欄位，index 還有用嗎？
延續電話簿的比喻，這裡分三種情況：
**情況 A：中間斷掉——你知道姓氏和地址，但不知道名字**
Index 是 `(colA, colB, colC)`，查詢是 `colA = ? AND colC = ?`（跳過 colB）。
你可以先跳到對的姓氏區塊，但這個區塊裡的人是按「名字」再細分的，你不知道名字，只好把這個姓氏底下**所有名字的子區塊**都翻一遍，逐一比對地址。如果這個姓氏底下名字種類不多（比如只有 5 種），翻一遍也不費事，這叫 **Index Skip Scan**，還算划算。但如果名字種類有幾百種，等於要翻幾百個子區塊，可能還不如直接整本翻（full scan）。
（要注意：不是每個資料庫都支援 skip scan，實際會不會用、划不划算，要看 EXPLAIN 的結果。）
**情況 B：尾端沒給——你知道姓氏和名字，但沒問地址**
Index 是 `(colA, colB, colC, colD, colE)`，查詢是 `colA = ? AND colB = ? AND colC = ?`。
完全沒問題。你已經用姓氏+名字+地址三層鎖定到很小的範圍了，後面的 colD、colE 有沒有問根本不影響前面這幾層的效率——用多少前綴，就精準到多少層，這是最正常的用法。
**情況 C：這個欄位根本沒在任何 index 裡**
不管怎麼查，這個欄位永遠沒辦法被 index 加速，只能等資料庫用 index 找到候選的那幾筆、回表之後，再逐筆檢查這個欄位符不符合條件（table-level filter）。如果這種篩選刷掉的比例很高，代表這個欄位可能也該考慮納入某支 index。
**一句話總結**：一個欄位有沒有用，看它在 index 定義裡的**位置**，不是看它有沒有出現在 WHERE 子句裡。中間斷掉打折扣、尾端沒給不影響前面、根本不在 index 裡就完全無加速。
---
## 5. WHERE 子句「寫的順序」到底重不重要？
這題常常眾說紛紜，直接做兩個實驗看結果，比空講理論更清楚。
### 實驗 1：交換順序，看優化器選的執行計畫會不會變
建一張 2 萬筆的表，`colA` 只有 5 種值、`colB` 只有 50 種值，建一支複合 index `(colA, colB)`，分別用兩種順序查同一組條件：
```sql
-- 順序 1
WHERE colB = 'type3' AND colA = 'org2'
-- 順序 2（交換）
WHERE colA = 'org2' AND colB = 'type3'
```
實測結果，兩者的執行計畫**一模一樣**：
```
=== 順序 1 ===
SEARCH t USING INDEX idx_ab (colA=? AND colB=?)
=== 順序 2 ===
SEARCH t USING INDEX idx_ab (colA=? AND colB=?)
```
不管你怎麼交換順序，優化器選出來的 index、掃描方式完全相同。這是因為優化器不是「照你打字的順序」逐條處理 WHERE，它是先把所有條件收集起來，再根據 index 定義和統計資訊算出最佳路徑——跟你打字的先後順序沒有關係。
### 實驗 2：如果條件裡有一個「沒被 index 覆蓋」的昂貴運算呢？
10 萬筆資料，`colX = 0` 是一個便宜、選擇性高的條件（只命中 1% 的資料），`EXPENSIVE(colY)` 模擬一個沒有 index 可用、運算成本很高的條件（例如函式運算、複雜字串比對）：
```
便宜條件在前，昂貴函式被呼叫次數：1000
昂貴條件在前，昂貴函式被呼叫次數：100000
```
**差了 100 倍。** 原因是 AND 的短路求值：資料庫會先用便宜條件擋掉 99% 的資料，昂貴條件只需要對剩下的 1000 筆計算；但如果昂貴條件寫在前面，它就得對全部 10 萬筆都先算一次，才知道要不要繼續看下一個條件。
### 兩個實驗合起來看，答案其實很清楚
- **「要不要用某支 index、走哪種 scan」**——順序不影響（實驗 1）。
- **「沒被 index 覆蓋、需要逐列運算的條件，實際被算幾次」**——順序有影響，而且影響可能很大（實驗 2）。
實務上感覺到「改順序真的有差」，幾乎都是落在第二種情況：條件裡有某個沒被 index 覆蓋的運算，順序改變了它被執行的次數，而不是改變了「有沒有用到 index」。這兩件事表面上很像，機制卻完全不同，混在一起講才會讓人覺得「順序不重要」這句話太武斷。
**真正決定「能不能用到 index」的順序，是 index 定義時欄位的先後順序**（`CREATE INDEX ... (colA, colB, colC)`），這個順序一旦建好就固定了，它決定：
1. 哪個欄位是 leading column（能不能用上這支 index 的關鍵）。
2. 排序方向能不能被 `ORDER BY` 直接利用（省一次額外排序）。
3. Skip scan（若該資料庫支援）划不划算——越前面的欄位 distinct 值越少，skip scan 越省事。
---
## 6. 實務設計複合 index 的順序原則
1. **Selectivity 高、又常被查詢的欄位放最前面**——類似訂單編號這種近乎唯一、常拿來當篩選條件的欄位。
2. 有明確「先篩大範圍、再篩小範圍」邏輯的欄位，依這個邏輯排列（例如先按分類、再按日期區間查）。
3. 同一組欄位如果有多種查詢組合（有時用 A+B、有時用 A+C），與其硬塞一支萬用 index，不如直接建兩支各自對應的 index。
4. 選擇性低的分類欄位（只有幾種值）盡量別放在最前面，除非幾乎每次查詢都會用到它。
5. Index 越多，寫入成本越高，記得在查詢效能跟寫入效能之間取捨。
6. 想確認實際效果，直接用該資料庫的執行計畫工具驗證（`EXPLAIN` / `EXPLAIN ANALYZE` / SQL Monitor），理論推斷終究比不上實測。