性能优化

查询优化

EXPLAIN ANALYZE

EXPLAIN ANALYZE SELECT * FROM users WHERE name = '张三';
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE name = '张三';

查询优化建议

1. 避免 SELECT *

SELECT * FROM users;
SELECT id, name, email FROM users;

2. 使用 LIMIT

SELECT * FROM users LIMIT 100;

3. 避免 OR，使用 UNION

SELECT * FROM users WHERE name = '张三' OR email = 'test@example.com';

SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'test@example.com';

4. 使用 EXISTS 替代 IN

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

SELECT * FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

5. 批量插入

INSERT INTO users (name) VALUES ('张三');
INSERT INTO users (name) VALUES ('李四');

INSERT INTO users (name) VALUES ('张三'), ('李四'), ('王五');

配置优化

内存配置

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 64MB
maintenance_work_mem = 512MB

连接配置

max_connections = 200
superuser_reserved_connections = 3

WAL 配置

wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB
min_wal_size = 1GB

查询规划器配置

random_page_cost = 1.1
effective_io_concurrency = 200
default_statistics_target = 100

并行查询配置

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_parallel_maintenance_workers = 4

统计信息

更新统计信息

ANALYZE;
ANALYZE users;
ANALYZE users (name, age);
VACUUM ANALYZE;

增加统计精度

ALTER TABLE users ALTER COLUMN name SET STATISTICS 500;
ANALYZE users;

查看统计信息

SELECT * FROM pg_stats WHERE tablename = 'users';
SELECT * FROM pg_class WHERE relname = 'users';

VACUUM

自动 VACUUM

autovacuum = on
autovacuum_max_workers = 3
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 50
autovacuum_analyze_threshold = 50

手动 VACUUM

VACUUM users;
VACUUM FULL users;
VACUUM ANALYZE users;

VACUUM FULL

VACUUM FULL users;

注意：VACUUM FULL 会锁表，生产环境谨慎使用。

查看膨胀

SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS total_size,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 2) AS dead_ratio
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

连接池

PgBouncer 配置

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

连接池模式

模式	说明
session	会话级连接池
transaction	事务级连接池
statement	语句级连接池

分区表

范围分区

CREATE TABLE orders (
    id SERIAL,
    order_date DATE,
    amount DECIMAL
) PARTITION BY RANGE (order_date);

CREATE TABLE orders_2023 PARTITION OF orders
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE orders_2024 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

列表分区

CREATE TABLE users (
    id SERIAL,
    name VARCHAR(50),
    region VARCHAR(20)
) PARTITION BY LIST (region);

CREATE TABLE users_east PARTITION OF users
    FOR VALUES IN ('北京', '上海');

CREATE TABLE users_west PARTITION OF users
    FOR VALUES IN ('成都', '重庆');

哈希分区

CREATE TABLE logs (
    id SERIAL,
    message TEXT
) PARTITION BY HASH (id);

CREATE TABLE logs_0 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);

CREATE TABLE logs_1 PARTITION OF logs
    FOR VALUES WITH (MODULUS 4, REMAINDER 1);

分区管理

CREATE TABLE orders_2025 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

ALTER TABLE orders DETACH PARTITION orders_2023;
DROP TABLE orders_2023;

并行查询

启用并行查询

SET max_parallel_workers_per_gather = 4;
SET parallel_setup_cost = 100;
SET parallel_tuple_cost = 0.01;

查看并行执行

EXPLAIN ANALYZE SELECT COUNT(*) FROM large_table;

并行参数

max_parallel_workers_per_gather = 4
max_parallel_workers = 8
parallel_setup_cost = 1000
parallel_tuple_cost = 0.1
min_parallel_table_scan_size = 8MB
min_parallel_index_scan_size = 512kB

监控

慢查询日志

log_min_duration_statement = 1000
log_destination = 'csvlog'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

pg_stat_statements

CREATE EXTENSION pg_stat_statements;

SELECT
    query,
    calls,
    total_time,
    mean_time,
    rows
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

活动查询

SELECT * FROM pg_stat_activity WHERE state = 'active';

锁等待

SELECT * FROM pg_stat_activity WHERE wait_event_type = 'Lock';

表统计

SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

性能诊断工具

pg_stat_statements

SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_user_tables

SELECT
    schemaname,
    tablename,
    seq_scan,
    idx_scan,
    n_tup_ins,
    n_tup_upd,
    n_tup_del
FROM pg_stat_user_tables;

pg_stat_database

SELECT * FROM pg_stat_database WHERE datname = 'mydb';

下一步学习

• 备份恢复 - 掌握备份恢复