MySQL 索引優化核心原則：建立策略、複合索引最左前綴、避坑指南與實戰技巧

索引設計優劣直接決定查詢效能。本文凝練 8 大核心原則，涵蓋建立時機、複合索引最左前綴、NULL 值陷阱、短索引、排序優化、LIKE 模式、運算子失效與否定條件改寫。

1. 該建索引 vs 過度索引

• 必建：查詢主力表、大表篩選欄位、JOIN 關聯鍵、WHERE/ORDER BY/GROUP BY 高頻欄位
• 勿建：基數極低欄位（如性別、狀態碼）、頻繁更新欄位、極小表（全表掃描更快）
• 過度索引：增加寫入開銷（每次 INSERT/UPDATE/DELETE 需維護所有索引）、佔用磁碟空間

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

查詢：WHERE area='beijing' AND age=22

• 單列索引：MySQL 單查詢僅用一索引，效率有限
• 複合索引 (area, age, salary) 等效於 (area,age,salary)、(area,age)、(area) 三索引
• 最左前綴：查詢條件必須從索引最左列開始連續匹配，中間不可斷裂
• 建索引順序：最高選擇性（基數最大）列放最左，依序遞減

3. 索引不包含 NULL 值

• 單列含 NULL → 不進索引
• 複合索引任一列 NULL → 整行不進該複合索引
• 設計預設值 NOT NULL DEFAULT '' 或 0，避免 NULL 污染索引

4. 短索引：前綴長度足夠即可

ALTER TABLE tbl ADD INDEX idx_name (name(10));  -- 僅索引前 10 字元

• 適用：CHAR/VARCHAR/TEXT/BLOB 長欄位
• 效益：縮小索引體積、減少 I/O、提升快取命中率
• 判斷依據：前 N 字元區分度接近全欄位（SELECT COUNT(DISTINCT LEFT(col,10))/COUNT(*)）

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

• MySQL 單查詢僅用一索引：WHERE 用了索引 → ORDER BY 無法再用索引
• 避免多欄排序，必要時建複合索引 (where_col, order_col)
• 預設排序滿足需求時，勿顯式 ORDER BY

6. LIKE 模式決定索引生死

全文搜尋場景請用 Fulltext 索引或外部搜尋引擎。

7. 索引列上不可做運算

-- ❌ 失效：每行計算 YEAR()
WHERE YEAR(adddate) < 2007

-- ✅ 生效：常數範圍
WHERE adddate < '2007-01-01'

函數、運算、型別轉換皆會導致索引失效。

8. 否定條件拒絕索引

• NOT IN、<>、!= → 全表掃描
• 改寫：NOT IN → NOT EXISTS；id <> 3 → id > 3 OR id < 3

索引優化檢查清單

1. EXPLAIN 確認 type=ref/range、key 非 NULL、rows 最小化
2. 覆蓋索引：Using index 避免回表
3. 定期 ANALYZE TABLE 更新統計資訊
4. 監控 Handler_read_key / Handler_read_rnd_next 比率

索引是資料庫效能的杠桿，精準設計勝過盲目堆砌。