mysql - 没有偏移的基于光标的分页？

对于大型数据集，基于 offset 的分页会变慢，因此更快的方法是使用基于游标的分页。基本上，一个 anchor ，数据库知道从那个点开始查找结果。考虑到这一点，这是我面临的问题:

我有一个带有自动递增 tv_watchers 、 id 和 mins_watching_tv 的表 user_id (下面总共 20 行)。在这个例子中 user_id 将是相同的 1 ，所以无需担心。我们想按看电视的分钟数从高到低排序。

使用此查询可以轻松完成此操作:

SELECT * FROM tv_watchers
ORDER BY mins_watching_tv DESC, id ASC

这将返回按 id 以这种方式排序的 20 个字段的正确顺序:

2, 17, 1, 16, 15, 5, 6, 7, 8, 9, 10, 11, 12, 13, 20, 3, 4, 14, 19, 18

问题是我们想把它分成 5 个块(我们称之为批次)，因为我们想按照上面的顺序返回 5 个结果。为此，我们检索前 6 个结果，将前 5 个返回给用户，并使用第 6 个(如果存在)作为光标( anchor )从以下位置获取下一批:
这将正确返回第一批:

-- (Batch 1) 2, 17, 1, 16, 15, 5
SELECT * FROM tv_watchers
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6

这里的第 6 项是 id 5 ，它有一个 mins_watching_tv 的 60 ，所以由于这是游标，我们用它来获取下一个 6 像这样:

-- (Batch 2) 5, 6, 7, 8, 9, 10
SELECT * FROM tv_watchers
WHERE mins_watching_tv <= 60 OR id=5
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6

这里的第 6 项是 id 10 ，它也有一个 mins_watching_tv 的 60 ，所以由于这是游标，我们用它来获取下一个 6 像这样:

-- (Batch 3 should be) 10, 11, 12, 13, 20, 3
-- (Batch 3 returns incorrectly) 5, 6, 7, 8, 9, 10
SELECT * FROM tv_watchers
WHERE mins_watching_tv <= 60 OR id=10
ORDER BY mins_watching_tv DESC, id ASC
LIMIT 6

但问题是返回的结果不正确，它返回上面评论中看到的不正确的批次 3 id。我确定它与 WHERE 部分有关，它似乎提取了 mins_watching_tv <= 60 部分，但 id=10 部分是为了让数据库知道从 60 分钟和 id 10 的 anchor 提取结果，但这并没有正常工作。

最终的批处理结果应如下所示:

-- (Batch 4) 3, 4, 14, 19, 18

我设置了一个 sql fiddle here 来显示这个问题。我们如何修复查询，使其尊重 mins_watching_tv 的游标组合，并结合 id 批量返回正确的结果？

最佳答案

像你已经做的那样选择你的前 6 个，在 WHERE 中没有任何内容。

SELECT *
       FROM tv_watchers
       ORDER BY mins_watching_tv DESC,
                id ASC
       LIMIT 6;

Duration @duration 和上一步结果最后一行的ID @id 放入WHERE中

SELECT *
       FROM tv_watchers
       WHERE mins_watching_tv < @duration
              OR mins_watching_tv = @duration
                 AND id >= @id
       ORDER BY mins_watching_tv DESC,
                id ASC
       LIMIT 6;

重复 2. 直到结束。

解释:

如果 mins_watching_tv < @duration 我们可以确定，相应的行不在我们之前的结果中，因为 mins_watching_tv 小于我们之前结果的最小 @duration ，我们做了 ORDER BY mins_watching_tv DESC 。

如果 mins_watching_tv = @duration 我们还不知道我们是否已经有了该行。但是当我们另外做了一个 ORDER BY id ASC 时，我们知道我们已经拥有相同 mins_watching_tv 的所有行的 id 小于或等于当前最大 @id (每个 mins_watching_tv )。所以我们只想要 id > @id 的那些行，或者，因为我们还想要重复上一个结果的最后一行， id = @id 。简而言之，就是 id >= @id 。

由于我们想要这两个集合的并集，我们必须分离上述谓词，因此使用 OR 。我们得到(括号只是为了清楚起见，它们不是必需的):

(mins_watching_tv < @duration)
 OR (mins_watching_tv = @duration
     AND id >= @id)

here 是 fiddle 。

关于mysql - 没有偏移的基于光标的分页？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/50335720/