database-schema-design

数据库架构设计

概述

指导数据库架构的设计、实现和优化，采用可靠的数据建模、安全的迁移、有效的索引以及恰当的查询模式。本技能涵盖从概念建模到物理优化的完整生命周期，确保架构经过规范化、性能良好且能够安全演进。

开始时宣告：“我正在使用 database-schema-design 技能来设计数据库架构。”

阶段 1：需求发现与概念模型

通过以下问题了解数据需求：

#	问题	确定内容
1	系统管理哪些实体？	表名
2	实体之间有哪些关系？	外键、联接表
3	每个实体有哪些关键属性？	列定义
4	主要查询模式是什么？	索引策略
5	预期数据量是多少？（行数、增长率）	分区、扩展
6	读写比例是多少？	规范化还是反规范化
7	使用 SQL 还是 NoSQL？（或两者都用？）	存储引擎选择

存储引擎决策表

因素	选择 SQL（PostgreSQL、MySQL）	选择文档型（MongoDB）	选择键值型（Redis）
数据形态	结构化、关系型	半结构化、嵌套	简单查找、缓存
查询复杂度	复杂联接、聚合	文档级查询	仅基于键访问
一致性需求	需要 ACID	可接受最终一致性	临时数据或缓存数据
架构演进	迁移易于管理	灵活的无架构模式	无架构
扩展模式	先纵向扩展，再添加只读副本	水平分片	内存中存储，容量有限

完成需求发现后暂停——提交概念模型（实体、关系、基数）以供确认。

阶段 2：逻辑模型设计

将概念模型转换为表、列、类型和约束。

列设计规则

决策	指导原则
主键	分布式系统使用 UUID，单节点使用自增值
列类型	使用最具体的类型（日期使用 timestamptz，而不是 varchar）
是否允许 NULL	默认 NOT NULL；仅在缺失具有实际含义时允许 NULL
默认值	设置合理的默认值（created_at DEFAULT now()）
约束	在架构层添加 CHECK、UNIQUE 和 FK 约束
命名	使用 snake_case，表名用单数或复数均可——保持一致

规范化指南

范式	规则	违反示例	修复方法
1NF	值具备原子性，不包含重复组	tags VARCHAR "urgent,priority,vip"	拆分为单独的 order_tags 表
2NF	所有非键列都依赖于完整主键	order_items 中的 product_name（复合主键）	移至 products 表
3NF	不存在传递依赖	city 依赖于 zip_code，而非 user_id	拆分为单独的 zip_codes 表

规则： 始终从规范化开始。只有在有测量数据作为依据时才进行反规范化。

反规范化决策表

场景	模式	适用时机
读取密集型仪表盘	物化视图或汇总表	测量结果显示查询缓慢
频繁联接的数据	嵌入为 JSONB 列	联接占查询时间的 80% 以上
报表 / 分析	单独的反规范化报表表	OLAP 工作负载
缓存层	写入时刷新的计算列	高频读取

关系模式

关系	实现方式	所需索引
一对一	在子表中使用带 UNIQUE 约束的 FK	FK 列
一对多	在“多”端使用 FK	FK 列
多对多	使用带复合 PK 的关联表／连接表	两个 FK 列
多态	使用带 CHECK 约束的独立 FK 列（首选），或类型 + ID 模式	类型 + ID 或每个 FK
自引用（树结构）	parent_id FK 指向同一张表；或使用 ltree／物化路径	parent_id 或路径

在逻辑模型完成后停止——提供表定义以供审核。

第 3 阶段：物理模型和索引

索引类型决策表

索引类型	最适用场景	示例
B-tree（默认）	等值和范围查询	CREATE INDEX idx_users_email ON users(email)
GIN	全文搜索、JSONB、数组	CREATE INDEX idx_posts_search ON posts USING GIN(to_tsvector('english', body))
部分索引	符合条件的行子集	CREATE INDEX idx_active_users ON users(email) WHERE active = true
覆盖索引（INCLUDE）	避免查表的仅索引扫描	CREATE INDEX idx_users_email ON users(email) INCLUDE (name)
复合索引	多列查询	CREATE INDEX idx_orders ON orders(tenant_id, status)

复合索引列顺序

位置	列类型	原因
第一	高基数等值列	优先使用选择性最高的筛选条件
中间	其他等值列	进一步缩小结果范围
最后	范围列（日期、数字）	对剩余行执行范围扫描

规则： (A, B, C) 上的复合索引支持对 A、A+B、A+B+C 的查询，但不支持单独查询 B 或 C。

查询优化检查清单

EXPLAIN ANALYZE 中的信号	问题	修复方法
大表上的 Seq Scan	缺少索引	添加适当的索引
外层表很大的 Nested Loop	连接效率低	添加索引或重构查询
实际行数与估算行数相差很大	统计信息过期	对表运行 ANALYZE
Hash Join 内存占用高	work_mem 太低	调整 work_mem 或重构查询

N+1 检测与预防

-- N+1 problem (bad):
SELECT * FROM users;
-- Then for EACH user: SELECT * FROM orders WHERE user_id = ?;

-- Fixed with join:
SELECT u.*, o.* FROM users u LEFT JOIN orders o ON o.user_id = u.id;

-- Fixed with batch load:
SELECT * FROM orders WHERE user_id = ANY($1);

在物理模型完成后停止——提供索引和优化策略以供审核。

第 4 阶段：迁移策略

零停机迁移（扩展-收缩）

切勿在单次迁移中进行破坏性变更。分两个阶段进行：

扩展阶段（向后兼容）：

