MySQL 單列索引與組合索引區別詳解（轉載自 IT 專家網 2010）

前言

本文轉載自 IT 專家網 2010 年經典文章，雖年代久遠，但關於 MySQL 單列索引與組合索引的核心原理、最左前綴法則至今完全有效。為方便閱讀，已重新排版並修正部分語法。

一、測試場景構建

CREATE TABLE myIndex (
  i_testID INT NOT NULL AUTO_INCREMENT,
  vc_Name VARCHAR(50) NOT NULL,
  vc_City VARCHAR(50) NOT NULL,
  i_Age INT NOT NULL,
  i_SchoolID INT NOT NULL,
  PRIMARY KEY (i_testID)
);

1 萬行數據，vc_Name='erquan' 僅 5 行，城市/年齡/學校組合各異。目標查詢：

SELECT i_testID FROM myIndex
WHERE vc_Name='erquan' AND vc_City='鄭州' AND i_Age=25;

二、單列索引方案

在 vc_Name 上建索引：

1. 索引定位到 5 行 erquan（中間結果集）。
2. 回表過濾 vc_City='鄭州'、i_Age=25。
3. 最終得到 1 行。

同理，單獨在 vc_City 或 i_Age 建索引效率相近。雖比全掃快，但仍有回表與 Server 層過濾開銷。

三、組合索引方案

ALTER TABLE myIndex ADD INDEX name_city_age (vc_Name(10), vc_City, i_Age);

為何 vc_Name(10)？ 實際名字長度多 ≤10，前綴索引可縮小索引體積、提升查詢速度、減少寫入開銷。

效果：B+ 樹直接按三元組定位到唯一行，**零掃描、零回表、零過濾**。

四、三個單列索引 ≠ 一個組合索引

即使分別在三列建單列索引，MySQL 單次查詢通常**只能用到其中一個**（Index Merge 例外但效能一般），遠不如組合索引高效。

五、組合索引的「隱形福利」：最左前綴

建立 (vc_Name, vc_City, i_Age) 等價於同時擁有：

• (vc_Name, vc_City, i_Age)
• (vc_Name, vc_City)
• (vc_Name)

可用查詢：

WHERE vc_Name='erquan' AND vc_City='鄭州'
WHERE vc_Name='erquan'

不可用查詢：

WHERE i_Age=20 AND vc_City='鄭州'
WHERE vc_City='鄭州'

原因：B+ 樹按 (vc_Name, vc_City, i_Age) 字典序排序，必須從最左列連續匹配才能利用有序性。

六、實戰建議

• 高頻聯合查詢 → 組合索引，列順序 = 區分度降序 = WHERE 順序 = ORDER BY 順序。
• 單列高區分度且獨立查詢多 → 單列索引。
• 單表索引數建議 ≤ 6，權衡寫入性能。
• 前綴索引長度通過 COUNT(DISTINCT LEFT(col,n))/COUNT(*) 計算區分度決定。

來源：IT 專家網 2010-11-22，作者佚名。本站整理發布僅供學習交流。