在应用LIMIT之前获得结果计数的最佳方法 [英] Best way to get result count before LIMIT was applied

问题描述

当分页来自数据库的数据时，您需要知道将呈现多少页来呈现页面跳转控件.

When paging through data that comes from a DB, you need to know how many pages there will be to render the page jump controls.

当前，我通过运行两次查询来完成此任务，一次包裹在count()中以确定总结果，第二次使用限制以仅获取我当前页面所需的结果.

Currently I do that by running the query twice, once wrapped in a count() to determine the total results, and a second time with a limit applied to get back just the results I need for the current page.

这似乎效率低下.是否有更好的方法来确定在应用LIMIT之前将返回多少结果?

This seems inefficient. Is there a better way to determine how many results would have been returned before LIMIT was applied?

我正在使用PHP和Postgres.

I am using PHP and Postgres.

推荐答案

纯SQL

自2008年以来，情况发生了变化.您可以使用窗口功能在一次查询中获得全部计数和有限的结果.在 2009年PostgreSQL 8.4中引入.

Pure SQL

Things have changed since 2008. You can use a window function to get the full count and the limited result in one query. Introduced with PostgreSQL 8.4 in 2009.

SELECT foo
     , count(*) OVER() AS full_count
FROM   bar
WHERE  <some condition>
ORDER  BY <some col>
LIMIT  <pagesize>
OFFSET <offset>;

请注意，此可能比没有总数的情况要昂贵得多.必须对所有行进行计数，可能的快捷方式仅从匹配的索引中获取最前面的行可能不再有用.
对于小型表或full_count< = OFFSET + LIMIT没什么关系.对于更大的full_count来说很重要.

Note that this can be considerably more expensive than without the total count. All rows have to be counted, and a possible shortcut taking just the top rows from a matching index may not be helpful any more.
Doesn't matter much with small tables or full_count <= OFFSET + LIMIT. Matters for a substantially bigger full_count.

小写 :当OFFSET至少等于基本查询的行数时， 无行 返回.因此，您也不会得到full_count.可能的选择:

Corner case: when OFFSET is at least as great as the number of rows from the base query, no row is returned. So you also get no full_count. Possible alternative:

• (0.CTE分别进行评估和实现.在Postgres 12或更高版本中，计划者可以在上班之前内联诸如子查询之类的东西.)不在这里.

  ( 0. CTEs are evaluated and materialized separately. In Postgres 12 or later the planner may inline those like subqueries before going to work.) Not here.

  1. WHERE子句(和JOIN条件，尽管在您的示例中没有条件)从基表中筛选出符合条件的行. 其余基于已过滤的子集.
  1. WHERE clause (and JOIN conditions, though none in your example) filter qualifying rows from the base table(s). The rest is based on the filtered subset.

  (2. GROUP BY和聚合函数将放在此处.)不在此处.

  ( 2. GROUP BY and aggregate functions would go here.) Not here.

  (3.根据分组/聚集的列对其他SELECT列表表达式进行求值.)不在此处.

  ( 3. Other SELECT list expressions are evaluated, based on grouped / aggregated columns.) Not here.

  取决于OVER子句和该函数的框架规范，将应用

  1. Window函数.简单的count(*) OVER()基于所有符合条件的行.

  1. Window functions are applied depending on the OVER clause and the frame specification of the function. The simple count(*) OVER() is based on all qualifying rows.

  ORDER BY

  (6. DISTINCT或DISTINCT ON会在这里.)不在这里.

  ( 6. DISTINCT or DISTINCT ON would go here.) Not here.

  根据已建立的顺序应用
  1. LIMIT/OFFSET来选择要返回的行.
  1. LIMIT / OFFSET are applied based on the established order to select rows to return.

  随着表中行数的增加，

  LIMIT/OFFSET的效率越来越低.如果您需要更好的性能，请考虑使用其他方法:

  LIMIT / OFFSET becomes increasingly inefficient with a growing number of rows in the table. Consider alternative approaches if you need better performance:

  • 使用大表上的OFFSET优化查询

  有完全不同的方法来获取受影响的行的计数( 不是 在应用OFFSET和LIMIT之前的完整计数). Postgres具有内部记帐功能，其中最后一个SQL命令影响了多少行.一些客户端可以访问该信息或自己计算行数(例如psql).

  There are completely different approaches to get the count of affected rows (not the full count before OFFSET & LIMIT were applied). Postgres has internal bookkeeping how many rows where affected by the last SQL command. Some clients can access that information or count rows themselves (like psql).

  例如，您可以使用以下命令执行SQL命令后立即在 plpgsql 中检索受影响的行数:

  For instance, you can retrieve the number of affected rows in plpgsql immediately after executing an SQL command with:

  GET DIAGNOSTICS integer_var = ROW_COUNT;
  

  手册中的详细信息.

  或者您可以在 PHP中使用 pg_num_rows .或其他客户端中的类似功能.

  Or you can use pg_num_rows in PHP. Or similar functions in other clients.

  相关:

  • 计算受批处理影响的行数在PostgreSQL中查询

  这篇关于在应用LIMIT之前获得结果计数的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！