偏移/极限性能优化 [英] offset/limit performance optimization

问题描述

我有一个表样结构：

• ID（串行INT）（在此指数）


• 邮政（文字）


• ...


• CreationDate（DATETIME）（在此说明指数）

我需要实现分页。我简单的查询是这样的：

I need to implement pagination. My simple query looks like :

SELECT Id, Post, etc FROM Posts ORDER BY CreationDate desc OFFSET x LIMIT 15

当有几个记录（低于1万）性能有些可以忍受，但在表生长有一个明显的区别。

When there are few records (below 1 mln) performance is somewhat bearable, but when the table grows there is a noticeable difference.

跳绳的事实，有很好的配置像缓存大小，工作存储器，成本，共同纪念，等...什么可以做，以提高性能和什么是使用的Postgres分页的最佳实践DB设置。有类似的东西问<一个href=\"http://stackoverflow.com/questions/6618366/improving-offset-performance-in-postgresql\">here,但我不知道这是否可以在我的情况下被应用了。

Skipping the fact that there is good to configure DB settings like cache size, work memory, cost, shared mem, etc... What can be done to improve the performance and what are the best practices of pagination using Postgres. There is something similar asked here, but I am not sure if this can be applied in my case too.

由于我的编号是自动递增的（所以predictable）我在想其他选项之一是有这样的事情

Since my Id is auto incremented (so predictable) one of the other options I was thinking is to have something like this

SELECT Id, Post...FROM Posts WHERE Id > x and Id < y

不过，这似乎使事情变得复杂，我得记录计数所有的时间，除了它不能保证，我总是会得到15条记录（例如，如果该职位之一已被删除，ID是不直序列了）。

But this seems to complicate things, I have to get the count of records all the time and besides it is not guaranteed that I will always get 15 records(for example if one of the posts has been deleted and Ids are not in "straight" sequence anymore).

我在想CURSOR过，但如果我没有记错光标将保持连接打开，这是不是在我的情况可以接受的。

I was thinking about CURSOR too, but if I am not mistaken CURSOR will keep the connection open, which is not acceptable in my case.

推荐答案

分页是硬;关系数据库模型并不适合大量使用状态滚动短命查询。正如你提到的，资源利用趋于过高。

Pagination is hard; the RDBMS model isn't well suited to large numbers of short-lived queries with stateful scrolling. As you noted, resource use tends to be too high.

您有选择：

• 限制和 OFFSET


• 使用光标


• 复制结果到一个临时表或memcached的或类似的，然后从那里看完


• X＆GT; ID 和限制

• LIMIT and OFFSET
• Using a cursor
• Copying the results to a temporary table or into memcached or similar, then reading it from there
• x > id and LIMIT

这些，我preFER X'GT; ID 与限制。只要记住你看到的最后一个ID，并要求下一个。如果你有一个单调递增序列，这将是简单，可靠和简单的查询这将是有效的。

Of these, I prefer x > id with a LIMIT. Just remember the last ID you saw and ask for the next one. If you have a monotonously increasing sequence this will be simple, reliable, and for simple queries it'll be efficient.

这篇关于偏移/极限性能优化的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！