MySQL 索引類型全景：普通/唯一/主鍵/外鍵/組合/全文/空間索引優缺點與適用場景

MySQL 支援 7 大索引類型，各有結構特性、寫入代價與查詢加速場景。本文系統對比優缺點、限制條件與選型指南。

一、索引分類速覽

二、核心類型深度解析

1. 普通索引

• 任務：加速 WHERE/ORDER BY
• 選擇：高選擇性、緊湊類型（整數 > 字串）
• 長度：CHAR/VARCHAR 可指定前綴長度（如 INDEX(name(10))）

2. 唯一索引

• 雙重效用：加速查詢 + 業務約束
• 插入時自動檢測重複，拋 ERROR 1062
• 管理開銷 < 普通索引（優化器知曉唯一性）

3. 主鍵索引

• InnoDB：資料即索引（聚集），葉節點存完整行
• MyISAM：非聚集，葉節點存行指標
• 無顯式主鍵 → 隱式 6B row_id，極度不推薦

4. 外鍵索引

• 僅 InnoDB 支援，自動建立內部普通索引
• 高併發下鎖競爭劇烈，**生產環境建議應用層維護完整性**

5. 組合索引

• INDEX(a,b,c) 可作 a、(a,b) 索引用，**不可作 b、c、(b,c)**
• 列序原則：高頻 > 高選擇性 > 短長度

6. 索引長度限制

• InnoDB 單列 ≤ 767B（組合 ≤ 3072B），超長自動轉前綴
• BLOB/TEXT **必須**指定前綴長度（≤ 255 字符）

7. 全文索引

• 建立：ALTER TABLE t ADD FULLTEXT(col1, col2)
• 查詢：SELECT * FROM t WHERE MATCH(col1,col2) AGAINST('word1 word2')
• InnoDB 5.6+ 支援，5.7+ 支援中文分詞（需 ngram parser）
• 不支援事務隔離級別中的幻讀防護

三、索引不可用的 6 大限制

1. WHERE col != ... / NOT IN
2. WHERE FUNC(col) = ...（函數/表達式）
3. JOIN 關聯列類型不一致
4. LIKE '%abc' 通配符開頭
5. ORDER BY 條件非索引列或跨表
6. 低選擇性列（如性別 0/1）建索引無效

四、EXPLAIN 實戰讀懂執行計畫

索引是雙面刃：查詢加速、寫入拖累。精準建立、定期審視、無用即刪。