MySQL 索引類型全解析：普通、唯一、主鍵、組合索引建立與選擇指南

索引是資料庫查詢效能的核心杠桿。MySQL 支援多種索引類型，各有適用場景與建立語法。本文系統梳理 4 大核心索引類型、建立三種方式、組合索引「最左前綴」原理與實戰選擇策略，助你精準建立高效索引。

四大核心索引類型

1. 普通索引 — 最基礎、無約束

僅加速查詢，允許重複值與 NULL 值。

-- 三種建立方式
-- 方式一：獨立建立
CREATE INDEX idx_username ON mytable(username(10));

-- 方式二：ALTER TABLE
ALTER TABLE mytable ADD INDEX idx_username (username(10));

-- 方式三：建表時指定
CREATE TABLE mytable (
  id INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  INDEX idx_username (username(10))
);

-- 刪除
DROP INDEX idx_username ON mytable;

長度策略：CHAR/VARCHAR 可指定前綴長度（如 username(10)），BLOB/TEXT 必須 指定長度。適當前綴可大幅縮小索引體積、提升插入速度。

2. 唯一索引 — 值必須唯一，允許 NULL

CREATE UNIQUE INDEX uk_email ON users(email(50));
ALTER TABLE users ADD UNIQUE uk_email (email(50));
CREATE TABLE users (... UNIQUE uk_email (email(50)) );

組合唯一索引時，列值組合必須唯一。NULL 值在唯一索引中視為「不相等」，可存在多個 NULL。

3. 主鍵索引 — 特殊唯一索引，不允許 NULL

-- 建表時指定（推薦）
CREATE TABLE mytable (
  id INT NOT NULL AUTO_INCREMENT,
  username VARCHAR(16) NOT NULL,
  PRIMARY KEY (id)
);

-- 事後新增
ALTER TABLE mytable ADD PRIMARY KEY (id);

鐵律：一個表僅能有一個主鍵。InnoDB 若無顯式主鍵，會隱式建立 6 字節行 ID，強烈建議顯式定義業務主鍵或自增代理鍵。

4. 組合索引 — 多欄位聯合索引，順序決定一切

-- 建立 name(10), city, age 組合索引
ALTER TABLE mytable ADD INDEX idx_name_city_age (name(10), city, age);

組合索引核心原理：最左前綴匹配

上述索引等價於隱式建立三組索引：

(name, city, age)
(name, city)
(name)

✅ 會用到索引的查詢

SELECT * FROM mytable WHERE name = 'admin' AND city = '台北';
SELECT * FROM mytable WHERE name = 'admin';

❌ 不會用到索引的查詢

SELECT * FROM mytable WHERE age = 20 AND city = '台北'; -- 缺少最左列 name
SELECT * FROM mytable WHERE city = '台北';             -- 缺少最左列 name

索引選擇決策樹

1. 業務唯一性約束 → 唯一索引 / 主鍵
2. 高頻查詢欄位 → 普通索引
3. 多欄位聯合查詢 → 組合索引（按區分度高低、查詢頻次排序欄位順序）
4. 模糊查詢 → LIKE 'prefix%' 可用索引，LIKE '%suffix' 無法用索引
5. JOIN 關聯欄位 → 必須建立索引

建立索引的最佳時機

• 出現在 WHERE、JOIN ON、ORDER BY、GROUP BY 的欄位
• 區分度高（唯一值/總行數 > 0.1）的欄位優先
• 避免對頻繁更新的欄位過度索引（每次更新需同步維護索引）
• 小表（< 1 萬行）全表掃描可能比索引更快，勿過度索引

驗證索引生效

EXPLAIN SELECT * FROM mytable WHERE name = 'admin' AND city = '台北';
-- 關鍵欄位：
-- type: ref/range/eq_ref 表示用到索引
-- key: 實際使用的索引名
-- key_len: 使用的索引長度（驗證組合索引用了幾列）
-- rows: 預估掃描行數（越小越好）

索引設計是「測試-觀測-調整」的迭代過程。善用 EXPLAIN、SHOW INDEX、sys.schema_index_statistics 等工具，持續優化索引策略。