資料庫索引概論 (Database Indexing Overview)
什麼是索引
索引像書本的 目錄：讓資料庫不用從頭到尾翻整本書（資料表），就能快速找到需要的資料。
沒索引 vs 有索引
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
);
對 email 做查詢：
SELECT * FROM users WHERE email = 'bohr@example.com';
| 情境 | 行為 | 複雜度 |
|---|---|---|
| 沒索引 | Full Table Scan 逐筆比對 | O(n) |
建立索引 CREATE INDEX idx_email ON users(email); |
透過索引跳到對應位置 | 接近 O(log n) |
索引加速哪些操作
| 操作 | 範例 |
|---|---|
| 條件查詢（等值/範圍） | WHERE email = 'x'、WHERE age > 20 |
| JOIN | 兩表 join 時避免巢狀迴圈全表比對 |
| 排序 | ORDER BY email（若索引已排序） |
| 前綴搜尋 | LIKE 'abc%'（但 '%abc' 無法） |
索引的成本（Trade-offs）
索引不是免費午餐
| 代價 | 說明 |
|---|---|
| 額外儲存空間 | 索引是獨立結構，每個索引都多佔一份空間 |
| 寫入變慢 | INSERT/UPDATE/DELETE 時要同步更新所有相關索引 |
| 太多索引反而拖效能 | 每次寫入要跑 N 個索引維護；查詢優化器也要花時間挑選哪個索引 |
索引策略原則
- 只在經常被查詢的欄位建索引
- 寫密集欄位少建索引
- 複合索引要注意欄位順序（左前綴法則）
常見索引種類
| 索引類型 | 最適合 | 不擅長 |
|---|---|---|
| 04-Database-Indexing/02-B-Tree-Index | 平衡讀寫、範圍查詢、排序 | 超高寫入吞吐 |
| 04-Database-Indexing/03-LSM-Tree | 寫多讀少、時序資料、log 類 | 單點讀取有放大效應 |
| 04-Database-Indexing/04-Hash-Index | 等值查詢（WHERE id = 123） |
範圍查詢、排序 |
| 04-Database-Indexing/05-Geospatial-Index | 地理座標鄰近搜尋 | 一般欄位 |
| 04-Database-Indexing/06-Inverted-Index | 全文檢索、關鍵字搜尋 | 精確欄位比對 |
Simplification-with-exceptions
邊界情況
- Primary Key 一定自帶索引（幾乎所有 RDBMS）；
UNIQUE約束也會自動建索引。 - Covering Index — 索引本身已包含查詢需要的所有欄位，可完全避開回表（index-only scan）。
- Partial / Filtered Index — 只對符合條件的 row 建索引（如只對
status = 'active'），省空間。 - Clustered vs Non-clustered — Clustered 直接把資料按索引順序存放（每表只能一個），
Non-clustered 只存 key → 資料指標。InnoDB Primary Key = Clustered。
決策流程
查詢效能不夠？
├─ 等值查詢為主 → Hash Index
├─ 範圍 / 排序 / 通用 → B-Tree Index（預設）
├─ 寫入量極大 → LSM Tree
├─ 地理座標鄰近搜尋 → Geospatial Index
└─ 全文關鍵字搜尋 → Inverted Index