与分页相结合的数据库迭代器 [英] Database Iterators in Conjunction with Paging

问题描述

我的问题具有概念性，但对我来说非常重要：

my question is of a conceptual nature but very important to me:

使用数据库时，通常会从包含结果集的查询中获取迭代器。但是：当您想要进行分页时，您希望能够来回移动该结果集。但是，使用迭代器是不可能的。所以这里的天真想法是使用转换列表。但是这又需要O（n）的时间，这可能导致严重的性能问题。

When using databases you typically get an iterator back from the query containing the resultset. However: when you want to do paging, you wanna be able to move that resultset back and forth. this is however not possible using an iterator. so the naive idea here would be to use a conversion to list. but that again takes a time of O(n) which can cause a serious problem performance wise.

我确定必须有一个解决这个问题的方法（除了使用elasticsearch ：D）。
解决此问题的最佳方法是什么？

Im sure there must be a solution to this problem (other than using elasticsearch :D ). What's the best method of approaching this problem?

最好的问候，

Stefan

推荐答案

你想要分页，但你不想做 O（n）将未分页的结果集加载到内存中。足够公平 - 从逻辑上讲，这意味着数据库必须提供分页的块。我认为大多数RDMS数据库都有类似'LIMIT'和'OFFSET'的SQL：

You want paging, but you don't want to do an O(n) loading of your unpaged result set into memory. Fair enough - logically that implies that the database must hand you paged chunks. I think most RDMS databases have something like 'LIMIT' and 'OFFSET' SQL:

select id, name from foo where date > ? LIMIT $start, $page_size;

如果您正在处理MySQL，并且正在编写原始SQL，那么它就是这样的。但是对于像Slick这样的库，你可以

If you were dealing with MySQL, and were writing raw SQL, then it would be something like that. But with libraries such as Slick, you could have

val query = for { 
  d <- Parameter[Date]
  f <- foo if f.date > d
} yield (f.id, f.name)

所以获取所有行未发送你

So to get all rows unpaged you do

query(yesterday).list
// Select id, name from foo

如果你想要分页，那很简单：

And if you want paging, it's simple:

query(yesterday).drop(20).take(5).list
// Select id, name from foo limit 20, 5 ; %% whatever; I suck at SQL and can't remember syntac

                                          %% but you get the point.

这将返回列表（Id，Name），包含5个元素，假设您每页只需要5个元素。这意味着这个子序列将是结果的第5页。

Which will return a list of (Id, Name), of 5 elements, assuming your want only 5 per page. That means this subsequence will be the 5th page of results.

如果不是查询（昨天），这不是你可能会做的事情你在内存中有一个 List 的结果：SLICK为你提供了查询的抽象，查询类型
包含许多通常在集合中找到的有用方法。 .list 方法实际执行最终查询以获得 List [T] （在此示例中列表[（Int，String）] ）但在调用之前你可以点你的结果（通过调用 .take ， drop 等，构建uopn原始查询）并在此示例中，SQL为您执行分页
并且SLICK生成该SQL，因此您只需执行 .take ， .drop 或其他。

Not that this is what you'd likely do if instead of query(yesterday) you had a List of results in memory: SLICK provides you with an abstraction for queries, a Query type that contains many useful methods usually found in collections. The .list method is what actually executes the final query to get you a List[T] (in this example List[(Int, String)]) but before calling it you can 'page' your results (by calling .take, drop etc, that build uopn the original query) and in this example the SQL does the paging for you and SLICK generates that SQL so you just do .take, .drop or whatever.

如果您的模型层利用了SLICK的可组合性：您可以在SLICK中定义
基本查询，而不是编写原始SQL，这些查询可用作其他查询的构建块。

It helps if your model layer takes advantage of SLICK's composability: you define basic queries in SLICK instead of writing raw SQL and these queries are usable as building blocks for others queries.

这篇关于与分页相结合的数据库迭代器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！