PostgreSQL 性能优化与调优（六）

1. 索引优化

1.1 创建索引

索引可以显著提高查询性能。创建索引的基本语法如下：

CREATE INDEX index_name ON table_name (column_name);

例如，为 users 表的 username 列创建索引：

CREATE INDEX idx_username ON users (username);

1.2 常见索引类型

• B树索引（B-Tree Index）： 默认类型，适用于大多数情况。
• 哈希索引（Hash Index）： 适用于等值比较。
• GIN 和 GiST 索引： 适用于全文搜索和复杂数据类型。
• BRIN 索引（Block Range INdex）： 适用于非常大的表和顺序访问的情况。

1.3 多列索引

多列索引可以同时加速涉及多列的查询：

CREATE INDEX idx_user_email ON users (username, email);

1.4 使用索引的注意事项

• 避免为小表创建索引。
• 谨慎使用太多索引，因为索引也会影响写性能。
• 定期分析和维护索引，使用 VACUUM 和 ANALYZE 命令。

2. 查询优化

2.1 查询计划（EXPLAIN）

使用 EXPLAIN 命令查看查询的执行计划，找出潜在的性能问题：

EXPLAIN SELECT * FROM users WHERE username = 'alice';

2.2 常见优化技巧

• 选择合适的索引： 确保查询使用了正确的索引。
• *避免 SELECT ： 仅选择需要的列，减少不必要的数据传输。
• 优化 JOIN 操作： 使用小表驱动大表，合理设计索引。
• 减少子查询： 使用 JOIN 或 CTE（公用表表达式）替代复杂的子查询。

2.3 示例优化

优化前：

SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE age > 30);

优化后：

SELECT orders.* FROM orders
JOIN users ON orders.user_id = users.id
WHERE users.age > 30;

3. 配置调优

3.1 调整配置参数

调整 PostgreSQL 配置文件（postgresql.conf）中的参数，以优化性能。

3.1.1 内存参数

• shared_buffers： 用于缓存表和索引数据。通常设置为服务器内存的 25%。
• work_mem： 用于临时操作的内存。适当调整以提高复杂查询的性能。
• maintenance_work_mem： 用于维护操作（如VACUUM和CREATE INDEX）。可以临时提高此值以加快大规模维护操作。

3.1.2 并发参数

• max_connections： 最大并发连接数。设置过高可能导致内存不足。
• max_worker_processes： 最大后台工作进程数。适当增加以支持更多并行操作。

3.1.3 自动化维护

• autovacuum： 自动清理和优化表。确保启用并根据需要调整频率。

3.2 监控与日志

配置日志记录和监控，以识别和诊断性能问题。

3.2.1 日志配置

在 postgresql.conf 中配置日志参数：

log_min_duration_statement = 1000   # 记录执行时间超过 1000 毫秒的查询
log_statement = 'all'               # 记录所有SQL语句

3.2.2 使用pg_stat_statements

安装并配置 pg_stat_statements 扩展，监控SQL查询的性能：

CREATE EXTENSION pg_stat_statements;-- 查询最耗时的SQL语句
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

4. 并行查询与批处理

4.1 并行查询

PostgreSQL 支持并行查询，可以利用多核处理器提升查询性能。确保配置参数支持并行查询：

max_parallel_workers_per_gather = 4

4.2 批量操作

尽量使用批量操作替代逐条操作，以提高性能。

批量插入：

INSERT INTO users (username, email) VALUES 
('user1', '[e-mail]'),
('user2', '[e-mail]');

批量更新：

UPDATE users SET email = '[e-mail]' WHERE username IN ('user1', 'user2');

5. 实战演练

5.1 练习题目

1. 为 orders 表创建适当的索引，以加速按用户和订单日期查询的操作。
2. 使用 EXPLAIN 分析下列查询的执行计划并优化：

   SELECT * FROM orders WHERE user_id = 1 AND order_date > '2023-01-01';
   

3. 调整 PostgreSQL 配置参数以优化内存使用和并发性能。
4. 安装并使用 pg_stat_statements 扩展，识别最耗时的查询。

5.2 示例答案

1. 创建索引：

CREATE INDEX idx_user_order_date ON orders (user_id, order_date);

1. 使用 EXPLAIN 分析和优化：

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND order_date > '2023-01-01';-- 优化后（索引已创建）
SELECT * FROM orders WHERE user_id = 1 AND order_date > '2023-01-01';

1. 调整配置参数：

在 postgresql.conf 文件中进行如下调整：

shared_buffers = 4GB
work_mem = 64MB
maintenance_work_mem = 1GB
max_connections = 200
max_worker_processes = 16
autovacuum = on

1. 安装并使用 pg_stat_statements：

CREATE EXTENSION pg_stat_statements;-- 查询最耗时的SQL语句
SELECT query, total_time, calls
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

---

系统文章目录：

PostgreSQL 简介与基础（一）

PostgreSQL 基本SQL语法（二）

PostgreSQL 高级SQL查询（三）

PostgreSQL 数据库设计与管理（四）

PostgreSQL 高级功能（五）

PostgreSQL 性能优化与调优（六）

PostgreSQL 高可用性与灾难恢复策略（七）

PostgreSQL 安全性与权限管理（八）

PostgreSQL 高级功能与扩展（九）

PostgreSQL 分区表与并行查询（十）

PostgreSQL 索引优化与性能调优（十一）

PostgreSQL 日志管理与故障排查（十二）

PostgreSQL 高可用性与容错性（十三）