最佳实践

嗨！我是长安。这是我多年使用PostgreSQL的经验总结！

🎯 数据库设计

1. 命名规范

-- ✅ 好的命名
CREATE TABLE users (...)              -- 表名：小写复数
CREATE INDEX idx_users_email (...)    -- 索引：idx_表名_列名
CREATE TABLE order_items (...)        -- 下划线分隔

-- ❌ 不好的命名
CREATE TABLE User (...)               -- 大写
CREATE TABLE user (...)               -- 单数
CREATE INDEX email_index (...)        -- 不清晰

2. 主键设计

-- ✅ 推荐：使用BIGSERIAL
id BIGSERIAL PRIMARY KEY

-- ✅ 或使用UUID
id UUID PRIMARY KEY DEFAULT gen_random_uuid()

-- ❌ 不推荐：用业务字段做主键
email VARCHAR PRIMARY KEY  -- email可能会改变

3. 外键约束

-- ✅ 始终使用外键
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE
);

-- 并创建索引
CREATE INDEX idx_posts_user_id ON posts(user_id);

4. 时间戳字段

-- ✅ 每个表都应该有
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP

-- 使用TIMESTAMPTZ而不是TIMESTAMP
-- TZ会自动处理时区

5. 软删除

-- ✅ 重要数据使用软删除
is_deleted BOOLEAN DEFAULT FALSE,
deleted_at TIMESTAMPTZ,
deleted_by BIGINT

-- 配合索引
CREATE INDEX idx_users_active ON users(id) WHERE is_deleted = FALSE;

🔍 查询优化

1. 只查询需要的列

-- ❌ 不好
SELECT * FROM users;

-- ✅ 好
SELECT id, username, email FROM users;

2. 避免N+1查询

-- ❌ 不好（应用层循环查询）
for post in posts:
    author = SELECT * FROM users WHERE id = post.user_id

-- ✅ 好（一次JOIN）
SELECT p.*, u.username 
FROM posts p 
JOIN users u ON p.user_id = u.id;

3. 使用EXPLAIN分析

-- 养成习惯：复杂查询先EXPLAIN
EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 1;

4. 合理使用索引

-- ✅ 为WHERE、JOIN、ORDER BY的列创建索引
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_created ON posts(created_at DESC);

-- ❌ 不要为所有列都创建索引
-- 索引也有维护成本

🔒 安全性

1. 参数化查询

# ❌ 危险！SQL注入
query = f"SELECT * FROM users WHERE username = '{username}'"

# ✅ 安全：参数化查询
query = "SELECT * FROM users WHERE username = %s"
cursor.execute(query, (username,))

2. 最小权限原则

-- 为应用创建专用用户
CREATE USER app_user WITH PASSWORD 'strong_password';

-- 只授予必要的权限
GRANT SELECT, INSERT, UPDATE ON users TO app_user;
GRANT SELECT, INSERT, UPDATE ON posts TO app_user;

-- 不要用超级用户连接

3. 密码加密

-- ❌ 永远不要明文存储密码
password VARCHAR(50)

-- ✅ 存储加密后的哈希值
password_hash VARCHAR(255)

-- 在应用层使用bcrypt等算法加密

4. 敏感数据脱敏

-- 日志中隐藏敏感信息
SELECT 
    id,
    username,
    CONCAT(LEFT(email, 3), '***@', SPLIT_PART(email, '@', 2)) AS email,
    LEFT(phone, 3) || '****' || RIGHT(phone, 4) AS phone
FROM users;

⚡ 性能优化

1. 连接池

# 使用连接池而不是每次创建连接
from psycopg2 import pool

connection_pool = pool.SimpleConnectionPool(
    minconn=1,
    maxconn=20,
    host='localhost',
    database='mydb'
)

2. 批量操作

-- ❌ 慢
INSERT INTO logs VALUES (1);
INSERT INTO logs VALUES (2);
-- ... 1000次

-- ✅ 快
INSERT INTO logs VALUES (1), (2), (3), ..., (1000);

