MySQL 索引建立原則與優缺點深度解析

MySQL 索引建立原則與優缺點深度解析

索引是資料庫高效存取的核心資料結構，隨資料量增長效能差距呈指數級擴大。本文系統梳理索引優缺點、適用場景、建立方法、類型選擇，助你做正確決策。

為什麼需要索引？五大核心優勢

1. 唯一性約束：主鍵/唯一索引保證資料唯一
2. 查詢加速：B+Tree 結構將 O(N) 掃描降為 O(log N) 查找
3. 連接優化：外鍵索引大幅提升 JOIN 效能
4. 排序分組消除 filesort：索引天然有序，ORDER BY/GROUP BY 零排序開銷
5. 優化器輔助：統計資訊幫助優化器選擇最佳執行計劃

索引的三大代價（為什麼不給每列建索引）

✅ 適合建立索引的欄位特徵

1. 頻繁出現在 WHERE 條件中
2. 主鍵列（強制唯一+聚簇）
3. JOIN ON 關聯列（外鍵）
4. 範圍查詢列（BETWEEN、>、<、>=、<=）
5. 頻繁 ORDER BY/GROUP BY 列
6. 高選擇性（基數大，如用戶 ID、郵箱、手機號）

❌ 不適合建立索引的欄位特徵

1. 極少查詢引用（維護成本 > 查詢收益）
2. 低選擇性：性別、狀態、布林值、少數枚舉（掃描比例 > 30% 優化器放棄索引）
3. 大物件類型：TEXT、BLOB、JSON（長度大、前綴索引效果有限）
4. 寫多讀少場景：日誌表、流水表、臨時表
5. 經常批量更新的列（索引頻繁分裂合併）

索引建立三種語法

-- 1. 建表時指定
CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  INDEX idx_name (name),
  UNIQUE INDEX uk_email (email)
);

-- 2. ALTER TABLE 新增
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD UNIQUE INDEX uk_email (email);

-- 3. CREATE INDEX
CREATE INDEX idx_name ON t (name);
CREATE UNIQUE INDEX uk_email ON t (email);

索引類型與適用場景

修改索引結構

-- 刪除主鍵並重建複合主鍵
ALTER TABLE t DROP PRIMARY KEY, ADD PRIMARY KEY (col_a, col_b);

查看索引資訊

SHOW INDEX FROM t;
-- 或
SHOW KEYS FROM t;

核心決策原則

1. 業務驅動：根據慢查詢、核心業務 SQL 設計索引，非預測性建立
2. 複合優先：多條件查詢用複合索引，單列索引無法服務組合查詢
3. 最左前綴：索引列順序 = 查詢使用頻序，高選擇性在前
4. 覆蓋索引：高頻查詢嘗試 INDEX(where_col, select_col1, select_col2) 避免回表
5. 定期審視：pt-index-usage 分析未用索引，pt-duplicate-key-checker 檢測重複索引

索引是把雙面刃，用得好是效能倍增器，用不好是寫入殺手。沒有最好的索引，只有最適合當前業務讀寫模式的索引。