时间戳

首页 > 解决方案 > 基于光标的分页没有偏移？

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

问题描述

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

我有tv_watchers一个自动递增的表id，mins_watching_tv, 和user_id（下面总共 20 行小提琴）。在这个例子user_id中也是一样的1，所以不用担心。我们想按照看电视的分钟数从高到​​低排序。

使用此查询很容易做到这一点：

SELECT * FROM tv_watchers
ORDER BY mins_watching_tv DESC, id ASC

这将返回 20 个字段的正确顺序，按照我们希望的方式按 id 排序：

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 个（如果存在）作为光标（锚点）来获取下一批结果：这将正确返回第一批：

-- (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_tvof 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_tvof 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 的锚点获取结果，但这不能正常工作。

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

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

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

标签： mysqlsqldatabase-cursor

解决方案

  1. 像你已经做的那样选择你的前 6 个，在WHERE.

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

  2. 上一步结果的最后一行的持续时间@duration和ID@id并放入WHERElike

    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（per mins_watching_tv）。所以我们只想要那些行，id > @id或者，因为我们还希望上一个结果的最后一行重复，id = @id。总之就是id >= @id。

由于我们想要这两个集合的并集，我们必须分离上述谓词，所以使用OR. 我们得到（括号只是为了清楚起见，不需要它们）：

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

这是小提琴。

推荐阅读