在应用 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 年以来情况发生了变化.您可以使用 窗口函数 在一个查询中获得完整的计数和有限的结果.随 PostgreSQL 8.4 于 2009 年推出.

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:

• 使用 LIMIT/OFFSET 运行查询并获取总行数

( 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.

1. 窗口函数的应用取决于OVER 子句和函数的框架规范.简单的 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;

手册中的详细信息.

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

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

相关:

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

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