MySQL 索引設計六大核心原則與最佳實踐

MySQL 索引設計黃金法則：從入門到精通

索引是查詢效能的關鍵，但「索引越多越好」是危險誤區。掌握六大設計原則，打造高效能資料庫。

原則 1：索引列 ≠ 選擇列 (WHERE/JOIN 優於 SELECT)

最適合建索引的欄位出現在 WHERE 條件、JOIN ON 關聯條件、ORDER BY/GROUP BY 子句中，而非 SELECT 選取清單。查詢優化器根據條件列選擇索引，選取列只影響回表成本。

原則 2：高基數列優先 (唯一值越多效果越好)

基數 = 不重複值數量 / 總行數。身分證號、手機號、UUID 基數接近 1，索引效果極佳；性別、狀態、布林旗標 基數極低 (約 0.5/0.01)，建索引幾無幫助，甚至拖慢寫入。

原則 3：使用短索引 (前綴索引節省空間)

長字串欄位 (如 VARCHAR(200)) 建立前綴索引：INDEX idx_name (name(10))。若前 10-20 字元區分度已高，無需索引全欄位。優勢：

• 索引體積大幅縮小，減少磁碟 I/O
• 更多索引鍵值放入 Buffer Pool，提高命中率
• 比較運算更快 (短鍵值)

注意：過短前綴 (如 1 字元) 區分度太低，失去索引意義。

原則 4：善用最左前綴 (複合索引 = 多個索引)

建立 INDEX idx_a_b_c (a, b, c) 實際可用於：

• WHERE a = ?
• WHERE a = ? AND b = ?
• WHERE a = ? AND b = ? AND c = ?
• ORDER BY a, b, c

但 WHERE b = ? 或 WHERE a = ? AND c = ? (跳過 b) **無法用到索引**。設計複合索引時，將區分度最高、最常用於等值查詢的列放最左。

原則 5：拒絕過度索引 (寫入效能殺手)

每個索引皆須維護：

• 佔用額外磁碟空間 (InnoDB 為 B+樹節點)
• INSERT/UPDATE/DELETE 需同步更新所有索引，寫入延遲隨索引數線性增長
• 優化器生成執行計劃時需評估更多索引，解析時間變長
• 統計資訊不準時可能誤選次優索引

原則：只保留必要索引。新增索引前問：是否為現有複合索引的最左列？是 → 不需新增。否 → 評估查詢頻率與寫入代價。

原則 6：匹配索引支援的比較運算子

索引支援：=, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%'。
不支援：LIKE '%suffix'、LIKE '%mid%'、STRCMP()、函數運算 WHERE YEAR(date) = 2024 (需用函數索引或生成列)。

實戰檢查清單