---
tags: [MySQL, 分页优化, 游标分页, 延迟关联]
create time: 2026-05-16 00:00
---

# 深分页优化

## 概述

当 OFFSET 达到几十万、百万级别时，`LIMIT offset, size` 的性能会急剧下降。这不仅是 MySQL 的问题，而是所有关系型数据库的通病。本章提供三种成熟的解决方案。

## 正文

### 问题复现

```sql
-- 典型的深分页查询
SELECT * FROM orders
ORDER BY created_at DESC
LIMIT 999990, 20;

-- 发生了什么？
-- 1. MySQL 从聚簇索引开始扫描
-- 2. 逐行读取并排序（或使用 order by 索引）
-- 3. 跳过前 999990 行
-- 4. 取出接下来的 20 行
-- 5. 丢弃前面跳过的所有行
```

```mermaid
flowchart LR
    A["Page 1: LIMIT 0, 20"] -->|"扫描 20 行，取 20 行"| T1["✅ 很快"]
    B["Page 1000: LIMIT 19980, 20"] -->|"扫描 20000 行，取 20 行"| T2["⚠️ 慢"]
    C["Page 50000: LIMIT 999990, 20"] -->|"扫描 ~100万行，取 20 行"| T3["🔴 极慢"]
    
    style T1 fill:#00D866,color:#fff
    style T2 fill:#FF9F43,color:#000
    style T3 fill:#EE5A24,color:#fff
```

> [!QUESTION] 为什么不能像编程语言那样直接从数组索引取值？
> 数据库不是内存数组。每一次 `LIMIT offset, N` 都需要从 B+ Tree 根部重新定位到第 offset 行——这是一次完整的扫描 + 排序操作。偏移量越大，越像是在浩瀚星海中逐粒计数沙子。

### 方案一：延迟关联（Deferred Join）

```sql
-- 核心思路：先用紧凑的主键索引定位 ID，再回表查数据
-- 关键：必须使用 (created_at, id) 联合索引，确保排序可覆盖
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders
    ORDER BY created_at DESC, id DESC  -- 用主键做稳定排序的 tie-breaker
    LIMIT 999990, 20
) tmp ON o.id = tmp.id;
```

> [!QUESTION] 为什么需要 `id DESC` 作为第二排序条件？
> 如果多条记录具有相同的 `created_at`（比如同一秒内多个用户下单），仅按 `created_at` 排序的结果是不确定的——每次查询行顺序可能不同。加上主键作为 tie-breaker 可以**保证排序稳定性**，避免翻页时出现重复或遗漏的数据。

> [!TIP] 索引前提
> - 子查询必须是**覆盖索引扫描**：`(created_at, id)` 联合索引足以满足 `ORDER BY` + `LIMIT`，无需回表
> - 如果没有此索引，子查询本身也会退化为慢查询

```mermaid
sequenceDiagram
    participant SQ as 子查询
    participant PK as 聚簇索引(id)
    participant Main as 主查询

    SQ->>PK: LIMIT 999990, 20 只取 id
    PK-->>SQ: 返回 20 个 ID（各 8 bytes）
    
    loop 20 个 ID
        Main->>PK: SELECT * WHERE id = ?
        PK-->>Main: 精准返回完整行
    end
```

#### 性能对比

| 指标 | 传统方式 | 延迟关联 |
|------|---------|---------|
| 扫描行数 | ~1,000,000 行完整数据 | 1,000,000 行仅主键 (8 bytes) |
| 回表次数 | 1,000,000 次（隐式） | 20 次（精准） |
| 内存占用 | 1,000,000 × 行大小 | 20 × 8 bytes |
| 典型耗时 | 3~10 秒 | 0.1~0.3 秒 |

