MySQL ORDER BY 與 LIMIT 分頁優化實戰：索引下推、延遲關聯、游標分頁與效能對比

前言

ORDER BY 排序與 LIMIT 分頁是 Web 應用最常用的查詢模式。隨著資料量增長，「深分頁」 (LIMIT 1000000, 20) 成為效能殺手。本文系統講解排序索引匹配規則、分頁優化三大技巧 (延遲關聯、游標分頁、覆蓋索引)、MySQL 8.0 降序索引新特效，並給出效能對比實測。

一、ORDER BY 與索引匹配規則

1.1 可用索引排序的條件

• ORDER BY 列順序、方向 (ASC/DESC) **完全匹配** 索引順序 (MySQL 8.0+ 支援混合方向索引)。
• WHERE 條件為索引最左前綴等值查詢，ORDER BY 使用後續列。
• 無 WHERE 時，ORDER BY 必須匹配索引最左前綴。

1.2 典型可用/不可用案例

1.3 filesort 內部機制

• sort_buffer_size (預設 256KB) 記憶體排序，超出則建磁碟臨時表 (Using temporary; Using filesort)。
• 兩種算法：
  - **雙路排序** (舊版)：讀取排序鍵 + rowid，排序後再回表讀取選擇列。
  - **單路排序** (預設)：一次讀取所有選擇列 + 排序鍵，排序後直接輸出，避免二次 I/O，但佔用更多 sort_buffer。

二、LIMIT 深分頁效能災難

SELECT * FROM article ORDER BY id LIMIT 1000000, 20;

**執行流程**：掃描索引/表 → 排序 (或索引順序讀取) → **跳過 100 萬行** → 回傳 20 行。
**代價**：隨 OFFSET 增大線性增長，IOPS、CPU 雙高。

三、三大優化策略

3.1 延遲關聯

-- 原始 (慢)
SELECT * FROM article ORDER BY id LIMIT 1000000, 20;

-- 優化 (快)：內層只掃描主鍵索引 (Covering)，外層回表 20 行
SELECT a.* FROM article a
INNER JOIN (
    SELECT id FROM article ORDER BY id LIMIT 1000000, 20
) b USING (id);

**原理**：內層 type=index (Full Index Scan)，無回表；外層 type=eq_ref 精準回表 20 行。

3.2 游標分頁

-- 首頁
SELECT * FROM article WHERE id > 0 ORDER BY id LIMIT 20;

-- 後續頁：記住上一頁最後一行的 id
SELECT * FROM article WHERE id > 1000020 ORDER BY id LIMIT 20;

**優勢**：無 OFFSET，索引範圍查詢 type=range，效能恆定 O(1)。
**適用**：無需跳頁、按時間/ID 遞增瀏覽 (Feed、日誌、訊息列表)。

3.3 覆蓋索引 + 只查必要欄位

-- 建立覆蓋索引
ALTER TABLE article ADD INDEX idx_created_id (created_at, id);

-- 查詢只需 id, title, created_at
SELECT id, title, created_at FROM article
WHERE created_at >= '2024-01-01'
ORDER BY created_at, id LIMIT 20;

EXPLAIN 顯示 Using index，全程不回表，極快。

四、MySQL 8.0 降序索引

-- 建立混合方向索引
ALTER TABLE article ADD INDEX idx_status_created (status, created_at DESC);

-- 查詢：狀態=1，按建立時間倒序
SELECT * FROM article WHERE status = 1 ORDER BY created_at DESC LIMIT 20;
-- 8.0 可直接用索引排序，5.7 需 filesort

五、效能實測對比 (article 1000 萬行，id 主鍵)

六、實戰建議決策樹

需要跳頁 (頁碼導航)？
├─ 是 → OFFSET 分頁
│    ├─ 資料量 < 10萬 → 直接 LIMIT offset, size
│    ├─ 資料量 10萬~100萬 → 延遲關聯 + 覆蓋索引
│    └─ 資料量 > 100萬 → 禁止深分頁，改用「搜尋+篩選」縮小結果集
└─ 否 (無限滾動、Feed) → 游標分頁 (WHERE id > last_id)
     └─ 必須支援排序切換 → 為每種排序建立對應覆蓋索引

結語： 分頁優化的核心是**減少掃描與回表行數**。掌握「延遲關聯處理深 OFFSET」、「游標分頁消除 OFFSET」、「覆蓋索引消除回表」、「降序索引支援混合排序」四大技巧，配合 EXPLAIN 驗證 Using index/Using index condition，即可從容應對億級資料分頁。