跳到主要内容

分页查询优化

问题​

MySQL 的 LIMIT offset, size 在深分页时为什么很慢？有哪些优化方案？

答案​

深分页问题​

-- 第 1 页：很快
SELECT * FROM orders ORDER BY id LIMIT 0, 10;

-- 第 100000 页：非常慢！
SELECT * FROM orders ORDER BY id LIMIT 999990, 10;

为什么慢：LIMIT 999990, 10 实际上 MySQL 会扫描 1000000 行，然后丢弃前 999990 行，只返回最后 10 行。offset 越大越慢。

方案 1：延迟关联（推荐）​

先通过覆盖索引获取主键，再用主键回表查完整数据：

-- ❌ 原始查询：扫描大量行并回表
SELECT * FROM orders ORDER BY id LIMIT 999990, 10;

-- ✅ 延迟关联：子查询走覆盖索引，外层只回表 10 次
SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 999990, 10
) t ON o.id = t.id;

原理：子查询 SELECT id FROM orders ORDER BY id 走主键索引（覆盖索引），不需要回表；外层 JOIN 只对 10 条记录回表，效率大幅提升。

方案 2：游标分页（最佳实践）​

记住上一页最后一条的 ID，下一页从这个 ID 开始：

-- 第 1 页
SELECT * FROM orders WHERE id > 0 ORDER BY id LIMIT 10;
-- 假设最后一条 id = 10

-- 第 2 页（传入上一页最后的 id）
SELECT * FROM orders WHERE id > 10 ORDER BY id LIMIT 10;
-- 假设最后一条 id = 20

-- 第 N 页（传入 last_id）
SELECT * FROM orders WHERE id > :last_id ORDER BY id LIMIT 10;

优势：

  • 每次都走索引范围查询（type: range），不受 offset 影响
  • 无论第几页都同样快

限制：

  • 不能跳页（只能上一页/下一页）
  • 排序字段必须有索引且唯一（否则可能漏数据或重复）

方案 3：WHERE + 条件范围​

如果按时间排序，可以用时间范围替代 offset：

-- ❌ 深分页
SELECT * FROM orders ORDER BY create_time DESC LIMIT 100000, 10;

-- ✅ 转为范围查询（需要前端传入上一页最后一条的 create_time）
SELECT * FROM orders 
WHERE create_time < '2024-01-15 10:30:00'
ORDER BY create_time DESC 
LIMIT 10;
时间可能重复

如果 create_time 有重复值，可能导致分页数据重复或遗漏。解决方案是加上唯一字段作为二级排序：

SELECT * FROM orders 
WHERE (create_time, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY create_time DESC, id DESC
LIMIT 10;

方案 4：子查询取 ID 范围​

-- 利用主键连续性（适用于自增 ID 且数据无大量删除的场景）
SELECT * FROM orders 
WHERE id >= (SELECT id FROM orders ORDER BY id LIMIT 999990, 1)
ORDER BY id 
LIMIT 10;

方案 5：搜索引擎分页​

对于海量数据分页（如商品搜索结果），使用 Elasticsearch 的 search_after：

// Elasticsearch search_after
{
  "query": { "match_all": {} },
  "size": 10,
  "sort": [
    { "create_time": "desc" },
    { "_id": "asc" }
  ],
  "search_after": ["2024-01-15T10:30:00", "doc_id_123"]
}

方案对比​

方案实现复杂度是否支持跳页性能适用场景
延迟关联低✅ 支持中等通用
游标分页中❌ 不支持最好瀑布流、移动端
WHERE 范围中❌ 不支持好按时间排序
ES search_after高❌ 不支持好搜索场景
限制最大页码低✅ 有限支持好后台管理
实际项目建议
  1. 后台管理系统：延迟关联 + 限制最大页码（如最多翻到 100 页）
  2. App/移动端列表：游标分页（上拉加载，不需要跳页）
  3. 搜索结果：ES search_after 或限制只展示前 1000 条

常见面试问题​

Q1: LIMIT 1000000, 10 为什么慢？​

答案：

MySQL 的 LIMIT offset, size 执行过程是：

  1. 根据查询条件和 ORDER BY 扫描数据
  2. 从第 1 行开始逐行扫描，直到第 offset + size 行
  3. 丢弃前 offset 行，返回后 size 行

所以 LIMIT 1000000, 10 实际上扫描了 1000010 行，只返回 10 行。offset 越大，扫描行数越多，性能越差。

Q2: 延迟关联为什么快？​

答案：

SELECT * FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 999990, 10
) t ON o.id = t.id;

子查询 SELECT id FROM orders ORDER BY id 只扫描主键索引（覆盖索引），不需要回表读取完整行数据，速度很快。得到 10 个 ID 后，外层查询只需要精确回表 10 次。

核心：把大量扫描控制在索引层面（覆盖索引），减少回表次数。

Q3: 游标分页有什么局限？​

答案：

  1. 不支持跳页：只能"下一页/上一页"，不能直接跳到第 N 页
  2. 排序字段要求：必须有唯一且有序的排序字段（通常是主键 ID）
  3. 不能用 COUNT 统计总数：总页数需要另外查（或不显示总页数）
  4. 删除数据影响：如果分页过程中数据被删除，可能导致结果缺失

Q4: 如何在不支持跳页的情况下获取第 N 页数据？​

答案：

如果业务必须支持跳页，可以：

  1. 延迟关联：仍然用 LIMIT offset, size 但通过覆盖索引加速
  2. 限制最大页码：Google 搜索也只显示 10 页左右，超过的引导用户缩小搜索条件
  3. 预计算：提前生成分页索引表 (page_no, start_id)

相关链接​