索引與外鍵的關係深度解析：為什麼外鍵列通常需要索引？

核心結論

外鍵列不強制要求創建索引，但強烈建議創建。 兩者是獨立的數據庫對象，服務於不同目的：

• 外鍵：約束機制，保證引用完整性（子表值必須存在於父表主鍵/唯一鍵中）。
• 索引：訪問路徑，加速查詢、連接及外鍵驗證操作。

為什麼「有外鍵必有索引」是最佳實踐？

1. 避免父表操作時的全表鎖定（關鍵）

當父表執行 DELETE 或 UPDATE 主鍵 時，數據庫必須檢查子表是否存在引用行。無索引時，需全表掃描子表並加排他鎖（TX 鎖），導致嚴重並發阻塞。有索引則可快速定位相關行，僅鎖定少量記錄。

2. 加速連接查詢

典型場景：SELECT * FROM parent p JOIN child c ON p.id = c.parent_id。子表 parent_id 有索引時，優化器可選擇 Nested Loop Join，大幅提升性能。

3. 異構數據庫一致性

Oracle、PostgreSQL、MySQL (InnoDB) 行為不同：

• Oracle：不自動創建 外鍵索引，必須手動添加。
• MySQL InnoDB：自動創建 外鍵索引（若不存在）。
• SQL Server：不自動創建，但建議手動創建。

「有索引無外鍵」的場景與影響

業務邏輯層保證完整性，數據庫僅建索引加速查詢：

• 優點：避免外鍵鎖開銷，適合高併發寫入、數據倉庫、微服務各自管理完整性。
• 風險：數據庫層面無法阻止孤兒數據（如父表刪除行而子表仍引用），需應用層嚴格控制。
• 連接查詢表現：索引依然生效，Join 性能不受影響。缺少的只是「刪除父表時的快速檢查」能力。

實戰建議

1. OLTP 核心業務表：外鍵 + 索引雙建，保數據質量、防鎖爭用。
2. 高併發寫入/日誌/歷史表：僅建索引，應用層控制完整性。
3. 數據倉庫/報表表：通常無外鍵，按查詢模式建組合索引。
4. 檢查現有外鍵索引：

   -- Oracle 查詢無索引外鍵
   SELECT c.table_name, c.constraint_name, c.column_name
   FROM user_cons_columns c
   JOIN user_constraints m ON c.constraint_name = m.constraint_name
   WHERE m.constraint_type = 'R'
   MINUS
   SELECT table_name, index_name, column_name
   FROM user_ind_columns;
   

總結

索引與外鍵是「加速器」與「守門人」的關係。**外鍵列加索引不是為了查詢，而是為了讓外鍵約束本身高效運行**。理解這一點，才能在設計時做出正確取捨。