跳到主要内容

数据库设计文档

概述​

本文档详细描述了井云服务中心后端系统的数据库设计，包括表结构说明、关键索引、数据增长预期、数据保留与清理策略以及迁移策略。

数据库架构​

数据库选型​

  • 数据库类型：PostgreSQL 17.5
  • 字符集：UTF8
  • 时区：Asia/Shanghai
  • 连接池：最大 200 连接，最小 10 连接
  • 备份策略：每日全量备份，增量备份每小时

数据库分片策略​

  • 当前阶段：单数据库实例
  • 未来规划：按租户 ID 进行水平分片
  • 分片键：tenant_id
  • 分片数量：预留 16 个分片

核心数据模型​

用户服务数据模型​

用户表 (users)​

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    phone VARCHAR(20) UNIQUE NOT NULL,
    nickname VARCHAR(100),
    avatar VARCHAR(500),
    email VARCHAR(100),
    status INTEGER DEFAULT 1, -- 1:正常 2:禁用 3:删除
    tenant_id BIGINT,
    inviter_id BIGINT,
    openid VARCHAR(100),
    unionid VARCHAR(100),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT users_phone_check CHECK (phone ~ '^[0-9]{11}$'),
    CONSTRAINT users_email_check CHECK (email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$' OR email IS NULL)
);

-- 索引
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_users_inviter_id ON users(inviter_id);
CREATE INDEX idx_users_openid ON users(openid);
CREATE INDEX idx_users_unionid ON users(unionid);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_users_created_at ON users(created_at);

素材表 (materials)​

CREATE TABLE materials (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    category_id BIGINT,
    type INTEGER DEFAULT 1, -- 1:文本 2:图片 3:视频 4:音频
    file_url VARCHAR(500),
    file_size BIGINT,
    tags TEXT[], -- PostgreSQL 数组类型
    status INTEGER DEFAULT 1, -- 1:正常 2:禁用 3:删除
    tenant_id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT materials_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT materials_category_id_fkey FOREIGN KEY (category_id) REFERENCES material_categories(id)
);

-- 索引
CREATE INDEX idx_materials_user_id ON materials(user_id);
CREATE INDEX idx_materials_category_id ON materials(category_id);
CREATE INDEX idx_materials_tenant_id ON materials(tenant_id);
CREATE INDEX idx_materials_type ON materials(type);
CREATE INDEX idx_materials_status ON materials(status);
CREATE INDEX idx_materials_tags ON materials USING GIN(tags);
CREATE INDEX idx_materials_created_at ON materials(created_at);

素材分类表 (material_categories)​

CREATE TABLE material_categories (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id BIGINT,
    sort_order INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT true,
    tenant_id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT material_categories_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES material_categories(id)
);

-- 索引
CREATE INDEX idx_material_categories_tenant_id ON material_categories(tenant_id);
CREATE INDEX idx_material_categories_parent_id ON material_categories(parent_id);
CREATE INDEX idx_material_categories_is_active ON material_categories(is_active);
CREATE INDEX idx_material_categories_sort_order ON material_categories(sort_order);

用户点数账本表 (user_point_ledgers)​

CREATE TABLE user_point_ledgers (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    balance BIGINT NOT NULL DEFAULT 0,
    total_granted BIGINT NOT NULL DEFAULT 0,
    total_consumed BIGINT NOT NULL DEFAULT 0,
    source VARCHAR(50), -- 来源：purchase/gift/activity/refund
    expires_at TIMESTAMP WITH TIME ZONE,
    status INTEGER DEFAULT 1, -- 1:正常 2:过期 3:冻结
    tenant_id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT user_point_ledgers_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT user_point_ledgers_balance_check CHECK (balance >= 0)
);

-- 索引
CREATE INDEX idx_user_point_ledgers_user_id ON user_point_ledgers(user_id);
CREATE INDEX idx_user_point_ledgers_tenant_id ON user_point_ledgers(tenant_id);
CREATE INDEX idx_user_point_ledgers_expires_at ON user_point_ledgers(expires_at);
CREATE INDEX idx_user_point_ledgers_status ON user_point_ledgers(status);
CREATE INDEX idx_user_point_ledgers_source ON user_point_ledgers(source);

