MySQL应对深度分页问题的几种方案对比

一、面试题

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

二、真实业务场景

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

SELECT id, order_no, user_id, amount, status, created_atFROM ordersWHERE status = 1ORDER BY id DESCLIMIT 999900, 10;

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

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

三、方案一：子查询优化

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

SELECT id, order_no, user_id, amount, status, created_atFROM ordersWHERE id IN (    SELECT id    FROM orders    WHERE status = 1    ORDER BY id DESC    LIMIT 999900, 10)ORDER BY id DESC;

创建联合索引：

CREATE INDEX idx_status_idON orders(status, id);

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

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

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

第一次查询：

SELECT id, order_no, user_id, amount, status, created_atFROM ordersWHERE status = 1ORDER BY id DESCLIMIT 10;

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

SELECT id, order_no, user_id, amount, status, created_atFROM ordersWHERE status = 1  AND id < 985000ORDER BY id DESCLIMIT 10;

创建索引：

CREATE INDEX idx_status_idON orders(status, id);

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

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

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

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

SELECT id, order_no, user_id, amount, status, created_atFROM ordersWHERE 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 DESCLIMIT 10;

对应索引：

CREATE INDEX idx_status_created_idON 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	支持复杂搜索	需要维护数据同步	搜索、日志、订单检索

八、面试总结

解决 MySQL 深度分页问题，核心不是简单修改 LIMIT，而是减少数据库需要扫描和丢弃的数据量。

实际项目中通常这样选择：

• 数据量较小：直接使用 LIMIT
• 必须支持页码跳转：使用子查询和覆盖索引
• 连续翻页或无限滚动：使用基于 ID 或时间的游标分页
• 复杂搜索场景：使用 Elasticsearch 的 search_after
• 排序字段不唯一：使用“排序字段 + 主键”构造稳定游标

以上就是MySQL解决深度分页问题的几种方案对比的详细内容，更多关于MySQL解决深度分页问题的资料请关注本站其它相关文章！

您可能感兴趣的文章:

• MySQL深度分页问题的三种解决方法
• 一文教你解决MySQL的深度分页问题
• 如何解决mysql深度分页问题