復合索引(複合索引)是優化多欄位查詢的核心手段,但其效能高度依賴欄位順序與排序方向一致性。本文通過實測案例,揭示復合索引生效與失效的關鍵規則,並總結索引設計鐵律。
表 dlog_user,主鍵 username。查詢:
SELECT * FROM dlog_user ORDER BY online_status, username;
EXPLAIN:type=ALL(全表掃描),Extra=Using filesort
(username, online_status) — 順序錯誤CREATE INDEX idx_online_status ON dlog_user(username, online_status);
結果:仍 type=ALL、Using filesort —— 索引無效
(online_status, username) — 順序正確CREATE INDEX idx_online_status ON dlog_user(online_status, username);
結果:type=index、Extra=空 —— 索引生效,避免 filesort
ORDER BY 子句順序完全一致ASC 或全 DESC,混合方向(ASC, DESC)導致索引失效EXPLAIN 驗證SELECT a.* FROM dlog_user a
INNER JOIN dlog_friend b ON a.username = b.friend_username
WHERE b.username = 'ld'
ORDER BY a.online_status DESC, a.username ASC; -- 方向相反
無論如何調整索引順序,混合排序方向必觸發 filesort。
表 test(a,b,c),索引 KEY a(a,b,c)
| SQL 模式 | 效能 | 說明 |
|---|---|---|
WHERE a=10 AND b>50 | ✅ 優 | 最左前綴 a=常量,b 範圍查詢用到索引 |
WHERE a>10 AND b=50 | ❌ 差 | a 範圍查詢阻斷 b 索引使用 |
ORDER BY a | ✅ 優 | 索引順序掃描 |
ORDER BY b | ❌ 差 | 非最左列排序需 filesort |
ORDER BY c | ❌ 差 | 同左 |
WHERE a=10 ORDER BY a | ✅ 優 | 常量過濾+索引順序 |
WHERE a=10 ORDER BY b | ✅ 優 | a=常量後,b 有序 |
WHERE a=10 ORDER BY c | ❌ 差 | b 缺失,c 無序 |
WHERE a>10 ORDER BY a | ✅ 優 | 範圍查詢但排序欄位同索引首列 |
WHERE a>10 ORDER BY b | ❌ 差 | a 範圍阻斷 b 有序性 |
WHERE a=10 AND b=10 ORDER BY c | ✅ 優 | a,b 為常量,c 有序 |
WHERE a=10 AND b>10 ORDER BY b | ✅ 優 | a=常量,b 範圍查詢兼排序 |
WHERE a=10 AND b>10 ORDER BY c | ❌ 差 | b 範圍阻斷 c |
ORDER BY/GROUP BYSELECT 列全包含於索引中,避免回表SHOW INDEX 檢查 Cardinality,移除重複/低選擇性/未使用索引CREATE INDEX idx_desc ON t (a DESC, b ASC) 真正支援混合排序方向(5.7 忽略 DESC)ALTER TABLE t ALTER INDEX idx INVISIBLE 驗證索引必要性INDEX ((LOWER(email))) 支援表達式索引復合索引威力巨大,但也是雙刃劍。遵循「順序一致、方向一致、按需創建、定期審計」原則,讓索引真正成為查詢加速器而非寫入負擔。