SELECT * FROM [user] WHERE gender = '1' ORDER BY id DESC;
資料量:70,000 筆,查詢前 100 筆。
| 策略 | 索引定義 | 執行計畫存取類型 | 耗時 (ms) | 分析 |
|---|---|---|---|---|
| 1. 單欄 gender | INDEX idx_gender (gender) | const (ref) | 70 | 精準定位 gender='1' 的行,再按 id 排序(檔案排序),但因只取前 100,成本極低 |
| 2. 單欄 id | INDEX 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 + 有序輸出 | < 10 | gender 定位 + id 已排序,無檔案排序,直接取前 100,極致效能 |
(gender, id DESC),實現「過濾+排序」一氣呵成。gender 雖非最佳,但因選擇度高(假設男/女各半),配合 LIMIT 100 實際表現尚可。id 或順序錯誤的聯合索引 (id, gender) 均導致全索引掃描,效能最差。EXPLAIN 中 type=const/ref 代表索引查找,type=index 代表全索引掃描,後者應避免。INDEX idx_gender_id (gender, id DESC),完美匹配 ORDER BY id DESC。CREATE INDEX idx_gender_id ON [user](gender, id DESC),查詢最佳化工具同樣可利用索引順序避免 Sort 運算子。gender 選擇度極低(如 99% 為 '1'),最佳化工具可能改為表掃描,需參考實際統計資訊。