深度剖析PostgreSQL慢SQL：原因与优化方案大揭秘_postgresql数据库优化

​

PostgreSQL 是一种成熟稳定的关系型数据库管理系统，它支持高级的数据类型、索引以及查询语言。但是，尽管 PostgreSQL
在性能和可靠性方面表现出色，但偶尔也会出现慢 SQL 的情况。本文将探讨 PostgreSQL 慢 SQL
的原因和优化方案，帮助你更好地优化和管理 PostgreSQL 数据库。

慢SQL 的原因

慢 SQL 的出现可能是由多种不同的因素引起的。下面列出了最常见的一些原因。

1.1. 复杂的查询语句

复杂的查询语句通常会耗费更多的时间和资源，从而导致慢 SQL 的出现。如果查询中包含多个子查询和联合查询，就可能会出现性能瓶颈。

1.2. 缺少索引

在执行 SQL 查询时，数据库需要在表中查找满足条件的数据。如果表中的数据量很大，没有索引会导致查询速度变慢。

1.3. 数据库表过大

当数据库表过大时，读取和写入数据的速度都会变慢。这可能会导致慢 SQL 的出现。

1.4. 内存使用不当

PostgreSQL 使用共享内存来提高其性能。如果内存使用不当，SQL 查询的执行速度就可能变慢。

1.5. 硬件配置不足

如果硬件配置不足，例如 CPU、内存或硬盘容量不足，就可能会导致慢 SQL 的出现。

1.6.版本太低

PostgreSQL 的版本过低，性能可能没有新版本的性能优越！

慢SQL 的解决方案

1.关闭会话

查询慢sql的执行会话，关闭进程。
查看数据库后台连接进程

SELECT count(*) FROM pg_stat_activity;

SELECT * FROM pg_stat_activity;
1
2
3

查看数据库后台连接进程，但是此条SQL不包含当前查询进程

SELECT count(*) FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();

SELECT * FROM pg_stat_activity WHERE NOT pid=pg_backend_pid();
1
2
3

查看当前慢SQL,例如查询执行时间超过1秒的SQL

select * from pg_stat_activity where state<>'idle' and now()-query_start > interval '1 s' order by query_start ; 
1

可以使用**pg_terminate_backend()**终止连接。您必须是超级用户才能使用此功能。这在所有操作系统上都是相同的。

SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    -- 不删除当前连接
    pid <> pg_backend_pid()
    -- 不删除当前连接数据库database_name的连接
    AND datname = 'database_name'
    ;
1
2
3
4
5
6
7
8
9
10

2.版本升级：

将PostgreSQL版本升级到最新版本，以实现更好的性能和功能。

查看服务器端版本

2.1 查看详细信息

SELECT version();
1

2.2 查看版本信息

SHOW server_version;
1

2.3 查看数字版本信息包括小版号

SHOW server_version_num;
1

升级为更高的版本。

3.优化内存和缓存

调整PostgreSQL内存设置，如shared_buffers和effective_cache_size等参数。

要让PostgreSQL达到最佳性能，还要使用像pg_tune这样的PostgreSQL优化工具，可以根据系统的内存大小，I/O和网络性能，来调整PostgreSQL参数。例如常用的shared_buffers和effective_cache_size，它们是与访问文件并维护内存缓存有关的重要参数，可以控制PostgreSQL访问磁盘文件的频繁程度。除此之外，还可以根据测试结果做出改变，例如increasing wal_buffers to improve write performance，这有助于将PostgreSQL写入操作提升到最高水平。

最后，正确的PostgreSQL内核性能调优优化必须包含两个要素：PostgreSQL参数设置以及服务器的配置。因此，对于数据库管理员或性能调优者而言，正确的性能调优优化消耗大量时间，但它也是实现PostgreSQL最佳性能的必要之道。

举例来说，想要提升PostgreSQL的性能，可以使用以下代码：

ALTER SYSTEM SET shared_buffers = '1000MB';

ALTER SYSTEM SET effective_cache_size = '2000MB';

ALTER SYSTEM SET wal_buffers = '12MB';
1
2
3
4
5

一般shared_buffers 值应该被设为整个机器内存的 15% ~ 25%。

effective_cache_size参数有操作系统和数据库评估多少内存可用磁盘缓存，PostgreSQL查询计划决定它是否固定在RAM中。索引扫描最有可能用于较高的值;如果该值为低将使用顺序扫描。建议将effecve_cache_size设置为机器总RAM的50%。

wal buffer是预写日志(wal)缓冲区，缓冲区的默认大小由wal_buffers设置设置—最初为16MB。如果要调优的系统有大量并发连接，那么wal_buffers的值越高，性能越好。

4.合理的索引

数据库表结构，建立合理的索引可以极大的提高查询性能。
注：
可以使用navicat ，选中数据库，右键》》维护》》重建索引

​​

5.优化表设计

将表分成有意义的符合逻辑的、尽可能小和彼此独立的部分，以减少查询中的不必要数据量。

6.安装和使用适当的扩展

安装如pgTune和pgBadger等与PostgreSQL性能优化的工具，以及如pg_hint_plan和pg_stat_statements等扩展程序。

​