MySQL 索引設計原理：單列 vs 多列索引、最左前綴、索引合併深度解析

核心誤區：一條 SQL 只能用一個索引？

MySQL 5.0+ 引入 Index Merge（索引合併），單查詢可使用多個單列索引取交集/並集。

一、多列索引優勢與最左前綴

1.1 單列索引局限

三個單列索引 idx_firstname、idx_lastname、idx_age，優化器只會選擇過濾性最強的一個，遠弱於一個複合索引 idx_fname_lname_age。

1.2 最左前綴原則

複合索引 (firstname, lastname, age) 可服務以下查詢：

• WHERE firstname = ? AND lastname = ? AND age = ?
• WHERE firstname = ? AND lastname = ?
• WHERE firstname = ?

等價於隱式創建了三個索引：(fn,ln,age)、(fn,ln)、(fn)。

二、索引合併機制

2.1 定義

將多個索引的範圍掃描結果合併（Union/Intersection/Union-of-Intersections），僅限單表。

2.2 三種合併模式

2.3 收益

減少回表次數，提升查詢效率；避免為每種組合創建複合索引導致寫入放大。

三、設計建議

1. 高頻 AND 查詢 → 複合索引（遵循最左前綴）
2. 高頻 OR 查詢、列組合不可預測 → 單列索引 + 依賴 Index Merge
3. 避免過度索引：單表索引建議 ≤ 5 個，關注 Handler_read_key 等狀態變量評估命中率
4. 使用 EXPLAIN 觀察 key、Extra 確認是否觸發 Index Merge

四、參考資源

• MySQL 官方文檔：Index Merge Optimization
• CSDN 論壇：多條件查詢建立索引策略討論
• SOHU DBA 微頭條：Index Merge 實戰案例