MySQL 索引類型全解析：六大類型特點、優缺點與適用場景

索引基礎認知

索引是特殊檔案（InnoDB 中屬表空間一部分），包含資料表所有記錄的引用指標。索引非萬能：加速查詢但降低寫入效能；佔用磁碟空間；MySQL 單表最多 16 個索引。

InnoDB 索引的特殊地位

InnoDB 中索引不僅用於查詢，更是行級鎖定的基礎。鎖定發生在索引上而非資料表本身，故無適當索引則無法行級鎖定，影響 SELECT ... LOCK IN SHARE MODE、SELECT ... FOR UPDATE、INSERT/UPDATE/DELETE 等操作。

索引失效常見場景

• WHERE 條件使用 != 或 <>
• WHERE 條件對索引列使用函數（如 DAY(column) = ...）
• JOIN 時主鍵/外鍵資料型別不一致
• LIKE '%abc' 以萬用字元開頭
• ORDER BY 條件非查詢條件表達式
• 欄位重複值過多（如性別、布林值）

六大索引類型詳細對比

1. 普通索引

• 關鍵字：KEY 或 INDEX
• 唯一任務：加速查詢
• 適用：常用於 WHERE、ORDER BY 的欄位；優先選擇整數等緊湊型別

2. 唯一索引

• 關鍵字：UNIQUE
• 值不可重複，簡化管理、提高效率
• INSERT 時自動檢查重複，違反則拒絕
• 常用於業務唯一性約束（如身分證、Email）

3. 主索引

• 關鍵字：PRIMARY KEY
• 與唯一索引差異僅在關鍵字
• 必須建立，值唯一且非空

4. 外鍵索引

• 定義外鍵約束時自動建立內部索引
• 輔助高效管理參照完整性

5. 複合索引（多列索引）

• 語法：INDEX(colA, colB, colC)
• 遵循最左前綴原則：可作為 colA、(colA, colB) 索引，但不可單獨作為 colB、colC 或 (colB, colC) 索引
• 列順序選擇：常用查詢優先、高選擇性優先、小欄位優先

6. 全文索引

• 適用於 CHAR、VARCHAR、TEXT 大文本欄位
• 建立單詞清單，支援 MATCH ... AGAINST 語法
• InnoDB 5.6+ 開始支援（早期僅 MyISAM）
• 建立：ALTER TABLE t ADD FULLTEXT(col1, col2)

索引長度設定

• CHAR/VARCHAR 可指定前綴長度（如 10-15 字元），縮小索引體積
• BLOB/TEXT 必須指定長度
• MySQL 最大索引長度 255 字元（舊版本限制）

查詢與索引優化工具：EXPLAIN

僅在資料量超過記憶體時測試才有參考價值。關鍵欄位：

• type：system > const > eq_ref > ref > range > index > ALL
• possible_keys / key：可用/實際使用的索引
• key_len：使用索引長度（越小越快）
• rows：預估掃描行數
• Extra：Using temporary、Using filesort 等警示