xnscu

Appearance

优化查询获取总记录数 ​

用户: ​

postgresql在输出页面视图所需数据时包括两个数据,一是特定条件下的总记录数, 二是相同特定条件下的第N页的记录. 比如表t,特定条件usr_id=1, 返回第2页的页面视图, 每页50条记录,对应下面两条语句:

sql
SELECT
  count(*)
FROM t
Where usr_id=1;

和

sql
SELECT
 *
FROM t
Where
usr_id=1
LIMIT
  50
OFFSET
  1;

那么有没有优化的空间? 比如能否在第二条语句的处理过程中就获得该条件下的总记录数?

ChatGPT: ​

在PostgreSQL中，通常需要两个独立的查询来实现你的需求：一个查询用来计算满足特定条件的总记录数，另一个查询用来获取特定页的记录。这种方法虽然直观，但效率并不是最优的，特别是在数据量较大的情况下。

为了优化这种情况，可以考虑以下几种方法：

1. 使用窗口函数 ​

可以在查询数据的同时使用窗口函数来计算总记录数。这样可以在单个查询中获得所需的所有信息，避免了两次访问数据库的开销。例如：

sql
SELECT *, count(*) OVER() AS total_count
FROM t
WHERE usr_id = 1
LIMIT 50 OFFSET 50;  -- 注意这里的 OFFSET 应该是 (页数-1)*每页数量，即第2页的 OFFSET 应该是 50

这个查询在返回第2页数据的同时，会在每条记录中添加一个 total_count 字段，显示满足条件的总记录数。这种方法的优点是只需要执行一次查询，缺点是它会增加返回的数据量，因为每行数据都会包含总数。

2. 使用CTE（公共表表达式） ​

另一种方法是使用公共表表达式（CTE），先计算总数，然后查询具体的页面数据：

sql
WITH Counted AS (
    SELECT *, count(*) OVER() AS total_count
    FROM t
    WHERE usr_id = 1
)
SELECT *
FROM Counted
LIMIT 50 OFFSET 50;

这种方法同样在单个查询中解决了问题，但实际执行效率与上一方法类似，也会增加数据传输的负担。

3. 使用单独的统计表或缓存 ​

如果你的应用经常需要分页和计数，考虑使用触发器或定期作业更新一个单独的统计表，这个表可以存储各个 usr_id 的总记录数。这样，你的查询可以即时获取统计数据，而不必每次都重新计算。

4. 性能考虑 ​

如果数据表非常大，上述方法可能仍然不够高效，特别是在数据频繁更新的情况下。这时可以考虑以下策略：

  • 确保 usr_id 字段上有适当的索引。
  • 分析查询计划，确保查询是通过索引执行的，而不是全表扫描。
  • 考虑数据库的物理设计，如分区表，以提高查询性能。

结论 ​

选择最佳方法取决于你的具体需求，包括数据量大小、查询频率、数据更新频率以及对查询延迟的容忍度。在实际应用中，可能需要在实现简单性和查询性能之间做出权衡。

用户: ​

那这种语句count(*) OVER() AS total_count是每一行都要计算一次总数还是只计算一次总数?

sql
SELECT *, count(*) OVER() AS total_count
FROM t
WHERE usr_id = 1
LIMIT 50 OFFSET 50;

ChatGPT: ​

在使用 count(*) OVER() AS total_count 这种窗口函数的情况下，总数计算实际上只执行一次，而不是每行都重新计算。窗口函数会在查询处理的过程中计算一次总记录数，然后这个计算结果会被复制到查询结果的每一行中。

具体到你的例子：

sql
SELECT *, count(*) OVER() AS total_count
FROM t
WHERE usr_id = 1
LIMIT 50 OFFSET 50;

这里的窗口函数 count(*) OVER() 会对符合 WHERE usr_id = 1 条件的所有记录进行计数，得到一个总数。这个总数会被作为 total_count 字段添加到输出结果的每一行中。即便是使用了 LIMIT 和 OFFSET 子句，窗口函数计算的是整个查询结果集的总记录数，而不仅仅是返回给用户的那部分数据。

因此，尽管结果集中的每一行都包含了总记录数，但总记录数的计算只进行了一次，这对性能的影响较小。这个方法的优势在于它减少了数据库的访问次数，因为你不需要发出两个独立的查询来分别获取总记录数和页面数据。不过，这种方法可能会增加网络传输的数据量，因为每一行数据都会附带相同的总记录数。

用户: ​

解释下count(*) OVER()是什么意思

ChatGPT: ​

在 SQL 中，count(*) OVER() 是一种使用窗口函数（Window Function）来进行聚合计算的表达式。窗口函数允许你在一个结果集的“窗口”（一组行）上执行计算，而不是像传统的聚合函数那样对整个查询结果或分组进行操作。

解释 count(*) OVER() ​

  1. count(*)：这是一个聚合函数，用于计算行的总数。* 表示计算所有行，不考虑行中的具体列值。

  2. OVER()：这个部分定义了窗口函数的“窗口”，即在哪些行上执行 count(*)。当 OVER() 中没有指定任何分区或排序规则时，它默认作用于整个结果集。这意味着 count(*) OVER() 会计算整个查询结果集中的行数。

使用场景 ​

使用 count(*) OVER() 可以在不分组整个查询的情况下，为查询结果的每一行添加一个总行数的列。这在需要同时获取详细记录和统计信息（如总记录数）时非常有用。

示例 ​

假设有一个员工表 employees，你想获取所有员工的列表，并在每行显示当前部门的员工总数。如果不使用窗口函数，你可能需要两个查询：一个查询员工详细信息，另一个查询每个部门的员工总数。但是使用窗口函数，你可以一次性完成：

sql
SELECT
    name,
    department,
    count(*) OVER(PARTITION BY department) AS department_count
FROM
    employees;

在这个例子中，PARTITION BY department 使得 count(*) 在每个部门的上下文中被计算，为每个部门的每个员工提供该部门的员工总数。

总结 ​

count(*) OVER() 提供了一种强大的方式来在单个查询中融合详细数据和聚合数据，极大地增强了 SQL 的灵活性和表达能力。这使得开发者可以避免编写多个查询和在应用层面进行数据合并，从而简化了数据处理过程并可能提高性能。

version 0.2.0