点数交易记录表 (point_transactions)​

CREATE TABLE point_transactions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    ledger_id BIGINT NOT NULL,
    type INTEGER NOT NULL, -- 1:获得 2:消费 3:过期 4:退款
    amount BIGINT NOT NULL,
    balance_before BIGINT NOT NULL,
    balance_after BIGINT NOT NULL,
    order_id VARCHAR(100), -- 关联订单ID
    description TEXT,
    metadata JSONB, -- 附加信息
    tenant_id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT point_transactions_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT point_transactions_ledger_id_fkey FOREIGN KEY (ledger_id) REFERENCES user_point_ledgers(id),
    CONSTRAINT point_transactions_amount_check CHECK (amount != 0)
);

-- 索引
CREATE INDEX idx_point_transactions_user_id ON point_transactions(user_id);
CREATE INDEX idx_point_transactions_ledger_id ON point_transactions(ledger_id);
CREATE INDEX idx_point_transactions_tenant_id ON point_transactions(tenant_id);
CREATE INDEX idx_point_transactions_type ON point_transactions(type);
CREATE INDEX idx_point_transactions_order_id ON point_transactions(order_id);
CREATE INDEX idx_point_transactions_created_at ON point_transactions(created_at);

点数消费记录表 (point_consumption_records)​

CREATE TABLE point_consumption_records (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    transaction_id BIGINT NOT NULL,
    agent_id BIGINT,
    usage_type VARCHAR(50), -- 使用类型：chat/generate/image等
    usage_amount INTEGER, -- 使用量
    point_cost BIGINT NOT NULL,
    session_id VARCHAR(100),
    tenant_id BIGINT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT point_consumption_records_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT point_consumption_records_transaction_id_fkey FOREIGN KEY (transaction_id) REFERENCES point_transactions(id)
);

-- 索引
CREATE INDEX idx_point_consumption_records_user_id ON point_consumption_records(user_id);
CREATE INDEX idx_point_consumption_records_transaction_id ON point_consumption_records(transaction_id);
CREATE INDEX idx_point_consumption_records_agent_id ON point_consumption_records(agent_id);
CREATE INDEX idx_point_consumption_records_tenant_id ON point_consumption_records(tenant_id);
CREATE INDEX idx_point_consumption_records_usage_type ON point_consumption_records(usage_type);
CREATE INDEX idx_point_consumption_records_created_at ON point_consumption_records(created_at);

租户服务数据模型​

租户表 (tenants)​

CREATE TABLE tenants (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    domain VARCHAR(50) UNIQUE NOT NULL,
    status INTEGER DEFAULT 1, -- 1:正常 2:过期 3:禁用 4:删除
    expires_at TIMESTAMP WITH TIME ZONE,
    version_id BIGINT,
    member_id BIGINT, -- 会员ID
    metadata JSONB, -- 租户元数据
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT tenants_domain_check CHECK (domain ~ '^[a-z0-9-]+$'),
    CONSTRAINT tenants_name_check CHECK (length(name) >= 2)
);

-- 索引
CREATE INDEX idx_tenants_domain ON tenants(domain);
CREATE INDEX idx_tenants_status ON tenants(status);
CREATE INDEX idx_tenants_expires_at ON tenants(expires_at);
CREATE INDEX idx_tenants_version_id ON tenants(version_id);
CREATE INDEX idx_tenants_member_id ON tenants(member_id);
CREATE INDEX idx_tenants_created_at ON tenants(created_at);

版本表 (versions)​

CREATE TABLE versions (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price BIGINT NOT NULL DEFAULT 0, -- 价格（分）
    duration INTEGER NOT NULL, -- 有效期（天）
    permissions BIGINT[], -- 权限ID数组
    custom_explanations JSONB, -- 自定义说明
    is_active BOOLEAN DEFAULT true,
    sort_order INTEGER DEFAULT 0,
    features JSONB, -- 功能特性
    limits JSONB, -- 限制条件
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT versions_price_check CHECK (price >= 0),
    CONSTRAINT versions_duration_check CHECK (duration > 0)
);

