PostgreSQL：十五. 性能优化

￡神魔★判官ぃ 2023-03-12 09:16 7阅读 0赞

### 十五. 性能优化 ###

优化简介：

1.  PostgreSQL优化一方面是找出系统的瓶颈，提高PostgreSQL数据库整体的性能；
2.  另一方面，需要合理的结构设计和参数调整，以提高用户操作响应的速度；
3.  同时还要尽可能的节省系统资源，以便系统可以提供更大负荷的服务。

PostgreSQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度。例如：

*  通过优化文件系统，提高磁盘IO的读写速度；
 *  通过优化操作系统调度策略，提高PostgreSQL的在高负荷情况下负载能力；
 *  优化表结构、索引、查询语句等使查询响应更快。

#### 15.1 优化查询 ####

1、分析查询语句EXPLAIN

【例15.1】使用EXPLAIN语句来分析一个查询语句，执行如下语句：

EXPLAIN ANALYZE SELECT * FROM fruits;

2、索引对查询速度的影响

【例15.2】下面是查询语句中不使用索引和使用索引的对比。首先，分析未使用索引时的查询情况，EXPLAIN语句执行如下：

EXPLAIN  SELECT * FROM fruits WHERE f_name='apple';

然后，在fruits表的f\_name字段上加上索引。执行添加索引的语句及结果如下：

CREATE INDEX index_name ON fruits(f_name);

现在，再分析上面的查询语句。执行的EXPLAIN语句及结果如下：

EXPLAIN ANALYZE  SELECT * FROM fruits WHERE f_name='apple';

3、优化子查询

子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。子查询虽然可以使查询语句很灵活，但执行效率不高。执行子查询时，PostgreSQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记 录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

在PostgreSQL中可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用到索引的话，性能会更好。连接之所以更有效率，是因为PostgreSQL不需要在内存中创建临时表来完成查询工作。

#### 15.2 优化数据库结构 ####

1、将字段很多的表分解成多个表

2、增加中间表

3、增加冗余字段

*  设计数据库表时尽量遵循范式理论的规约，尽可能少的冗余字 段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。

4、优化插入记录的速度

*  删除索引
 *  使用批量插入
 *  删除外键约束
 *  禁止自动提交
 *  使用COPY批量导入

5、分析表的统计信息  
PostgreSQL中提供了ANALYZE语句收集表内容的统计信息，然后把结果保存在系统表pg\_statistic 里。  
【例15.3】使用ANALYZ来分析fruits表，执行的语句：

ANALYZE VERBOSE fruits;

#### 15.3 优化PostgreSQL服务器 ####

1、优化服务器硬件

*  配置较大的内存。足够大的内存，是提高PostgreSQL数据库性能的方法之一。内存的速度比磁盘I/0快得多，可以通过增加系统的缓冲区容量，使数据在内存中停留的时间更 长，以减少磁盘I/0。
 *  配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。
 *  合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。
 *  配置多处理器，PostgreSQL是多线程的数据库，多处理器可同时执行多个线程。

2、优化PostgreSQL的参数

*  maintenance\_work\_mem：在装载大量的数据的时候，临时增大maintenance\_work\_mem 配置变量可以改进性能。
 *  checkpoint\_segments：PostgreSQL里面装载大量的数据可以导致检查点操作比平常更加频繁发生。
 *  effective\_cache\_size：此参数代表PostgreSQL能够使用的最大缓存。
 *  max\_connections： max\_connections的目的是防止max\_connections \* work\_mem超出了实际内存大小。
 *  shared\_buffers：postgresql通过shared\_buffers和内核和磁盘打交道，因此应该尽量大，让更多的数据缓存在shared\_buffers 中。
 *  work\_mem：postgresql在执行排序操作时，会根据work\_mem的大小决定是否将一个大的结果集拆分为几个小的和work\_mem查不多大小的临时文件。