MySQL解决深度分页问题的几种方案对比_Mysql

一、面试题

在电商后台的订单列表中，用户可以按下单时间倒序分页查询。当翻到第 10000 页时，sql 查询明显变慢，如何分析和优化？

二、真实业务场景

订单表约有 5000 万条数据，后台查询最近订单：

select id, order_no, user_id, amount, status, created_at
from orders
where status = 1
order by id desc
limit 999900, 10;

limit 999900, 10 并不是直接定位到第 999901 条数据，而是先扫描、排序并跳过前 999900 条记录，再返回 10 条数据。

页码越大，扫描的数据越多，性能越差，这就是深度分页问题。

三、方案一：子查询优化

先利用覆盖索引查询出当前页的主键，再根据主键回表查询完整数据：

select id, order_no, user_id, amount, status, created_at
from orders
where id in (
    select id
    from orders
    where status = 1
    order by id desc
    limit 999900, 10
)
order by id desc;

创建联合索引：

create index idx_status_id
on orders(status, id);

子查询只查询 id，可以尽量利用覆盖索引，减少回表次数。

适合页码分页、需要跳转到指定页码的场景，但深度很大时，子查询仍然需要扫描前面的数据。

四、方案二：基于游标或最大 id 分页

第一次查询：

select id, order_no, user_id, amount, status, created_at
from orders
where status = 1
order by id desc
limit 10;

假设本次返回的最小 id 是 985000，下一页查询：

select id, order_no, user_id, amount, status, created_at
from orders
where status = 1
  and id < 985000
order by id desc
limit 10;

创建索引：

create index idx_status_id
on orders(status, id);

这种方式可以直接从索引位置继续读取，不需要扫描并丢弃前面的数据，性能基本不受页码影响。

但它只适合连续翻页，不适合用户直接跳转到第 10000 页。

五、排序字段不唯一时的写法

如果按照 created_at 倒序排序，多个订单可能拥有相同的创建时间，需要增加 id 作为唯一排序条件：

select id, order_no, user_id, amount, status, created_at
from orders
where status = 1
  and (
      created_at < '2026-07-16 10:00:00'
      or (
          created_at = '2026-07-16 10:00:00'
          and id < 985000
      )
  )
order by created_at desc, id desc
limit 10;

对应索引：

create index idx_status_created_id
on orders(status, created_at, id);

其中 created_at 和 id 组成稳定的排序游标，避免数据重复或遗漏。

六、方案三：使用 elasticsearch

如果业务是商品搜索、订单搜索、日志检索等复杂查询，可以使用 elasticsearch。

传统分页：

{
  "from": 999900,
  "size": 10
}

深度分页时仍然需要维护大量搜索结果，性能会下降。

推荐使用 search_after：

{
  "size": 10,
  "query": {
    "term": {
      "status": 1
    }
  },
  "sort": [
    {
      "created_at": "desc"
    },
    {
      "id": "desc"
    }
  ],
  "search_after": [
    "2026-07-16t10:00:00",
    985000
  ]
}

search_after 需要携带上一页最后一条记录的排序值，适合连续翻页。

七、方案对比

方案	优点	缺点	适用场景
`limit offset,size`	写法简单	页码越大越慢	数据量小
子查询	减少回表数据	深度很大时仍需扫描	需要页码跳转
游标分页	性能稳定	不支持随机跳页	无限滚动、连续翻页
elasticsearch	支持复杂搜索	需要维护数据同步	搜索、日志、订单检索