基于游标的无偏移分页？

y3bcpkx1 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(324)

对于大型数据集， offset 基于指针的分页变得很慢，因此一种更快的方法是使用基于指针的分页。基本上是一个锚定点，数据库知道从该点开始查找结果。有鉴于此，我面临的问题是：
我有一张table 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个结果（如果存在）作为光标（定位点）从中获取下一批结果：这将正确返回第一批：

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

第六项是身份证 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

第六项是身份证 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

但是问题是返回的结果是不正确的，它返回了上面评论中看到的不正确的batch3id。我相信这和 WHERE 部分，它似乎拿起了 mins_watching_tv <= 60 部分，但是 id=10 第二部分是让数据库知道从60分钟和id 10的锚定点获取结果，但这并不能正常工作。
最终批次结果应如下所示：

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

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

sql mysql database-cursor

来源：https://stackoverflow.com/questions/50335720/cursor-based-pagination-without-offset

2条答案

按热度按时间

pbgvytdp1#

像以前一样选择前6个，但不在列表中选择任何内容 WHERE .

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

持续时间 @duration 身份证呢 @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 .
当我们想要这两个集合的并集时，我们必须分离上面的 predicate ，所以使用 OR . 我们得到（括号只是为了清楚起见，不需要）：

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

这是小提琴。

赞(0）回复(0）举报 2021-06-21

biswetbf2#

我只略读了一下，但我认为你只需要调整你的条件（例如） mins_watching_tv < 60 OR (mins_watching_tv = 60 AND id>=5)

赞(0）回复(0）举报 2021-06-21

我来回答

基于游标的无偏移分页？

2条答案

相关问题

热门标签

最新问答