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

MySQL 聯合索引（復合索引）實戰全攻略：最左前綴原理、排序優化、引擎差異與避坑指南

編輯：MySQL 綜合教學

核心原理：最左前綴匹配

聯合索引 KEY idx(a,b,c) 等價於建立三棵 B+ 樹的前綴：(a)、(a,b)、(a,b,c)。查詢**必須從最左列開始連續匹配**，中斷即停止使用索引。

WHERE 條件	是否走索引	使用的索引列
`a=1`	✅	a
`a=1 AND b=2`	✅	a,b
`a=1 AND b=2 AND c=3`	✅	a,b,c
`b=2`	❌	全表掃描
`b=2 AND c=3`	❌	全表掃描
`a=1 AND c=3`	⚠️ 部分	僅用 a，c 為過濾條件

排序優化：索引列順序與方向一致性

聯合索引定義列排序方向（ASC/DESC），ORDER BY **完全一致或完全相反** 時可消除 Filesort。


CREATE INDEX idx_example ON t (col1 ASC, col2 DESC, col3 ASC);

-- ✅ 消除 Filesort
ORDER BY col1 ASC, col2 DESC, col3 ASC;   -- 同序
ORDER BY col1 DESC, col2 ASC, col3 DESC;  -- 逆序

-- ❌ 觸發 Filesort
ORDER BY col1 ASC, col2 ASC, col3 ASC;    -- col2 方向不一致

查詢優化器決策邏輯

多列索引存在時，優化器評估 **各列選擇性**，優先使用過濾性最強的最左前綴。
基數原則：將**區分度高（基數大）的列放在最左**，但需平衡業務查詢模式（最左必須是高頻查詢列）。
LIKE 'prefix%' 可用索引，LIKE '%suffix' 或 LIKE '%mid%' **不可用**。

引擎差異：InnoDB vs MyISAM 覆蓋索引策略

場景：SELECT id FROM articles WHERE category_id=? ORDER BY created DESC LIMIT 10（id 為主鍵）

引擎	推薦聯合索引	原理
InnoDB	`(category_id, created)`	二級索引葉子自帶主鍵值，直接覆蓋查詢，無需回表
MyISAM	`(category_id, created, id)`	二級索引葉子僅存行指針，需顯式包含 `id` 才能覆蓋

結論：InnoDB 天然支持主鍵回表優化，MyISAM 需手工構建覆蓋索引。

索引失效常見陷阱與破解

陷阱	示例	破解方案
不等於操作	`WHERE dept_id <> 1000`	改寫 `WHERE dept_id < 1000 OR dept_id > 1000`
IS NULL 判斷	`WHERE col IS NULL`	建表定義 `NOT NULL DEFAULT ''`，或使用位圖索引 (外部引擎)
函數/運算作用於索引列	`WHERE TRUNC(birthdate) = '...'`	改寫範圍查詢 `WHERE birthdate >= '...' AND birthdate < '...'+1`；或 8.0 函數索引
隱式類型轉換	`WHERE varchar_col = 123`	統一使用字符串 `WHERE varchar_col = '123'`
OR 跨列未建聯合索引	`WHERE a=1 OR b=2`	改寫 `UNION ALL` 或建立 `(a,b)`、`(b,a)` 雙索引

基數評估與字符集選型細節

基數閾值：Cardinality / Rows < 20% 索引效用極低，僅增加寫入開銷，建議刪除。
字符集影響：純英文/數字/下劃線字段（如 username），**優先使用 latin1 或 ascii**，索引體積比 utf8mb4 縮小 50%+，查詢更快。