MySQL LIMIT 分頁優化深度解析：偏移量大時的高效查詢策略

MySQL LIMIT 分頁優化深度解析：偏移量大時的高效查詢策略

LIMIT 基本語法

LIMIT [offset,] row_count
LIMIT row_count OFFSET offset

• offset：跳過的行數（從 0 開始）
• row_count：返回最大行數
• LIMIT 5 等價於 LIMIT 0,5
• LIMIT 95,-1：從第 96 行到末尾（-1 表示無上限）

大偏移量性能問題

LIMIT 100000, 10 需掃描前 100000 行再丟棄，IO 與 CPU 淨浪費。偏移量越大越慢。

優化方案對比實測

各方案適用場景

1. 小偏移量（< 1000）：直接 LIMIT offset, N 簡單可維護
2. 大偏移量、傳統分頁（需跳頁）：子查詢定位 / 延遲關聯，利用覆蓋索引
3. 無限滾動、API 分頁（無需跳頁）：游標分頁（記住上次最大 ID/時間戳），性能最優、無深分頁問題
4. 按非主鍵排序分頁：建立聯合索引 (sort_col, pk)，子查詢選 pk 回表

優化模板

SELECT * FROM `table`
WHERE `id` >= (
    SELECT `id` FROM `table`
    ORDER BY `id` LIMIT 100000, 1
)
LIMIT 20;

SELECT t.* FROM `table` t
INNER JOIN (
    SELECT `id` FROM `table`
    ORDER BY `id` LIMIT 100000, 1
) d USING (`id`)
LIMIT 20;

-- 首頁
SELECT * FROM `table` ORDER BY `id` LIMIT 20;

-- 下一頁（客戶端傳遞上次最大 id=10020）
SELECT * FROM `table` WHERE `id` > 10020 ORDER BY `id` LIMIT 20;

注意事項

• 子查詢 LIMIT offset, 1 仍需掃描 offset 行，但只讀主鍵索引（覆蓋索引），比回表快得多
• OFFSET 關鍵字僅為語法糖，性能無差異
• MySQL 8.0+ LIMIT ... OFFSET ... 支援準備語句參數化
• 分庫分表場景，全局分頁極其複雜，建議業務層面避免深分頁

對比：SQL Server TOP vs MySQL LIMIT

SQL Server TOP N 必須掃描前 N 行，無偏移語法；MySQL LIMIT offset, N 靈活但大偏移慢。現代應用優先游標分頁。