MySQL 索引實戰全攻略：類型、建立語法、最左前綴、優化原則、避坑指南

索引是關聯式資料庫效能調校的核心槓桿。本文以 200 萬筆實測數據為背景，系統闡述 MySQL 索引類型、建立語法、組合索引最左前綴原則、優化檢查清單與常見反模式。

實測場景

• 表 article：200 萬列、定長結構 (id PK、title VARCHAR、content TEXT、time INT)。
• 查詢：SELECT id, FROM_UNIXTIME(time) FROM article WHERE title = 'PHP筆試題...'
• 無索引：50-100 秒 (全表掃描)；加上 INDEX idx_title (title) 後：< 0.01 秒。

索引類型與建立語法

最左前綴原則 (關鍵)

組合索引 (a, b, c) 實際可服務的查詢模式：

• ✅ WHERE a = ?
• ✅ WHERE a = ? AND b = ?
• ✅ WHERE a = ? AND b = ? AND c = ?
• ❌ WHERE b = ? (非最左)
• ❌ WHERE c = ?
• ❌ WHERE a = ? AND c = ? (中間斷裂)

本質：B+ 樹索引按 (a, b, c) 排序，查詢必須從最左欄位開始連續匹配才能利用索引定位。

優化檢查清單 (10 大原則)

1. 選擇性高的欄位建索引：基數 (Cardinality) 越大效果越好，性別/狀態碼等低基數欄位不宜建索引。
2. 短索引：VARCHAR(255) 前 10-20 字元唯一性高時，用 col(10) 節省空間與 I/O。
3. 避免 NULL 值：含 NULL 欄位不會被索引 (InnoDB 除外)，複合索引任一欄為 NULL 則該列不入索引。
4. 索引列不運算：WHERE YEAR(dt) = 2023 → 失效；改 WHERE dt >= '2023-01-01' AND dt < '2024-01-01'。
5. LIKE 只用後置萬用字元：LIKE 'abc%' 可用索引；LIKE '%abc%' 全表掃描。
6. 單表索引數適度：理論上限 16 個，過多拖慢 INSERT/UPDATE/DELETE (需同步維護索引)。
7. 排序欄位建索引：ORDER BY col 若無索引會觸發 Filesort。
8. 覆蓋索引：SELECT a, b FROM tbl WHERE c = ? → 建 INDEX idx_c_ab (c, a, b)，查詢只掃索引不回表。
9. 外鍵欄位必建索引：InnoDB 要求 FK 欄位有索引，否則自動建立。
10. 定期 ANALYZE TABLE：更新統計資訊，讓優化器做正確決策。

聚集 vs 非聚集索引選擇指引

常見反模式

• 「為了保險」在每個欄位建單列索引 → 寫入效能崩潰、優化器選錯索引。
• 對 TEXT/BLOB 全長建索引 → 體積龐大、維護成本高，改用前綴或全文索引。
• WHERE col + 1 = 100 等運算導致索引失效。
• 忽略 EXPLAIN 驗證，上線後才發現全表掃描。