資料庫索引的原理是什麼？如何決定為哪些欄位建立索引？

索引原理

資料庫索引是一種加速查詢的資料結構，最常見的是 B-Tree 索引。原理類似書本的目錄——不需要掃描整張表，直接定位到目標資料的位置。

B-Tree 索引運作方式

SELECT * FROM users WHERE email = 'alice@example.com';

• 無索引：Full Table Scan，逐行比對，O(n) 複雜度
• 有索引：透過 B-Tree 二分搜尋，O(log n) 複雜度

適合建立索引的欄位

應該建索引：

• WHERE 子句常用的過濾欄位（如 email, status, created_at）
• JOIN 的連接欄位（外鍵）
• ORDER BY / GROUP BY 的欄位
• 高基數（cardinality）欄位：唯一值多的欄位效益最高

不應該建索引：

• 低基數欄位（如 gender, is_deleted）：索引效益低
• 寫入頻繁的欄位：每次 INSERT/UPDATE/DELETE 都要維護索引
• 很少查詢的欄位

複合索引（Composite Index）

-- 遵守最左前綴原則
CREATE INDEX idx_user_status_created ON orders (user_id, status, created_at);

-- 有效：WHERE user_id = ?
-- 有效：WHERE user_id = ? AND status = ?
-- 無效：WHERE status = ?（跳過第一欄）

常見問題

• 索引失效：WHERE YEAR(created_at) = 2024（函數導致全表掃）→ 改用範圍查詢
• 覆蓋索引（Covering Index）：查詢欄位全在索引內，不需回表，效能最佳
• EXPLAIN：EXPLAIN SELECT ... 確認是否使用索引