MySQL 索引類型全解析：普通、唯一、主鍵、外鍵、復合、全文

MySQL 索引類型全解析：普通、唯一、主鍵、外鍵、復合、全文

索引是提升查詢性能的核心手段。MySQL 支持六大類索引，各有適用場景與限制。本文系統梳理定義、創建語法、特性與優化建議。

1. 普通索引 (INDEX/KEY)

• 唯一任務：加速 WHERE、ORDER BY、JOIN。
• 允許重複值與 NULL。
• 語法：CREATE INDEX idx_name ON tbl(col) 或建表時 KEY idx_name (col)。

2. 唯一索引 (UNIQUE)

• 約束：鍵值唯一（允許多個 NULL）。
• 雙重作用：業務約束 + 查詢加速。
• 語法：CREATE UNIQUE INDEX uk_email ON user(email)。

3. 主鍵索引 (PRIMARY KEY)

• 特殊唯一索引：不允許 NULL、一張表僅一個。
• InnoDB 聚簇索引：數據按主鍵順序物理存儲，二級索引葉子存主鍵值（回表查數據）。
• 建議：業務無關自增整數（BIGINT UNSIGNED AUTO_INCREMENT）。

4. 外鍵索引 (FOREIGN KEY)

• 自動創建內部索引輔助約束檢查。
• 僅 InnoDB 支持，父表需有索引（主鍵或唯一）。
• 高併發下鎖爭用大，微服務架構常移至應用層校驗。

5. 復合索引 (Composite Index)

• 多列組合：INDEX idx_a_b_c (a, b, c)。
• 最左前綴原則：可用於 a、a,b、a,b,c；不可用於 b、c、b,c。
• 順序設計：區分度高（基數大）的列靠左；WHERE a=1 AND b=2 ORDER BY c 完美覆蓋。

6. 全文索引 (FULLTEXT)

• 針對長文本 CHAR/VARCHAR/TEXT 做分詞倒排索引。
• 語法：ALTER TABLE t ADD FULLTEXT(content)。
• 查詢：SELECT * FROM t WHERE MATCH(col) AGAINST('keyword')。
• 限制：InnoDB 5.6+ 支持，中文需分詞插件（如 ngram parser）；LIKE '%word%' 不走全文索引。

索引優化核心口訣

1. EXPLAIN 看 type、key、rows、Extra。
2. 覆蓋索引避免回表（Using index）。
3. 避免函數/運算破壞索引（WHERE YEAR(dt)=2023 → WHERE dt >= '2023-01-01' AND dt < '2024-01-01'）。
4. 單表索引數建議 ≤ 5，權衡寫入開銷。
5. 定期 ANALYZE TABLE 更新統計信息。