postgresql性能优化2：sql语句和缓存配置

1、索引优化

索引尽量建在数据比较分散的列上， 不要在变化很小的字段上加索引，比如性别之类的。

原因就是：
索引本质上是一种空间换时间的操作，通过B Tree这种数据结构减少io的操作次数以此来提升速度。如果在变化很小的字段上建立索引，那么可能单个叶子节点上的数据量也是庞大的，反而增加了io的次数（如果查询字段有包含非索引列，索引命中之后还需要回表）

2、缓存配置

shared_buffers、work_mem、maintenance_work_mem这三个核心内存参数的合理配置需要结合业务场景

在 32GB 内存的服务器上，以下是通用优化建议：

1. shared_buffers（共享缓冲区）

• 作用：PostgreSQL 用于缓存数据页和索引页的内存区域，是数据库性能的核心参数。
• 推荐值：物理内存的 1/4 ~ 1/3（32GB 内存建议设置为 8GB ~ 10GB）。

  shared_buffers = 8GB  # 基础推荐，若系统仅运行PostgreSQL可设为10GB

• 原理：
  过大可能导致系统剩余内存不足（影响操作系统缓存page cache），过小则数据库频繁读写磁盘。32GB 内存中，保留足够空间给操作系统缓存（通常比shared_buffers更大），两者协同提升性能。

2. work_mem（工作内存）

• 作用：单个 SQL 操作（如排序、哈希连接、聚合等）使用的内存，每个连接 / 操作独立占用。
• 推荐值：根据并发量动态调整，通常 16MB ~ 64MB。

  work_mem = 32MB  # 基础推荐

• 计算逻辑：
  公式：work_mem * 最大并发查询数 * 每个查询的操作数 ≤ 剩余内存的50%
  例如：32GB 内存中，扣除shared_buffers（8GB）和系统预留（8GB），剩余约 16GB。若最大并发查询为 20，每个查询最多 3 个排序 / 哈希操作，则：
  work_mem ≤ 16GB / (20 * 3) ≈ 268MB，实际取保守值 32~64MB 避免内存溢出。
• 注意：
  复杂查询（多表连接、大排序）可能消耗更多内存，可通过EXPLAIN ANALYZE查看操作是否溢出到磁盘（Sort Method: external merge表示使用临时文件，需调大work_mem）。

3. maintenance_work_mem（维护工作内存）

• 作用：用于数据库维护操作（如VACUUM、CREATE INDEX、ALTER TABLE等）的内存，单任务独占。
• 推荐值：1GB ~ 2GB（32GB 内存建议 1.5GB）。

  maintenance_work_mem = 1.5GB

• 原理：
  维护操作通常低频但耗资源，调大此值可加速索引创建、大表清理等操作。但需注意：
  • 若同时运行多个维护任务（如并行VACUUM），总消耗可能超过预期，可通过max_parallel_maintenance_workers限制并行数。
  • 不建议超过 4GB（32GB 内存下），避免挤占其他内存资源。

其他辅助配置（重要）

1. max_connections（最大连接数）
   连接数过大会导致内存耗尽（每个连接消耗额外内存），32GB 内存建议：

   max_connections = 100 ~ 200  # 结合业务并发量，超过200建议使用连接池（如pgbouncer）

2. effective_cache_size（有效缓存大小）
   告知 PostgreSQL 操作系统和数据库缓存的总可用内存（用于执行计划优化），建议设为物理内存的 1/2 ~ 3/4：

   effective_cache_size = 20GB  # 帮助优化器选择索引扫描还是全表扫描
   

    

总结配置（32GB 内存参考）

shared_buffers = 8GB                  # 共享缓冲区（1/4内存）
work_mem = 32MB                       # 工作内存（根据并发调整）
maintenance_work_mem = 1.5GB          # 维护内存
max_connections = 150                 # 最大连接数
effective_cache_size = 20GB           # 预估总缓存大小