MySQL 索引深度解析：原理、類型與效能優化實戰

索引是關係型資料庫效能調校的核心關鍵。本文系統梳理 MySQL 索引的底層結構、主要類型、單列與多列索引的抉擇邏輯、最左前綴原理、索引選擇策略與 EXPLAIN 實戰分析，助您建立高效的索引體系。

一、索引本質：B+ 樹結構與查找加速

MySQL 所有索引均以 B+ 樹 形式儲存。無索引時，查詢需掃描全表（全表掃描），資料量增長時效能呈線性下降；有索引時，MySQL 可直接定位目標行，百萬級資料查找速度可提升百倍以上。

運作機制： 索引葉節點儲存索引鍵值與指向資料檔案實際行的「指標」。查詢時先在索引樹中定位鍵值，再透過指標直接讀取資料行，避免掃描大量無關資料。

二、四大核心索引類型

三、單列 vs 多列索引：效能差異實測

以 people(firstname, lastname, age) 為例，查詢 WHERE firstname='Mike' AND lastname='Sullivan' AND age=17：

• 單列索引（firstname）： 先縮小至 firstname='Mike' 的中間結果集，再逐行過濾 lastname 與 age，仍需掃描大量行。
• 多列索引（firstname, lastname, age）： B+ 樹依序比對三欄，一次定位目標行，零掃描資料檔案。

關鍵結論： MySQL 單次查詢僅能使用一個索引，多列索引的聯集過濾能力遠超單列索引。

四、最左前綴原理

多列索引 (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

設計啟示： 多列索引欄位順序應依「區分度高→低」與「查詢頻率高→低」排列。

五、索引欄位選擇黃金法則

1. WHERE 條件欄位：WHERE col = ...、col > ...、col BETWEEN ...、col IN (...)、col LIKE 'prefix%'（非通配符開頭）。
2. JOIN 關聯欄位：ON t1.col = t2.col。
3. 避免索引：僅出現在 SELECT 清單、ORDER BY（非聚簇索引）、LIKE '%suffix'、函數運算（ABS(col)）、NOT IN、OR 等非 SARGable 條件的欄位。

六、EXPLAIN 實戰讀懂執行計畫

關鍵欄位解讀：

• type：連接類型，ref 為非唯一索引查找，ALL 為全表掃描（需優化）。
• possible_keys / key：可能用到的索引與實際使用的索引。
• key_len：實際使用的索引長度（位元組），可判斷多列索引用到第幾欄。
• rows：預估掃描行數，越接近 1 越好。
• Extra：Using where、Using index（覆蓋索引）、Using filesort（需優化）等提示。

七、索引的代價與取捨

• 空間開銷： 索引檔案佔用磁碟，過多組合索引可能超過檔案系統限制。
• 寫入效能： INSERT/UPDATE/DELETE 需同步維護索引，降低寫入吞吐。

最佳實務： 只為高頻查詢、高區分度欄位建立索引；定期用 EXPLAIN 驗證；大表考慮分區或歸檔而非盲目加索引。

結語

索引是大型資料庫效能的關鍵杠桿。理解 B+ 樹原理、掌握最左前綴、善用 EXPLAIN 分析、在「查詢加速」與「寫入開銷」間取得平衡，才能構建可擴展的高效能 MySQL 架構。