MySQL的两种分页方式：Offset/Limit分页和游标分页

对于数据库查询，优秀的开发和DBA始终遵循一个核心原则：只提取需要的数据。这一原则的意义不仅在于优化查询性能，也在于减少传输和处理不必要的数据。比如，除非确实需要所有列，否则应避免使用SELECT *语句，而是选择性地提取所需的列。

同样，这一原则在分页中也得到了完美体现，区别在于分页限制的是行数而非列数。通过分页，我们可以仅向用户展示一个页面的数据，而不是加载整个数据集。

在MySQL中，分页的实现主要有两种方式：Offset/Limit分页和游标分页。选择具体方式通常取决于业务场景和性能需求。这两种方法各有优劣，不存在绝对的最佳选择。下面就带大家一起来分析这两种方式各自的特性。

确定性排序的重要性

讨论分页之前，首先需要确保查询结果具备确定性排序（Deterministic Ordering）。所谓确定性排序，是指查询结果集在任何情况下都按照完全一致的顺序排列。没有确定性排序的结果集可能因数据库的内部行为而返回不同的顺序，给分页带来问题。

看下面的实例，有一个表people，其中有多个名为“Aaron”的人：

id

first_name

last_name

1

Aaron

Francis

2

Aaron

Smith

3

Aaron

Jones

执行以下查询：

因为first_name列的值相同，MySQL可能会以不同的顺序返回这些行。例如，以下两组结果都符合查询条件：

结果集 1：

id

first_name

last_name

2

Aaron

Smith

1

Aaron

Francis

3

Aaron

Jones

结果集 2：

id

first_name

last_name

3

Aaron

Jones

2

Aaron

Smith

1

Aaron

Francis

我们没有给MySQL足够的指令来生成一个确定性排序的结果集。我们要求按first_name排序，MySQL已经忠实地执行了操作，但返回的行顺序可能不同。

生成确定性排序的最简单方法是按一个唯一列排序，因为每个值都不重复，MySQL只能每次都以相同顺序返回行。当然，如果你需要按非唯一列排序，这种做法并不适用！在这种情况下，可以在排序中附加一个唯一列来解决问题。通常，添加id列是最好的选择。

在同一个first_name值情况下，MySQL会进一步查看id列来决定行的顺序，从而实现确定性排序。确保分页的前提是查询结果的排序必须具有完全确定性，否则分页结果可能会出现问题。

Offset/Limit分页

Offset/Limit分页可能是MySQL中最常见的分页方式，因为它最简单易用。利用这种分页方式，可以使用两个SQL关键字：OFFSET和LIMIT。LIMIT告诉MySQL需要返回多少行，而OFFSET告诉MySQL需要跳过多少行。

在这个示例中，我们从people表中选择所有用户，按first_name和id排序，然后限定结果集为10行，同时跳过前10行，返回第11-20行。

要构建一个Offset/Limit查询，你需要知道页面大小（page size）以及页面编号（page number）。页面大小是你每页想显示的记录数量，而页面编号是你想展示的页面。LIMIT由页面大小决定，而OFFSET由页面大小和页面编号决定。

计算正确的OFFSET时，你可以用以下公式：

例如，第一页的OFFSET为(1 - 1) * 10 = 0，即不跳过任何行；第二页的OFFSET为(2 - 1) * 10 = 10，即跳过前10行。

完整的查询示例如下：

Offset/Limit分页的优点

Offset/Limit分页的一个显著优点是实现起来简单易懂。它不需要长期维护任何状态；每个请求都是独立的。你不需要关心用户之前访问了哪些页面。查询构造始终保持一致。数学计算简单，查询结构也很直观。

另一个优点是，页面直接可寻址。如果用户想从页面1直接跳到页面10，只要你的接口提供页面链接，便很容易实现。（游标分页无法做到这一点。）

Offset/Limit分页的缺点

数据漂移问题（Drifting Pages）

Offset/Limit分页最大的问题是数据漂移。当数据集发生变动（如新增或删除记录）时，用户可能会看到不一致的页面内容。例如用户浏览页面1和页面2时，某条记录被删除导致页面2缺失此前属于页面内容的数据。这一问题在游标分页中也存在，但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"。用户无法看到她，除非再回退到页面1。

这种行为在处理不断变化的数据时非常常见。如果你的用例能够容忍这一问题，那么Offset/Limit分页或许仍是一个适当的选择。不过即使游标分页也会发生类似问题，但发生的概率较低。

性能缺陷

Offset关键字的工作原理是舍弃结果集中的前n行，而非直接跳过这些行进行定位。实际上，它需要读取这些行并丢弃它们。这意味着当分页较深时，查询性能会显著下降，因为数据库必须读取并丢弃更多行。

对于非常深的页面，查询可能需要数秒才能完成加载。这是Offset/Limit分页的一个重大问题，也正是游标分页被广泛使用的原因之一。游标分页没有这种性能缺陷，因为它不依赖OFFSET。

使用延迟联结优化性能

针对Offset/Limit分页，有一种称为延迟联结（Deferred Join）的技术可以优化性能。

延迟联结是一种分页优化解决方案，它优先在子查询中过滤出一部分数据，然后再将这部分数据与原始表进行联结。这种延迟操作可以避免直接对整个表进行分页，从而提高查询效率。

示例查询：

这种技术已经被广泛采用，并在流行的Web框架中有相关库支持，比如Rails中的FastPage和Laravel中的FastPaginate。

对比延迟联结与标准Offset/Limit分页的性能，可以看到延迟联结在处理深度页面时的优势。

以下是一个性能对比图（来自介绍FastPage的博客文章）：

深度页面数

标准分页耗时

延迟联结耗时

1000

>5秒

<1秒

2000

>10秒

几乎线性性能

如果你决定在项目中使用Offset/Limit分页，建议考虑使用延迟联结优化你的查询。

游标分页

上面已经了解了Offset/Limit分页的工作原理，接下来聊聊游标分页。游标分页是一种通过“游标”（cursor）决定下一页结果的分页方式。需要注意的是，此处的游标概念与数据库游标不同。在分页上下文中，游标指的是指针、标识符、令牌或定位器。

游标分页的工作原理

游标分页的核心思想是记录用户最后看到的记录，并基于此记录下一批数据。当用户请求下一页数据时，需要提供游标信息，利用游标构建查询以确定从哪开始返回下一页数据。

与Offset/Limit分页不同的是，游标分页利用WHERE条件来过滤掉用户已经看过的数据，而不是使用OFFSET跳过。

首次分页的简单示例

假设有一个用户表，按id逐行分页。当用户请求数据的第一页时，没有游标，因此返回前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编码，但为了简单起见，我们不做此处理。

返回给前端的数据结构：

当用户请求下一页时，需要提供游标信息，服务端利用此游标确定下一页的记录。

高级排序的游标分页

如果需要按多个列排序，游标不仅需要记录最后一条记录的ID，还需记录其他列的排序值。例如如下情况：

假设我们按first_name和id两列排序，用户看到的最后一条记录是(first_name=Aaron, id=25995)，下一页的游标为(first_name=Aaron, id=25995)。查询如下：

总结

分页方式的选择需依据具体应用场景与性能要求。如果你的应用允许宽松的精确度或需要支持随机页面访问，Offset/Limit分页可能是不错的选择。然而对于深度分页或大数据场景，游标分页表现更为优秀，尤其是在动态数据集上避免了数据漂移问题。两者并无绝对优劣，最重要的是根据业务需求选择最适合的实现方式。