1. 添加新列／新表（允许为空或设置默认值）
2. 部署同时写入旧位置和新位置的代码
3. 分批回填现有数据
4. 部署从新位置读取数据的代码

收缩阶段（所有代码都使用新架构后）：

1. 移除写入旧位置的代码
2. 删除旧列／旧表

迁移安全规则

规则	理由
每个迁移都要有对应的回滚方案	确保可以安全恢复
在生产环境部署前，先在预发布环境测试回滚	验证可逆性
破坏数据的回滚需要明确批准	防止意外丢失数据
迁移文件应用后保持不变	确保状态可复现
对大型表分批回填（每批 1000 行）	避免锁表

回填模式

-- Backfill in chunks of 1000
UPDATE users SET display_name = username
WHERE display_name IS NULL
AND id IN (SELECT id FROM users WHERE display_name IS NULL LIMIT 1000);

迁移类型决策表

变更类型	安全做法	危险做法
添加列	添加可空列或设置默认值	添加无默认值的 NOT NULL 列
删除列	扩展-收缩（分两次部署）	直接删除列
重命名列	添加新列、复制数据、删除旧列	ALTER RENAME（会导致查询失效）
添加索引	CREATE INDEX CONCURRENTLY	CREATE INDEX（会锁表）
更改列类型	添加新列并迁移数据	ALTER COLUMN TYPE（会锁表）

在迁移计划完成后停止——在最终确定前确认回滚策略。

阶段 5：保存并过渡

获得明确批准后：

1. 将架构设计保存到 docs/database/，或生成迁移文件
2. 提交信息：docs(db): add schema design for <feature>

过渡决策表

用户意图	下一个技能	原因
“创建迁移”	planning	规划迁移实现
“为此编写规格”	spec-writing	编写数据操作的行为规格
“实现架构”	test-driven-development	使用迁移测试进行 TDD
“只保存设计”	无	架构设计就是交付成果
“检查性能”	performance-optimization	分析查询模式

ORM 指南

ORM	语言	优势	注意事项
Prisma	TypeScript	类型安全的架构、迁移	嵌套查询中的 N+1 问题、原生 SQL 支持有限
Drizzle	TypeScript	类 SQL 的 API、轻量	生态系统较新，指南较少
SQLAlchemy	Python	成熟、灵活、支持原生 SQL	会话管理复杂
GORM	Go	基于约定、自动迁移	静默失败、隐式行为

ORM 最佳实践

• 始终检查生成的 SQL（在开发环境中启用查询日志）
• 使用预加载来避免 N+1 查询
• 对复杂查询编写原生 SQL，而不是勉强使用 ORM
• 使用 ORM 迁移，不要在生产环境中自动同步
• 使用真实的数据量测试查询性能

连接池

• 使用连接池工具（PgBouncer、内置连接池）
• 连接池大小公式：connections = (CPU cores * 2) + disk spindles
• 对大多数工作负载使用事务级连接池
• 应用服务器不应打开原始连接

反模式／常见错误

错误	错误原因	正确做法
不设外键约束	产生孤立数据、破坏关系	始终定义 FK 约束
所有字段都用 VARCHAR	丧失类型安全、浪费存储空间	使用特定类型（timestamptz、int、uuid）
FK 列不建索引	关联表连接缓慢	为每个 FK 列建立索引
过早反规范化	增加复杂度，却没有经测量证实的收益	从规范化开始，有证据后再反规范化
直接删除列	破坏正在运行的应用代码	使用扩展-收缩模式
不带 CONCURRENTLY 执行 CREATE INDEX	创建索引期间锁定表	在生产环境中始终使用 CONCURRENTLY
在生产环境中自动同步架构	可能产生不可预期的破坏性更改	使用显式迁移文件
不为迁移制定回滚计划	部署失败后无法恢复	为每个向上迁移编写对应的回滚迁移
到处使用可空列	丧失数据完整性保障	默认使用 NOT NULL，有意为之时才允许 NULL

防止自我合理化的守则

• 不要跳过概念模型——先了解实体及其关系
• 不要臆测性地添加索引——先测量查询模式
• 不要在没有经测量证实的性能问题时反规范化
• 不要在没有回滚计划的情况下创建迁移
• 不要跳过探索阶段——先了解查询模式和数据量
• 不要在生产环境中不使用扩展-收缩模式就删除列或表

文档查找（Context7）

使用 mcp__context7__resolve-library-id，然后使用 mcp__context7__query-docs 查找最新文档。返回的文档优先于记忆中的知识。

• prisma — 用于架构语法、关系或迁移 API
• typeorm — 用于实体装饰器、仓库模式或查询构建器
• knex — 用于查询构建器语法、迁移或种子文件

---

集成点

技能	关系
api-design	上游：API 资源映射到数据库实体
spec-writing	上游：规范定义数据持久化要求
planning	下游：架构设计为实现计划提供依据
test-driven-development	下游：在编写迁移代码前先编写迁移测试
performance-optimization	下游：架构上线后进行查询优化
reverse-engineering-specs	上游：对现有架构的行为进行逆向工程
senior-backend	并行：负责 ORM 和查询模式的后端专家

验证关卡

在声称架构设计已完成之前：

1. 验证所有实体和关系均已建模
2. 验证规范化至少达到 3NF（或有充分理由进行反规范化）
3. 验证已为所有查询模式和 FK 列定义索引
4. 验证迁移策略为每个步骤都包含回滚方案
5. 验证用户已批准架构设计
6. 验证已为生产环境定义连接池策略

技能类型

灵活 — 根据项目需求调整存储引擎、规范化级别和索引策略，同时保留从概念建模到物理建模的过程、迁移安全规则，以及反规范化前先取得实测证据的原则。