```go
// Go 中实现延迟关联的分页 helper
func PaginatedQuery(db *gorm.DB, page, pageSize int) ([]Order, error) {
    offset := (page - 1) * pageSize

    // 子查询：只查主键（覆盖索引扫描）
    var ids []int64
    if err := db.Model(&Order{}).
        Select("id").
        Order("created_at DESC, id DESC").
        Limit(pageSize).
        Offset(offset).
        Pluck("id", &ids).Error; err != nil {
        return nil, err
    }
    if len(ids) == 0 {
        return []Order{}, nil
    }

    // 主查询：IN 精确查完整数据
    var results []Order
    if err := db.Where("id IN ?", ids).Find(&results).Error; err != nil {
        return nil, err
    }
    return results, nil
}
```

> [!WARNING] 延迟关联的限制
> - `ORDER BY` 必须能用索引覆盖，否则子查询本身就很慢
> - `IN` 列表过大时（比如 > 1000 条）也会退化
> - 如果每行数据很小（< 50 bytes），收益有限

### 方案二：游标分页（Seek Method / Keyset Pagination）

```sql
-- 首次查询（第一页）
SELECT * FROM orders
ORDER BY id ASC
LIMIT 20;

-- 下一页：取上一页最后一条记录的 id
SELECT * FROM orders
WHERE id > 987654  -- 上一页最后一条的 id
ORDER BY id ASC
LIMIT 20;

-- 再下一页
SELECT * FROM orders
WHERE id > 987674  -- 这次最后一条的 id
ORDER BY id ASC
LIMIT 20;
```

```mermaid
flowchart TD
    Page1["第一页: WHERE id > 0 LIMIT 20<br/>获取最后一条 id = 10001"] --> Page2
    Page2["第二页: WHERE id > 10001 LIMIT 20<br/>获取最后一条 id = 10021"] --> Page3
    Page3["第三页: WHERE id > 10021 LIMIT 20"]
    
    Page1 -->|"O(1) 索引范围扫描"| R1["✅ 恒定速度"]
    Page2 -->|"O(1) 索引范围扫描"| R2["✅ 恒定速度"]
    Page3 -->|"O(1) 索引范围扫描"| R3["✅ 恒定速度"]
    
    style R1 fill:#00D866,color:#fff
    style R2 fill:#00D866,color:#fff
    style R3 fill:#00D866,color:#fff
```

#### Go 实现

```go
// Cursor-based pagination with stable sort
type CursorResult struct {
    Items     []Order
    NextCursor  *string // nil = 最后一页
    PrevCursor *string // nil = 第一页
}

func GetOrdersByCursor(db *gorm.DB, afterID, beforeID *int64, limit int, desc bool) (*CursorResult, error) {
    q := db.Model(&Order{}).Limit(limit + 1)

    // 稳定排序：主键确保顺序一致
    if desc {
        q.Order("id DESC")
        if afterID != nil {
            q = q.Where("id < ?", *afterID) // 上一页最后一条的 id
        }
    } else {
        q.Order("id ASC")
        if afterID != nil {
            q = q.Where("id > ?", *afterID)
        }
    }

    var items []Order
    if err := q.Find(&items).Error; err != nil {
        return nil, err
    }

    result := &CursorResult{}
    hasNext := len(items) > limit
    hasPrev := true // 查了 limit+1 条就说明前面还有数据

    if hasNext {
        items = items[:limit]
    } else {
        hasPrev = len(items) > limit/2 // 半经验判断：不足半页说明接近头部
    }

    // 生成 cursor token
    if len(items) > 0 {
        lastID := items[len(items)-1].ID
        firstID := items[0].ID
        if hasNext {
            nextVal := fmt.Sprintf("%d", lastID)
            result.NextCursor = &nextVal
        }
        if hasPrev && (beforeID == nil || *beforeID == 0) {
            prevVal := fmt.Sprintf("%d", firstID)
            result.PrevCursor = &prevVal
        }
    }

    result.Items = items
    return result, nil
}
```

> [!TIP] 游标的高级用法
> - **复合排序**：当需要按非唯一字段排序时，用 `WHERE (created_at, id) > (?, ?)` 实现 tuple 比较——MySQL 支持行值的字典序比较
> - **双向翻页**：同时返回 `nextCursor` 和 `prevCursor`，前端无需维护额外状态
> - **Token 编码**：生产环境中建议将 cursor 加密或签名（如 JWT），防止用户篡改

