MySQL 索引類型全解析：分類、創建時機、使用技巧與避坑指南

索引類型分類

索引創建語法

-- 1. 建表時創建
CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  city VARCHAR(20),
  age INT,
  INDEX idx_name (name(10)),           -- 前綴索引
  UNIQUE INDEX uk_email (email),
  INDEX idx_city_age (city, age)       -- 組合索引
) ENGINE=InnoDB;

-- 2. 事後添加
ALTER TABLE t ADD INDEX idx_name (name(10));
ALTER TABLE t ADD UNIQUE INDEX uk_phone (phone);
ALTER TABLE t ADD INDEX idx_city_age (city, age);

-- 3. 刪除索引
DROP INDEX idx_name ON t;
ALTER TABLE t DROP INDEX uk_email;

組合索引核心：最左前綴原則

索引 INDEX(a, b, c) 等價於擁有：

• (a)
• (a, b)
• (a, b, c)

能用到索引的查詢：

WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3
WHERE a = 1 AND b > 2          -- b 為範圍，c 用不到
WHERE a = 1 ORDER BY b         -- 排序也用到

用不到索引的查詢：

WHERE b = 1                    -- 缺最左列 a
WHERE b = 1 AND c = 2          -- 缺最左列 a
WHERE a = 1 AND c = 3          -- 中間斷層 (b 缺失)，c 用不到

何時創建索引 (WHERE/JOIN/ORDER BY/GROUP BY)

1. WHERE 條件列： =, >, <, >=, <=, BETWEEN, IN, LIKE 'prefix%'
2. JOIN 關聯列： ON t1.id = t2.id 兩側皆需索引
3. ORDER BY / GROUP BY 列： 避免 filesort，但**單查詢只能用一個索引**，WHERE 用了索引則 ORDER BY 通常用不到
4. 覆蓋索引： SELECT a,b FROM t WHERE c=1 建 INDEX(c,a,b)，只掃描索引不回表

索引失效常見場景 (必背)

索引設計黃金法則

1. 單表索引數 ≤ 5 (寫入性能代價)
2. 組合索引列數 ≤ 3 (過長維護成本高、選擇度遞減)
3. 區分度高的列放前面： INDEX(高區分度, 低區分度)
4. 盡量延長索引長度： VARCHAR(200) 實際存 20 字符 → INDEX(name(20)) 節省空間、提速
5. 主鍵用自增整數： 避免 UUID/字符串主鍵導致聚簇索引分裂、二級索引膨脹
6. 覆蓋索引優於回表： EXPLAIN 出 Using index 為佳
7. 定期 ANALYZE TABLE 更新統計信息 (或 innodb_stats_persistent=ON 自動持久化)

索引維護成本

• 寫入開銷： 每次 INSERT/UPDATE/DELETE 需同步更新所有相關索引
• 空間佔用： 每個索引佔獨立 B+Tree 空間，組合索引尤甚
• 統計信息失效： 大量數據變更後統計信息偏差，導致優化器誤判

驗證索引效果

EXPLAIN SELECT * FROM t WHERE name = 'abc';
-- 關鍵欄位：
-- type: ref/range/eq_ref (好) vs ALL (全表掃描，壞)
-- key: 實際用到的索引名
-- key_len: 索引長度 (判斷組合索引用了幾列)
-- rows: 預估掃描行數 (越小越好)
-- Extra: Using index (覆蓋索引), Using where, Using filesort (需優化)