MySQL 索引優化核心常識：從創建策略到避坑指南

1. 何時創建索引

查詢為主的應用，索引至關重要。缺少索引導致全表掃描，大表下性能災難性下降。但非所有場景都需索引：如性別只有兩值，建索引不僅無優勢，還拖慢寫入（過度索引）。

2. 組合索引與最左前綴原則

單列索引查詢只能用一個；組合索引 (area, age, salary) 等價於 (area,age,salary)、(area,age)、(area) 三個索引。創建時將最常用篩選列放最左，依次遞減。

3. NULL 值與索引失效

含 NULL 的列不會被納入索引；組合索引中任一列含 NULL，該列對組合索引無效。設計時避免字段默認值為 NULL。

4. 短索引優勢

CHAR(255) 列若前 10-20 字符區分度高，僅索引前綴長度。短索引提升查詢速度，同時節省磁盤空間與 I/O。

5. 排序與索引的互斥

MySQL 單查詢只用一個索引。WHERE 已用索引時，ORDER BY 列不再使用索引。能用默認排序滿足時勿加排序；多列排序需建組合索引。

6. LIKE 查詢的索引邊界

• LIKE 'aaa%' ✅ 可用索引
• LIKE '%aaa%' ❌ 全表掃描

7. 列運算導致索引失效

-- ❌ 悲劇：每行計算 YEAR()，索引失效
SELECT * FROM users WHERE YEAR(adddate) < 2007;

-- ✅ 正確：直接比較日期常量，走索引
SELECT * FROM users WHERE adddate < '2007-01-01';

8. 避開 NOT IN 與 <>

兩者均導致全表掃描。替代方案：NOT IN → NOT EXISTS；id <> 3 → id > 3 OR id < 3。

總結：索引優化檢查清單

1. WHERE/JOIN 條件列是否有索引
2. 組合索引列順序是否符合最左前綴
3. 是否存在隱式類型轉換、函數運算、% 開頭 LIKE
4. NULL 值處理、短索引應用
5. 定期 EXPLAIN 檢查執行計劃