目录
- Offset/Limit分页
- Offset/Limit分页的优点
- Offset/Limit分页的缺点
- 数据漂移问题（Drifting Pa ges）
- 性能缺陷
- 使用延迟联结优化性能
- 游标分页
- 游标分页的工作原理
- 首次分页的简单示例
- 高级排序的游标分页
- 总结
我们没有给mysql足够的指令来生成一个确定性排序的结果集。我们要求按first_name排序，MySQL已经忠实地执行了操作，但返回的行顺序可能不同。
生成确定性排序的最简单方法是按一个唯一列排序，因为每个值都不重复，MySQL只能每次都以相同顺序返回行。当然，如果你需要按非唯一列排序，这种做法并不适用！在这种情况下，可以在排序中附加一个唯一列来解决问题。通常，添加id列是最好的选择。
SELECT * FROM people ORDER BY first_name, id -- 添加 ID 以保证确定性排序
在同一个first_name值情况下，MySQL会进一步查看id列来决定行的顺序，从而实现确定性排序。确保分页的前提是查询结果的排序必须具有完全确定性，否则分页结果可能会出现问题。
Offset/Limit分页
Offset/Limit分页可能是MySQL中最常见的分页方式，因为它最简单易用。利用这种分页方式，可以使用两个SQL关键字：OFFSET和LIMIT。LIMIT告诉MySQL需要返回多少行，而OFFSET告诉MySQL需要跳过多少行。
SELECT * FRandroidOM people ORDER BY first_name, id LIMIT 10 -- 只返回10行 OFFSET 10 -- 跳过前10行
在这个示例中，我们从people表中选择所有用户，按first_name和id排序，然后限定结果集为10行，同时跳过前10行，返回第11-20行。
要构建一个Offset/Limit查询，你需要知道页面大小（pa ge size）以及页面编号（pa ge number）。页面大小是你每页想显示的记录数量，而页面编号是你想展示的页面。LIMIT由页面大小决定，而OFFSET由页面大小和页面编号决定。
计算正确的OFFSET时，你可以用以下公式：
OFFSET = (pa ge_number - 1) * pa ge_size
例如，第一页的OFFSET为(1 - 1) * 10 = 0，即不跳过任何行；第二页的OFFSET为(2 - 1) * 10 = 10，即跳过前10行。
完整的查询示例如下：
SELECT * FROM people ORDER BY first_name, id LIMIT 10 -- 页面大小 OFFSET 10 -- (pa ge_number - 1) * pa ge_size
Offset/Limit分页的优点
Offset/Limit分页的一个显著优点是实现起来简单易懂。它不需要长期维护任何状态；每个请求都是独立的。你不需要关心用户之前访问了哪些页面。查询构造始终保持一致。数学计算简单，查询结构也很直观。
另一个优点是，页面直接可寻址。如果用户想从页面1直接跳到页面10，只要你的接口提供页面链接，便很容易实现。（游标分页无法做到这一点。）
Offset/Limit分页的缺点
数据漂移问题（Drifting Pa ges）
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 | Lueilwpythonitz |
| 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）的技术可以优化性能。
延迟联结是一种分页优化解决方案，它优先在子查询中过滤出一部分数据，然后再将这部分数据与原始表进行联结。这种延迟操作可以避免直接对整个表进行分页，从而提高查询效率。
示例查询：
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/Limipythont分页的工作原理，接下来聊聊游标分页。游标分页是一种通过“游标”（cursor）决定下一页结果的分页方式。需要注意的是，此处的游标概念与数据库游标不同。在分页上下文中，游标指的是指针、标识符、令牌或定位 器。
游标分页的工作原理
游标分页的核心思想是记录用户最后看到的记录，并基于此记录下一批数据。当用户请求下一页数据时，需要提供游标信息，利用游标构建查询以确定从哪开始返回下一页数据。
与Offset/Limit分页不同的是，游标分页利用WHERE条件来过滤掉用户已经看过的数据，而不是使用OFFSET跳过。
首次分页的简单示例
假设有一个用户表，按id逐行分页。当用户请求数据的第一页时，没有游标，因此返回前10行：javascript
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": [
// 第一页的记录
]
}
当用户请求下一页时，需要提供游标信息，服务端利用此游标确定下一js页的记录。
高级排序的游标分页
如果需要按多个列排序，游标不仅需要记录最后一条记录的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分页可能是不错的选择。然而对于深度分页或大数据场景，游标分页表现更为优秀，尤其是在动态数据集上避免了数据漂移问题。两者并无绝对优劣，最重要的是根据业务需求选择最适合的实现方式。
以上就是MySQL的两种分页方式之Offset/Limit分页和游标分页详解的详细内容，更多关于MySQL分页方式Offset/Limit和游标的资料请关注编程客栈(www.devze.com)其它相关文章！
加载中,请稍侯......
精彩评论