一、面试题
在电商后台的订单列表中，用户可以按下单时间倒序分页查询。当翻到第 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 | 支持复杂搜索 | 需要维护数据同步 | 搜索、日志、订单检索 |
八、面试总结
解决 MySQL 深度分页问题，核心不是简单修改 LIMIT，而是减少数据库需要扫描和丢弃的数据量。
实际项目中通常这样选择：
-
数据量较小：直接使用
LIMIT - 必须支持页码跳转：使用子查询和覆盖索引
- 连续翻页或无限滚动：使用基于 ID 或时间的游标分页
-
复杂搜索场景：使用 Elasticsearch 的
search_after - 排序字段不唯一：使用“排序字段 + 主键”构造稳定游标
以上就是MySQL解决深度分页问题的几种方案对比的详细内容，更多关于MySQL解决深度分页问题的资料请关注其它相关文章！
