MySQL LIMIT 深度分页性能问题与优化

问题根源：MySQL 是怎么执行 LIMIT m,n 的？

核心问题：LIMIT m,n 会先扫描并丢弃前 m 条记录，然后返回 n 条记录。当 m 很大时，前面 m 条记录已经占据大量内存和 IO！

-- 普通分页：跳过前 100000 条，取 10 条 SELECT * FROM orders ORDER BY id LIMIT 100000, 10;

⚠️ 全表扫描

即使有索引，MySQL 也需要从头扫描到第 100010 条记录，才能确定要返回哪 10 条。

⚠️ 回表查询

如果 SELECT 的字段不在索引中，需要先定位索引，再回表查完整数据，消耗翻倍。

⚠️ 内存占用

MySQL 需要将前 100010 条数据加载到内存中排序/过滤，占用大量 buffer pool。

⚠️ 响应时间递增

页码越大，m 值越大，查询时间呈线性增长。越往后翻越慢。

性能对比：OFFSET 越大，越慢

普通 LIMIT m,n

游标分页 (WHERE id > last_id)

查询方式	第 1 页 (0,10)	第 100 页 (990,10)	第 10000 页 (99990,10)	稳定性
LIMIT m,n	~5ms	~50ms	~5000ms+	差
WHERE id > last_id	~5ms	~5ms	~5ms	优秀

优化方案：告别深度分页

✓ 游标分页 (推荐)

利用上一页的最后一条记录的 ID，作为下一页查询的起点。

-- 第一次查询：获取第一页 SELECT * FROM orders ORDER BY id LIMIT 10; -- 返回 last_id = 10 -- 第二次查询：基于游标 SELECT * FROM orders WHERE id > 10 -- 使用上一页最后的ID ORDER BY id LIMIT 10;

优势：无论翻到第几页，查询复杂度始终是 O(1)，性能恒定！

✓ 范围查询分页

将 OFFSET 转换为范围查询，减少扫描行数。

-- 假设每页 20 条，第 5 页就是 81-100 SELECT * FROM orders WHERE id BETWEEN 81 AND 100 ORDER BY id;

前提：需要保证 ID 连续且有序，否则需要额外的排序子查询。

✓ 覆盖索引 + 子查询

先用覆盖索引定位 ID，再回表查完整数据，减少回表次数。

-- 子查询：只查索引列，快速定位 ID SELECT * FROM orders WHERE id IN ( SELECT id FROM orders WHERE created_at > '2024-01-01' ORDER BY created_at DESC LIMIT 990, 10 );

技巧：内层子查询只扫索引，外层查询根据 ID 直接定位数据，减少 IO。

✓ 总数缓存 + 限制页码

对于超大数据量，限制最大翻页深度，或使用瀑布流无限滚动。

-- 方案 A：限制最大翻页深度（如最多 1000 页） SELECT * FROM orders WHERE ... ORDER BY id LIMIT 0, 20 OFFSET 0; -- max offset: 20000 -- 方案 B：前端只允许加载"附近"数据，用游标代替页码 -- 不提供"跳转到第 1000 页"，只提供"上一页/下一页"

执行流程对比

交互演示：模拟 LIMIT 查询

总数据量： OFFSET (m)： LIMIT (n)：

最佳实践总结

1. 优先使用游标分页

记录上一页的最后一条 ID，下一页用 WHERE id > last_id，性能恒定。

2. 确保有合适的索引

分页字段和排序字段应在索引中，避免全表扫描和大量回表。

3. 限制最大翻页深度

对于超大数据量，限制用户能翻到的最大页码，或改用"搜索/筛选"功能。

4. 使用覆盖索引

将 SELECT 字段加入索引，避免回表查询，直接从索引返回数据。

MySQL LIMIT m,n 深度分页性能问题与优化