-- 索引
CREATE INDEX idx_versions_is_active ON versions(is_active);
CREATE INDEX idx_versions_sort_order ON versions(sort_order);
CREATE INDEX idx_versions_price ON versions(price);
CREATE INDEX idx_versions_permissions ON versions USING GIN(permissions);

租户快照表 (tenant_snapshots)​

CREATE TABLE tenant_snapshots (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    version_id BIGINT NOT NULL,
    permissions BIGINT[] NOT NULL,
    custom_explanations JSONB,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(20) DEFAULT 'active', -- active/inactive/archived
    metadata JSONB, -- 快照时的版本信息
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT tenant_snapshots_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT tenant_snapshots_version_id_fkey FOREIGN KEY (version_id) REFERENCES versions(id)
);

-- 索引
CREATE INDEX idx_tenant_snapshots_tenant_id ON tenant_snapshots(tenant_id);
CREATE INDEX idx_tenant_snapshots_version_id ON tenant_snapshots(version_id);
CREATE INDEX idx_tenant_snapshots_expires_at ON tenant_snapshots(expires_at);
CREATE INDEX idx_tenant_snapshots_status ON tenant_snapshots(status);
CREATE INDEX idx_tenant_snapshots_permissions ON tenant_snapshots USING GIN(permissions);

菜单表 (menus)​

CREATE TABLE menus (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    scope VARCHAR(20) NOT NULL, -- platform/tenant
    parent_id BIGINT,
    path VARCHAR(200),
    component VARCHAR(200),
    meta JSONB, -- 菜单元数据（标题、图标、排序等）
    sort_order INTEGER DEFAULT 0,
    is_visible BOOLEAN DEFAULT true,
    is_active BOOLEAN DEFAULT true,
    tenant_id BIGINT, -- 租户级菜单必填
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT menus_scope_check CHECK (scope IN ('platform', 'tenant')),
    CONSTRAINT menus_tenant_id_check CHECK ((scope = 'platform' AND tenant_id IS NULL) OR (scope = 'tenant' AND tenant_id IS NOT NULL)),
    CONSTRAINT menus_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES menus(id)
);

-- 索引
CREATE INDEX idx_menus_scope ON menus(scope);
CREATE INDEX idx_menus_parent_id ON menus(parent_id);
CREATE INDEX idx_menus_tenant_id ON menus(tenant_id);
CREATE INDEX idx_menus_sort_order ON menus(sort_order);
CREATE INDEX idx_menus_is_visible ON menus(is_visible);
CREATE INDEX idx_menus_is_active ON menus(is_active);

智能体服务数据模型​

智能体表 (agents)​

CREATE TABLE agents (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    type VARCHAR(50) NOT NULL, -- link/workflow_collection/workflow/bot/prompt
    status INTEGER DEFAULT 1, -- 1:正常 2:禁用 3:删除
    category_id BIGINT,
    avatar VARCHAR(500),
    background_image VARCHAR(500),
    cover_image VARCHAR(500),
    demo_video VARCHAR(500),
    pricing_type INTEGER DEFAULT 1, -- 1:免费 2:按次付费 3:订阅
    price BIGINT DEFAULT 0,
    is_member_only BOOLEAN DEFAULT false,
    is_source_downloadable BOOLEAN DEFAULT false,
    is_featured BOOLEAN DEFAULT false,
    sort_order INTEGER DEFAULT 0,
    view_count BIGINT DEFAULT 0,
    use_count BIGINT DEFAULT 0,
    tags TEXT[],
    metadata JSONB, -- 智能体元数据
    platform_agent_id VARCHAR(100), -- 平台智能体ID
    platform_type VARCHAR(50), -- 平台类型：coze/doubao等
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT agents_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT agents_category_id_fkey FOREIGN KEY (category_id) REFERENCES agent_categories(id),
    CONSTRAINT agents_price_check CHECK (price >= 0)
);

