山己几

MySQL 深度分页优化

4 min read

MySQL 深度分页优化

MySQL 深度分页（如 LIMIT 1000000, 10）面临严重的性能问题，核心原因是大量不必要的数据扫描。本文分析原因并给出多种优化方案。

为什么深度分页慢？

根本原因

SELECT * FROM table ORDER BY id LIMIT 1000000, 10;
  • MySQL 需要扫描 1,000,010 行，然后丢弃前 1,000,000 行
  • 即使有索引，MySQL 也无法直接跳跃到第 1000000 行
  • InnoDB 按**聚簇索引（B+Tree）**顺序扫描，必须遍历到偏移位置

额外开销

  • 回表查询：如果 SELECT *（或包含非索引字段），需要从二级索引回表查找完整数据
  • 文件排序：如果 ORDER BY 的字段不是索引的第一个列，可能还需要 filesort
  • Buffer Pool 污染：大量无用数据被加载到 Buffer Pool

优化方案对比

方案原理支持跳页适用场景
子查询优化（延迟关联）先覆盖索引定位 ID，再 JOIN 回原表✅ 是通用深度分页
游标分页（Cursor Based）传最后 ID WHERE id > last_id❌ 否滚动加载/APP
范围查询WHERE id BETWEEN 1000000 AND 1000010❌ 否已知 ID 范围
预计算偏移映射表定时任务建立偏移→ID映射✅ 是固定排序需求

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

SELECT * FROM table t
INNER JOIN (
    SELECT id FROM table
    ORDER BY id
    LIMIT 1000000, 10
) tmp ON t.id = tmp.id;
  • 内层子查询利用覆盖索引只扫描 id 字段，避免回表
  • 外层按内层返回的 10 个 ID 回表查完整数据
  • 支持跳页

方案二：游标分页（最推荐的做法）

-- 前端传 lastId = 1000000（上一页最后一条的 ID）
SELECT * FROM table
WHERE id > 1000000
ORDER BY id
LIMIT 10;
  • 利用 B+Tree 索引直接定位到 ID=1000000 的位置，然后顺序取 10 条
  • 优缺点：无法跳页；但性能稳定，每页耗时一致
  • 适合：瀑布流、列表滚动、APP 端

方案三：跳页优化 — 偏移量映射

用户请求第 100001 页 → 查映射表 → 偏移量对应的 ID → 范围查询
  • 定时任务维护 (页面号, 偏移ID) 映射关系
  • 页面号变化慢时有效（如文章列表）
  • 仅支持固定排序规则

面试要点

  • 核心矛盾：OFFSET 越大，扫描的数据越多，性能瓶颈在于 扫描-丢弃 模式
  • 不要问”优化 LIMIT OFFSET”，而应问”你的业务是否真的需要跳页？”
  • 如果不是必须跳页：游标分页是性能最佳的方案
  • 如果必须跳页：延迟关联 + 覆盖索引是最通用的方案

参考链接

Graph View

Backlinks