MySQL LIMIT 分頁查詢優化：延遲關聯法解決深分頁效能崩潰

深分頁效能痛點

LIMIT 10000, 10 會掃描前 10010 行再丟棄 10000 行，隨著 Offset 增大，IO 與 CPU 線性增長。測試環境對比：

優化原理：延遲關聯

利用覆蓋索引快速定位目標主鍵，再回表查試完整欄位：

-- 原始慢查詢
SELECT * FROM yanxue8_visit LIMIT 10000, 10;

-- 優化：先取主鍵，再 JOIN 回表
SELECT v.* 
FROM yanxue8_visit v
INNER JOIN (
    SELECT vid FROM yanxue8_visit ORDER BY vid LIMIT 10000, 1
) tmp ON v.vid = tmp.vid
LIMIT 10;

適用條件與限制

• 必須有有序唯一索引（主鍵或 UNIQUE NOT NULL 索引）作為定位鍵。
• ORDER BY 欄位與定位鍵一致，否則結果集順序不匹配。
• 小 Offset 反而較慢：子查詢開銷大於直接掃描，建議 Offset < 100 時用原生 LIMIT。

進階：ID 不連續/有刪除場景

若主鍵有空洞，WHERE id >= (SELECT id ...) 可能取不到足夠行數。改用 ROW_NUMBER() 窗口函數：

-- MySQL 8.0+
WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (ORDER BY vid) AS rn
  FROM yanxue8_visit
)
SELECT * FROM ranked WHERE rn BETWEEN 10001 AND 10010;

應用層替代方案

1. 游標分頁：前端傳上一頁最大 ID，WHERE id > last_id ORDER BY id LIMIT 10，無 Offset 問題。
2. 搜尋後翻頁：Elasticsearch 等搜尋引擎天然支援深分頁。
3. 禁止深分頁：UI 限制最多顯示前 100 頁，超過需用搜尋/篩選。

總結

深分頁優化首選「延遲關聯」或「游標分頁」，核心是避免掃描大量無用行。應結合業務場景選擇：後台報表用延遲關聯，前端列表用游標分頁，極大資料集交給搜尋引擎。