mysql中查询数据时使用LIMIT和OFFSET的组合

OFFSET越大查询越慢，因MySQL需扫描并丢弃前offset行，导致I/O和CPU开销激增；超100万时还可能引发内存不足、数据不一致等问题。

为什么 OFFSET 越大查询越慢

MySQL 在执行 LIMIT offset, size 时，即使只返回 size 行，也必须先跳过前 offset 行——这意味着它得扫描并丢弃那么多行。如果 OFFSET 是 100 万，MySQL 就得读取并跳过 100 万行（哪怕这些行早就在内存里），I/O 和 CPU 开销都会上升，尤其在没有合适索引或数据量大的表上，响应可能从毫秒级变成秒级。

常见错误现象：SELECT * FROM orders ORDER BY created_at DESC LIMIT 1000000, 20 执行缓慢、CPU 占用高、主从延迟加剧。

• 不是所有排序字段都适合做分页依据；created_at 有重复值时，结果可能不稳定（同秒内多条记录，OFFSET 分页会漏或重）
• 使用 WHERE id > ? ORDER BY id ASC LIMIT 20 替代 OFFSET，性能提升通常在 10 倍以上
• 如果业务允许，优先用「游标分页」（cursor-based pagination），即记录上一页最后一条的 id 或复合排序键，避免依赖全局偏移

ORDER BY 必须匹配索引才能高效使用 LIMIT + OFFSET

即使加了索引，如果 ORDER BY 字段和 WHERE 条件没对齐索引顺序，MySQL 仍可能放弃索引而走全表扫描 + filesort，此时 LIMIT 不起加速作用。

例如：表上有联合索引 INDEX (status, created_at)，但你写的是 SELECT * FROM tasks WHERE status = 'pending' ORDER BY updated_at DESC LIMIT 10——updated_at 不在索引中，MySQL 无法利用该索引完成排序，就会回表+排序，再取前 10 行。

• 检查执行计划：EXPLAIN SELECT ...，确认 type 是 range 或 ref，且 Extra 中不含 Using filesort 或 Using temporary
• 若需按多个字段排序，索引字段顺序必须严格匹配 ORDER BY 的顺序（方向一致，除非都是 ASC）
• 覆盖索引能进一步减少回表：比如只需要 id 和 title，就建 INDEX (status, created_at, id, title)

OFFSET 超过 100 万后几乎不可靠

不只是慢的问题。当 OFFSET 极大时，MySQL 可能因内存不足触发临时表、磁盘排序，甚至在高并发下出现查询被 kill、连接超时，或者复制线程卡住。

更隐蔽的风险是数据一致性：如果分页过程中有大量 INSERT/DELETE，OFFSET 分页的结果会出现跳跃或重复（因为行物理位置变动，OFFSET 计算基准已偏移）。

• 线上系统应禁止前端传任意大的 page 参数，后端强制限制 OFFSET 并返回友好提示
• 替代方案不是“优化 SQL”，而是重构分页逻辑：用上次查询的末尾主键值作为下一页起点，例如 WHERE id > 1234567 ORDER BY id ASC LIMIT 20
• 对于搜索类场景，Elasticsearch 或 MySQL 8.0+ 的 WINDOW FUNCTION（如 ROW_NUMBER()）可辅助生成稳定序号，但要注意窗口函数本身不解决 OFFSET 性能问题

SELECT id, title, status
FROM articles 
WHERE status = 'published' 
  AND id > 8765432  -- 上一页最后一条的 id
ORDER BY id ASC 
LIMIT 20;

游标分页没法直接跳转到第 100 页，但对用户连续浏览、无限滚动等场景足够健壮；真正需要随机跳页的后台管理界面，应改用带条件过滤（如时间范围、状态筛选）来缩小数据集，再分页。