五大索引類型對比
| 類型 | 關鍵字 | 特點 | 適用場景 |
|---|
| 主鍵索引 | PRIMARY KEY | 唯一、非空、一張表只能有一個 | 業務主鍵（ID、訂單號等） |
| 唯一索引 | UNIQUE | 值唯一、允許 NULL（多個 NULL 不衝突） | 身份證號、手機號、郵箱等業務唯一字段 |
| 普通索引 | INDEX / KEY | 無限制、可重複、可為 NULL | 頻繁 WHERE、JOIN、ORDER BY 的字段 |
| 全文索引 | FULLTEXT | 僅 MyISAM/InnoDB 5.6+ 支持，針對長文本 | 文章搜索、日誌分析、產品描述檢索 |
| 空間索引 | SPATIAL | 針對幾何數據類型（POINT、POLYGON 等） | 地理位置服務、GIS 系統、範圍查詢 |
組合索引與最左前綴原則
ALTER TABLE `table` ADD INDEX idx_a_b_c (a, b, c);
- 等價於建立 (a)、(a,b)、(a,b,c) 三個索引
- 查詢必須從最左列開始匹配：
WHERE a=1 ✓ / WHERE b=1 ✗ / WHERE a=1 AND c=3 ✓（用到 a）
- 範圍查詢（>、<、>=、<=、LIKE %...）會中斷後續列索引使用
實戰選型案例：會員卡系統
| 字段 | 類型 | 建議索引 | 理由 |
|---|
| 會員編號 | INT | PRIMARY | 業務主鍵，唯一標識 |
| 會員姓名 | VARCHAR(10) | INDEX | 按姓名查詢，允許重名 |
| 身份證號 | VARCHAR(18) | UNIQUE | 國家標準唯一，業務強約束 |
| 電話 | VARCHAR(11) | UNIQUE/INDEX | 視業務是否允許綁定多帳號 |
| 地址 | VARCHAR(50) | — | 低頻查詢，不建議索引 |
| 備註信息 | TEXT | FULLTEXT | 長文本模糊搜索需求 |
索引管理語法
-- 創建
CREATE UNIQUE INDEX idx_idcard ON member(id_card);
CREATE FULLTEXT INDEX idx_remark ON member(remark);
-- 刪除
DROP INDEX idx_idcard ON member;
-- 查看
SHOW INDEX FROM member;
避坑指南
- 過度索引：寫入性能下降，佔用磁盤空間，優化器可能選錯索引
- 隱式轉換：
WHERE phone = 13800000000 導致 VARCHAR 字段索引失效
- 函數破壞：
WHERE DATE(created_at) = '2023-01-01' 無法用到 created_at 索引
- FULLTEXT 限制：短文本（<100 字）用 LIKE 或普通索引更高效
原則：只為高頻查詢條件建索引，定期用 EXPLAIN 分析執行計劃，刪除未使用索引。
