SQL 中限制返回记录条数最常用的语法是 LIMIT，它常与 OFFSET 一起出现。很多业务接口看到的形态就是 LIMIT pageSize OFFSET start，或者 LIMIT start, pageSize。但数据库执行这条语句时并不会只读取目标页的若干行，而是需要按照排序规则扫描大量数据，再把前面的行丢弃。理解这个前提，才能判断为什么同一个查询在浅分页时很快，在深分页时突然变慢。

一、LIMIT 与 OFFSET 的执行过程

先看一个常见的分页查询。假设订单表 orders 使用自增主键 id，业务需要按创建时间倒序展示第 100 万页之后的 20 条记录。SQL 可以写成下面这样：

SELECT id, order_no, buyer_id, created_at
FROM orders
ORDER BY id
LIMIT 20 OFFSET 1000000;

这条语句的实际工作方式，是从满足条件的行中按 id 顺序扫描前 1000020 行，只返回最后 20 行。也就是说，数据库虽然最终只给了 20 条数据，却读了 1000020 个索引项。若这些行还需要回表取 order_no、buyer_id、created_at，那么前 100 万行也会产生回表访问。对 InnoDB 这类聚簇索引存储引擎来说，二级索引扫描拿到主键后回表是随机 I/O，代价会随着 OFFSET 增加近似线性上升。

有人会误以为加上 LIMIT 就等于只扫描返回行数，实际上优化器会先按 ORDER BY 获取有序结果，再应用 OFFSET 丢弃头部。只要没有其他过滤条件把扫描范围缩小，数据库就必须从头部一路走到目标位置。这也解释了为什么 EXPLAIN 中 rows 可能仍然很大，而不是显示 20。

二、深分页性能瓶颈与两种主流优化

深分页慢的根因不是 LIMIT 本身，而是大量无效行的排序和回表。第一个思路是让扫描只发生在索引上，避免回表。第二个思路是先用索引定位目标主键，再只对目标主键回表，也就是延迟关联。

以 MySQL 为例，如果只需要返回主键和排序列，可以用覆盖索引：

SELECT id
FROM orders
ORDER BY id
LIMIT 20 OFFSET 1000000;

这条语句只读取二级索引 B+ 树节点，不需要回表，执行时间会比 SELECT * 短很多。但业务通常需要完整行，于是可以改写为延迟关联：

SELECT o.id, o.order_no, o.buyer_id, o.created_at
FROM orders o
INNER JOIN (
    SELECT id
    FROM orders
    ORDER BY id
    LIMIT 20 OFFSET 1000000
) AS tmp ON o.id = tmp.id;

子查询只读 id，利用覆盖索引快速跳过前 100 万行并拿到目标页的 20 个主键，外层再通过主键回表取完整字段。这样把 100 万次随机回表压缩成 20 次随机回表，性能提升通常非常明显。延迟关联的代价是多一次连接，但当页数很深时，回表开销远大于连接开销。

另一种情况是排序列和过滤列不在同一个索引里。比如按 created_at 倒序分页，但表上只有 id 主键索引。可以把排序字段和主键建成联合索引，例如 KEY idx_created_id (created_at, id)，再在子查询中只取这两个字段，避免回表。需要注意的是，如果 created_at 有大量重复值，排序不稳定会导致分页数据重复或遗漏，因此联合索引里最好带上具备唯一性的列。

三、书签分页与限制返回条数的边界处理

延迟关联仍然需要扫描并丢弃大 OFFSET，只是把回表开销降下来。对于不需要跳到任意页的流式列表，更彻底的做法是放弃 OFFSET，改用基于游标的书签分页。做法是记住上一页最后一条记录的排序键值，下一页直接从这个值之后开始取：

SELECT id, order_no, buyer_id, created_at
FROM orders
WHERE id > ? 
ORDER BY id
LIMIT 20;

这里 ? 是上一页最后一条记录的 id。数据库直接通过索引定位到 id 大于该值的位置，扫描 20 行后停止，不受页码深度影响。书签分页适合 App 信息流、聊天记录、操作日志等只提供上一页和下一页的场景。它的限制是无法直接跳转到第 N 页，并且当排序字段不是唯一时，需要把游标设计成多列组合，例如 (created_at, id) 共同决定位置。

限制返回条数不只是加一个 LIMIT，还要防止参数过大导致异常。很多系统会在 Service 层限制 pageSize 最大值，例如只允许 1 到 100。SQL 里也可以加参数绑定，但不要把用户传入的页码和条数直接拼接进 SQL。以 Java 的 PreparedStatement 为例，应使用 setMaxRows 或校验参数：

int page = Math.max(request.getPage(), 1);
int size = Math.min(request.getSize(), 100);
String sql = "SELECT id, order_no FROM orders ORDER BY id LIMIT ? OFFSET ?";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setInt(1, size);
ps.setInt(2, (page - 1) * size);

这里把 size 限制在 100，避免一次返回过多行导致网络和内存压力。对于不同数据库，限制行数的语法略有差异：MySQL、PostgreSQL、SQLite 使用 LIMIT 和 OFFSET；SQL Server 使用 OFFSET ... FETCH NEXT ... ROWS ONLY；Oracle 12c 以后也可以使用 FETCH FIRST ... ROWS ONLY。但核心思路相同：排序加跳过行数，底层都需要扫描一定范围。

四、工程实践中的建议与避坑

在真实项目中，分页查询变慢不一定只是 SQL 层的责任。首先要确认 ORDER BY 是否真的用上了索引。如果排序字段不在索引前缀里，数据库可能使用 filesort，这种情况下即使页数不深，查询也会因为排序而变慢。执行计划里出现 Using filesort 时需要检查索引设计，优先让过滤和排序都能被索引覆盖。

其次，避免无限翻页的产品形态。很多管理后台提供总页数和跳页输入框，用户可能直接跳到第 500 页甚至更远。此时除了延迟关联，还可以结合缓存热门页、限制最大页码、禁用直接跳页等方式降低数据库压力。若必须保留任意跳页，建议在前端展示估算总行数而不是精确 COUNT，因为 SELECT COUNT(*) 在千万级表上也可能消耗数秒。

第三，注意锁和事务隔离级别的影响。分页查询通常不需要用 SELECT ... FOR UPDATE，应使用普通快照读。如果每翻一页都开启一个长事务持有一致性视图，undo log 会膨胀，间接影响写入性能。读多写少场景可以把一致性要求降低，或者使用从库承接分页查询。

最后，评估是否真的需要那么深的分页。很多列表访问集中在第一页到第五页，后续页面点击量极少。针对这种长尾查询，可以单独做异步导出或搜索引擎分页。如果需要毫秒级响应的深分页，应优先采用基于时间或主键的范围过滤，而不是 OFFSET。把分页条件从全表范围缩窄到某个业务时间窗口，再配合索引，通常能获得最好的稳定性。