3.4 LIMIT 与 OFFSET 分页

3.4 LIMIT 与 OFFSET 分页

本节摘要：LIMIT 限制返回行数，OFFSET 跳过前几行，两者配合做分页。本节讲它们的用法、各 DBMS 的方言差异，以及深分页的性能问题。

读前必看

阅读完本节，你应当能够：

1. 用 LIMIT 限制返回行数
2. 用 OFFSET 做分页
3. 区分各 DBMS 的分页语法
4. 理解深分页的性能问题和解法

概念脉络

一、LIMIT 限制行数

只想要前几行，用 LIMIT：

SELECT * FROM Customers LIMIT 10;   -- 前 10 行
​

常配合 ORDER BY 取"前 N"或"最大/最小 N"：

SELECT * FROM Orders ORDER BY Amount DESC LIMIT 5;  -- 金额最大的 5 笔
​

二、OFFSET 分页

OFFSET 跳过前 N 行，配合 LIMIT 做分页：

-- 第 1 页（每页 10）
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10 OFFSET 0;
-- 第 2 页
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10 OFFSET 10;
-- 第 3 页
SELECT * FROM Customers ORDER BY CustomerID LIMIT 10 OFFSET 20;
​

公式：OFFSET = (页码 - 1) * 每页大小。

三、各 DBMS 方言

分页语法各 DBMS 不同，要查对应的：

-- MySQL 简写：跳过 10 行取 10 行
SELECT * FROM Customers LIMIT 10, 10;
​

四、深分页的性能问题

LIMIT 10 OFFSET 100000 这种深分页很慢——DBMS 要先扫前 100000 行再丢掉只取 10 行，扫描量巨大。

解法是用"游标分页"（keyset pagination）：记住上一页最后一条的排序值，下一页用 WHERE 跳过：

-- 假设上一页最后 CustomerID = 100
SELECT * FROM Customers
WHERE CustomerID > 100
ORDER BY CustomerID LIMIT 10;
​

这样只扫 10 行，深分页也快。代价是只能"上一页/下一页"，不能跳到任意页。

⚠️ 常见坑：深分页用 OFFSET 越翻越慢。后台管理翻几千页慢得没法用。改游标分页，或限制最大页数。

💡 关键直觉：LIMIT/OFFSET 适合浅分页和"跳页"，深分页用游标分页（WHERE + 排序值）。分页必须配稳定 ORDER BY，否则数据可能重复或漏。

核心回顾

• LIMIT 限制返回行数，常配 ORDER BY 取前 N。
• OFFSET 跳过前 N 行，配合 LIMIT 分页，公式 OFFSET=(页码-1)*页大小。
• 方言：MySQL/PG 用 LIMIT OFFSET，SQL Server/Oracle 语法不同。
• 深分页慢：OFFSET 大时扫描量大，改游标分页（WHERE + 排序值）。
• 分页必须配稳定 ORDER BY，最好带唯一列。

第 3 章结束。你已经能写基础查询：选列、过滤、排序、分页。下一章进入高级查询——聚合、JOIN、子查询、集合操作。

常见疑问

*Q1：OFFSET 公式为什么是 (页码-1)每页大小？

因为要跳到第 n 页，得跳过前面 (n-1) 页的所有行。每页 size 行，所以偏移量是 (n-1)*size。比如每页 10 条，第 3 页就是 LIMIT 10 OFFSET 20——跳过前两页共 20 条，取接下来 10 条。

Q2：深分页为什么慢？怎么优化？

OFFSET 100000 时，数据库要先扫描、丢弃前 100000 行再返回，前面的扫描全部白费。数据量越大越慢。优化手段：1) 游标分页（keyset pagination）：记住上一页最后一条的位置，用 WHERE 条件定位；2) 只翻有限页，如限制最多翻到 100 页；3) 对大数据量列表用"加载更多"而非页码跳转。游标分页是后端高频考点，务必理解。

Q3：各数据库分页语法为什么不一样？

因为分页是后加的标准化功能，各厂商先有了自己的实现：MySQL 最早提供 LIMIT，SQL Server 后来用 OFFSET FETCH，Oracle 老版本只能用 ROWNUM。ANSI 标准晚于这些实现，所以形成了方言。应对方法：ORM 帮你屏蔽差异，或写兼容层；面试常考这点，记住三种主流写法即可。

Q4：分页时数据在变化（有人插入/删除）会怎样？

可能错位：第 1 页读完后有人插入了新数据，第 2 页可能重复或漏掉某行。原因是 OFFSET 是"物理位置偏移"，不是"基于快照"。对一致性敏感的分页（如订单列表），可配合 ORDER BY 唯一列 + 游标分页缓解，或用事务/时间戳约束。这是生产环境容易踩的坑。

动手做一做

分页的概念不难，难在理解偏移和深分页的性能。建议动手做几个实验。

第一个实验：在一张有几十行的表上，分别取第一页、第二页、第三页，观察偏移量如何递增，验证"偏移等于页数减一再乘每页大小"这个公式。

第二个实验：在偏移量很小和偏移量很大的两种情况下，观察返回速度。如果数据量不够大，可以先把表复制多份、把行数撑到上万行，再比较深分页和浅分页的耗时差异，你会直观感受到深分页的代价。

第三个实验：模拟"数据在翻页时变化"。先取第一页，然后插入一条新记录，再取第二页，观察是否有行被跳过或重复。这个实验能让你理解为什么分页必须配合稳定的排序。

第四个实验：用游标分页的思路写一条查询——记住上一页最后一条的位置，用条件把它排除在外。你会发现，深分页瞬间变快。

这四个实验做完，分页的公式、深分页的代价、数据变动的影响、游标分页的思路，就都变成你脑子里的具体图景了。这些都是后端开发的高频考点，提前动手体验，面试和工作都能用上。

一句话记忆

分页由两个要素组成：每页多大、从哪开始。深分页慢是因为数据库要白白跳过前面的行，游标分页用条件直接定位，绕开了这个浪费。