原文链接：https://dzone.com/articles/counting-faster-with-postgres
作者: Zach Naimon
计数查询可能是关系数据库中最常用的聚合查询。计数是许多 CRUD 应用程序（如排序、分页和搜索）所需的基本操作。但是随着数据集的增长，计数记录可能会变得非常缓慢。幸运的是，有一些策略可以解决这个问题。本文将向您展示一些方法。
数据设置
由于我们只是在探索计数查询，因此我们不需要大量的数据设置。我们可以创建一个只有一列的简单表。您可以使用以下命令执行此操作：
CREATE TABLE sample_data (id int);
INSERT INTO sample_data SELECT generate_series(1, 200000);
简单计数查询
让我们了解一个简单计数查询的查询计划。
这将在表上运行基本的 Seq Scan。
要了解有关扫描的一般信息，您可能还会发现这篇文章很有帮助。
计数的并行处理
PostgreSQL 不使用并行处理，因为行数太少，并且使用并行工作器可能会减慢计数过程。因此，让我们在示例表中插入更多数据。
INSERT INTO sample_data SELECT generate_series(200000, 4000000);
SET max_parallel_workers_per_gather = 4;
然后尝试分析一下方案，如下图：
并行查询是不费吹灰之力的。他们将硬件用于解决问题，以实现更快的查询执行。但是在事务数据库中，我们不能简单地依赖并行查询——正如你在这个例子中看到的，它仍然需要 342 毫秒。
为什么索引对普通计数没有帮助
任何数据库用户都会做的第一件事就是添加索引以加快查询速度。让我们在这里试试。
CREATE INDEX id_btree ON sample_data USING BTREE(id)
与其他查询不同，索引在这里没有帮助。
这是因为计数必须触及表格的所有行。如果有 where 子句，索引会有所帮助，但扫描索引会很慢。我们可以理解，当我们关闭 Seq scan。
SET enable_seqscan = OFF;
BTree 数据结构中的节点计数需要 O(n)，其中 n 是行数，并且还需要额外的内存 - O(h)，其中 h 是树的高度。直接访问索引也会增加访问成本，因此对普通元组/表数据进行顺序扫描是可取的。
用 Where 子句计数
一旦涉及到 where 子句，那么规划器就很容易使用索引，因为它会从结果中减少很多行。让我们考虑一个完整索引和一个部分索引。
完整索引
由于我们已经有一个完整的索引，因此在 id 列上带有 where 子句的计数查询会非常快。
SELECT COUNT(id) FROM sample_data WHERE id = 200000
部分索引
如果我们要仅使用特定的 where子句计算行数，那么我们可以使用部分索引。
CREATE INDEX id_btree ON sample_data USING BTREE(id) WHERE id = 200000;
部分索引更快，由于它们的大小更易于缓存，并且更易于维护。
不同计数与重复计数
默认情况下，计数查询会计算所有内容，包括重复项。让我们来谈谈哪个经常与计数一起使用。
SELECT DISTINCT(id) FROM sample_data
此命令使用仅索引扫描，但仍需要大约 3.5 秒。速度取决于许多因素，包括基数、表的大小以及索引是否被缓存。现在，让我们尝试计算表中唯一行的数量。
SELECT COUNT(DISTINCT(id)) FROM sample_data
此命令不使用索引,并诉诸顺序扫描。作为规划者，您选择顺序扫描是正确的，因为完全遍历索引的成本更高，正如我们在上面已经看到的那样。
关系数据库系统的经验法则是永远不要处理不同的查询。当然，我们永远无法完全摆脱，但是通过适当的数据库建模（将重复的行移动到不同的表并使用一对多关系，单独存储唯一计数等），带有 where 子句的查询将更快。
近似计数
在大多数实际用例中，我们永远不需要表的确切行数。我们可能想用 where 子句（可以用索引来解决）来计算特定的行数，但从不计算完整的行数。典型的 OLTP 工作负载一天不会增加一百万行。可能有几千行分布在不同的时间窗口中。 PostgreSQL 中有一些方法可以获得近似的行数而不是实际的行数，并且仍然可以满足业务用例。
使用统计数据获得近似计数
PostgreSQL 中基于成本的查询计划器使用统计信息来计划查询。例如，我们可以利用这些信息来给出近似的行数。
SELECT
reltuples::bigint AS estimate
FROM
pg_class
WHERE
oid = 'public.sample_data' ::regclass;
这将返回行计数为4000001。我们还可以运行全计数查询来验证此行计数是否准确。这在生产工作负载中可能不准确，具体取决于 VACCUM ANALYZE 的运行时间。我们将在下一节中看到这一点。
使统计数据保持最新
讨论 Vaccuum 流程的内部结构超出了本博客的范围，但对于大多数 OLTP 工作负载，默认的 Auto Vacuum 设置工作正常。如果表上有任何巨大的数据负载，如果需要，我们可以手动进行真空分析 <table_name>。真空过程执行它的命名。它使用最新和最准确的信息清理和更新统计表。
重新思考问题和外部解决方案
保持准确的表计数不一定是单独的数据库问题。可以使用各种其他方式来保持计数。让我们通过几个简单的想法。
在应用程序级别使用缓存
如果应用程序是一个简单的两层应用程序，只有 UI 和某种形式的后端，那么我们可以使用缓存层（例如 EH Cache 或缓存工具）来维护插入时的行数。这些缓存可以由持久性支持，这样数据就不会丢失。缓存是轻量级的并且非常快。或者，可以将计数存储在数据库本身中。这种方法的关键特性是更新计数的触发器是应用程序的责任。
在触发器/hook的帮助下使用变量
使用触发器或钩子，我们可以在 Postgres 表中或系统外部维护一个变量。选择此策略取决于您的数据库的设置方式，并且通常适用于具有许多下游系统以供使用的应用程序。一般来说，触发器或钩子更可靠，适用于更复杂的应用。
像 Redis 一样的缓存
在许多服务与数据库通信的微服务架构中，存储许多表的所有行计数相关信息将成为瓶颈，因为可能有数百个微服务和许多数据库。它也可能导致与同步相关的问题。
Redis 是一个适合这种 N 层架构的系统。这是：
- 快速地。
- 线程安全
- 可扩展（使用分片）
- 持久（可以启用）
所有单个服务都可以根据 Saga 模式或 API 组合模式调用 Redis 来更新值。
它在微服务领域被广泛用作缓存，但我们必须记住，将更多的外部系统引入图片会导致学习曲线、维护和故障排除增加。如果您没有复杂的 N 层系统，最好使用更直接的解决方案。
结论
我们详细了解了 count 和 distinct 查询是如何在表面下运行的，以及如何使它们变得更快。总结一下：
- 如果您需要相当准确的计数，请进行近似计数。
- 顺序扫描非常慢并且会给数据库带来压力。
- 并行扫描可用于加速顺序扫描。
- 使用完整索引或部分索引可以更快地计算特定用例。
- 如果维护计数的用例要求很高，则可以使用外部解决方案。
