MySQL 索引類型全解析：普通、唯一、主鍵、全文、組合索引創建時機與七大使用注意事項

索引是查詢加速器，但濫用會拖垮寫入。本文系統梳理五大索引類型定義、創建語法、組合索引最左前綴原則、建立時機判斷、索引不足與七大使用注意事項，構建完整索引治理認知。

一、 五大索引類型

二、 組合索引最左前綴原則

INDEX idx_a_b_c (a, b, c)
-- 能用到索引的查詢：
WHERE a = ?                    -- ✅
WHERE a = ? AND b = ?          -- ✅
WHERE a = ? AND b = ? AND c = ? -- ✅
WHERE a = ? AND c = ?          -- ⚠️ 僅用 a，c 無法用索引
WHERE b = ?                    -- ❌ 全掃
WHERE b = ? AND c = ?          -- ❌ 全掃

本質：B+Tree 按 (a,b,c) 字典序排序，只有前綴有序才能二分查找。

三、 建立索引的時機（WHERE/JOIN/ORDER BY/GROUP BY 列）

• ✅ WHERE、JOIN ON、ORDER BY、GROUP BY 出現的高選擇性列
• ✅ 外鍵關聯列（隱式 JOIN）
• ⚠️ LIKE 'abc%' 後綴通配可用索引，LIKE '%abc%' 不可
• ⚠️ 函數/表達式包裹列 → 索引失效（參考優化 28 條軍規）
• ❌ 低選擇性列（性別、狀態碼少值）單列索引無效
• ❌ 表小（< 1 萬行）全表掃描更快

四、 索引的不足（代價）

1. 寫入放大：INSERT/UPDATE/DELETE 需同步維護所有索引 B+Tree。
2. 空間佔用：每個索引獨立 B+Tree，大表組合索引易達百 GB。
3. 優化器迷路：索引過多導致統計信息收集慢、執行計劃選擇錯誤。

五、 七大使用注意事項

1. NULL 值不進索引：單列含 NULL 不入索引，組合索引任一列 NULL 導致該行不入組合索引。設計 NOT NULL + DEFAULT。
2. 短索引/前綴索引：VARCHAR(255) 實際區分度在前 10 字符 → INDEX idx_name (name(10))，節省空間、提速。
3. 索引列排序：WHERE 用了索引，ORDER BY 列不再用索引；需排序時建組合索引 (where_col, order_col)。
4. LIKE 後綴只用：LIKE 'abc%' 走索引，LIKE '%abc' 全掃。
5. 拒絕列運算：YEAR(dt) < 2024 → dt < '2024-01-01'。
6. 拒絕 NOT IN / <>：優化器放棄索引，改 NOT EXISTS/LEFT JOIN ... IS NULL。
7. 覆蓋索引優先：SELECT a,b FROM t WHERE c=? → INDEX idx_c_ab (c,a,b)，避免回表。

六、 一頁決策卡

高選擇性 + 高頻過濾/排序/分組/關聯  → 建索引
低選擇性 / 小表 / 含NULL / 函數包裹  → 不建
組合索引遵循最左前綴，單表≤6個
覆蓋索引消除回表，短索引省空間
定期ANALYZE TABLE更新統計，防優化器迷路

索引治理 = 類型選對 + 列序排對 + 定期養護。