# 数据库模式 ## 核心原则 * **PostgreSQL Primary** — 关系数据、事务、复杂查询 * **Redis 辅助** — 缓存、会话、实时数据 * **索引优先设计** — 在索引之前设计查询 * **JSONB 谨慎** — 结构化数据更喜欢列 * **Cache-Aside Default** — 通读、绕写 * **分层存储** — 热/温/冷数据分离 * **无向后兼容性** — 迁移数据，不保留旧模式 ** ** ## PostgreSQL ### 数据类型选择使用案例|类型 |避免 ---|---|--- 主键 | `UUID` / `BIGSERIAL` | `INT`（范围限制）时间戳 | `TIMESTAMPTZ` | `TIMESTAMP`（无时区）钱| `数字（19,4）` | `FLOAT`（精度损失）状态 | `文本` \+ 检查 | `INT`（不可读）半结构化| `JSONB` | `JSON`（无索引）全文| `TSVECTOR` | `喜欢'%..%'` ### 架构设计 -- 使用 UUID 作为分布式友好的 ID 如果不存在“uuid-ossp”，则创建扩展；创建表用户（ id UUID 主键默认 uuid_generate_v4(), 电子邮件文本唯一不为空，名称文本不为空，状态 TEXT NOT NULL DEFAULT 'active' CHECK (状态 IN ('活动', '非活动', '暂停')), 元数据 JSONB DEFAULT '{}', 创建时间为 TIMESTAMPTZ 默认值 NOW()， Updated_at TIMESTAMPTZ 默认现在（））； -- 更新了时间戳触发器创建或替换函数 update_updated_at() 回报触发为$$ 开始 NEW.updated_at = NOW(); 返回新的；结尾; $$ 语言 plpgsql; 创建触发器 users_updated_at 用户更新之前对于每一行执行函数 update_updated_at(); ### 索引策略 -- B-Tree：相等、范围、排序（默认）在用户（电子邮件）上创建索引 idx_users_email； -- 复合：最左前缀规则 -- 支持：(user_id)、(user_id、created_at) -- 不支持： (created_at) 单独创建索引 idx_orders_user_date ON 订单（user_id，created_at DESC）； -- 部分：减少索引大小在用户（电子邮件）上创建索引 idx_active_users WHERE 状态 = '活动'; -- JSONB 的 GIN：包含查询使用 GIN 创建索引 idx_metadata ON 用户（元数据 jsonb_path_ops）； -- 表达式：特定 JSONB 字段在用户上创建索引 idx_user_role ((元数据->>'角色')); -- 全文检索使用 GIN 在产品上创建索引 idx_search (to_tsvector('english', name || ' ' || description)); ### JSONB 用法 -- 好：动态属性，很少查询的字段创建表产品（ id UUID 主键，名称文本不为空，价格 NUMERIC(19,4) NOT NULL, 类别 TEXT NOT NULL, -- 提取：经常查询属性 JSONB DEFAULT '{}' -- 动态：颜色、尺寸、规格）； -- 包含查询从产品中选择 * WHERE 类别 = '电子产品' -- B 树索引 AND 属性 @> '{"brand": "Apple"}'; -- 杜松子酒指数 -- 查询特定字段从产品中选择 * WHERE 属性->>'颜色' = '黑色'; -- 表达索引 -- 更新 JSONB 字段更新产品设置属性=属性|| '{“特色”：true}' 其中 id = '...'; ### 查询优化 -- 始终使用解释分析解释分析 SELECT u.*, COUNT(o.id) 作为 order_count 来自用户 u LEFT JOIN 命令 o ON o.user_id = u.id WHERE u.status = '活动' 按 u.id 分组按 u.created_at DESC 排序限制 20； -- 注意： -- ❌ 对大表进行顺序扫描 → 添加索引 -- ❌排序 → 使用索引排序 -- ❌ 多行嵌套循环 → 考虑 JOIN 顺序 -- ❌ 大表上的哈希连接 → 添加索引 ### 连接池 // PgBouncer 或内置池从'pg'导入{池}；常量池=新池（{ max: 20, // 最大连接数 idleTimeoutMillis: 30000, // 关闭空闲连接 connectionTimeoutMillis: 2000, // 快速失败 }); // 连接数计算公式： // 连接数 = (核心 * 2) + effective_spindle_count // 通常10-30就足够了 ** ** ## Redis ### 数据结构选择使用案例|结构|示例 ---|---|--- 缓存对象 |字符串| `用户：123` → JSON 专柜|字符串 + INCR | `浏览量：文章：456` 会议 |哈希 | `会话：abc` → {userId，...} 排行榜 |排序集 | `scores` → {userId: 分数} 队列 |列表/流 | `任务` → LPUSH/RPOP 独特套装 |设置| `在线用户` 实时 |发布/订阅/流 |通知 ### 键命名 # 格式：<实体>::<属性> 用户：123：个人资料用户：123：设置订单：456：项目会话：abc123 # 使用冒号表示层次结构 # 启用与 SCAN 的模式匹配扫描 0 匹配“用户：*：个人资料”计数 100 ### TTL 策略常量 TTL = { 会话：24 * 60 * 60, // 24 小时缓存: 15 * 60, // 15 分钟 RATE_LIMIT: 60, // 1 分钟锁定: 30, // 30 秒 }; // 用TTL设置等待redis.set（`缓存：用户：$ {id}`，JSON.stringify（用户），'EX'，TTL.CACHE）; // 检查TTL const剩余=等待redis.ttl（`缓存：用户：$ {id}`）; ** ** ## 缓存模式 ### 缓存旁路（延迟加载）异步函数 getUser(id: string): Promise { const cacheKey = `用户：${id}`; // 1.检查缓存 const 缓存 = 等待 redis.get(cacheKey); 如果（缓存）{ 返回 JSON.parse(缓存); } // 2. 缓存未命中→查询数据库 const user = wait db.user.findUnique({ 其中：{ id } }); 如果（！用户）{ throw new NotFoundError('未找到用户'); } // 3. 填充缓存等待 redis.set(cacheKey, JSON.stringify(user), 'EX', 900); 返回用户； } ### 直写式异步函数 updateUser(id: string, data: UpdateInput): Promise { // 1.更新数据库 const 用户 = 等待 db.user.update({ 其中：{id}，数据， }); // 2. 立即更新缓存等待redis.set（`用户：$ {id}`，JSON.stringify（用户），'EX'，900）; 返回用户； } ### 缓存失效异步函数deleteUser(id: string): Promise { // 1. 从数据库中删除等待 db.user.delete({ 其中：{ id } }); // 2. 使缓存失效等待 redis.del(`用户：${id}`); // 3. 使相关缓存失效 constkeys=awaitredis.keys(`用户:${id}:*`); if (keys.length > 0) { 等待 redis.del(...keys); } } ### 缓存踩踏预防异步函数 getUserWithLock(id: string): Promise { const cacheKey = `用户：${id}`; const lockKey = `锁：用户：${id}`; // 检查缓存 const 缓存 = 等待 redis.get(cacheKey); 如果（缓存）{ 返回 JSON.parse(缓存); } // 尝试获取锁 const 获取 = wait redis.set(lockKey, '1', 'EX', 10, 'NX'); 如果（！获得）{ // 另一个进程正在加载，等待并重试等待睡眠（100）；返回 getUserWithLock(id); } 尝试{ // 仔细检查缓存（另一个进程可能已填充它） const 重新检查 = 等待 redis.get(cacheKey); 如果（重新检查）{ 返回 JSON.parse(重新检查); } // 从数据库加载 const user = wait db.user.findUnique({ 其中：{ id } }); 等待 redis.set(cacheKey, JSON.stringify(user), 'EX', 900); 返回用户； }最后{ 等待 redis.del(lockKey); } } ### 缓存渗透防护异步函数 getUserSafe(id: string): Promise { const cacheKey = `用户：${id}`; const 缓存 = 等待 redis.get(cacheKey); // 检查缓存是否为空 if (缓存 === 'NULL') { 返回空值； } 如果（缓存）{ 返回 JSON.parse(缓存); } const user = wait db.user.findUnique({ 其中：{ id } }); 如果（！用户）{ // 缓存 null 且 TTL 短等待 redis.set(cacheKey, 'NULL', 'EX', 60); 返回空值； } 等待 redis.set(cacheKey, JSON.stringify(user), 'EX', 900); 返回用户； } ** ** ## 分层存储 ┌──────────────────────────────────────────────────┐ │ 应用领域 │ └──────────────────────────────────────────────────┘ │ ┌────────────────┼────────────────┐ ▼ ▼ ▼ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ Redis │ │ Postgres │ │ 存档 │ │ （热） │ │ （暖） │ │ （冷） │ └──────────┘ └──────────┘ └──────────┘ < 1ms ~10ms ~100ms+ 活跃数据近期数据历史数据内存 SSD 对象存储 ### 冷数据分区 -- 按日期范围分区创建表订单（ id UUID 不为空， user_id UUID NOT NULL, 总数 NUMERIC(19,4) NOT NULL, 创建时间 TIMESTAMPTZ NOT NULL ）按范围分区（created_at）； -- 创建分区创建表orders_2025_q1 订单分区对于从 ('2025-01-01') 到 ('2025-04-01') 的值；创建表orders_2025_q2 订单分区对于从 ('2025-04-01') 到 ('2025-07-01') 的值； -- 归档旧数据创建表orders_archive（如订单，包括所有）； -- 将旧数据移至存档随着移动 AS ( 从订单中删除 WHERE create_at < NOW() - INTERVAL '1 年' 返回 * ） INSERT INTO order_archive SELECT * FROM 已移动； ** ** ## 交易 ### 酸合规性 // 使用事务进行多表操作异步函数transferFunds(fromId: string, toId: string, amount: number) { 等待 db.$transaction(async (tx) => { // 从源中扣除 const from = 等待 tx.account.update({ 其中：{ id: fromId }, 数据：{余额：{减量：金额}}， }); if (from.balance < 0) { throw new Error('资金不足'); } // 添加到目的地等待 tx.account.update({ 其中：{ id: toId }, 数据：{余额：{增量：金额}}， }); }); } ### 乐观锁定 -- 添加版本栏 ALTER TABLE 产品添加列版本 INT DEFAULT 1； -- 更新并检查版本更新产品设定库存 = 库存 - 1，版本 = 版本 + 1 其中 id = $1 并且版本 = $2 返回*； -- 如果没有返回行，则发生并发修改 ** ** ## 清单 ## 架构 - [ ] 主键的 UUID 或 BIGSERIAL - [ ] TIMESTAMPTZ 用于所有时间戳 - [ ] 数字表示金钱，而不是 FLOAT - [ ] 检查枚举的约束 - [ ] 带有 ON DELETE 的外键 ## 索引 - [ ] 每个 WHERE 子句模式的索引 - [ ] 复合索引匹配查询顺序 - [ ] JSONB 包含的 GIN 索引 - [ ] EXPLAIN ANALYZE 对于慢速查询 ## 缓存 - [ ] Cache-aside 作为默认模式 - [ ] 所有缓存数据的 TTL - [ ] 写入时缓存失效 - [ ] 踩踏/穿透保护 ## 操作 - [ ] 配置连接池 - [ ] 启用慢速查询日志记录 - [ ] 备份和恢复经过测试 - [ ] 增长分区策略 ** ** ## 另请参阅 * [reference/postgresql.md]() — PostgreSQL 深入研究 * [reference/redis.md]() — Redis 模式 * [reference/caching.md]() — 缓存策略

数据库模式

包含技能