Navicat 面向 PostgreSQL 查询超时的工具解决方案

Navicat 面向 PostgreSQL 查询超时的工具解决方案

图片

早前，我们发表过一篇《PostgreSQL 与 Navicat ：数据库的中坚力量》 ，从产品的发展介绍了两者的渊源与共性，获得了许多童鞋的认可。而随着PostgreSQL 在国内热度愈发高涨，应用也愈发广泛。近期，我们收到许多用户的问询，涉及一些使用时的技术问题，例如：PostgreSQL 查询延时的优化方法等。今天，小编就带大家解析如何通过 Navicat 工具便捷地跟踪、设置查询超时语句时长和设置权限来确保你的 PostgreSQL 数据库实例保持良好状况和可用性，并保障数据库系统的性能稳定。

查询超时的必要性

查询超时指在执行数据库查询操作时，如果在一定时间内无法完成查询，则会自动终止查询操作并返回错误结果。其目的是为了保护数据库系统的稳定性和性能，并避免查询操作耗尽系统资源。它是数据库系统稳定性和性能的保障措施之一。如果没有设置查询超时，当一个查询操作耗费过多的时间时，系统资源就会不足，影响其他操作和整个系统的运行。因此，设置查询超时时间具有非常重要的必要性。

** **

应用场景

1. 并发操作较多：在高并发的情况下，如果一些查询耗时过长，则可能会影响其他查询的执行效率，进而影响整个系统的响应速度。
2. 大数据量查询：如果查询数据量过大，会消耗大量的系统资源，可能会导致系统崩溃，因此需要设置查询超时时间，防止查询过长时间执行。
3. 防止恶意攻击：对于一些恶意的SQL注入攻击，我们可以设置查询超时时间来防止攻击者通过无限循环查询来耗尽系统资源。

查询分析器 | Navicat Monitor

在 Navicat Monitor 3 监控工具 - 查询分析器画面顶部（如下图），我们设计了一个图表，用以显示等待时间最长的查询：

图片

它能够准确地标识出滞后的查询。这一点非常重要，因为它们可以让一切陷入瘫痪。

除了在标识出慢速查询并对其进行修复外，另一种策略就是全面限制查询执行时间。在PostgreSQL 等专业级数据库中，可以通过设置 statement_timeout 变量来限制整个数据库甚至每个用户的查询执行时间。下面，我们将学习如何在 Navicat 16 for PostgreSQL 中运用这个重要的数据库变量。

在数据库级别设置

statement_timeout 变量

为数据库设置默认语句超时是很常用的方式。这可确保连接到数据库的任何应用程序或人员的查询运行时间都不会超时。合理的默认值建议是 30 秒或 60 秒。但如果你愿意，可以设置更长的时间。以下是将值设置为 60 秒的语句：

ALTER DATABASE mydatabase SET statement_timeout = ‘60s’;

在 Navicat 16 for PostgreSQL 中，我们可以选择主菜单中的“工具”>“服务器监控”>“PostgreSQL”以查看 statement_timeout 变量。你会在“变量”选项卡找到它：

图片

事实上，因为服务器有很多变量，你可能要使用查找工具来找出 statement_timeout 变量。你可以单击“全部高亮显示”切换按钮以更有效地找到匹配的变量。

当然，如果你想直接 show 语句，也可以在 Navicat 轻松实现：

图片

为特定用户设置查询超时

为了更精确地控制，我们可以为特定用户设置查询超时值（总是会有人选择整个数据库…）。这能使用 ALTER ROLE 语句做到，它可以设置许多数据库变量，包括 statement_timeout。

我们尝试创建一个名为“guest”的新用户角色：

图片

现在我们可以使用 ALTER ROLE 语句来限制查询执行时间，如下所示：

ALTER ROLE guest SET statement_timeout=‘5min’;

我们可以查询pg_roles 表来获取关于 statement_timeout 的信息（包括它是如何设置的）：

图片

rolconfig 值是一个数组，因此我们可以使用 unnest 取消嵌套，那么一行会显示一个设置：

图片

** **

结语

为用户标识出滞后的查询是非常重要的，因为它让你对查询时间了如指掌，让你免受数据库性能陷入瘫痪的风险。为此，Navicat Monitor 3 监控工具的查询分析器画面顶部就设计出了这个费时查询图表。

另一种方法是限制查询在超时之前可以执行多久。正如在本文中提到，可以在 PostgreSQL 的数据库、会话甚至单个角色级别设置查询超时。如果你还没有设置 statement_timeout 变量，我们建议你尽快设置。这只是优化数据库性能的其中一步，但它有助于确保你的数据库实例保持良好状况和可用。

结语

如果这篇文章对您有所帮助，或者有所启发的话，求一键三连：点赞、评论、收藏➕关注，您的支持是我坚持写作最大的动力。