尽管数据库在设计开发中已经对性能进行了优化；但在使用中，我们仍然有必要进行更精准的调优，以达到最佳使用效果。对于SQL调优不同数据库之间有些共性的东西，本文主要针对PostgreSQL介绍，主要内容有：系统改进：
- 主机系统硬件升级
- 数据库系统定向配置
- 使用Vacuum避免膨胀
- 分析查询性能
- 分析查询日志
- 添加索引改善查询性能
配置文件：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 ;
max_connections （最大连接数）：连接是应用与数据库之间的通信方式：应用需要通过连接向数据库发送查询；数据库的内存分配基于连接数，不必要的连接会产生内存陈本。以上只是数据库配置优化中的一部分，其他的还有很多可做。使用Vacuum进行GC清理dead tuplesVacuum 是一种扫描并将dead tuples的位置标记为不再使用，从而使得这些位置可以被再次使用。不做Vacuum意味着系统将无法清除这些dead tuples，造成空间的浪费。因此要在内存分配和连接数之间取得平衡。checkpoint_segments （检查点segements）：检查点是存储有关系统信息的定期操作。默认情况下，检查点将在几个segements之后运行，但根据系统情况，一般可能需要增大此值。
我们将在本文的后面部分讨论如何注销检查点数据，但检查点配置很重要，因为它可能是个很昂贵的操作。通常认为：默认配置执行检查点太频繁，因此通常可能需要增大此值，使检查点不那么频繁。work_mem（工作内存）：如前面所提，内存分配与管理是性能调整的重要部分。如果您的系统执行大量复杂排序，增加排序内存可帮助数据库优化其设置配置。
这允许 PostgreSQL 在执行其排序时在内存中缓存更多数据，而不是对磁盘进行昂贵的调用（磁盘I/O比内存的时间成本高很多）。random_page_cost（随机页时间成本）：此设置实质上是：优化器在到达磁盘之前 读取内存时 应花费时间量。
仅当完成了即将介绍的其他基于计划的优化（如vacuum、index或修改查询及架构）时，才应该更改此设置。
这些dead tuples通常称作bloat；bloat主要来源于被delete、update或者insert的记录。PostgreSQL 默认配置了Vacuum，但就像配置其他参数一样，我们也可以自主配置Vacuum。我们甚至可以在基于每张表配置Vacuum，以便进行更细粒度的优化。 --执行vacuum VACUUM [ ] [ 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 [ ] VACUUM FULL ：它可以回收更多空间，但需要更长的时间并专门锁定表。 此方法还需要额外的磁盘空间，因为它写入表的新副本，并且在操作完成之前不会释放旧副本。通常，只有在需要从表中回收大量空间时，才应使用此项。 VACUUM FREE ：指定 FREEZE 等效于使用将 vacuum_freeze_min_age和vacuum_freeze_table_age参数设置为0执行 VACUUM 。重写表时，始终执行主动冻结，因此在指定 FULL 时，此选项是多余的。 --查看vacuum执行历史 SELECT * FROM pg_stat_user_tables
ANALYZE [ ] [ 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 [ ] EXPLAIN [ ] 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 }log_line_prefix：log_statement：log_statement：log_checkpoints：logging_connection：添加索引改善查询性能如果没有索引，对数据库的每个请求都将导致对整个表进行全面扫描，以查找相关结果。
这在数据集很大时，会非常缓慢，索引就是为解决这个问题而来。就像一本书中的索引一样，索引会向数据库引擎提供有关正在寻找的数据在系统中大致位置的信息。要正确索引我们的系统，需要了解数据以及如何尝试访问它。
这就是为什么我们有可观察性和监控工具，如Retrace 来帮助我们，这很重要。但是，索引并不是免费的，就像每次更新书中内容时需要更新索引，数据库中表每次更新后必须更新索引。索引可以降低查询的成本，但会增加更新的成本。PostgreSQL中的索引类型（根据算法分）有以下5种。
PostgreSQL中，在创建主键和唯一键约束时，将会创建隐式索引；其他的索引需要手动添加。
- B-tree ：适用于快速随机访问及其他大多数情况；
- Hash：适用于快速定位某行；
- GiST
- SP-GiST
- GIN
免责声明：本平台仅供信息发布交流之途，请谨慎判断信息真伪。如遇虚假诈骗信息，请立即举报
举报
