MySQL 索引實戰心法：單列 vs 組合、類型選擇、最左前綴原理與避坑指南

索引本質

索引是排序後的資料結構（B+Tree），讓 MySQL 能以 O(log N) 定位行，而非全表掃描 O(N)。100 萬行表，有索引查詢可能只需 20 次比較，快 5 萬倍。

索引分類

刪除：DROP INDEX idx_name ON tbl_name

單列 vs 組合索引：實測對比

表 myIndex(id PK, name, city, age, school_id)，1 萬行，5 筆 name='erquan'。

單列索引（name）

1. 索引定位 5 筆 name='erquan' → 中間結果集。
2. 掃描中間集過濾 city='鄭州'、age=25。
3. 仍需回表讀取其餘欄位。

組合索引 (name(10), city, age)

1. 直接定位唯一符合 name='erquan' AND city='鄭州' AND age=25 的行，零掃描。
2. 索引鍵長縮短（name(10)） → 索引更小、更快、插入更新更輕。

結論：組合索引 ≫ 多個單列索引。MySQL 單次查詢只能用到「一個」最佳索引，多個單列索引無法合併使用（Index Merge 例外但效率低）。

最左前綴原理

組合索引 (A, B, C) 等價於擁有三個虛擬索引：

• (A, B, C)
• (A, B)
• (A)

只有從最左列開始的連續前綴才能用到索引：

何時建立索引？

• WHERE、JOIN ON、ORDER BY、GROUP BY 出現的欄位。
• 運算子：=、<、>、<=、>=、BETWEEN、IN、LIKE 'prefix%'（不支援 %prefix 或 %prefix%）。

索引的代價（缺點）

1. 寫入變慢：INSERT/UPDATE/DELETE 需同步維護索引樹。
2. 佔磁碟空間：每個索引都是獨立 B+Tree 檔案。大表多組合索引可能超過資料本身體積。

實戰檢核清單

1. 用 EXPLAIN 確認 type=ref/range、key=預期索引、rows 遠小於總行數。
2. 避免 SELECT *，只取需要欄位 → 可能觸發「覆蓋索引」Using index，零回表。
3. 定期 ANALYZE TABLE 更新統計資訊，避免優化器誤判。
4. 大字串欄位建前綴索引 col(10~20)，平衡區分度與體積。
5. 低基數欄位（性別、狀態）別建索引，全表掃描更快；改用 ENUM。