MySQL 索引分析與優化完整方案：B+樹原理、索引類型、複合索引最左前綴、EXPLAIN 實戰解讀與缺點權衡

索引是關係型資料庫效能核心。本文從 B+樹儲存原理出發，系統講解 4 大索引類型、單列 vs 複合索引、最左前綴原理、EXPLAIN 執行計畫逐欄位解讀，最後客觀分析索引寫入開銷與空間代價。

一、索引本質：B+樹結構

• 所有索引以 B+樹儲存，葉節點含資料指標
• 無索引：全表掃描 O(N)；有索引：樹高 O(log N) 定位，1000 行表快 100 倍
• 叢集索引 (InnoDB 主鍵) 葉節點直接存完整行；非叢集索引葉節點存主鍵值，需回表

二、四大索引類型

三、單列 vs 複合索引

表 people(fn, ln, age)，複合索引 idx_fn_ln_age (fn, ln, age)

• 查詢 WHERE fn='John' AND ln='Doe' AND age=30：單次樹搜尋直達
• 三單列索引：優化器僅選一個 (最嚴格者)，再回表篩選，效率遠低於複合

四、最左前綴原理

複合索引 (a,b,c) 等效於 (a,b,c)、(a,b)、(a) 三索引。可用場景：

• WHERE a=1 AND b=2 AND c=3
• WHERE a=1 AND b=2
• WHERE a=1

不可用：WHERE b=2、WHERE c=3、WHERE b=2 AND c=3 (斷層)

五、EXPLAIN 實戰解讀

EXPLAIN SELECT * FROM people WHERE fn='John' AND ln='Doe' AND age=30;

紅旗：type=ALL (全掃)、key=NULL、rows 巨大、Using temporary/Using filesort

六、索引缺點與權衡

七、優化檢查清單

1. 慢查詢日誌 + EXPLAIN 定位全掃描
2. 補建複合索引滿足最左前綴
3. 覆蓋索引消除回表 (Using index)
4. 短索引縮小體積
5. 避免函數/運算/隱式轉換破壞索引
6. 定期 ANALYZE/OPTIMIZE

大型系統中，索引設計優於硬體升級。投資時間分析 EXPLAIN，回報率極高。