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

快来打我* 2024-03-17 00:01 81阅读 0赞

> 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;

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

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

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

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

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

SELECT
        pg_terminate_backend(pid)
    FROM
        pg_stat_activity
    WHERE
        -- 不删除当前连接
        pid <> pg_backend_pid()
        -- 不删除当前连接数据库database_name的连接
        AND datname = 'database_name'
        ;

### 2.版本升级： ###

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

**查看服务器端版本**

#### 2.1 查看详细信息 ####

SELECT version();

#### 2.2 查看版本信息 ####

SHOW server_version;

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

SHOW server_version_num;

**升级为更高的版本。**

### 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';

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

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

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

### 4.合理的索引 ###

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

![在这里插入图片描述][9558ca07fd694ad79602bfd4c75b0afb.png_pic_center]

### 5.优化表设计 ###

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

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

安装如pgTune和pgBadger等与PostgreSQL性能优化的工具，以及如pg\_hint\_plan和pg\_stat\_statements等扩展程序。

[9558ca07fd694ad79602bfd4c75b0afb.png_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/eaa0a8c093074e66bd45cfcbc57be419.png