MySQL 索引優化精髓：原理、類型、最左前綴、選擇準則與避坑指南

一、索引為什麼快？

B+ 樹結構，將無序全表掃描（O(N)）轉為有序樹搜尋（O(log N)）。1000 行表建索引後範圍查詢快 100 倍；若需訪問幾乎全表，順序掃描反而更快（避免隨機 I/O）。

二、單表與多表查詢的索引效應

單表

WHERE 條件列建索引 → 直接定位 → 避開全表掃描。

多表聯結

三表各 1000 行，無索引聯結需掃 10 億組合；建索引後外層掃 1000 行，內層索引定位 O(log N)，理論提速百萬倍。

三、複合索引與最左前綴原則

建立 INDEX(col1, col2, col3) 等同擁有：

• (col1)
• (col1, col2)
• (col1, col2, col3)

失效案例：WHERE col2=...、WHERE col2=... AND col3=...、WHERE col1=... OR col2=... 均無法使用複合索引。

四、LIKE 與索引的微妙關係

• LIKE 'Patrick%' ✅ 索引生效（範圍：'Patrick' <= col < 'Patricl'）
• LIKE 'Pat%_ck%' ✅ 索引生效（前綴 'Pat' 定界）
• LIKE '%Patrick%' ❌ 通配符開頭
• LIKE other_col ❌ 非常量

五、索引的代價（必須清醒認知）

• 磁盤空間：索引文件可能超過數據文件
• 寫入放大：每次 INSERT/UPDATE/DELETE 需同步修改所有相關索引
• 優化器負擔：索引越多，執行計劃枚舉越慢，甚至誤選

六、六大選擇索引黃金準則

1. 看 WHERE/JOIN 不看 SELECT：出現在過濾條件、聯結條件的列優先
2. 高選擇性優先：唯一值密度大（如身份證號）效果佳；低選擇性（如性別）幾乎無效
3. 短索引：CHAR(200) 只索引前 10-20 字符，節省空間、加快比較、提高緩存命中
4. 最左前綴：複合索引順序 = 查詢條件順序；INDEX(state, city, zip) 支援 state / state+city / state+city+zip
5. 不重複建設：已有複合索引覆蓋的單列索引勿再建
6. 匹配操作符：只服務 =, >, >=, <, <=, BETWEEN, LIKE 'prefix%'；STRCMP() 等函數無效

七、索引的額外能力

• MAX()/MIN() 直接走索引極值
• ORDER BY/GROUP BY 命中最左前綴可避免 filesort
• 覆蓋索引：查詢列全在索引中 → 僅讀索引樹，零回表

八、總結

索引是查詢優化的「核武器」：優先用好、精準建設、定期審計。大表無索引 = 自殺；濫用索引 = 慢性自殺。平衡之道在於「以查詢負載為導向，以寫入代價為約束」。