萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 復合索引效能實戰:順序、排序方向與失效場景全解析

復合索引(複合索引)是優化多欄位查詢的核心手段,但其效能高度依賴欄位順序與排序方向一致性。本文通過實測案例,揭示復合索引生效與失效的關鍵規則,並總結索引設計鐵律。

實測案例:順序決定生死

表 dlog_user,主鍵 username。查詢:

SELECT * FROM dlog_user ORDER BY online_status, username;

測試 1:無額外索引

EXPLAIN:type=ALL(全表掃描),Extra=Using filesort

測試 2:索引 (username, online_status) — 順序錯誤

CREATE INDEX idx_online_status ON dlog_user(username, online_status);

結果:仍 type=ALL、Using filesort —— 索引無效

測試 3:索引 (online_status, username) — 順序正確

CREATE INDEX idx_online_status ON dlog_user(online_status, username);

結果:type=index、Extra=空 —— 索引生效,避免 filesort

核心結論:三大鐵律

  1. ORDER BY 欄位順序 = 索引欄位順序:索引定義順序必須與 ORDER BY 子句順序完全一致
  2. ORDER BY 排序方向必須一致:全 ASC 或全 DESC,混合方向(ASC, DESC)導致索引失效
  3. 建了索引不一定有效:必須用實際 SQL 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。

綜合測試矩陣:WHERE + ORDER BY 組合

表 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

索引設計鐵律總結

  1. 索引越少越好:每個索引增加寫入開銷(INSERT/UPDATE/DELETE 需維護所有索引)
  2. 最窄/最高選擇性欄位靠左:優先過濾性強的欄位在前
  3. 避免 filesort、臨時表、全表掃描:通過索引順序滿足 ORDER BY/GROUP BY
  4. 覆蓋索引:SELECT 列全包含於索引中,避免回表
  5. 定期清理:SHOW INDEX 檢查 Cardinality,移除重複/低選擇性/未使用索引

MySQL 8.0 新特性支援

  • 降序索引:CREATE INDEX idx_desc ON t (a DESC, b ASC) 真正支援混合排序方向(5.7 忽略 DESC)
  • 隱藏索引:ALTER TABLE t ALTER INDEX idx INVISIBLE 驗證索引必要性
  • 函數索引:INDEX ((LOWER(email))) 支援表達式索引

復合索引威力巨大,但也是雙刃劍。遵循「順序一致、方向一致、按需創建、定期審計」原則,讓索引真正成為查詢加速器而非寫入負擔。

copyright © 萬盛學電腦網 all rights reserved