MySQL 深分页 LIMIT 100000,10 越往后越慢怎么优化？

Question

仓乃文 · Accepted Answer

MySQL 的 LIMIT offset, size 会先扫描前 offset+size 行，再丢弃前 offset 行，只返回后 size 行。offset 越大，扫描和丢弃的行越多，100 万行数据翻到最后一页可能需要扫描 100 万行。

问题复现：
```sql
-- 第 1 页：快，扫描 10 行
SELECT * FROM orders ORDER BY id LIMIT 0, 10;
-- 第 10000 页：慢，扫描 100010 行，丢弃前 100000 行
SELECT * FROM orders ORDER BY id LIMIT 100000, 10;
```

优化方案：
```sql
-- 方案 1：游标分页（推荐，性能最好）
-- 前端记住上一页最后一条的 id，下次从该 id 开始查
SELECT * FROM orders WHERE id > 100000 ORDER BY id LIMIT 10;
-- 优点：无论翻到第几页都只扫描 10 行
-- 缺点：不能跳页，只能上一页/下一页

-- 方案 2：子查询先查主键（适合必须跳页的场景）
SELECT * FROM orders
WHERE id IN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
);
-- 子查询只查主键走覆盖索引，不回表
-- 外层查询根据主键回表取完整数据

-- 方案 3：延迟关联（方案 2 的 JOIN 写法）
SELECT o.* FROM orders o
INNER JOIN (
    SELECT id FROM orders ORDER BY id LIMIT 100000, 10
) t ON o.id = t.id;
-- 子查询走覆盖索引，外层只回表 10 行

-- 方案 4：限制最大页数
-- 业务上限制只能看前 100 页，超过的用搜索条件缩小范围
-- 搜索引擎、电商商品列表都是这么做的
```

补充说明：
- 游标分页是性能最优的方案，但无法跳转到指定页码
- 子查询/延迟关联适合必须支持跳页的场景，性能提升 10-100 倍
- 没有 ORDER BY 的 LIMIT 结果不确定，必须加排序字段
- 规则：深分页场景优先用游标分页，其次用延迟关联