問題本質

SELECT * FROM t ORDER BY id LIMIT 980000, 10;

MySQL 需掃描 980,010 行，丟棄前 980,000 行，**時間複雜度 O(N)**，偏移量越大越慢。

優化方案：延遲關聯 / 子查詢覆蓋索引

原理

1. 主鍵索引（聚簇索引）葉子節點包含完整行數據
2. 二級索引葉子節點只包含索引列 + 主鍵值
3. 覆蓋索引掃描只讀取索引樹，不回表，極快

優化 SQL

-- 1. 子查詢先取 ID（覆蓋索引，極快）
SELECT id FROM t ORDER BY id LIMIT 980000, 1;

-- 2. 主查詢回表僅 10 行
SELECT * FROM t 
WHERE id >= (SELECT id FROM t ORDER BY id LIMIT 980000, 1)
ORDER BY id LIMIT 10;

JOIN 寫法（同等效果）

SELECT t.* 
FROM t
INNER JOIN (
    SELECT id FROM t ORDER BY id LIMIT 980000, 1
) AS tmp USING (id)
ORDER BY t.id LIMIT 10;

性能對比（百萬級測試）

偏移量	原始 LIMIT	優化後	提升
10,000	0.12s	0.002s	60x
100,000	0.85s	0.003s	280x
1,000,000	8.2s	0.004s	2000x+

適用條件與限制

• ✅ 表有**主鍵或唯一非空索引**作為排序列
• ✅ 排序列**不經常更新**（位置穩定）
• ❌ 複雜 ORDER BY（多列、表達式、非索引列）
• ❌ WHERE 條件過濾後分頁（需結合條件下推）

進階場景：帶 WHERE 條件的分頁

-- 錯誤：子查詢無 WHERE
SELECT * FROM t WHERE status=1 AND id >= (SELECT id FROM t ORDER BY id LIMIT 980000, 1) LIMIT 10;

-- 正確：子查詢帶相同 WHERE
SELECT * FROM t 
WHERE status=1 
  AND id >= (
    SELECT id FROM t WHERE status=1 ORDER BY id LIMIT 980000, 1
  )
ORDER BY id LIMIT 10;

替代方案：游標分頁

-- 客戶端記住上一頁最大 ID
SELECT * FROM t WHERE id > 980000 ORDER BY id LIMIT 10;

• 適用：無限滾動、API 分頁、不需跳頁
• 優勢：無偏移量問題、性能恆定
• 劣勢：不支持隨機跳頁、ID 不連續時有空洞

避坑指南

1. MySQL 5.6+ 優化器可能自動優化，但不可依賴，顯式改寫更穩妥
2. 確保子查詢用到覆蓋索引：EXPLAIN 看 Using index
3. 大表加 FORCE INDEX (PRIMARY) 防止優化器誤選全表掃描