PostgreSQL 性能优化

https://www.huaweicloud.com/articles/12609889.html

sql优化

优化查询

1. 分析查询语句EXPLAIN

EXPLAIN ANALYZE SELECT * FROM fruits;

• EXPLAIN 使用浅析

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

3. 优化子查询

执行子查询时，PostgreSQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句从临时表中查询记 录。查询完毕后，再撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

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

索引对查询速度的影响

优化数据库结构

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

• 2、增加中间表

• 3、增加冗余字段(合理的加入冗余字段可以提高查询速度, 即空间换时间)

• 4、优化插入记录的速度

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

优化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查不多大小的临时文件。