使用 SQL 查询时对网站进行分页的最有效方法是什么？
我正在尝试对 SQL 查询的结果进行分页以在网页上使用。 语言和数据库后端是 PHP 和 SQLite。
我正在使用的代码的工作原理如下（页码从 0 开始）
http://example.com /table?page=0
page = request(page)
per = 10 // results per page
offset = page * per
// take one extra record so we know if a next link is needed
resultset = query(select columns from table where conditions limit offset, per + 1)
if(page > 0) show a previous link
if(count(resultset) > per) show a next link
unset(resultset[per])
display results
还有比这更有效的分页方法吗？
我使用当前方法发现的一个问题是，在开始显示结果之前，我必须将所有 10 个（或任意多个）结果存储在内存中。 我这样做是因为 PDO 不保证行计数可用。
发出 COUNT(*) 查询来了解存在多少行，然后将结果传输到浏览器是否更有效？
这是“这取决于表的大小，以及 count(*) 查询是否需要在数据库后端进行全表扫描”、“自己做一些分析”这类问题之一？
I am trying to paginate the results of an SQL query for use on a web page. The language and the database backend are PHP and SQLite.
The code I'm using works something like this (page numbering starts at 0)
http://example.com/table?page=0
page = request(page)
per = 10 // results per page
offset = page * per
// take one extra record so we know if a next link is needed
resultset = query(select columns from table where conditions limit offset, per + 1)
if(page > 0) show a previous link
if(count(resultset) > per) show a next link
unset(resultset[per])
display results
Are there more efficient ways to do pagination than this?
One problem that I can see with my current method is that I must store all 10 (or however many) results in memory before I start displaying them. I do this because PDO does not guarantee that the row count will be available.
Is it more efficient to issue a COUNT(*) query to learn how many rows exist, then stream the results to the browser?
Is this one of those "it depends on the size of your table, and whether the count(*) query requires a full table scan in the database backend", "do some profiling yourself" kind of questions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
我怀疑您的用户等待后端返回十行会是一个问题。 （您可以通过擅长指定图像尺寸、使网络服务器在可能的情况下协商压缩数据传输等来弥补它们）
我认为最初进行 count(*) 对您来说不会很有用。
如果您需要进行一些复杂的编码：当用户查看页面 x 时，使用类似 ajax 的魔法来预加载页面 x+1 以改善用户体验。
关于分页的一般注意事项：
如果用户浏览您的页面时数据发生变化，并且您的解决方案需要非常高的一致性，那么可能会出现问题。 我已经在其他地方写了一条注释。
I doubt that it will be a problem for your users to wait for the backend to return ten rows. (You can make it up to them by being good at specifying image dimensions, make the webserver negotiate compressed data transfers when possible, etc.)
I don't think that it will be very useful for you to do a count(*) initially.
If you are up to some complicated coding: When the user is looking at page x, use ajax-like magic to pre-load page x+1 for improved user experience.
A general note about pagination:
If the data changes while the user browses through your pages, it may be a problem if your solution demands a very high level of consistency. I've writte a note about that elsewhere.
我选择使用 COUNT(*) 两种查询方法，因为它允许我创建直接到最后一页的链接，而其他方法不允许这样做。 首先执行计数还允许我流式传输结果，因此应该可以在内存较少的情况下很好地处理大量记录。
页面之间的一致性对我来说不是问题。 感谢您的帮助。
I've opted to go with the COUNT(*) two query method, because it allows me to create a link directly to the last page, which the other method does not allow. Performing the count first also allows me to stream the results, and so should work well with higher numbers of records with less memory.
Consistency between pages is not an issue for me. Thank you for your help.
在某些情况下，我有一个相当复杂的（9-12 个表连接）查询，返回数千行，我需要对其进行分页。 显然，为了更好地分页，您需要知道结果的总大小。 对于 MySQL 数据库，在 SELECT 中使用 SQL_CALC_FOUND_ROWS 指令可以帮助您轻松实现此目的，尽管对于您这样做是否会更有效尚无定论。
但是，由于您使用的是 SQLite，我建议坚持使用 2 查询方法。 这里是关于此事的非常简洁的线索。
There are several cases where I have a fairly complex (9-12 table join) query, returning many thousands of rows, which I need to paginate. Obviously to paginate nicely, you need to know the total size of the result. With MySQL databases, using the SQL_CALC_FOUND_ROWS directive in the SELECT can help you achieve this easily, although the jury is out on whether that will be more efficient for you to do.
However, since you are using SQLite, I recommend sticking with the 2 query approach. Here is a very concise thread on the matter.
我建议先数一下。 计数（主键）是一个非常有效的查询。
i'd suggest just doing the count first. a count(primary key) is a very efficient query.