MySQL中的分页
任何优秀的DBA都会告诉你“只选择你需要的数据”。这是最常见的箴言之一，而且有充分的理由支持！我们永远不希望选择数据后又将它丢弃。该原则的一个体现就是，当你不需要所有列时，切勿使用SELECT *语句。通过限制返回的列数，你就只获取所需的数据。
分页也是一种“只选择你需要的数据”的方法。不同的是，这次我们限制的是行数，而不是列数。与其从数据库中提取所有记录，我们只提取一个页面的数据，并显示给用户。
在MySQL中，分页主要有两种方式：offset/limit分页和游标分页。选择哪种方式取决于你的具体用例和应用需求。两种方法并无绝对高下，而是各有优缺点。
确定性排序的重要性
在讨论分页的妙处之前，我们需要谈谈确定性排序（deterministic ordering）。当查询使用确定性排序时，这意味着MySQL具备足够的信息，每次都能以完全一致的顺序排列结果集。如果你按一个非唯一列对行进行排序，MySQL可以自行决定如何返回这些行的顺序。以下是一个示例。
假设有一个充满名叫Aaron的人的表：
| id | first_name | last_name |
|---|---|---|
| 1 | Aaron | Francis |
| 2 | Aaron | Smith |
| 3 | Aaron | Jones |
现在我们运行一个查询，根据first_name排列这些行：
SELECT *
FROM people
ORDER BY first_name
由于所有人员的first_name都相同，MySQL可以自行决定如何返回行的顺序！具体顺序可能因某些因素而变化。这是因为排序不足够确定性（不够具体）。
以下这组结果是有效的，因为它按first_name排序：
| id | first_name | last_name |
|---|---|---|
| 2 | Aaron | Smith |
| 1 | Aaron | Francis |
| 3 | Aaron | Jones |
而这组结果也是有效的，因为它也按first_name排序：
| id | first_name | last_name |
|---|---|---|
| 3 | Aaron | Jones |
| 2 | Aaron | Smith |
| 1 | Aaron | Francis |
我们没有给MySQL足够的指令来生成一个确定性排序的结果集。我们要求按first_name排序，MySQL已经忠实地执行了操作，但返回的行顺序可能不同。
生成确定性排序的最简单方法是按一个唯一列排序，因为每个值都不重复，MySQL只能每次都以相同顺序返回行。当然，如果你需要按非唯一列排序，这种做法并不适用！在这种情况下，可以在排序中附加一个唯一列来解决问题。通常，添加id列是最好的选择。
SELECT *
FROM people
ORDER BY first_name, id -- 添加 ID 以保证确定性排序
现在MySQL知道，在遇到两个相同的first_name值时，它应该查看id列以决定顺序。这就是确定性排序，它是实现有效分页的前提条件。
Offset/Limit分页
Offset/Limit分页可能是MySQL中最常见的分页方式，因为它最简单易用。利用这种分页方式，我们可以使用两个SQL关键字：OFFSET和LIMIT。LIMIT告诉MySQL需要返回多少行，而OFFSET告诉MySQL需要跳过多少行。
SELECT *
FROM people
ORDER BY first_name, id
LIMIT 10 -- 只返回10行
OFFSET 10 -- 跳过前10行
在这个示例中，我们从people表中选择所有用户，按first_name和id排序，然后限定结果集为10行，同时跳过前10行，返回第11-20行。
要构建一个Offset/Limit查询，你需要知道页面大小（page size）以及页面编号（page number）。页面大小是你每页想显示的记录数量，而页面编号是你想展示的页面。LIMIT由页面大小决定，而OFFSET由页面大小和页面编号决定。
计算正确的OFFSET时，你可以用以下公式：
OFFSET = (page_number - 1) * page_size
这样，当用户位于第一页时，OFFSET计算为0，意味着没有跳过任何行。
完整的查询示例如下：
SELECT *
FROM people
ORDER BY first_name, id
LIMIT 10 -- 页面大小
OFFSET 10 -- (page_number - 1) * page_size
提示
如果你更喜欢视频内容，我们也提供了关于Offset/Limit分页的概述视频。
Offset/Limit分页的优点
Offset/Limit分页的一个显著优点是实现起来简单易懂。它不需要长期维护任何状态；每个请求都是独立的。你不需要关心用户之前访问了哪些页面。查询构造始终保持一致。数学计算简单，查询结构也很直观。
另一个优点是，页面直接可寻址。如果用户想从页面1直接跳到页面10，只要你的接口提供页面链接，便很容易实现。（游标分页无法做到这一点。）
Offset/Limit分页的缺点
数据漂移问题（Drifting Pages）
Offset/Limit分页的一个显著缺点是页面可能会发生漂移。这一问题在游标分页中也存在，但Offset/Limit分页更容易发生。
我们来看一个例子。假设用户正在浏览页面1，页面包含10条记录。他们在页面1看到的最后一个人是”Judge Bins”，而页面2的第一条记录应该是”Sonya Dickens”。
页面1的记录：
| id | first_name | last_name |
|---|---|---|
| 1 | Phillip | Yundt |
| 2 | Aaron | Francis |
| 3 | Amelia | West |
| 4 | Jennifer | Becker |
| 5 | Macy | Lind |
| 6 | Simon | Lueilwitz |
| 7 | Tyler | Cummerata |
| 8 | Suzanne | Skiles |
| 9 | Zoe | Hill |
| 10 | Judge | Bins |
页面2的记录（紧接页面1）：
| id | first_name | last_name |
|---|---|---|
| 11 | Sonya | Dickens |
| 12 | Hope | Streich |
| 13 | Kristian | Kerluke |
| 14 | Stanton | Fisher |
| 15 | Rasheed | Little |
但是，当用户正在浏览页面1时，某个记录被删除了，比如id为2的”Aaron Francis”被删除：
更新后的页面1记录：
| id | first_name | last_name |
|---|---|---|
| 1 | Phillip | Yundt |
| 3 | Amelia | West |
| 4 | Jennifer | Becker |
| 5 | Macy | Lind |
| 6 | Simon | Lueilwitz |
| 7 | Tyler | Cummerata |
| 8 | Suzanne | Skiles |
| 9 | Zoe | Hill |
| 10 | Judge | Bins |
更新后的页面2记录：
| id | first_name | last_name |
|---|---|---|
| 11 | Sonya | Dickens |
| 12 | Hope | Streich |
| 13 | Kristian | Kerluke |
由于用户无法直接感知行被删除的变化，他们跳转到页面2时会直接跳过”Sonya Dickens”。Sorry，Sonya。用户无法看到她，除非他们回退到页面1。
这种行为在处理不断变化的数据时非常常见。如果你的用例能够容忍这一问题，那么Offset/Limit分页或许仍是一个适当的选择。不过即使游标分页也会发生类似问题，但发生的概率较低。
性能缺陷
Offset关键字的工作原理是舍弃结果集中的前n行，而非直接跳过这些行进行定位。实际上，它需要读取这些行并丢弃它们。这意味着当你分页较深时，查询性能会显著下降，因为数据库必须读取并丢弃更多行。
对于非常深的页面，查询可能需要数秒才能完成加载。这是Offset/Limit分页的一个重大问题，也正是游标分页被广泛使用的原因之一。游标分页没有这种性能缺陷，因为它不依赖OFFSET。
使用延迟联结优化性能
针对Offset/Limit分页，有一种称为延迟联结（Deferred Join）的技术可以优化性能。
延迟联结是一种分页优化解决方案，它优先在子查询中过滤出一部分数据，然后再将这部分数据与原始表进行联结。这种延迟操作可以避免直接对整个表进行分页，从而提高查询效率。
示例查询：
SELECT *
FROM people
INNER JOIN (
-- 仅对一个子查询进行分页，而不是对整个表分页
SELECT id FROM people ORDER BY first_name, id LIMIT 10 OFFSET 450000
) AS tmp USING (id)
ORDER BY first_name, id
这种技术已经被广泛采用，并在流行的Web框架中有相关库支持，比如Rails中的FastPage和Laravel中的FastPaginate。
对比延迟联结与标准Offset/Limit分页的性能，我们可以看到延迟联结在处理深度页面时的优势。以下是一个性能对比图（来自介绍FastPage的博客文章）：
| 深度页面数 | 标准分页耗时 | 延迟联结耗时 |
|---|---|---|
| 1000 | >5秒 | <1秒 |
| 2000 | >10秒 | 几乎线性性能 |
如果你决定在项目中使用Offset/Limit分页，建议考虑使用延迟联结优化你的查询。
游标分页
现在我们深入了解了Offset/Limit分页的工作原理，接下来聊聊游标分页。游标分页是一种通过“游标”（cursor）决定下一页结果的分页方式。需要注意的是，此处的游标概念与数据库游标不同。在分页上下文中，游标指的是指针、标识符、令牌或定位器。
游标分页的工作原理
游标分页的核心思想是记录用户最后看到的记录，并基于此记录下一批数据。当用户请求下一页数据时，他们需要提供游标信息，我们利用游标构建查询以确定从哪开始返回下一页数据。
与Offset/Limit分页不同的是，游标分页利用WHERE条件来过滤掉用户已经看过的数据，而不是使用OFFSET跳过。
首次分页的简单示例
假设我们有一个用户表，按id逐行分页。当用户请求数据的第一页时，没有游标，因此我们返回前10行：
SELECT *
FROM people
ORDER BY id
LIMIT 10
返回结果如：
| id | first_name | last_name |
|---|---|---|
| 1 | Phillip | Yundt |
| 2 | Aaron | Francis |
| 3 | Amelia | West |
| 4 | Jennifer | Becker |
| 5 | Macy | Lind |
| 6 | Simon | Lueilwitz |
| 7 | Tyler | Cummerata |
| 8 | Suzanne | Skiles |
| 9 | Zoe | Hill |
| 10 | Judge | Bins |
将游标发送到前端：游标通常为用户看到的最后一条记录的标志。在本例中，该游标为id=10。通常游标会进行base64编码，但为了简单起见，我们不做此处理。
返回给前端的数据结构：
{
"next_page": "(id=10)",
"records": [
// 第一页的记录
]
}
当用户请求下一页时，他们需要提供游标信息，服务端利用此游标确定下一页的记录。
高级排序的游标分页
如果需要按多个列排序，游标不仅需要记录最后一条记录的ID，还需记录其他列的排序值。例如如下情况：
假设我们按first_name和id两列排序，用户看到的最后一条记录是(first_name=Aaron, id=25995)，下一页的游标为(first_name=Aaron, id=25995)。查询如下：
SELECT *
FROM people
WHERE
(
(first_name > 'Aaron')
OR
(first_name = 'Aaron' AND id > 25995)
)
ORDER BY first_name, id
LIMIT 10
总结：
游标分页更复杂，但在处理变动数据时非常高效。它能避免Offset/Limit分页带来的性能和漂移问题，但无法直接跳转到具体页面。至于选用哪种方式，依赖具体应用场景和需求。
关注公众号：程序新视界，一个让你软实力、硬技术同步提升的平台
除非注明，否则均为程序新视界原创文章，转载必须以链接形式标明本文链接
本文链接：http://www.choupangxia.com/2025/09/13/pagination-in-mysql/
