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

Question

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

我有一张 table tv_watchers自动递增 id , mins_watching_tv , 和 user_id (下面总共 20 行 fiddle )。在这个例子中 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 个 block (我们称之为批处理)，因为我们想按上面的顺序返回 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批量返回正确结果？

Answer 1

1. 像之前一样选择前 6 个，不在 WHERE 中选择任何内容.

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

2. 持续时间 @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;
   

3. 重复 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 。