-- 索引
CREATE INDEX idx_agents_tenant_id ON agents(tenant_id);
CREATE INDEX idx_agents_type ON agents(type);
CREATE INDEX idx_agents_status ON agents(status);
CREATE INDEX idx_agents_category_id ON agents(category_id);
CREATE INDEX idx_agents_pricing_type ON agents(pricing_type);
CREATE INDEX idx_agents_is_featured ON agents(is_featured);
CREATE INDEX idx_agents_sort_order ON agents(sort_order);
CREATE INDEX idx_agents_tags ON agents USING GIN(tags);
CREATE INDEX idx_agents_platform_agent_id ON agents(platform_agent_id);
CREATE INDEX idx_agents_platform_type ON agents(platform_type);

智能体分类表 (agent_categories)​

CREATE TABLE agent_categories (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    sort_order INTEGER DEFAULT 0,
    is_featured BOOLEAN DEFAULT false,
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_agent_categories_tenant_id ON agent_categories(tenant_id);
CREATE INDEX idx_agent_categories_is_featured ON agent_categories(is_featured);
CREATE INDEX idx_agent_categories_is_active ON agent_categories(is_active);
CREATE INDEX idx_agent_categories_sort_order ON agent_categories(sort_order);

机器人表 (bots)​

CREATE TABLE bots (
    id BIGSERIAL PRIMARY KEY,
    agent_id BIGINT NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    bot_id VARCHAR(100) NOT NULL, -- 平台机器人ID
    platform_type VARCHAR(50) NOT NULL, -- coze/doubao等
    icon_url VARCHAR(500),
    prompt TEXT,
    skills JSONB, -- 技能配置
    config JSONB, -- 机器人配置
    status INTEGER DEFAULT 1, -- 1:正常 2:禁用 3:删除
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT bots_agent_id_fkey FOREIGN KEY (agent_id) REFERENCES agents(id),
    CONSTRAINT bots_unique_platform_bot UNIQUE(platform_type, bot_id)
);

-- 索引
CREATE INDEX idx_bots_agent_id ON bots(agent_id);
CREATE INDEX idx_bots_platform_type ON bots(platform_type);
CREATE INDEX idx_bots_bot_id ON bots(bot_id);
CREATE INDEX idx_bots_status ON bots(status);

支付服务数据模型​

订单表 (orders)​

CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    tenant_id BIGINT,
    type VARCHAR(50) NOT NULL, -- tenant_purchase/version_service/agent_usage
    amount BIGINT NOT NULL, -- 订单金额（分）
    status VARCHAR(20) DEFAULT 'pending', -- pending/paid/cancelled/refunded/expired
    product_id BIGINT, -- 产品ID（版本ID、智能体ID等）
    product_name VARCHAR(200),
    product_description TEXT,
    payment_method VARCHAR(50), -- wechat/alipay等
    payment_order_id VARCHAR(100), -- 第三方支付订单ID
    paid_at TIMESTAMP WITH TIME ZONE,
    expired_at TIMESTAMP WITH TIME ZONE,
    metadata JSONB, -- 订单元数据
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    
    CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id),
    CONSTRAINT orders_tenant_id_fkey FOREIGN KEY (tenant_id) REFERENCES tenants(id),
    CONSTRAINT orders_amount_check CHECK (amount >= 0),
    CONSTRAINT orders_status_check CHECK (status IN ('pending', 'paid', 'cancelled', 'refunded', 'expired'))
);

-- 索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_tenant_id ON orders(tenant_id);
CREATE INDEX idx_orders_type ON orders(type);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_payment_order_id ON orders(payment_order_id);
CREATE INDEX idx_orders_paid_at ON orders(paid_at);
CREATE INDEX idx_orders_expired_at ON orders(expired_at);
CREATE INDEX idx_orders_created_at ON orders(created_at);

关键索引设计​

主要索引策略​

1. 主键索引​

  • 所有表都使用 BIGSERIAL 作为主键
  • 主键自动创建唯一索引
  • 主键作为聚簇索引（PostgreSQL 特性）

2. 外键索引​

  • 所有外键字段创建索引
  • 提高关联查询性能
  • 支持级联操作优化

3. 业务查询索引​

  • 按租户 ID 查询：所有业务表都有 tenant_id 索引
  • 按用户 ID 查询：用户相关表都有 user_id 索引
  • 按状态查询：状态字段创建索引
  • 按时间查询：时间字段创建索引

4. 复合索引​

