-- 数据库性能优化脚本
-- scripts/optimize-database.sql
-- 创建复合索引
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_status ON users(email, status) WHERE status = 1;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_created_at ON users(created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tokens_user_id_status ON tokens(user_id, status) WHERE status = 1;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_tokens_created_at ON tokens(created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_channels_status_type ON channels(status, type);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logs_user_id_created_at ON logs(user_id, created_at DESC);
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_logs_created_at_type ON logs(created_at DESC, type);
-- 部分索引(提高效率)
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_active_users ON users(id) WHERE status = 1;
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_active_channels ON channels(id) WHERE status = 1;
-- 表达式索引
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_lower ON users(LOWER(email));
-- 分区表设计
CREATE TABLE IF NOT EXISTS logs_partitioned (
LIKE logs INCLUDING ALL
) PARTITION BY RANGE (created_at);
-- 自动创建分区的函数
CREATE OR REPLACE FUNCTION create_monthly_partition(table_name text, start_date date)
RETURNS void AS $$
DECLARE
partition_name text;
end_date date;
BEGIN
partition_name := table_name || '_' || to_char(start_date, 'YYYY_MM');
end_date := start_date + interval '1 month';
EXECUTE format('CREATE TABLE IF NOT EXISTS %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L)',
partition_name, table_name, start_date, end_date);
END;
$$ LANGUAGE plpgsql;
-- 创建最近几个月的分区
SELECT create_monthly_partition('logs_partitioned', date_trunc('month', CURRENT_DATE - interval '1 month'));
SELECT create_monthly_partition('logs_partitioned', date_trunc('month', CURRENT_DATE));
SELECT create_monthly_partition('logs_partitioned', date_trunc('month', CURRENT_DATE + interval '1 month'));
-- 更新表统计信息
ANALYZE users;
ANALYZE tokens;
ANALYZE channels;
ANALYZE logs;
-- 查询优化建议
-- 1. 避免SELECT *
-- 2. 使用LIMIT限制结果集
-- 3. 合理使用JOIN
-- 4. 避免在WHERE子句中使用函数