MySQL 索引深度解析：類型、限制、復合索引、全文索引與 EXPLAIN 優化

索引本質

特殊文件（InnoDB 為表空間一部分），包含所有行的引用指針。B+ 樹結構存儲。

優缺點

• ✅ 加速查詢
• ❌ 寫入變慢（每修改必須刷新索引）
• ❌ 佔磁盤空間
• ❌ MySQL 單表索引上限 16 個

大量導入可用 DELAY_KEY_WRITE 延遲刷新，導入完畢再統一刷新。

InnoDB 索引特殊性

行級鎖基於索引實現。無合適索引時退化為表鎖，影響 SELECT ... LOCK IN SHARE MODE、FOR UPDATE、INSERT/UPDATE/DELETE 併發度。

索引無效的六大場景

1. WHERE col != ... 不等號
2. WHERE FUNC(col) = ... 函數運算
3. JOIN 時主外鍵類型不一致
4. LIKE '%abc' 以通配符開頭
5. ORDER BY 條件非查詢條件表達式（多表時索引對排序無效）
6. 列基數極低（如性別 0/1）

索引分類

復合索引最左前綴實例

INDEX(firstname, lastname, age)
-- 可用：firstname | firstname,lastname | firstname,lastname,age
-- 不可用：lastname | age | lastname,age

索引長度建議

• 字串列：前 10~15 字符通常足夠區分
• BLOB/TEXT 必須指定長度，上限 255 字符
• 短鍵值優於長，整數優於字串
• 有序鍵值（自增）優於隨機（UUID）

EXPLAIN 優化檢查點

1. type：避免 ALL/index
2. possible_keys vs key：實際用到索引
3. key_len：越短越好
4. rows：預估掃描行數
5. Extra：紅燈 Using temporary/Using filesort，綠燈 Using index

維護命令

OPTIMIZE TABLE t;   -- 整理碎片、排序索引
ANALYZE TABLE t;    -- 更新統計信息