我要评论我们没有给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 * from 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 | 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）的技术可以优化性能。
延迟联结是一种分页优化解决方案，它优先在子查询中过滤出一部分数据，然后再将这部分数据与原始表进行联结。这种延迟操作可以避免直接对整个表进行分页，从而提高查询效率。
示例查询：
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分页可能是不错的选择。然而对于深度分页或大数据场景，游标分页表现更为优秀，尤其是在动态数据集上避免了数据漂移问题。两者并无绝对优劣，最重要的是根据业务需求选择最适合的实现方式。
以上就是mysql的两种分页方式之offset/limit分页和游标分页详解的详细内容，更多关于mysql分页方式offset/limit和游标的资料请关注代码网其它相关文章！
发表评论