您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 分頁查詢實現指南：LIMIT 子句語法、偏移量陷阱與百萬級資料優化方案

編輯：MySQL 資料庫相關知識

LIMIT 語法完整形式

SELECT ... LIMIT [offset,] row_count
# offset 從 0 開始，預設 0
# 等價寫法
LIMIT 5       -- 前 5 行 (offset=0, count=5)
LIMIT 0, 5    -- 前 5 行
LIMIT 5, 10   -- 第 6-15 行 (offset=5, count=10)

分頁邏輯對應

# 第 page 頁，每頁 size 條 (page 從 1 開始)
offset = (page - 1) * size
LIMIT offset, size

深分頁效能崩塌根因

LIMIT 1000000, 20 → 優化器必須掃描前 1,000,000 行、排序、捨棄，僅返回 20 行。
時間複雜度 O(offset + limit)，offset 越大越慢。

四大優化方案

方案	原理	適用場景	代碼範例
延遲關聯 (Deferred Join)	子查詢僅掃描主鍵 + 排序 + LIMIT，再回表	大表深分頁、主鍵有序	`SELECT * FROM t INNER JOIN (SELECT id FROM t ORDER BY id LIMIT 100000, 20) d USING (id)`
Keyset Pagination (游標分頁)	記住上頁最大/最小值，範圍查詢替代 OFFSET	無需隨機跳頁、單向瀏覽	`SELECT * FROM t WHERE id > 100000 ORDER BY id LIMIT 20`
覆蓋索引	索引包含所有查詢欄位，避免回表	查詢欄位固定、索引可接受大小	索引 `(category, id, title)` 查 `SELECT id, title FROM t WHERE category=1 ORDER BY id LIMIT ...`
應用層快取	熱門頁面 (首頁、前 10 頁) 緩存 HTML/JSON	讀多寫少、允許秒級延遲	Redis 緩存分頁結果，TTL 60s

方案選擇決策樹

需支援隨機跳頁 (第 100 頁) → 延遲關聯 + 覆蓋索引。
僅支援上一頁/下一頁/首頁 → Keyset Pagination (最佳效能)。
極高併發熱點頁面 → 應用層快取 + Keyset 回源。

實戰範例：電商商品列表分頁

-- 方案 A：延遲關聯 (支援跳頁)
SELECT p.id, p.name, p.price, p.img
FROM products p
INNER JOIN (
    SELECT id FROM products
    WHERE status=1 AND category_id=5
    ORDER BY created_at DESC
    LIMIT 50000, 20
) d USING (id);

-- 方案 B：Keyset (僅支援翻頁)
SELECT id, name, price, img
FROM products
WHERE status=1 AND category_id=5 AND created_at < '2023-01-15 10:00:00'
ORDER BY created_at DESC
LIMIT 20;