一、数据库优化概述

数据库性能优化是保障应用响应速度的关键。

二、安装PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib -y
sudo systemctl start postgresql
sudo systemctl enable postgresql

三、配置优化

3.1 内存配置

sudo nano /etc/postgresql/15/main/postgresql.conf

# 共享缓冲区
shared_buffers = 256MB

# 工作内存
work_mem = 64MB

# 维护工作内存
maintenance_work_mem = 128MB

3.2 连接配置

# 最大连接数
max_connections = 200

# 有效缓存大小
effective_cache_size = 1GB

四、查询优化

4.1 索引优化

-- 创建索引
CREATE INDEX idx_users_email ON users(email);

-- 查看索引
SELECT indexname, tablename FROM pg_indexes;

-- 分析查询计划
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

4.2 慢查询日志

# 启用慢查询日志
sudo nano /etc/postgresql/15/main/postgresql.conf

# 设置阈值
log_min_duration_statement = 1000

五、自动清理

5.1 VACUUM配置

# 自动vacuum
autovacuum = on
autovacuum_max_workers = 3

5.2 手动清理

-- 清理表
VACUUM ANALYZE users;

-- 清理整个数据库
VACUUM FULL;

六、监控工具

6.1 pg_stat_statements

-- 安装扩展
CREATE EXTENSION pg_stat_statements;

-- 查看最慢查询
SELECT query, calls, mean_time 
FROM pg_stat_statements 
ORDER BY mean_time DESC 
LIMIT 10;

6.2 系统监控

# 查看连接数
SELECT count(*) FROM pg_stat_activity;

# 查看锁等待
SELECT * FROM pg_locks WHERE granted = false;

七、备份优化

# 并行备份
pg_dump -j 4 -Fc mydb -f backup.dump

八、常见问题

Q1：连接数过多？

调整max_connections，优化连接池配置。

Q2：查询很慢？

使用EXPLAIN分析，优化索引。

九、总结

通过合理的配置和优化，可以显著提升数据库性能。

注：本文基于2026年Ubuntu 22.04编写