MySQL的深度分页如何优化？

核心概念

深度分页是指LIMIT偏移量很大的分页查询（如LIMIT 1000000, 20），会导致MySQL扫描并丢弃大量数据，性能极差。优化核心是减少扫描行数或避免回表。

一、深度分页的性能问题

问题表现 

-- 浅分页（快）
SELECT * FROM orders ORDER BY id LIMIT 0, 20;
-- 执行时间：0.01秒

-- 深度分页（慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 执行时间：5.8秒

性能差距：580倍！

问题根源

执行流程分析 

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

MySQL内部执行步骤：

1. 扫描主键索引，按顺序读取1000020行
2. 对每一行进行回表，获取完整行数据
3. 丢弃前1000000行
4. 返回最后20行

关键问题：

  • ❌ 需要扫描100万+20行
  • ❌ 每行都要回表查询（100万次随机IO）
  • ❌ 只用最后20行，前100万行全部浪费

EXPLAIN分析 

EXPLAIN SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

结果：

type: index
key: PRIMARY
rows: 1000020   ← 需要扫描的行数
Extra: Using index（只扫描索引）

注意：虽然Extra: Using index，但仍需扫描100万行索引并回表。

二、优化方案（从高到低优先级）

🔥 方案1：主键定位法（最推荐）

原理

记录上一页的最大ID，通过WHERE条件直接定位，避免OFFSET。

实现方式

传统分页：

-- 第1页
SELECT * FROM orders ORDER BY id LIMIT 0, 20;

-- 第2页
SELECT * FROM orders ORDER BY id LIMIT 20, 20;

-- 第50000页（极慢）
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

优化后：

-- 第1页
SELECT * FROM orders ORDER BY id LIMIT 20;
-- 记录最后一条的ID：max_id = 20

-- 第2页
SELECT * FROM orders WHERE id > 20 ORDER BY id LIMIT 20;
-- 记录最后一条的ID：max_id = 40

-- 第50000页（快）
SELECT * FROM orders WHERE id > 999999 ORDER BY id LIMIT 20;
-- 直接从999999开始，无需扫描前100万行

性能对比： | 方案 | 扫描行数 | 执行时间 | |——|———|———| | LIMIT 1000000, 20 | 1000020行 | 5.8秒 | | WHERE id > 999999 LIMIT 20 | 20行 | 0.001秒 |

提升：5800倍！

适用场景

  • ✅ 适合：按ID、时间顺序翻页（如订单列表、消息记录）
  • ❌ 不适合：允许跳页（如直接跳到第1000页）

前端实现 

// 第一页请求
GET /api/orders?limit=20

// 返回数据
{
  "data": [...],
  "next_cursor": 20  // 最后一条的ID
}

// 第二页请求
GET /api/orders?cursor=20&limit=20

// 返回数据
{
  "data": [...],
  "next_cursor": 40
}

🔥 方案2：延迟关联（子查询优化）

原理

先在索引上完成分页，获取主键ID列表，再通过主键ID关联查询完整数据，减少回表次数。

实现方式

优化前：

SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 回表100万次

优化后：

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 1000000, 20
) t ON o.id = t.id;
-- 只回表20次

原理：

1. 子查询：在主键索引上扫描100万行，获取20个ID（无需回表）
2. 外查询：用20个ID进行回表（仅20次随机IO）

性能对比： | 方案 | 回表次数 | 执行时间 | |——|———|———| | 直接LIMIT | 100万次 | 5.8秒 | | 延迟关联 | 20次 | 1.2秒 |

提升：4.8倍

适用场景

  • ✅ 适合：必须支持跳页的场景
  • ✅ 适合：ORDER BY的字段有索引
  • ⚠️ 注意：仍需扫描100万行索引（但无回表）

🔥 方案3：覆盖索引 + 反向查询

原理

通过覆盖索引避免回表，或通过反向查询减少扫描行数。

方式1：覆盖索引 

-- ❌ 需要回表
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- ✅ 覆盖索引（只查索引中的字段）
SELECT id, user_id, create_time FROM orders 
ORDER BY id 
LIMIT 1000000, 20;
-- 索引：(id, user_id, create_time)

优势：

  • 无需回表，只扫描索引
  • 索引通常比数据表小得多

方式2：反向查询（总数已知） 

-- 假设总共100万条数据，查询第50000页（每页20条）
-- 传统方式：从头扫描到第100万行
SELECT * FROM orders ORDER BY id ASC LIMIT 1000000, 20;

-- 优化方式：从尾部反向查询（只扫描40行）
SELECT * FROM (
    SELECT * FROM orders ORDER BY id DESC LIMIT 40
) t ORDER BY id ASC LIMIT 20;

适用场景：

  • ✅ 适合：查询后半部分数据（如最后几页）
  • ⚠️ 需要知道总数

🔥 方案4：使用缓存

方式1：缓存热点分页数据 

// 前端通常只查看前几页，缓存前10页数据
String cacheKey = "orders:page:" + pageNum;
List<Order> result = redis.get(cacheKey);
if (result == null) {
    result = db.query("SELECT * FROM orders LIMIT ?, 20", offset);
    redis.set(cacheKey, result, 300);  // 缓存5分钟
}

方式2：缓存总数和ID映射 

// 缓存所有ID（只有ID，数据量小）
List<Long> allIds = redis.get("orders:all_ids");
if (allIds == null) {
    allIds = db.query("SELECT id FROM orders ORDER BY id");
    redis.set("orders:all_ids", allIds, 3600);
}

