MySQL 索引類型全解析：優缺點對比、選型指南與避坑清單

MySQL 索引類型全解析：優缺點對比、選型指南與避坑清單

索引是查詢性能的加速器，也是寫入性能的成本中心。本文系統梳理 MySQL 六大索引類型（普通、唯一、主鍵、外鍵、復合、全文）的特點、適用場景、優缺點，並給出基於業務特徵的選型決策樹與生產環境避坑清單。

一、 索引本質與核心權衡

• 本質：B+ 樹數據結構（InnoDB），存儲鍵值 + 指向聚簇索引主鍵的指針
• 收益：查詢從全表掃描 O(N) 降為索引查找 O(log N)
• 代價：
  • 寫入放大：每次 INSERT/UPDATE/DELETE 需同步維護所有相關索引
  • 空間佔用：每個索引佔用額外磁盤空間（InnoDB 非聚簇索引葉子節點存主鍵值）
  • 優化器負擔：索引過多導致統計信息更新慢、執行計劃選擇困難
• 限制：MySQL 單表最多 64 個索引（InnoDB），單索引最多 16 列，前綴長度 ≤ 3072 字節 (utf8mb4 下 ≤ 768 字符)

二、 六大索引類型深度對比

三、 索引長度優化：前綴索引

-- 創建
ALTER TABLE `articles` ADD FULLTEXT(`title`, `content`);

-- 查詢：自然語言模式
SELECT * FROM `articles` 
WHERE MATCH(`title`, `content`) AGAINST('MySQL 性能優化' IN NATURAL LANGUAGE MODE);

-- 查詢：布爾模式（支援 + - * 運算符）
SELECT * FROM `articles` 
WHERE MATCH(`title`, `content`) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

⚠️ **中文分詞**：內置 ngram parser（按 bigram 切分），精度一般。生產建議接入 Elasticsearch 或使用 `parser ngram` 配合自定義詞典。

五、 索引選型決策樹

1. 是否為行唯一標識？→ **主鍵索引** (單列自增 BIGINT)
2. 業務上是否絕對唯一（手機、郵箱、身份證）？→ **唯一索引**
3. 是否為外鍵引用列？→ **外鍵索引**（生產建議應用層維護完整性，不建立 FK）
4. 查詢條件是否為多列固定組合？→ **復合索引**（列順序：高選擇性優先、範圍列靠後）
5. 單列高頻查詢/排序/分組？→ **普通索引**
6. 需自然語言全文搜索？→ **全文索引**（評估 ES 替代）

六、 EXPLAIN 實戰驗證索引效果