-- 用户点数查询优化
CREATE INDEX idx_user_point_ledgers_user_status_expires ON user_point_ledgers(user_id, status, expires_at);

-- 智能体列表查询优化
CREATE INDEX idx_agents_tenant_type_status_featured ON agents(tenant_id, type, status, is_featured);

-- 订单查询优化
CREATE INDEX idx_orders_user_type_status_created ON orders(user_id, type, status, created_at);

5. 部分索引​

-- 只为活跃用户创建索引
CREATE INDEX idx_users_active_phone ON users(phone) WHERE status = 1;

-- 只为未过期订单创建索引
CREATE INDEX idx_orders_unpaid_expires ON orders(expired_at) WHERE status = 'pending';

6. 表达式索引​

-- 域名小写索引
CREATE INDEX idx_tenants_domain_lower ON tenants(LOWER(domain));

-- 日期范围索引
CREATE INDEX idx_point_transactions_created_date ON point_transactions(DATE(created_at));

索引维护策略​

1. 索引监控​

-- 查看索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
ORDER BY idx_scan DESC;

-- 查看未使用的索引
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

2. 索引重建​

-- 重建索引（定期维护）
REINDEX INDEX CONCURRENTLY idx_users_phone;

-- 分析表统计信息
ANALYZE users;

数据增长预期​

用户数据增长​

  • 新用户注册：预计每日 100-500 新用户
  • 用户活跃度：预计 60% 用户月活跃
  • 数据保留：用户数据永久保留（删除状态）

素材数据增长​

  • 素材创建：预计每日 500-2000 新素材
  • 素材大小：平均每个素材 1MB，最大 100MB
  • 存储增长：每月约 30-60GB

智能体数据增长​

  • 智能体创建：预计每日 50-200 新智能体
  • 配置数据：平均每个智能体 10KB 配置
  • 存储增长：每月约 1.5-6MB（忽略不计）

交易数据增长​

  • 点数交易：预计每日 10000-50000 笔交易
  • 交易记录：平均每笔交易 1KB
  • 存储增长：每月约 300MB-1.5GB

订单数据增长​

  • 订单创建：预计每日 50-200 新订单
  • 订单记录：平均每个订单 2KB
  • 存储增长：每月约 3-12MB

总体存储预估​

  • 第一年：约 500GB-1TB
  • 第二年：约 1TB-2TB
  • 第三年：约 1.5TB-3TB

数据保留与清理策略​

数据分类保留策略​

1. 永久保留数据​

  • 用户基本信息（删除状态除外）
  • 租户信息
  • 版本信息
  • 菜单配置
  • 财务记录

2. 长期保留数据（7年）​

  • 点数交易记录
  • 订单记录
  • 用户操作日志
  • 系统审计日志

3. 中期保留数据（1年）​

  • 点数消费记录
  • 素材访问记录
  • 智能体使用统计
  • 错误日志

4. 短期保留数据（30天）​

  • 会话记录
  • 临时文件
  • 缓存数据
  • 验证码记录

自动清理策略​

1. 定时清理任务​

-- 清理过期的验证码记录
DELETE FROM verification_codes 
WHERE created_at < NOW() - INTERVAL '5 minutes';

-- 清理过期的会话记录
DELETE FROM user_sessions 
WHERE expires_at < NOW();

-- 清理临时文件记录
DELETE FROM temporary_files 
WHERE created_at < NOW() - INTERVAL '24 hours';

2. 数据归档策略​

-- 归档旧的交易记录到历史表
INSERT INTO point_transactions_archive 
SELECT * FROM point_transactions 
WHERE created_at < NOW() - INTERVAL '1 year';

DELETE FROM point_transactions 
WHERE created_at < NOW() - INTERVAL '1 year';

3. 软删除策略​

-- 用户软删除
UPDATE users 
SET status = 3, updated_at = NOW() 
WHERE id = ?;

-- 智能体软删除
UPDATE agents 
SET status = 3, updated_at = NOW() 
WHERE id = ?;

数据备份策略​

1. 全量备份​

  • 频率：每日凌晨 2 点
  • 保留：30 天
  • 存储：异地备份存储

2. 增量备份​

  • 频率：每小时
  • 保留：7 天
  • 存储：本地高速存储

