兩種索引建立方式

多列聯合索引（Composite Index）

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-Tree，按 last_name 排序，同值再按 first_name 排序。

多個單列索引

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 (last_name),
  INDEX idx_first (first_name)
);

各自獨立 B-Tree，優化器**只能選擇一個**使用（Index Merge 例外極少觸發）。

聯合索引生效條件：最左前綴原則

查詢必須從索引最左欄位開始匹配，才能利用索引：

WHERE 條件	是否用到 idx_name (last_name, first_name)	說明
last_name = 'Widenius'	✅ 是	滿足最左前綴
last_name = 'W' AND first_name = 'M'	✅ 是	完整匹配
last_name = 'W' AND first_name LIKE 'M%'	✅ 是	範圍查詢在最左之後
first_name = 'Michael'	❌ 否	跳過最左欄位，無法使用
last_name = 'W' OR first_name = 'M'	❌ 否	OR 導致全表掃描（除非 Index Merge）

為什麼聯合索引優於多單列索引？

1. 空間效率：一棵 B-Tree 替代多棵，節省磁碟與記憶體。
2. 寫入效能：INSERT/UPDATE/DELETE 僅維護一棵索引樹。
3. 查詢覆蓋：滿足最左前綴的複合條件可直接覆蓋索引掃描（Using index）。

建立聯合索引的欄位順序原則

1. 區分度最高（基數最大）的欄位放最左：如 user_id > status > gender。
2. 等值查詢欄位優先於範圍查詢欄位：範圍欄位（>、<、>=、<=、LIKE '%...'）會阻斷後續欄位使用索引。
3. 覆蓋查詢需求：若常用 SELECT a,b FROM t WHERE c=1 AND d=2，索引建為 (c,d,a,b) 實現覆蓋索引。

驗證索引使用

EXPLAIN SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael';
-- key: idx_name, key_len: 62, ref: const,const, type: ref → 完美使用