// 分页时直接取ID子集，再批量查询
List<Long> pageIds = allIds.subList(offset, offset + 20);
List<Order> result = db.query("SELECT * FROM orders WHERE id IN (?)", pageIds);

🔥 方案5：业务层优化

方式1：禁止深度分页 

// 限制最大页码
if (pageNum > 100) {
    throw new BusinessException("不支持查看超过100页的数据");
}

理由：

  • 实际业务中，用户很少翻到第100页以后
  • 深度分页的业务价值低

方式2：改用”加载更多”模式 

// 不显示页码，只显示"加载更多"按钮
// 前端逐步加载，后端用主键定位法
GET /api/orders?cursor=999999&limit=20

方式3：异步导出大数据 

// 如果确实需要查看大量数据，改用导出功能
@PostMapping("/export")
public void exportOrders() {
    // 异步任务，生成Excel/CSV文件
    asyncTaskService.export("SELECT * FROM orders");
}

三、优化方案对比

方案 扫描行数 回表次数 支持跳页 实现难度 性能提升
原始LIMIT 100万+ 100万+ ✅ ⭐ 基准
主键定位 20 20 ❌ ⭐⭐ ⭐⭐⭐⭐⭐
延迟关联 100万+ 20 ✅ ⭐⭐ ⭐⭐⭐
覆盖索引 100万+ 0 ✅ ⭐⭐⭐ ⭐⭐⭐⭐
反向查询 40 40 ✅ ⭐⭐⭐ ⭐⭐⭐⭐
缓存 0 0 ✅ ⭐⭐⭐⭐ ⭐⭐⭐⭐⭐
禁止深度分页 - - ❌ ⭐ ⭐⭐⭐⭐⭐

四、实战案例

案例：电商订单分页优化

场景：

  • 订单表1000万条数据
  • 用户需要查看自己的历史订单
  • 按时间倒序排列

原始SQL（性能差） 

SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 10000, 20;

性能：

  • 扫描行数：10020
  • 执行时间：0.5秒

优化步骤

步骤1：主键定位法（最优） 

-- 索引：idx_user_time(user_id, create_time)

-- 第一页
SELECT * FROM orders 
WHERE user_id = 1001 
ORDER BY create_time DESC 
LIMIT 20;
-- 记录最后一条的create_time

-- 后续页（无需OFFSET）
SELECT * FROM orders 
WHERE user_id = 1001 AND create_time < '2025-10-15 12:00:00'
ORDER BY create_time DESC 
LIMIT 20;

性能：

  • 扫描行数：20
  • 执行时间：0.01秒
  • 提升：50倍

步骤2：如果必须支持跳页（延迟关联） 

SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders 
    WHERE user_id = 1001 
    ORDER BY create_time DESC 
    LIMIT 10000, 20
) t ON o.id = t.id;

性能：

  • 扫描行数：10020（但无回表）
  • 执行时间：0.12秒
  • 提升：4倍

五、不同场景选择策略

场景1：移动端列表（推荐：主键定位） 

// 移动端通常是"下拉加载更多"模式
@GetMapping("/orders")
public Result list(@RequestParam Long cursor, @RequestParam Integer limit) {
    List<Order> orders = orderService.listByCursor(cursor, limit);
    Long nextCursor = orders.isEmpty() ? null : orders.get(orders.size() - 1).getId();
    return Result.success(orders, nextCursor);
}

场景2：PC端列表（推荐：延迟关联 + 限制页数） 

@GetMapping("/orders")
public Result list(@RequestParam Integer page, @RequestParam Integer size) {
    if (page > 100) {
        throw new BusinessException("最多查看前100页");
    }
    
    int offset = (page - 1) * size;
    List<Order> orders = orderService.listByDelayJoin(offset, size);
    return Result.success(orders);
}

场景3：数据导出（推荐：异步流式处理） 

@PostMapping("/export")
public Result export() {
    // 生成导出任务
    String taskId = exportService.createTask("orders");
    
    // 异步执行（流式查询，避免OOM）
    asyncTaskService.export(taskId, () -> {
        orderRepository.streamAll((order) -> {
            // 逐条写入Excel
        });
    });
    
    return Result.success("导出任务已创建，请稍后下载", taskId);
}

六、常见误区

误区1：以为加了索引就不慢 

-- ❌ 虽然用了索引，但仍需扫描100万行
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;
-- 索引：PRIMARY KEY (id)

正确做法：主键定位或延迟关联。

误区2：盲目增大LIMIT 

-- ❌ 一次查询1万条，内存占用大，传输慢
SELECT * FROM orders LIMIT 0, 10000;

正确做法：分批查询，流式处理。

误区3：忽视业务需求 

-- 用户实际上只看前3页，不需要支持跳到第1000页

正确做法：和产品经理沟通，限制最大页数。

面试答题要点

  1. 问题根源：深度分页需要扫描+丢弃大量数据，回表开销大
  2. 最优方案：主键定位法（避免OFFSET），性能提升百倍以上
  3. 兜底方案：延迟关联减少回表，覆盖索引避免回表
  4. 业务优化：限制最大页数，改用”加载更多”模式
  5. 实战经验：移动端用游标，PC端限制页数，导出用异步

总结

深度分页优化的核心是减少扫描行数和回表次数。最佳方案是主键定位法（性能提升百倍），其次是延迟关联和覆盖索引。在实际业务中，应结合场景选择方案：移动端用游标翻页，PC端限制页数，导出用异步流式处理。面试中能系统讲解多种方案并结合业务场景，体现对MySQL分页机制和业务架构的深刻理解。