SQL LIMIT 优化的几种常见方式

深分页性能差因需扫描跳过offset行；优化核心是减少扫描范围，常用游标分页、延迟关联、覆盖索引及业务层限制。

MySQL 中 LIMIT offset, size 在深分页（比如 LIMIT 1000000, 20）时性能急剧下降，根本原因是 MySQL 需要先扫描并跳过前 offset 行，再取后续数据——即使只返回 20 行，也可能扫描上百万行。优化核心是**减少扫描范围**，而不是“跳着走”。以下是几种经生产验证的常用方式：

游标分页（基于值的分页）

适用于顺序浏览场景（如信息流、日志翻页），要求排序字段有唯一性（如主键 id 或 created_at + id 组合），且已建索引。

• 第一页：SELECT id, title, created_at FROM articles ORDER BY id DESC LIMIT 20
• 第二页（假设上页最后 id 是 98765）：SELECT id, title, created_at FROM articles WHERE id
• 关键点：WHERE 条件必须能命中索引；避免用 或漏掉边界等号，防止重复或跳行

延迟关联（子查询定位主键）

当必须支持任意页码跳转（如后台管理），且无法改用游标时，用覆盖索引快速

拿到 ID，再回表取全量字段。

• 原始慢查询：SELECT * FROM users ORDER BY id LIMIT 100000, 20
• 优化后：SELECT u.* FROM users u INNER JOIN (SELECT id FROM users ORDER BY id LIMIT 100000, 20) t ON u.id = t.id
• 优势：子查询只走主键索引，不回表；外层 JOIN 比全字段排序+跳行快得多
• 前提：ORDER BY 字段必须有索引，否则子查询本身也慢

覆盖索引 + 显式字段选择

让查询完全在索引中完成，避免回表。适合 SELECT 字段固定、数量不多的分页场景。

• 例如按状态和时间分页：SELECT id, status, created_at, amount FROM orders WHERE status = 'paid' ORDER BY created_at DESC LIMIT 20 OFFSET 10000
• 对应联合索引：INDEX idx_status_ctime (status, created_at, id, amount)
• 注意：索引字段顺序要匹配 WHERE 和 ORDER BY 的使用逻辑，非排序字段也放进索引末尾以覆盖 SELECT

业务层兜底与限制

技术优化之外，合理约束使用方式可大幅降低风险。

• 后端对 OFFSET > 10000 的请求直接拒绝，提示“请缩小筛选条件”
• 前端禁用“跳转到第 N 页”输入框，改用“加载更多”或仅提供前后页按钮
• 强制结合筛选（如时间范围、分类、关键词），把结果集从千万级压到万级以内，让分页天然落在浅层