索引是提升查詢效能的利器，但濫用會拖垮寫入效能、佔用儲存空間、干擾優化器決策。本文歸納 9 大建立索引的核心場景、5 大不建場景，以及複合索引設計的 5 黃金法則。

✅ 必須建立索引的 9 大場景

1. 主鍵、外鍵：主鍵自動建立聚簇索引，外鍵需手動建立索引支援關聯查詢與約束檢查。
2. 數據量 > 300 行：小表全表掃描較快，大表索引收益明顯。
3. 頻繁 JOIN 的關聯欄位：ON a.id = b.id 兩側均需索引，避免 Nested Loop 全表掃描。
4. WHERE 條件高頻欄位：特別是大表，WHERE status=1 AND created_at>'2023-01-01' 等。
5. 高選擇性欄位：COUNT(DISTINCT col)/COUNT(*) > 0.1 以上，唯一值越多索引越有效。
6. 小欄位優先：INT、BIGINT、CHAR(32) 優於 TEXT、BLOB、VARCHAR(500)。大字段建前綴索引 col(10)。
7. 複合索引經嚴謹分析：遵循最左前綴、選擇性高的列靠左、查詢模式覆蓋。
8. 頻繁寫入表克制索引數量：每個索引增加 INSERT/UPDATE/DELETE 開銷，寫多讀少表嚴格控制。
9. 定期清理無用索引：pt-duplicate-key-checker 檢測重複/冗餘索引，刪除未被使用的索引 (sys.schema_unused_indexes)。

❌ 不建議建立索引的 5 大場景

• 極小表 (< 300 行) 或全表掃描更快的場景
• 極低選擇性欄位 (如性別、狀態枚舉、布林值)
• 頻繁更新的大字段 (VARCHAR(1000), TEXT)
• 從不出現在 WHERE/JOIN/ORDER BY/GROUP BY 的欄位
• 已有單列索引覆蓋的複合索引 (如有 idx_a, idx_b，再建 idx_a_b 通常多餘)

🔑 複合索引設計 5 黃金法則

法則	說明	決策
主列選擇性	最左列選擇性最高的欄位	如 status 只有 3 個值，user_id 百萬值 → (user_id, status)
AND 組合查詢	多列頻繁同時以 AND 出現在 WHERE	是 → 建複合索引；否 → 單列索引
單列獨立查詢	複合索引欄位常單獨出現在 WHERE	是 → 拆分為多個單列索引
欄位數量控制	複合索引欄位 > 3 個	審視必要性，考慮減少欄位或拆分
單列+複合共存	已有單列索引，又建同欄位複合索引	一般刪除複合索引，單列索引可服務更多查詢模式

驗證索引效果

EXPLAIN SELECT * FROM orders WHERE user_id=123 AND status=1;
-- 觀察 key=idx_user_status, rows=預估掃描行數, Extra=Using index condition

定期執行 ANALYZE TABLE 更新統計資訊，確保優化器決策準確。

總結

索引設計是「減法藝術」：只為核心查詢路徑建立最小必要索引集合。每次加索引前問三遍：這查詢是核心嗎？頻率多高？選擇性如何？寫入開銷能接受嗎？