萬盛學電腦網

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

SQL 條件查詢加排序:單欄索引 vs 聯合索引實測對比

測試場景

SELECT * FROM [user] WHERE gender = '1' ORDER BY id DESC;

資料量:70,000 筆,查詢前 100 筆。

三種索引策略對比

策略索引定義執行計畫存取類型耗時 (ms)分析
1. 單欄 genderINDEX idx_gender (gender)const (ref)70精準定位 gender='1' 的行,再按 id 排序(檔案排序),但因只取前 100,成本極低
2. 單欄 idINDEX idx_id (id)index (全索引掃描)200+需掃描整個 id 索引,逐行回表檢查 gender,效率極差
3. 聯合索引 (id, gender)INDEX idx_id_gender (id, gender)index (全索引掃描)~150索引順序為 id 先導,WHERE gender 非前導列,無法 Seek,退化為掃描
4. 聯合索引 (gender, id) 最佳INDEX idx_gender_id (gender, id DESC)ref + 有序輸出< 10gender 定位 + id 已排序,無檔案排序,直接取前 100,極致效能

核心結論

  1. WHERE 條件列優先,ORDER BY 列次之:聯合索引順序應為 (gender, id DESC),實現「過濾+排序」一氣呵成。
  2. 單欄索引 gender 雖非最佳,但因選擇度高(假設男/女各半),配合 LIMIT 100 實際表現尚可。
  3. 單欄 id 或順序錯誤的聯合索引 (id, gender) 均導致全索引掃描,效能最差。
  4. EXPLAIN 中 type=const/ref 代表索引查找,type=index 代表全索引掃描,後者應避免。

延伸:MySQL 8.0 / SQL Server 類似行為

  • MySQL 8.0 支援降序索引 INDEX idx_gender_id (gender, id DESC),完美匹配 ORDER BY id DESC。
  • SQL Server 建立 CREATE INDEX idx_gender_id ON [user](gender, id DESC),查詢最佳化工具同樣可利用索引順序避免 Sort 運算子。
  • 若 gender 選擇度極低(如 99% 為 '1'),最佳化工具可能改為表掃描,需參考實際統計資訊。
copyright © 萬盛學電腦網 all rights reserved