Web 應用中 ORDER BY ... LIMIT N 是最高頻查詢模式之一。能否利用索引避免 filesort、能否在找到 N 行後提前終止，決定了查詢是毫秒級還是秒級。本文深度剖析索引列順序、WHERE 與 ORDER BY 列關係、多值 IN 條件、大偏移量分頁、強制索引、表達式排序等核心場景，給出可落地的索引設計模式與優化決策樹。
當查詢滿足：
MySQL 會執行 Index Range Scan，掃描到 LIMIT N 行即停止，極快。
SELECT * FROM sites ORDER BY date_created DESC LIMIT 10;
-- 索引：idx_date_created (date_created)
-- 執行：索引逆序掃描，取前 10 行 → 瞬間返回
SELECT * FROM sites WHERE category_id = 5 ORDER BY date_created DESC LIMIT 10;
| 索引 | 效果 | 說明 |
|---|---|---|
(date_created) |
⚠️ 部分有效 | 需掃描大量 category_id=5 的行才湊齊 10 行 |
(category_id, date_created) |
✅ 最優 | 索引定位 category_id=5 區段，逆序掃描 date_created，取 10 行即停 |
設計模式：(WHERE 等值列, ORDER BY 列) 或 (WHERE 等值列, WHERE 範圍列, ORDER BY 列)
SELECT * FROM sites WHERE category_id IN (5, 10, 15) ORDER BY date_created DESC LIMIT 10;
問題：B-Tree 索引無法在多個離散值區段間保持全局有序。MySQL 會：
解決方案：
UNION ALL 分別查詢再合並，或應用層拆分category_id 改為單值查詢（業務允許時）
SELECT * FROM people
WHERE gender = 'm' AND age BETWEEN 18 AND 28 AND country_id = 5 AND city_id = 345
ORDER BY last_online DESC LIMIT 10;
索引策略：選擇「選擇性最高 + 等值」的列作為索引前綴：
-- 方案 A：gender 選擇性低（只有 m/f），但固定值
INDEX (gender, last_online)
-- 方案 B：city_id 選擇性高（數千城市）
INDEX (city_id, last_online)
-- 方案 C：複合覆蓋（視查詢頻率）
INDEX (country_id, city_id, gender, last_online) -- 覆蓋所有 WHERE + ORDER BY
實測 EXPLAIN 對比 rows 與 Extra 選最優。
SELECT * FROM tbl WHERE c = 5 ORDER BY a, b LIMIT 10;
-- 索引 (a, b, c) → 可用於 ORDER BY，但無法過濾 c → 大量掃描 + filesort
-- 索引 (c, a, b) → 可過濾 c，再按 a,b 有序 → Index Range Scan ✅
原則：WHERE 等值列 在前，ORDER BY 列 在後。範圍列（>, <, BETWEEN, IN）會中斷索引有序性，置於等值列之後、ORDER BY 之前。
-- 慢：掃描 100,000 行丟棄，再取 20 行
SELECT * FROM articles ORDER BY id DESC LIMIT 100000, 20;
-- 快：游標分頁（需 id 連續或應用層記錄 last_id）
SELECT * FROM articles WHERE id < 12345 ORDER BY id DESC LIMIT 20;
原理：LIMIT offset, N 必須掃描並丟棄 offset 行。offset 越大越慢，線性增長。游標分頁利用索引定位，O(log N) 定位 + O(N) 取數。
SELECT * FROM tbl FORCE INDEX (idx_c_a_b) WHERE c = 5 ORDER BY a, b LIMIT 10;
當優化器誤選擇「選擇性更高但無法排序」的索引時使用。慎用，優先優化統計信息（ANALYZE TABLE）。
-- 無法用索引
SELECT * FROM tbl WHERE uid = 1 AND status IN (1,3) AND (x_rank & 64) = 64 ORDER BY date2 DESC;
-- 可用索引：將計算結果物化為欄位
ALTER TABLE tbl ADD COLUMN has_rank64 TINYINT GENERATED ALWAYS AS ((x_rank & 64) = 64) STORED;
INDEX (uid, status, has_rank64, date2);
鐵律：ORDER BY expr(col)、ORDER BY func(col) 絕對無法使用索引排序。
ORDER BY ... LIMIT N？→ 是，進入 2EXPLAIN 看 type=ref/range、Extra=Using index、無 Using filesortORDER BY ... LIMIT 優化的本質是「讓索引同時服務過濾與排序，並在滿足 LIMIT 時立即停止」。掌握索引列順序設計、識別破壞有序性的條件、拒絕大偏移量分頁，是寫出高效分頁查詢的關鍵。
