MySQL 索引深度解析：B+樹原理、單列/複合/全文索引選擇、EXPLAIN 執行計劃優化實戰

索引底層：B+ 樹為什麼快

• **有序性**：葉子節點鏈表掃描範圍查詢極快
• **高扇出**：非葉節點只存鍵值+指針，單節點容納更多鍵，樹高通常 2-3 層
• **覆蓋索引**：葉子節點直接存數據 (聚簇索引) 或主鍵值 (二級索引)，避免回表
• **對比**：無索引全表掃描 O(N)；B+ 樹查找 O(log N)，100 萬行約 20 次 I/O vs 100 萬次

索引類型全景

單列 vs 複合索引：最左前綴原理

場景：查詢 WHERE firstname='Mike' AND lastname='Sullivan' AND age=17

• 三個單列索引：優化器只能選一個 (如 firstname)，掃描所有 Mike 再回表過濾 lastname/age，**效率低**
• 複合索引 (firstname, lastname, age)：B+ 樹按三列排序，**一次定位精確匹配行，零回表 (覆蓋索引)**，**效率最高**

最左前綴匹配規則

複合索引 (a, b, c) 可滿足：

• WHERE a=1 AND b=2 AND c=3 ✅
• WHERE a=1 AND b=2 ✅
• WHERE a=1 ✅
• WHERE b=2 ❌ (未用最左列 a)
• WHERE a=1 AND c=3 ⚠️ (用 a，c 無法用索引查找，僅過濾)
• ORDER BY a, b, c ✅ (避免 filesort)

索引選擇實戰：WHERE/JOIN/ORDER BY 三維分析

SELECT p.age, t.name
FROM people p
LEFT JOIN town t ON p.townid = t.townid
WHERE p.firstname='Mike' AND p.lastname='Sullivan';

**必建索引**：

1. people(firstname, lastname) — WHERE 等值條件
2. town(townid) — JOIN 關聯列 (被驅動表)
3. people(townid) — 可選，若 town 很大且作驅動表

**無需索引**：SELECT 列 (age, name) — 僅輸出，不參與過濾/連接/排序。

操作符與索引兼容性

EXPLAIN 實戰解讀

EXPLAIN SELECT peopleid FROM people 
WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;