千萬級表 LIMIT 8000000, 10 查詢耗時 1.3 秒，業務不可用。本文實測對比「延遲關聯」優化方案，將深分頁查詢從秒級降至亞毫秒級，附完整執行計劃分析。

問題現場：OFFSET 越大越慢

測試表 demo 1000 萬行，主鍵 id 自增：

SQL	耗時	說明
SELECT id,username,age,word FROM demo LIMIT 101,10	0.0005s	淺分頁極快
SELECT id,username,age,word FROM demo LIMIT 100001,10	0.012s	明顯變慢
SELECT id,username,age,word FROM demo LIMIT 8000001,10	1.3s	深分頁災難

根因：MySQL 掃描丟棄機制

LIMIT offset, count 實際執行：掃描 offset + count 行，丟棄前 offset 行，返回後 count 行。Offset 800 萬意味著掃描 800 萬行聚簇索引，全是無效 I/O。

優化方案：延遲關聯

核心思路

1. 子查詢僅掃描主鍵 id 定位偏移位置（覆蓋索引，極快）
2. 外層查詢用 WHERE id > (子查詢結果) 直接定位，避免大 Offset 掃描

實戰 SQL

-- 步驟1：獲取偏移位置的 id（覆蓋索引掃描）
SELECT id FROM demo LIMIT 8000001, 1;
-- 耗時：0.0004s，返回 id=8000010

-- 步驟2：主查詢利用 id 範圍直接定位
SELECT id, username, age, word 
FROM demo 
WHERE id > (SELECT id FROM demo LIMIT 8000001, 1) 
LIMIT 10;
-- 耗時：0.0005s，提升 2600 倍！

執行計劃對比

-- 原始：type=ALL，rows=8000010，Extra=Using where
-- 優化後：
-- 子查詢：type=index，key=PRIMARY，rows=8000002，Extra=Using index
-- 主查詢：type=range，key=PRIMARY，rows=10，Extra=Using where

子查詢走「Using index」（覆蓋索引），主查詢走「range」精確定位，無全表掃描。

適用條件與變體

• 必須：表有自增主鍵或唯一遞增索引
• 適用：WHERE 條件簡單或無額外過濾
• 變體：若有複雜 WHERE，子查詢需帶上相同條件：

  SELECT * FROM demo 
  WHERE id > (SELECT id FROM demo WHERE status=1 ORDER BY id LIMIT 8000001, 1)
    AND status=1 
  LIMIT 10;
  

• 游標分頁替代：前端改用 WHERE id > last_id LIMIT 10，彻底消除 Offset，適合無限滾動/瀑布流

效能對比總結

方案	800萬偏移耗時	適用場景	改造成本
原生 LIMIT	1.3s	淺分頁	0
延遲關聯	0.0005s	深分頁、主鍵遞增	低（SQL 改寫）
游標分頁	0.0003s	無限滾動、無需跳頁	中（前後端協調）
覆蓋索引+強制索引	0.02s	需查詢非主鍵列	中（索引設計）

避坑提醒

• 子查詢必須 ORDER BY id 確保確定性（隱式有序但不保證）
• 並發寫入導致 ID 不連續時，WHERE id > sub_id 可能漏行/重行，嚴格場景需事務/鎖
• 極大偏移（億級）子查詢仍可能慢，考慮分表/歸檔

深分頁優化本質是「用索引定位替換掃描丟棄」。延遲關聯是改動最小、收益最大的銀彈；游標分頁則是架構層面的徹底治愈。