您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 索引原理與實戰：單列、組合、最左前綴全解析

編輯：MySQL 綜合教學

MySQL 索引原理與實戰：單列、組合、最左前綴全解析

索引是資料庫效能優化的核心機制，合理設計索引可將查詢速度提升數十至數百倍。本文從索引基礎概念、類型分類、單列與組合索引對比、最左前綴原理、使用場景、失效條件、維護成本七個維度，系統講解 MySQL 索引實戰精要。

一、索引基礎：為什麼需要索引？

假設表 testIndex 有 1000 行資料，查詢 WHERE vc_Name='erquan'：

無索引： 全表掃描 1000 行，逐行比對
有索引： B+ 樹結構直接定位，僅需 3-4 次 IO 讀取
效能差距： 理論提升 100 倍以上（隨資料量增長優勢擴大）

二、索引分類

類型	關鍵字	特點	適用場景
主鍵索引	PRIMARY KEY	唯一、非空、聚簇索引（InnoDB）	每張表必須、業務主鍵
唯一索引	UNIQUE	值唯一、允許 NULL	業務唯一約束（郵箱、身分證）
普通索引	INDEX / KEY	無約束、可重複、可 NULL	高頻查詢條件、JOIN 關聯欄位
全文索引	FULLTEXT	支援自然語言/布爾模式搜尋	長文本搜尋（MySQL 5.6+ InnoDB 支援）
組合索引	INDEX(a,b,c)	多列組合、遵循最左前綴	多條件組合查詢、排序優化
空間索引	SPATIAL	GIS 幾何類型	地理位置、幾何運算

三、索引創建語法

-- 1. 建表時指定
CREATE TABLE tb (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(100),
  INDEX idx_name (name),
  UNIQUE uk_email (email)
);

-- 2. ALTER TABLE 添加
ALTER TABLE tb ADD INDEX idx_name (name);
ALTER TABLE tb ADD UNIQUE uk_email (email);

-- 3. CREATE INDEX 獨立創建
CREATE INDEX idx_name ON tb(name);
CREATE UNIQUE INDEX uk_email ON tb(email);

-- 4. 組合索引（指定前綴長度優化）
CREATE INDEX idx_name_city ON tb(name(10), city);  -- name 只索引前 10 字符

-- 5. 刪除索引
DROP INDEX idx_name ON tb;
ALTER TABLE tb DROP INDEX idx_name;

四、單列索引 vs 組合索引：實戰對比

場景： 表 myIndex 10000 行，查詢：

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

方案 A：三個單列索引

MySQL 僅能選擇一個看似最優的單列索引（如 idx_name）
通過索引定位 5 行 vc_Name='erquan' → 回表 → 過濾 city、age
效率提升有限，仍需回表與後續過濾

方案 B：組合索引 `idx_name_city_age (vc_Name, vc_City, i_Age)`

B+ 樹按 (name, city, age) 排序存儲
查詢直接定位到唯一滿足三條件的葉子節點，無需回表、無需額外過濾
效率最高，IO 最少

關鍵結論：

組合索引 (a,b,c) 等價於擁有三個索引：(a)、(a,b)、(a,b,c)
但不包含： (b)、(c)、(b,c)、(a,c)

五、最左前綴原理深度解析

定義： 組合索引的 B+ 樹按索引定義列順序構建排序規則，查詢必須從最左列開始連續匹配才能利用索引。

查詢條件	索引 (a,b,c)	是否用到索引	用到的索引列
WHERE a=1	✓	是	a
WHERE a=1 AND b=2	✓	是	a,b
WHERE a=1 AND b=2 AND c=3	✓	是	a,b,c
WHERE b=2	✗	否	全表掃描
WHERE b=2 AND c=3	✗	否	全表掃描
WHERE a=1 AND c=3	△	部分	僅用 a（中間斷層）
WHERE a=1 ORDER BY b	✓	是	a,b（避免 filesort）
WHERE a=1 ORDER BY c	△	部分	用 a 過濾，但排序需 filesort

六、索引使用條件與失效場景

✅ 會使用索引的運算符：

=, >, >=, <, <=, BETWEEN, IN, LIKE 'prefix%'

❌ 導致索引失效的常見場景：

LIKE 以通配符開頭： LIKE '%abc'、LIKE '%abc%'
索引列參與運算/函數： WHERE YEAR(date_col)=2023、WHERE id+1=100、WHERE UPPER(name)='ABC'
隱式類型轉換： WHERE varchar_col = 123（數字比較字串）
組合索引跳過最左列： WHERE b=1、WHERE c=1、WHERE b=1 AND c=2
OR 條件未全覆蓋索引： WHERE idx_a=1 OR non_idx_b=2
優化器判斷全表更快： 統計資訊顯示需掃描 > 20%-30% 表行數
不等於/不在： !=、NOT IN、NOT EXISTS（通常全表掃描）

七、索引維護成本與設計原則

成本：

寫入開銷： INSERT/UPDATE/DELETE 需同步更新所有索引 B+ 樹
空間佔用： 每個索引佔用額外磁碟空間，組合索引尤甚
統計維護： ANALYZE TABLE、自動統計更新帶來額外負載

設計黃金法則：

高頻 WHERE/JOIN/ORDER BY/GROUP BY 欄位建索引
高基數（區分度高）優先： 唯一值/總行數 > 0.1 才值得建單列索引
低基數欄位（如性別、狀態）不建單列索引，可作組合索引尾部
組合索引將高基數列前置，符合最左前綴查詢模式
避免過度索引： 單表建議 不超過 5 個索引，核心大表更嚴格
覆蓋索引優化： SELECT 欄位全包含在索引中，避免回表（Extra: Using index）
定期檢測索引效果： SHOW STATUS LIKE 'Handler_read%'，Handler_read_key 高表示索引命中好

八、驗證索引效果實戰命令

-- 查看表索引
SHOW INDEX FROM tb;

-- 分析查詢執行計劃
EXPLAIN SELECT * FROM tb WHERE name='abc' AND city='北京';

-- 監控索引使用情況
SHOW STATUS LIKE 'Handler_read%';
-- Handler_read_key: 索引讀取次數（越高越好）
-- Handler_read_rnd_next: 全表掃描次數（越低越好）

-- 檢查未使用的索引 (MySQL 5.7+ sys schema)
SELECT * FROM sys.schema_unused_indexes;

索引設計是「讀寫平衡」的藝術。理解 B+ 樹結構、最左前綴原理、優化器決策邏輯，結合業務查詢模式，才能創建真正高效的索引體系。建議建立索引設計評審機制，上線前必經 EXPLAIN 驗證，上線後持續監控 sys.schema_unused_indexes 清理無效索引。