3. 日志备份​

  • 频率：实时
  • 保留：24 小时
  • 存储：日志专用存储

数据库迁移策略​

迁移工具选择​

  • 主要工具：Goose（Go 语言数据库迁移工具）
  • 备用工具：Atlas（Schema-as-Code 工具）
  • 版本控制：Git 管理迁移文件

迁移文件规范​

1. 迁移文件命名​

格式：YYYYMMDDHHMMSS_description.sql
示例：20251227103000_create_users_table.sql

2. 迁移文件结构​

-- 20251227103000_create_users_table.sql
-- +goose Up
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    phone VARCHAR(20) UNIQUE NOT NULL,
    nickname VARCHAR(100),
    status INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 创建索引
CREATE INDEX idx_users_phone ON users(phone);
CREATE INDEX idx_users_status ON users(status);

-- +goose Down
DROP TABLE IF EXISTS users;

3. 迁移执行流程​

# 1. 创建新迁移文件
goose create add_user_email sql

# 2. 编辑迁移文件
vim migrations/20251227104500_add_user_email.sql

# 3. 执行迁移（开发环境）
goose -dir migrations postgres "user=akita dbname=jingyun_dev sslmode=disable" up

# 4. 执行迁移（生产环境）
goose -dir migrations postgres "user=akita dbname=jingyun sslmode=require" up

迁移最佳实践​

1. 迁移前检查​

-- 检查表是否存在
SELECT EXISTS (
    SELECT FROM information_schema.tables 
    WHERE table_name = 'users'
);

-- 检查列是否存在
SELECT EXISTS (
    SELECT FROM information_schema.columns 
    WHERE table_name = 'users' AND column_name = 'email'
);

2. 向前兼容迁移​

-- 添加新列（允许 NULL）
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- 设置默认值
UPDATE users SET email = '' WHERE email IS NULL;

-- 设置 NOT NULL 约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

3. 大表迁移策略​

-- 分批更新大表
DO $$
DECLARE
    batch_size INTEGER := 1000;
    offset_val INTEGER := 0;
    affected INTEGER := 1;
BEGIN
    WHILE affected > 0 LOOP
        UPDATE users 
        SET email = COALESCE(email, '')
        WHERE id >= offset_val AND id < offset_val + batch_size;
        
        GET DIAGNOSTICS affected = ROW_COUNT;
        offset_val := offset_val + batch_size;
        
        -- 提交每个批次
        COMMIT;
        
        -- 短暂休息，避免锁表时间过长
        PERFORM pg_sleep(0.1);
    END LOOP;
END $$;

4. 回滚策略​

-- 创建备份表
CREATE TABLE users_backup_20251227 AS SELECT * FROM users;

-- 执行迁移
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- 如需回滚
DROP TABLE users;
ALTER TABLE users_backup_20251227 RENAME TO users;

迁移验证​

1. 数据一致性检查​

-- 检查行数是否一致
SELECT COUNT(*) FROM users;

-- 检查数据完整性
SELECT COUNT(*) FROM users WHERE phone IS NULL OR phone = '';

-- 检查外键约束
SELECT COUNT(*) FROM users u 
LEFT JOIN tenants t ON u.tenant_id = t.id 
WHERE u.tenant_id IS NOT NULL AND t.id IS NULL;

2. 性能基准测试​

-- 迁移前性能测试
EXPLAIN ANALYZE SELECT * FROM users WHERE phone = '13800138000';

-- 迁移后性能测试
EXPLAIN ANALYZE SELECT * FROM users WHERE phone = '13800138000';

数据库监控​

性能监控指标​

1. 连接监控​

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

-- 查看连接状态
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;

2. 查询性能监控​

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

3. 锁监控​

-- 查看锁等待
SELECT 
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

存储监控​

1. 表大小监控​

-- 查看表大小
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;

2. 索引大小监控​

-- 查看索引大小
SELECT 
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) as index_size
FROM pg_stat_user_indexes 
ORDER BY pg_relation_size(indexrelid) DESC;

数据库安全​

访问控制​

1. 用户权限管理​

-- 创建应用用户
CREATE USER app_user WITH PASSWORD 'secure_password';

