MySQL 索引設計核心原則與避坑指南：從單列到聯合索引的 9 大準則

前言

索引是查詢性能的關鍵，但「索引越多越好」是嚴重誤區。本文總結 9 大核心判斷依據，幫助你構建高效、精準的索引體系。

九大核心準則

1. 主鍵、外鍵必建索引

主鍵自動建立聚簇索引；外鍵列必須手動建立索引 (InnoDB 不自動創建)，否則修改父表時會鎖定子表全表掃描，導致嚴重阻塞。

2. 數據量 > 300 萬建議建索引

小表 (幾百行) 全表掃描極快，索引維護開銷反而大於收益。大表無索引查詢將災難性。

3. 經常 JOIN 的關聯列必須建索引

`ON a.id = b.a_id` 中 `a.id` (通常是 PK) 和 `b.a_id` 都需索引。被驅動表的關聯列無索引會導致 Nested Loop Join 退化為全表掃描。

4. 高頻 WHERE 條件列 (特別是大表) 必須建索引

`WHERE status=1 AND create_time>'2023-01-01'` 中 `status`、`create_time` 為高頻過濾列，應建索引。

5. 索引選擇性高的列 (區分度高)

選擇性 = `COUNT(DISTINCT col) / COUNT(*)`。越接近 1 越好 (如身份證號、手機號)。性別、狀態枚舉 (區分度極低) **不適合**建單列索引。

6. 索引列盡量短、避免長文本

• 定長 `CHAR/INT/BIGINT` 優於變長 `VARCHAR/TEXT`。
• 長字符串可用**前綴索引** `INDEX idx_name (name(10))`，但無法用於 `ORDER BY`/`GROUP BY` 覆蓋索引掃描。
• 極長文本 (`TEXT/BLOB`) **不要建索引**，改用全文索引或外部搜索引擎。

7. 聯合索引 (復合索引) 設計需嚴謹分析 — 最佳左前綴原則

聯合索引 `(a, b, c)` 等價於擁有 `(a)`、`(a,b)`、`(a,b,c)` 三個索引。

決策樹：

1. A. 主列選擇：將**區分度最高**的列放最左 (通常是 `WHERE` 條件固定值的列)。
2. B. 查詢模式：若 `a,b` **經常同時出現且為 AND**，且**極少單獨查 `b`** → 建 `(a,b)`。
3. C. 拆分原則：若 `b` **經常單獨出現在 WHERE** → 拆分為單列索引 `(a)`、`(b)` 或建 `(b,a)` (視區分度)。
4. D. 列數控制：超過 3 列謹慎評估，考慮減少列數或拆分。
5. E. 避免冗餘：若已有單列索引 `(a)`、`(b)`，聯合索引 `(a,b)` 通常**無存在價值**，反而增加寫入開銷。

8. 高頻寫入表 (INSERT/UPDATE/DELETE) 慎建過多索引

每個索引都增加寫入開銷 (B+樹維護、Undo Log、Redo Log)。頻繁寫入表索引數建議 **≤ 3-5 個**，優先保障核心查詢。

9. 定期清理無用索引

• 長期未被查詢使用的索引 (`sys.schema_unused_indexes`)。
• 冗餘索引 (如前綴索引被全列索引覆蓋)。
• 無用索引干擾優化器選擇執行計劃，降低寫入性能。

索引類型選擇速查表

驗證索引效果

EXPLAIN SELECT * FROM orders WHERE user_id=100 AND status=1;
-- 關鍵看：type=ref/range, key=idx_user_status, rows=預估掃描行數 (應極小), Extra=Using index condition

總結

索引設計本質是**空間換時間、寫入換讀取**的權衡。**依據業務查詢模式建索引，而非依據表結構**。遵循「高區分度、最左前綴、少而精、定期清理」十六字真言，拒絕過度索引與無效索引。