MySQL 索引設計必知的 8 件事：避開過度索引、NULL 陷阱與 LIKE 失效

前言

索引設計好壞直接決定資料庫吞吐量上限。本文凝練 8 大關鍵原則，涵蓋建立時機、組合索引最左前綴、NULL 處理、短索引、排序、LIKE、運算、負向條件，助您建立高效、可維護的索引策略。

1. 該建時要建，不該建別建

• 查詢主導型應用：索引是命脈，缺索引 = 全表掃描 = 災難。
• 低選擇性欄位 (如性別、布林狀態) 建索引 = 過度索引，寫入效能受損且優化器可能拒用。

2. 組合索引 = 多個索引合一 (最佳左前綴特性)

建立 INDEX (a, b, c) 等同擁有 (a)、(a,b)、(a,b,c) 三個索引。最常用的過濾欄位放最左，依選擇性遞減排序。

3. 索引不包含 NULL 值

• 單欄含 NULL → 該行不入索引。
• 組合索引任一欄 NULL → 整組對該行失效。
• 設計時設 NOT NULL DEFAULT '' 或具體預設值。

4. 善用短索引

長字串 (如 VARCHAR(255)) 若前 10~20 字元區分度已高，用 INDEX idx_col (col(10))。效益：索引體積 ↓、I/O ↓、快取命中率 ↑、查詢速度 ↑。

5. 排序與索引的互斥關係

MySQL 單查詢只能用一個索引。若 WHERE 已用索引，ORDER BY 欄位若不在同一索引 → Using filesort。解法：建立涵蓋 WHERE + ORDER BY 的組合索引，或接受預設排序避免額外排序。

6. LIKE 只有前綴匹配才用索引

• LIKE 'abc%' ✅ 索引範圍掃描
• LIKE '%abc%' / LIKE '%abc' ❌ 全表掃描
• 需前導模糊搜尋 → 全文索引或 Elasticsearch。

7. 索引欄位嚴禁運算/函數

-- ❌ 失效
WHERE YEAR(adddate) < 2007;

-- ✅ 生效
WHERE adddate < '2007-01-01';

任何 func(col)、col + 1、UPPER(col) 都會阻礙索引使用。

8. 拒絕 NOT IN / <> / !=

• 負向條件無法走索引，必走全表掃描。
• 改寫：NOT IN → NOT EXISTS / LEFT JOIN ... IS NULL；id <> 3 → id > 3 OR id < 3 (視資料分布)。

延伸閱讀

• 如何查看 MySQL 索引 (SHOW INDEX 完整教學)
• MySQL 索引類型大彙總
• MySQL 索引經驗淺見
• MySQL 索引不足與注意事項
• MySQL 觸發器三種狀態