请问在应用极限前得到结果计数的最佳方法

Question

在应用极限前得到结果计数的最佳方法当分页浏览来自DB的数据时，您需要知道有多少页将呈现页面跳转控件。目前，我通过运行查询两次，一次是在count()要确定结果的总数，第二次要有一个限制，以获得当前页面所需的结果。这似乎没有效率。有没有更好的方法来确定之前会返回多少个结果？LIMIT申请了？我正在使用PHP和Postgres。

Answer 1

纯SQL

自2008年以来，情况发生了变化。您可以使用窗口函数若要在一个查询中获得完整计数和有限结果，请执行以下操作。(与2009年PostgreSQL 8.4).

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.

角箱*何时OFFSET至少与基本查询中的行数一样大，无排会被归还。所以你也不会full_count..可能的备选办法：

• 运行带有限制/偏移量的查询，并获得行总数

考虑来龙去脉:

1. WHERE条款(及JOIN条件，而不是这里)从基表中筛选符合条件的行。

   (GROUP BY聚合函数就在这里)。

2. 窗口函数的应用考虑了所有符合条件的行(取决于OVER子句和函数的框架规范)。简约count(*) OVER()基于所有行。

3. ORDER BY

   (DISTINCT或DISTINCT ON)

4. LIMIT / OFFSET根据已建立的顺序应用于选择要返回的行。

LIMIT / OFFSET随着表中行数的增加，效率越来越低。如果需要更好的性能，请考虑其他方法：

• 在大表上使用偏移量优化查询

获得最终计票的备选方案

获取受影响行数的方法完全不同(不以前的全部计数OFFSET & LIMIT适用)。Postgres有内部簿记，有多少行受上一条SQL命令的影响。一些客户端可以访问该信息或计数行本身(如psql)。

例如，可以检索plpgsql在执行SQL命令之后立即使用：

GET DIAGNOSTICS integer_var = ROW_COUNT;

手册中的细节。

或者你可以用pg_num_rows在……里面PHP..或者其他客户的类似功能。

有关：

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

Answer 2

正如我所描述的在我的博客上，MySQL有一个名为SQL_CALC_FRED行..这消除了执行两次查询的需要，但它仍然需要完整地执行查询，即使限制子句允许它提前停止。

据我所知，PostgreSQL没有类似的特性。在执行分页(使用IMHO时最常见的限制)时，需要注意的一件事情是：执行“偏移量1000限制10”意味着DB必须获取至少1010行，即使它只给出10行。一个更有表现力的方法是记住您要为前一行排序的行的值(在本例中是第1000行)，并重写如下查询：“.order_row>value_of_1000_thLim10”。优点是“ORDER_ROW”很可能是索引的(如果不是，您就有问题了)。缺点是，如果在页面视图之间添加了新元素，则可能会出现一些不同步的情况(但同样，访问者可能无法观察到这一点，并且可以获得很大的性能增益)。