-- 或使用COPY
COPY logs FROM '/tmp/logs.csv' CSV;

3. 分页优化

-- ❌ 慢（深分页）
SELECT * FROM posts ORDER BY id LIMIT 10 OFFSET 100000;

-- ✅ 快（使用WHERE）
SELECT * FROM posts 
WHERE id > 100000 
ORDER BY id 
LIMIT 10;

4. 定期维护

-- 定期VACUUM（清理死元组）
VACUUM ANALYZE users;

-- 重建索引
REINDEX TABLE users;

-- 更新统计信息
ANALYZE users;

🎯 备份和恢复

1. 定期备份

# 备份整个数据库
pg_dump -U postgres -d mydb > mydb_backup.sql

# 备份并压缩
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 只备份数据（不含结构）
pg_dump -U postgres -d mydb --data-only > data_backup.sql

# 只备份结构（不含数据）
pg_dump -U postgres -d mydb --schema-only > schema_backup.sql

2. 恢复数据

# 恢复数据库
psql -U postgres -d mydb < mydb_backup.sql

# 从压缩文件恢复
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb

3. 自动备份脚本

#!/bin/bash
# backup.sh

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
DB_NAME="mydb"

# 备份
pg_dump -U postgres -d $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 删除7天前的备份
find $BACKUP_DIR -name "${DB_NAME}_*.sql.gz" -mtime +7 -delete

# 设置定时任务
# crontab -e
# 0 2 * * * /path/to/backup.sh

📊 监控和日志

1. 慢查询日志

-- 开启慢查询日志（postgresql.conf）
log_min_duration_statement = 1000  -- 记录超过1秒的查询

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

2. 连接数监控

-- 查看当前连接数
SELECT COUNT(*) FROM pg_stat_activity;

-- 查看每个数据库的连接数
SELECT datname, count(*) 
FROM pg_stat_activity 
GROUP BY datname;

-- 查看最大连接数
SHOW max_connections;

3. 表大小监控

-- 查看表大小
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

🎓 开发规范

1. 版本控制

-- 使用迁移工具管理数据库变更
-- 例如：Flyway, Liquibase, Alembic

-- 每个变更一个文件
-- V1__create_users_table.sql
-- V2__add_email_to_users.sql
-- V3__create_posts_table.sql

2. 代码审查

-- 变更前检查清单：
-- ✅ 是否会锁表？
-- ✅ 是否有索引？
-- ✅ 是否有外键？
-- ✅ 是否需要事务？
-- ✅ 是否会影响性能？

3. 测试环境

-- 永远先在测试环境验证
-- 测试环境 → 预发布环境 → 生产环境

💡 常见陷阱

1. NULL的陷阱

-- ❌ 错误
WHERE column = NULL  -- 永远返回FALSE

-- ✅ 正确
WHERE column IS NULL

2. 隐式类型转换

-- ❌ 可能导致索引失效
WHERE id = '123'  -- id是INT，'123'是VARCHAR

-- ✅ 使用正确的类型
WHERE id = 123

3. OR的性能问题

-- ❌ 慢
WHERE status = 'active' OR status = 'pending'

-- ✅ 快
WHERE status IN ('active', 'pending')

4. COUNT(*)的性能

-- ❌ 慢（大表）
SELECT COUNT(*) FROM large_table;

-- ✅ 使用估算
SELECT reltuples::bigint FROM pg_class WHERE relname = 'large_table';

-- ✅ 或维护计数器
CREATE TABLE counters (
    table_name VARCHAR(50),
    count BIGINT
);

🎮 实用技巧

1. 查看执行中的查询

SELECT 
    pid,
    usename,
    state,
    query,
    now() - query_start AS duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;

2. 杀死长时间运行的查询

-- 终止查询
SELECT pg_cancel_backend(pid);

-- 强制终止连接
SELECT pg_terminate_backend(pid);

3. 查看表和索引的使用情况

-- 表扫描统计
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

-- 索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan;