MySQL 單列索引 vs 聯合索引：正確理解與選型指南

索引設計是查詢性能的關鍵。很多開發者習慣為每個查詢列建單列索引，實則聯合索引往往更高效。本文通過對比實驗，深度解析兩類索引的優化器行為差異。

索引結構對比

單列索引（多個獨立索引）

CREATE TABLE test (
  id INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_last_name (last_name),
  INDEX idx_first_name (first_name)
);

• 每個索引獨立維護 B+ 樹
• 優化器單次查詢只能使用一個索引（Index Merge 除外，但效率通常不如聯合索引）
• 寫入開銷：每次 INSERT/UPDATE 需維護 N 棵樹

聯合索引（多列索引）

CREATE TABLE test (
  id INT NOT NULL,
  last_name CHAR(30) NOT NULL,
  first_name CHAR(30) NOT NULL,
  PRIMARY KEY (id),
  INDEX idx_name (last_name, first_name)
);

• 單棵 B+ 樹，鍵值為 (last_name, first_name) 拼接
• 天然有序：先按 last_name 排序，last_name 相同再按 first_name 排序
• 最左前綴原則：WHERE 條件必須包含最左列

優化器行為實測

-- 數據分布：last_name='Widenius' 2944 行，其中 first_name='Michael' 10 行

聯合索引優勢總結

1. 覆蓋查詢：SELECT 列包含在索引中，避免回表
2. 避免排序：ORDER BY 符合索引順序時，直接按索引掃描輸出
3. 減少索引數：一個聯合索引替代多個單列索引，降低寫入開銷
4. 統計信息更準：單棵樹統計多列相關性

設計原則

1. 最左列區分度最高：如 INDEX(user_id, create_time) 而非反序
2. WHERE + ORDER BY 同索引：WHERE 條件列在前，ORDER BY 列在後
3. 包含 SELECT 列：實現覆蓋索引，Extra=Using index
4. 單表索引 ≤6：權衡讀寫比例，寫多讀少減少索引

Index Merge 例外

MySQL 5.0+ 支持 Index Merge（union/intersection/sort-union），如：

WHERE last_name='Widenius' OR first_name='Michael'
-- 可能使用 idx_last_name UNION idx_first_name

但實測性能通常不如聯合索引，且不可控，不建議依賴。

驗證命令

SHOW INDEX FROM test;
EXPLAIN SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';

正確選擇聯合索引而非堆疊單列索引，是索引設計從入門到進階的分水嶺。