引言
在处理数据库支持的应用程序时，管理性能是一项持续的任务。运行缓慢的查询可能导致超时，降低用户体验，消耗更多资源，甚至可能根据你支付数据库费用的方式影响你的预算。这些问题使得理解数据库的性能特性变得很重要，以便你能识别并修复有问题的查询。
在本指南中，我们将讨论在 PostgreSQL 数据库中识别低效查询的不同方法。随后，我们将讨论可用于修复慢查询以维护 PostgreSQL 性能的不同技术。
Prisma ORM 中的 PostgreSQL 连接器允许你直接从 JavaScript 和 TypeScript 应用程序管理你的 PostgreSQL 数据库。
了解如何将 Prisma 添加到 现有项目 或 从头开始 。
你可以通过 CLI 快速设置带有生产就绪的 Prisma Postgres 数据库 的 Prisma ORM
然后按照此 指南 构建一个完整的 TypeScript 应用程序。
检查活动查询和进程
在尝试追踪低性能查询时，首先要检查的是当前活动查询和进程的列表。PostgreSQL 通过
pg_stat_activity
视图
提供此数据。
The
pg_stat_activity
视图是 PostgreSQL 累积统计系统中的一个可用视图。它包含每个服务器进程的一行，帮助你查看每个进程当前正在处理什么。
要显示视图中的所有信息，请键入
注意：
使用
\gx
行终止符序列而不是传统的 `;` 分号告诉 PostgreSQL 为当前查询使用扩展输出模式。这会将每个记录的列和相关值垂直而不是水平显示，这在某些情况下可以提高可读性。
输出中有许多字段在查找较慢的查询时会很有帮助。其中一些最相关的包括
-
state：进程的当前状态。列为active的行正在执行。其他状态包括idle（等待新客户端命令的进程）、idle in transaction（在事务上下文中等待命令的进程）和idle in transaction (aborted)（语句导致错误的事务）。 -
query：最近执行的查询。对于活动进程，这将是当前正在执行的查询。 -
usename：与进程关联的用户名。 -
application_name：连接到进程的应用程序名称。 -
datname：用户连接到的数据库名称。 -
wait_event：进程正在等待的事件名称（如果有）。如果进程处于active状态并且存在wait_event，则表示查询当前被系统中的其他部分阻塞。 -
wait_event_type：进程正在等待的事件类别。 -
pid：进程的进程 ID。 -
query_start：对于活动查询，表示当前查询开始的时间戳。 -
xact_start：如果进程正在执行事务，表示当前事务开始的时间戳。
我们可以根据当前上下文的相关列来过滤查询。一个有用的模式是使用
age()
函数来计算查询已运行的时间。例如
这将显示非空闲查询的执行时间、用户名、数据库和查询文本。我们将结果按查询运行时间从长到短排序，并从结果中排除此特定查询。
同样，你可以看到所有非空闲但有等待事件的进程
这可以帮助你查看因系统其他部分（例如，锁竞争）而未当前进展的查询。
检查其他系统统计信息
尽管
pg_stat_activity
视图可能会提供你识别慢查询所需的大部分信息，但查看其他系统统计信息也很有用，以帮助识别额外的优化目标。
查看数据库统计信息
The
pg_stat_database
表包含每个数据库的统计信息
对于我们的目的，一些有趣的列包括
-
blks_read：数据库中读取的磁盘块数。 -
blks_hit：磁盘块在缓冲区缓存中被找到的次数（避免了从磁盘进行慢速读取）。 -
xact_commit：已提交的事务数。 -
xact_rollback：已回滚的事务数。
正如 Data Egret 团队在其博客上所示 ，你可以使用这些原始值来计算有趣的统计数据，例如你的缓存命中率
这可能是很有价值的信息，可以帮助你评估是否可以通过增加数据库集群的 RAM 来获益，以便最常见的查询可以有效地缓存。
查看表统计信息
另一组有用的视图是
pg_stat_all_tables
、
pg_stat_user_tables
和
pg_stat_sys_tables
。
pg_stat_all_tables
视图显示所有数据库的访问统计信息，而另外两个视图则根据表是用户表还是系统表进行过滤。
这些视图中一些有趣的列包括
-
seq_scan：在表上运行的顺序扫描次数。 -
seq_tup_read：顺序扫描返回的行数。 -
idx_scan：针对表运行的索引扫描次数。 -
idx_tup_fetch：通过索引检索的行数。
这些列中的数字可以帮助你评估索引的性能以及你的查询是否有效地使用了它们。如果你发现你的表有许多顺序扫描，你可能会受益于创建可供你最常见查询使用的额外索引。
查看索引命中情况
如果你需要更多关于当前索引的信息，可以查看
pg_stat_all_indexes
、
pg_stat_user_indexes
和
pg_stat_sys_indexes
视图
这些视图提供了关于你的每个索引被使用频率的信息。
idx_scan
列显示索引被扫描的次数。
idx_tup_read
列显示扫描返回的条目数，而
idx_tup_fetch
显示索引扫描返回的总行数。
这些信息有助于你了解何时存在未被查询使用的索引。一旦识别出这些索引，你可以重写查询以利用它们，或者删除未使用的索引以提高写入性能。
查看锁信息
你收集的一些关于慢查询的信息可能指向了锁定问题。你可以通过查询
pg_locks
视图来获取当前所有持有锁的更多信息
输出将提供 PostgreSQL 中所有锁的信息。这有助于你诊断当不同进程请求控制相同对象时可能发生的争用问题。
一些可能帮助你调查问题锁的列包括
启用慢查询日志记录
更轻松地查找长时间运行查询信息的一种方法是启用慢查询日志记录。启用慢查询日志记录允许 PostgreSQL 自动记录任何执行时间超过给定时间的查询。这使你能够收集有关在调查时未执行的慢查询的信息。
检查 PostgreSQL 是否已启用慢查询日志记录
你首先应该做的是验证慢查询日志记录的当前状态。如果慢查询日志记录已启用，你无需进行任何操作。
你可以通过键入以下内容来检查是否启用了慢查询日志记录
如果你检查
short_desc
和
extra_desc
列的值，你将找到允许我们评估日志记录是否当前启用的信息。我们可以看到慢查询日志记录当前*未*启用，因为
setting
列当前设置为
-1
。
现在你了解了当前状态，可以根据需要进行更改。
配置 PostgreSQL 以记录慢查询
在继续之前，需要注意的是，虽然慢查询日志记录非常有用，但它可能会产生额外的性能影响。PostgreSQL 必须执行额外的操作来计时每个查询并将其结果记录到日志中。这可能会影响性能并意外地占用硬盘空间。
始终记录慢查询可能不是一个好主意。相反，在你积极调查问题时启用该功能，并在完成后禁用它。
全局记录慢查询
考虑到这一点，你可以通过修改 PostgreSQL 服务器的配置文件来全局配置慢查询日志记录。你也可以交互式地修改这些值，但在配置文件中设置良好的默认值将使以后交互式调整变得更容易。
打开 PostgreSQL 的配置文件。你可以通过键入以下内容找到当前配置文件的位置
在文件中，搜索
log_min_duration_statement
设置。如果我们的示例输出值是从配置文件中读取的，它将被设置为
-1
以指示该功能当前已禁用。还有许多其他相关设置可以根据你的需要进行调整