数据库中的结果分页如何工作？

This is a general question that applies to MySQL, Oracle DB or whatever else might be out there.

这是一个普遍的问题，适用于MySQL，Oracle DB或其他任何可能存在的问题。

I know for MySQL there is LIMIT offset,size; and for Oracle there is 'ROW_NUMBER' or something like that.

我知道MySQL有LIMIT偏移，大小;对于Oracle，有“ROW_NUMBER”或类似的东西。

But when such 'paginated' queries are called back to back, does the database engine actually do the entire 'select' all over again and then retrieve a different subset of results each time? Or does it do the overall fetching of results only once, keeps the results in memory or something, and then serves subsets of results from it for subsequent queries based on offset and size?

但是，当这种“分页”查询被反复调用时，数据库引擎是否实际上重新执行整个“选择”，然后每次检索不同的结果子集？或者它只对结果进行一次整体提取，将结果保存在内存中，然后根据偏移和大小为后续查询提供结果的子集？

If it does the full fetch every time, then it seems quite inefficient.

如果它每次都进行完全获取，那么它似乎非常低效。

If it does full fetch only once, it must be 'storing' the query somewhere somehow, so that the next time that query comes in, it knows that it has already fetched all the data and just needs to extract next page from it. In that case, how will the database engine handle multiple threads? Two threads executing the same query?

如果它只进行一次完全获取，它必须以某种方式“存储”某个地方的查询，以便下次查询进入时，它知道它已经获取了所有数据并且只需要从中提取下一页。在这种情况下，数据库引擎将如何处理多个线程？两个线程执行相同的查询？

I am very confused :(

我很困扰：（

2 个解决方案

#1

Yes, the query is executed over again when you run it with a different OFFSET.

是的，当您使用不同的OFFSET运行查询时，将再次执行查询。

Yes, this is inefficient. Don't do that if you have a need to paginate through a large result set.

是的，这是低效的。如果您需要对大型结果集进行分页，请不要这样做。

I'd suggest doing the query once, with a large LIMIT — enough for 10 or 12 pages. Then save the result in a cache. When the user wants to advance through several pages, then your application can fetch the 10-12 pages you saved in the cache and display the page the user wants to see. That is usually much faster than running the SQL query for each page.

我建议用一个大的LIMIT进行一次查询 - 足够10或12页。然后将结果保存在缓存中。当用户想要浏览多个页面时，您的应用程序可以获取您在缓存中保存的10-12页并显示用户想要查看的页面。这通常比为每个页面运行SQL查询快得多。

This works well if, like most users, your user reads only a few pages and then changes their query.

如果像大多数用户一样，您的用户只读取几页然后更改其查询，则此方法很有效。

Re your comment:

你的评论：

By cache I mean something like Memcached or Redis. A high-speed, in-memory key/value store.

通过缓存我的意思是像Memcached或Redis。高速，内存中的键/值存储。

MySQL views don't store anything, they're more like a macro that runs a predefined query for you.

MySQL视图不存储任何内容，它们更像是为您运行预定义查询的宏。

Oracle supports materialized views, so that might work better, but querying the view would have the overhead of interpreting an SQL query.

Oracle支持物化视图，因此可能更好，但查询视图会产生解释SQL查询的开销。

A simpler in-memory cache should be much faster.

更简单的内存缓存应该更快。

2 个解决方案

#1

更多相关文章

随机推荐