```sql
-- 按创建时间 + ID 排序的游标翻页（tuple 比较）
-- 上一页最后一条: created_at = '2026-05-15', id = 987654
SELECT * FROM orders
WHERE (created_at, id) < ('2026-05-15 00:00:00', 987654)
ORDER BY created_at DESC, id DESC
LIMIT 20;
```

#### 优缺点对比

| | 游标分页 | 延迟关联 | OFFSET 分页 |
|--|---------|---------|------------|
| 性能 | 🏆 恒定 O(1) | ⭐ 好 | 🔴 随偏移量变差 |
| 支持跳页 | ❌ 不支持 | ✅ 支持 | ✅ 支持 |
| 前端改造 | 传 cursor | 传 offset | 传 offset |
| ORDER BY 要求 | 必须是有序主键/唯一键 | 可接受普通索引 | 任何 ORDER BY |
| 适用场景 | 无限滚动 / 瀑布流 | 后台管理 / Excel 导出 | 小偏移量 (< 1万) |

### 方案三：限制最大页码

最朴素的方案——从业务层面限制深度，从根本上消除深分页问题：

```go
// 前端 UI 限制：瀑布流最多加载 100 页
const MaxPage = 100

func PaginatedQuery(w http.ResponseWriter, r *http.Request) {
    page := parsePage(r)
    if page > MaxPage {
        respondError(w, 400, "结果太多，请添加筛选条件缩小范围")
        return
    }
    // ...正常查询
}
```

```mermaid
flowchart TD
    A["请求翻页"] --> B{page <= MAX?}
    B -->|是| C["正常查询"]
    B -->|否| D["提示: 请添加筛选条件"]
    D --> E["用户加筛选 ↓"]
    E --> B
    
    style C fill:#00D866,color:#fff
    style D fill:#FF9F43,color:#000
```

### 如何选择方案？

三种方案没有绝对优劣，关键在于匹配业务场景。下面的决策树可以快速帮你做出判断：

```mermaid
flowchart TD
    START["你的分页场景"] --> Q1{"需要跳页吗?"}
    Q1 -->|"需要: 后台管理, 报表"| Q2{"偏移量会超过 1 万行?"}
    Q1 -->|"不需要: 无限滚动, 瀑布流"| SOL_A["方案二: 游标分页"]
    Q2 -->|"不会"| SOL_B["传统 OFFSET 即可"]
    Q2 -->|"会"| SOL_C["方案一: 延迟关联"]
    SOL_A --> OPT["组合: 方案三限制最大页码兜底"]
    SOL_C --> OPT

    style SOL_A fill:#00D866,color:#fff
    style SOL_B fill:#54A0FF,color:#fff
    style SOL_C fill:#FF9F43,color:#000
    style OPT fill:#F8C291,color:#000
```

> [!NOTE] 不要追求银弹
> 实际项目中，三种方案经常组合使用。例如：电商后台商品列表用**延迟关联 + 最大页码限制**；C 端商品瀑布流用**游标分页**；数据导出接口用**主键范围分批**。理解原理后，按需混搭才是工程正道。

> [!TIP] 实际工程建议
> - **前台展示**（商品列表、文章列表）：用游标分页，用户体验最好
> - **后台管理**（运营后台、报表导出）：允许 OFFSET 但限制最大页码 + 提供筛选条件
> - **定时任务**（数据同步）：用主键范围扫描 `WHERE id > last_id AND id <= last_id + 1000`
> - **缓存策略**：热点分页数据可配合 Redis SET 或 Bloom Filter 预计算页码边界
>
> 核心思想：**不要让 OFFSET 成为决定性能的唯一变量**。最好的方案往往是结合业务场景的组合拳。

## 关联笔记

- [[hhs/MySQL/02-SQL核心/08-DQL SELECT 全解析]] — LIMIT 基础语法与 OFFSET 的定义
- [[hhs/MySQL/03-索引与查询优化/15-EXPLAIN 完全指南]] — 分页查询的 EXPLAIN 分析
- [[hhs/GORM/06-排序与分页]] — GORM 分页 API 与各方案的 Go 实现