MySQL 索引分析與優化完整指南：類型、多欄索引、最左前綴、EXPLAIN 解讀

索引是關係資料庫效能的核心。本文系統說明 MySQL 索引類型、單欄 vs 多欄索引、最左前綴原則、EXPLAIN 輸出欄位解讀、索引缺點與實務建議。

1. 什麼是索引？

• 所有索引以 B+ 樹 儲存
• 無索引：全表掃描 O(N)；有索引：樹搜尋 O(log N)
• 1000 筆資料，索引查詢比全表掃描快 ~100 倍

2. 索引類型

3. 單欄 vs 多欄 (複合) 索引

建立複合索引：

ALTER TABLE people ADD INDEX fname_lname_age (firstname, lastname, age);

• B+ 樹依欄位順序排序：先 firstname，再 lastname，最後 age
• 查詢 WHERE firstname='A' AND lastname='B' AND age=30 可直接定位，零掃描

三個單欄索引 ≠ 一個複合索引：MySQL 單次查詢只能使用一個索引，會挑選最具選擇性的單欄索引，遠不如複合索引精確。

4. 最左前綴原則

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

• 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（未包含最左欄 a）。

相當於隱含建立了 (a,b,c)、(a,b)、(a) 三個索引。

5. EXPLAIN 關鍵欄位解讀

6. 索引的缺點

• 佔用磁碟空間：過多索引導致檔案體積超過 OS 限制
• 寫入效能下降：INSERT/UPDATE/DELETE 需同步維護索引樹
• 經驗法則：單表索引 ≤ 5 個，高寫入表更少

7. 實務優化清單

1. 針對 WHERE、JOIN、ORDER BY、GROUP BY 欄位建立索引
2. 複合索引遵循「最左前綴、高選擇性在前」
3. 利用 覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表
4. 定期 ANALYZE TABLE 更新統計資訊
5. EXPLAIN 每條核心 SQL，消除 ALL、Using temporary、Using filesort

結語

索引設計是「空間換時間」的藝術。掌握 B+ 樹結構、最左前綴、EXPLAIN 解讀、覆蓋索引四大核心概念，並建立定期審視慢查詢 → 分析執行計畫 → 調整索引 → 驗證效果的閉環流程，是 DBA 與後端工程師的核心競爭力。