MySQL 索引使用場景與真正利用索引的 SQL 模式深度解析

MySQL 索引使用場景與真正利用索引的 SQL 模式深度解析

為什麼需要索引？

無索引時全表掃描，耗時與資料量線性相關。B+Tree 索引 (InnoDB 預設) 透過平衡樹結構，將查找複雜度降至 O(log n)，極大提升查詢速度。但索引會增加寫入開銷，非越多越好。

MySQL 會自動使用索引的 6 大場景

1. WHERE 條件快速定位：WHERE col = ?、WHERE col > ? 等。
2. 候選集縮小：多索引可用時，優先選擇過濾性最高 (候選集最小) 的索引。
3. 聯合索引最左前綴：INDEX (c1,c2,c3) 可支援 (c1)、(c1,c2)、(c1,c2,c3)；(c2,c3) 無效，(c1,c3) 僅用到 c1。
4. JOIN 關聯欄位有索引：驅動表與被驅動表關聯欄位均有索引時啟用優化。
5. 索引欄位 MIN/MAX：SELECT MIN(col) FROM tbl 直接走索引最左/最右葉節點。
6. 索引欄位 ORDER BY / GROUP BY：利用索引有序特性避免 filesort。

真正能用到 B-Tree 索引的 SQL 模式 (官方文檔依據)

1. 比較運算子：=, >, >=, <, <=, BETWEEN。
2. LIKE 固定前綴：LIKE 'Patrick%'、LIKE 'Pat%_ck%' (非萬用字元開頭) → 用到索引。LIKE '%Patrick%' 或 LIKE other_col → 全掃。
3. IS NULL：col IS NULL 可用索引。
4. 聯合索引最左前綴 (同上)。
5. Index Merge 優化：多條件 WHERE 時，MySQL 可合併多個索引掃描結果 (union/intersect) 縮小候選集。

額外補充

• LIKE '%string%' 且 string 長度 > 3 時，MySQL 採用 Turbo Boyer-Moore 演算法加速全掃，但仍非索引查找。
• 定期 ANALYZE TABLE 更新索引統計資訊，幫助優化器做正確決策。