您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 分頁查詢最佳實踐：LIMIT 偏移量優化與大數據量效能調優

編輯：MySQL 綜合教學

基本分頁語法

-- 第 1 頁，每頁 20 筆
SELECT * FROM product LIMIT 0, 20;

-- 第 N 頁
SELECT * FROM product LIMIT (N-1)*20, 20;

深分頁效能問題

當 OFFSET 很大時（如第 10000 頁），MySQL 需掃描並丟棄前 200,000 筆資料，導致查詢緩慢：

-- 低效：掃描 200,000 行後取 20 行
SELECT * FROM product LIMIT 200000, 20;

優化方案對比

方案	適用場景	原理	優/缺點
子查詢 + 索引覆蓋	單表、有主鍵/唯一索引	先只查 ID，再回表	✅ 大幅減少回表 ❌ 需覆蓋索引
延遲關聯	複雜 JOIN 查詢	先分頁查主鍵，再 JOIN 明細	✅ 通用性強 ❌ SQL 較複雜
游標/Keyset 分頁	無需跳頁、API 無限滾動	WHERE id > last_id LIMIT 20	✅ 極快、穩定 ❌ 不支援隨機跳頁
覆蓋索引優化	只需索引欄位	索引包含所有查詢欄位	✅ 避免回表 ❌ 受限於查詢欄位

推薦實作：延遲關聯範例

-- 步驟 1：只查主鍵（利用索引快速定位）
SELECT id FROM product
WHERE category_id = 5
ORDER BY created_at DESC
LIMIT 200000, 20;

-- 步驟 2：用 IN 關聯查明細
SELECT * FROM product
WHERE id IN (上述查詢的 20 個 ID)
ORDER BY created_at DESC;

Java 實作範例（優化版）

public List<Product> getProducts(int pageNo, int pageSize) {
    String sql = """
        SELECT p.* FROM product p
        INNER JOIN (
            SELECT id FROM product
            WHERE category_id = ?
            ORDER BY created_at DESC
            LIMIT ?, ?
        ) tmp ON p.id = tmp.id
        ORDER BY p.created_at DESC
        """;
    // 參數: categoryId, (pageNo-1)*pageSize, pageSize
    return jdbcTemplate.query(sql, new ProductRowMapper(),
        categoryId, (pageNo - 1) * pageSize, pageSize);
}