前言
索引是資料庫效能調優的關鍵，但盲目建立索引會拖慢寫入速度並佔用磁碟空間。本文整理索引建立的核心原則、適用場景與避坑指南。
一、適合建立索引的欄位特徵
- 主鍵：系統自動建立聚簇索引。
- 唯一鍵：系統自動建立唯一索引。
- 外鍵欄位：加速關聯查詢與約束檢查。
- JOIN 關聯欄位：
ON 條件欄位。
- 頻繁出現在 WHERE、ORDER BY、GROUP BY、DISTINCT 的欄位。
- 範圍查詢欄位：
>、<、BETWEEN、LIKE 'prefix%'。
- 高頻多欄位組合查詢：建立複合索引，順序依查詢頻率與區分度決定（最左前綴原則）。
二、不適合建立索引的場景
- 極少查詢的欄位：維護成本大於收益。
- 低基數欄位：如性別、狀態、是否推薦（取值極少），全表掃描更快。
- 大型物件類型：
TEXT、BLOB、JSON（可改用全文索引或前綴索引）。
- 寫入頻繁、讀取較少的表：索引會顯著降低 INSERT/UPDATE/DELETE 效能。
三、索引數量與維護原則
- 單表索引建議 ≤ 5 個，高寫入表更應精簡。
- 定期檢視
SHOW INDEX FROM tbl 與 sys.schema_unused_indexes（MySQL 8.0+），清理無用索引。
- 大量資料變更後執行
ANALYZE TABLE 更新統計資訊。
四、複合索引設計黃金法則
- 遵循最左前綴原則：查詢條件必須包含索引最左列才會被使用。
- 高區分度、高頻率欄位放左邊。
- 避免在索引列上使用函數/運算（如
WHERE YEAR(date_col)=2023），導致索引失效。
五、小表不一定需要索引
資料量極小（如 < 1000 行）時，全表掃描往往比索引查找更快，優化器可能直接忽略索引。
六、儲存引擎差異提醒
- InnoDB：聚簇索引（主鍵即資料），二級索引葉子節點存主鍵值，建議主鍵使用自增整數。
- MyISAM：非聚簇索引，索引葉子節點存資料指標。
