MySQL 索引優化深度指南：原理、類型、最左前綴與 EXPLAIN 實戰

MySQL 索引優化深度指南：原理、類型、最左前綴與 EXPLAIN 實戰

索引是資料庫效能調優的「核武器」。本文從 B+ 樹存儲原理出發，系統講解索引分類、單列與複合索引抉擇、最左前綴原理、列選擇策略、EXPLAIN 執行計劃分析及索引維護代價，並附贈實戰案例與避坑清單，助你從「會用索引」進階到「懂索引」。

一、索引本質：B+ 樹與指針機制

MySQL 所有索引預設採用 B+ 樹結構。葉子節點儲存「索引鍵值 + 行指針」，非葉子節點僅儲存鍵值導航。查找流程：

1. 從根節點二分查找鍵值範圍
2. 層層下沉至葉子節點
3. 根據行指針回表獲取完整行（聚簇索引除外）

數量級對比：1000 行表，索引查找僅需 1 次 I/O，全表掃描需 1000 次，效能差距百倍以上。

二、索引類型全景圖

三、單列 vs 複合索引：最左前綴法則

以 people(firstname, lastname, age) 為例：

• 建立複合索引 INDEX fname_lname_age(firstname, lastname, age)
• 等價於同時擁有：(firstname,lastname,age)、(firstname,lastname)、(firstname) 三個索引
• 生效條件：WHERE 條件必須包含最左列 firstname

失效案例：WHERE lastname='Sullivan'、WHERE age=17、WHERE lastname='Sullivan' AND age=17 均無法使用該複合索引。

四、索引列選擇黃金法則

1. 出現在 WHERE、JOIN、ORDER BY、GROUP BY 的高選擇性列
2. 選擇性 = COUNT(DISTINCT col) / COUNT(*)，越接近 1 越適合建索引
3. 避開 SELECT 列表、UPDATE 目標列（除非覆蓋索引）
4. 運算符限制：=, >, <, >=, <=, BETWEEN, IN, LIKE 'prefix%' 可用索引；LIKE '%suffix'、函數運算、隱式轉換導致索引失效

五、EXPLAIN 執行計劃深度解讀

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

關鍵欄位含義：

六、索引的代價與維護藝術

• 空間佔用：索引檔案體積可超過數據檔，大表需評估磁碟容量
• 寫入開銷：INSERT/UPDATE/DELETE 需同步維護 B+ 樹，索引越多寫入越慢
• 統計資訊：定期 ANALYZE TABLE 更新基數統計，確保優化器決策準確
• 碎片整理：OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建索引

七、實戰避坑清單

1. ❌ 為每個列建單列索引 → ✅ 依查詢模式建複合索引
2. ❌ 索引包含長字串全長 → ✅ 使用前綴索引 INDEX idx_name(name(10))
3. ❌ WHERE 條件用函數 WHERE YEAR(create_time)=2023 → ✅ 改為範圍查詢
4. ❌ 隱式類型轉換 WHERE phone=13800000000 → ✅ 統一字串比對
5. ❌ 過早優化 → ✅ 以慢查詢日誌 + EXPLAIN 為導向精準索引

索引設計是「業務理解 + 原理掌握 + 持續迭代」的動態平衡，而非一次性配置。建立「監控 → 分析 → 調整 → 驗證」閉環，方能駕馭龐大數據集下的查詢效能。