cs-note/hhs/MySQL/03-索引与查询优化/18-深分页优化.md

tags, create time

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

深分页优化

概述

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

正文

问题复现

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

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

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）

-- 核心思路：先用紧凑的主键索引定位 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，无需回表
• 如果没有此索引，子查询本身也会退化为慢查询

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 中实现延迟关联的分页 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）

-- 首次查询（第一页）
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;

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 实现

// 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），防止用户篡改

-- 按创建时间 + 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万)

方案三：限制最大页码

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

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

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

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

如何选择方案？

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

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 实现