尽管数据库在设计开发中已经对性能进行了优化；但在使用中，我们仍然有必要进行更精准的调优，以达到最佳使用效果。对于SQL调优不同数据库之间有些共性的东西，本文主要针对PostgreSQL介绍，主要内容有：

系统改进：

主机系统硬件升级
数据库系统定向配置
使用Vacuum避免膨胀

架构改进：

分析查询性能
分析查询日志
添加索引改善查询性能

主机系统硬件升级：

首先，考虑对系统内存进行升级或者扩容，缓存越多则对磁盘的IO越少，那么性能就越好；

其次，如果应用系统于数据库在同一台主机中，可以考虑将两者分离，这个根据具体情况；

数据库系统定向配置：

PostgreSQL有一套默认配置，主要用于兼容和适配大部分常见场景；对于特定业务需要定向精准调优。

配置文件：data/pstgresql.conf

配置方法：

1 可以通过 data/pstgresql.conf 配置文件修改/查看参数，数据库重启后生效；

2 也可以通过命令修改/查看参数： ALTER SYSTEM SET 、 SHOW [name | ALL] ；

--修改配置
ALTER SYSTEM SET 
--查看当前配置
SHOW [name ｜ ALL]
SELECT * FROM pg_settings WHERE pending_restart = true;

3 另外还可以通过工具 pgtune 进行参数调优；

关键调优参数：

max_connections （最大连接数）：连接是应用与数据库之间的通信方式：应用需要通过连接向数据库发送查询；数据库的内存分配基于连接数，不必要的连接会产生内存陈本。因此要在内存分配和连接数之间取得平衡。
checkpoint_segments （检查点segements）：检查点是存储有关系统信息的定期操作。默认情况下，检查点将在几个segements之后运行，但根据系统情况，一般可能需要增大此值。我们将在本文的后面部分讨论如何注销检查点数据，但检查点配置很重要，因为它可能是个很昂贵的操作。
通常认为：默认配置执行检查点太频繁，因此通常可能需要增大此值，使检查点不那么频繁。
work_mem（工作内存）：如前面所提，内存分配与管理是性能调整的重要部分。如果您的系统执行大量复杂排序，增加排序内存可帮助数据库优化其设置配置。这允许 PostgreSQL 在执行其排序时在内存中缓存更多数据，而不是对磁盘进行昂贵的调用（磁盘I/O比内存的时间成本高很多）。
random_page_cost（随机页时间成本）：此设置实质上是：优化器在到达磁盘之前读取内存时应花费时间量。仅当完成了即将介绍的其他基于计划的优化（如vacuum、index或修改查询及架构）时，才应该更改此设置。

以上只是数据库配置优化中的一部分，其他的还有很多可做。

使用Vacuum进行GC清理dead tuples

Vacuum 是一种扫描并将dead tuples的位置标记为不再使用，从而使得这些位置可以被再次使用。不做Vacuum意味着系统将无法清除这些dead tuples，造成空间的浪费。这些dead tuples通常称作bloat；bloat主要来源于被delete、update或者insert的记录。

PostgreSQL 默认配置了Vacuum，但就像配置其他参数一样，我们也可以自主配置Vacuum。我们甚至可以在基于每张表配置Vacuum，以便进行更细粒度的优化。

--执行vacuum
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]

where option can be one of:
    FULL [ boolean ]
    FREEZE [ boolean ]
    VERBOSE [ boolean ]
    ANALYZE [ boolean ]
    DISABLE_PAGE_SKIPPING [ boolean ]
    SKIP_LOCKED [ boolean ]
    INDEX_CLEANUP [ boolean ]
    TRUNCATE [ boolean ]

and table_and_columns is:
   table_name [ ( column_name [, ...] ) ]

VACUUM FULL：它可以回收更多空间，但需要更长的时间并专门锁定表。此方法还需要额外的磁盘空间，因为它写入表的新副本，并且在操作完成之前不会释放旧副本。通常，只有在需要从表中回收大量空间时，才应使用此项。
VACUUM FREE：指定 FREEZE 等效于使用将vacuum_freeze_min_age和vacuum_freeze_table_age参数设置为0执行 VACUUM。重写表时，始终执行主动冻结，因此在指定 FULL 时，此选项是多余的。
 
--查看vacuum执行历史
SELECT * FROM pg_stat_user_tables

一般来说，常规Vacuum是不够的。更频繁的Vacuum可以将bloat降至最低，并确保数据库的高性能。尽管已设置了自动vacuum，但一般需要将Vacuum配置的更激进一些。

分析查询性能

ANALYZE - 收集数据库统计信息；查询计划器使用这些统计信息来确定执行查询的最有效方法。

ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]

where option can be one of:
    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]

and table_and_columns is:
    table_name [ ( column_name [, ...] ) ]

EXPLAIN - 查看语句的执行计划

EXPLAIN [ ( option [, ...] ) ] statement
EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

where option can be one of:

    ANALYZE [ boolean ]
    VERBOSE [ boolean ]
    COSTS [ boolean ]
    SETTINGS [ boolean ]
    BUFFERS [ boolean ]
    TIMING [ boolean ]
    SUMMARY [ boolean ]
    FORMAT { TEXT | XML | JSON | YAML }

分析查询日志

1 收集查询日志，作为性能数据的高质量来源。

配置 PostgreSQL日志参数，来收集目标查询的日志。

log_line_prefix：

log_statement：

log_statement：

log_checkpoints：

logging_connection：

添加索引改善查询性能

如果没有索引，对数据库的每个请求都将导致对整个表进行全面扫描，以查找相关结果。这在数据集很大时，会非常缓慢，索引就是为解决这个问题而来。就像一本书中的索引一样，索引会向数据库引擎提供有关正在寻找的数据在系统中大致位置的信息。要正确索引我们的系统，需要了解数据以及如何尝试访问它。这就是为什么我们有可观察性和监控工具，如Retrace 来帮助我们，这很重要。

但是，索引并不是免费的，就像每次更新书中内容时需要更新索引，数据库中表每次更新后必须更新索引。索引可以降低查询的成本，但会增加更新的成本。

PostgreSQL中的索引类型（根据算法分）有以下5种。PostgreSQL中，在创建主键和唯一键约束时，将会创建隐式索引；其他的索引需要手动添加。

B-tree (默认索引)：适用于快速随机访问及其他大多数情况；
Hash：适用于快速定位某行；
GiST
SP-GiST
GIN

但是，在某些用例中不应使用索引，例如：当使用索引的开销超过算法的好处时，比如在小表中；在对表执行大量批处理update时，也可能看到性能问题：在update这些表时，暂时删除这些表上的索引，之后再还原索引，可能对这些表的update性能很有意义。

参考：

PostgreSQL Performance_Optimization wiki

PostgreSQL Performance Tuning Tutorial

Simple Tips for PostgreSQL Query Optimization

ITPub博客

PostgreSQL SQL调优