-- 授予必要权限
GRANT CONNECT ON DATABASE jingyun TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- 创建只读用户
CREATE USER readonly_user WITH PASSWORD 'readonly_password';
GRANT CONNECT ON DATABASE jingyun TO readonly_user;
GRANT USAGE ON SCHEMA public TO readonly_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user;

2. 行级安全策略​

-- 启用行级安全
ALTER TABLE agents ENABLE ROW LEVEL SECURITY;

-- 创建租户隔离策略
CREATE POLICY tenant_isolation ON agents
    FOR ALL TO app_user
    USING (tenant_id = current_setting('app.current_tenant_id')::BIGINT);

数据加密​

1. 传输加密​

-- 强制 SSL 连接
ALTER SYSTEM SET ssl = 'on';
ALTER SYSTEM SET ssl_cert_file = '/path/to/server.crt';
ALTER SYSTEM SET ssl_key_file = '/path/to/server.key';

2. 敏感数据加密​

-- 创建加密扩展
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- 加密敏感数据
INSERT INTO users (phone, nickname) 
VALUES (
    pgp_sym_encrypt('13800138000', 'encryption_key'),
    'Test User'
);

-- 解密数据
SELECT pgp_sym_decrypt(phone::bytea, 'encryption_key') FROM users;

数据库优化​

查询优化​

1. 查询计划分析​

-- 分析查询执行计划
EXPLAIN (ANALYZE, BUFFERS) 
SELECT u.*, a.name as agent_name 
FROM users u 
LEFT JOIN agents a ON u.tenant_id = a.tenant_id 
WHERE u.status = 1 
ORDER BY u.created_at DESC 
LIMIT 10;

2. 统计信息更新​

-- 更新表统计信息
ANALYZE users;
ANALYZE agents;

-- 更新特定列统计信息
ANALYZE users (phone, status, created_at);

3. 自动清理配置​

-- 配置自动清理
ALTER TABLE users SET (
    autovacuum_enabled = true,
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_scale_factor = 0.05
);

连接池优化​

1. 连接池配置​

data:
  database:
    max_open_conns: 200        # 最大连接数
    max_idle_conns: 20         # 最大空闲连接数
    conn_max_lifetime: "1h"    # 连接最大生命周期
    conn_max_idle_time: "10m"  # 连接最大空闲时间

2. 连接监控​

-- 监控连接使用情况
SELECT 
    state,
    count(*) as connection_count,
    max(query_start) as oldest_query
FROM pg_stat_activity 
WHERE datname = 'jingyun' 
GROUP BY state;

灾难恢复​

备份恢复策略​

1. 时间点恢复​

# 创建基础备份
pg_basebackup -h localhost -D /backup/base -U replication -v -P -W

# 恢复到指定时间点
pg_ctl start -D /backup/recovery -c recovery_target_time="2025-12-27 10:30:00"

2. 主从复制​

-- 配置主库
ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 3;
ALTER SYSTEM SET wal_keep_segments = 64;

-- 创建复制用户
CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator_password';

3. 故障切换​

# 提升从库为主库
pg_ctl promote -D /backup/slave

# 更新应用连接配置
# 修改数据库连接字符串指向新主库

恢复测试​

1. 定期恢复演练​

  • 每月进行一次恢复测试
  • 验证备份数据完整性
  • 测试恢复流程和时间
  • 更新恢复文档

2. RTO/RPO 目标​

  • RTO（恢复时间目标）：30 分钟
  • RPO（恢复点目标）：1 小时
  • 数据丢失容忍度：最多 1 小时数据

数据库最佳实践​

1. 设计原则​

  • 遵循第三范式，避免数据冗余
  • 合理使用索引，避免过度索引
  • 选择合适的数据类型
  • 设计合理的外键约束

2. 性能优化​

  • 定期更新统计信息
  • 监控慢查询并优化
  • 合理配置连接池
  • 使用分区表处理大数据

3. 安全管理​

  • 最小权限原则
  • 定期轮换密码
  • 启用审计日志
  • 加密敏感数据

4. 运维管理​

  • 自动化备份和恢复
  • 监控数据库性能
  • 定期维护和优化
  • 建立故障预案