database-design

数据库设计

数据库设计决定了数据驱动型应用的长期可维护性、性能和正确性。早期的架构决策很难撤销。每个表、列、索引和约束都应有充分理由，并以访问模式和业务规则为依据。

数据建模原则

从访问模式入手

围绕应用读写数据的方式设计表，而不是围绕领域模型中的实体外观设计。两个问题决定了每项架构决策：

1. 哪些查询的运行频率最高？ 这决定了表结构、索引和反规范化方案。
2. 数据需要满足哪些一致性保证？ 这决定了规范化程度、约束和事务边界。

实体关系

关系	实现方式	适用场景
一对一	在子表中使用带 UNIQUE 约束的外键	将很少访问的列拆分到单独的表中，或强制实现恰好一个的语义
一对多	子表中的外键引用父表	订单与订单项、用户与地址
多对多	使用复合主键的联结表	标签与文章、学生与课程
带属性的多对多	除两个外键外，还包含自身列的联结表	包含成绩的选课记录、包含角色的成员关系
自引用	外键引用同一张表	组织架构图、类别树、嵌套评论

命名约定

统一的命名可避免团队和工具之间产生混淆：

• 表：使用 snake_case 复数名词（order_items、user_addresses）
• 列：使用描述其值的 snake_case 单数名称（created_at、total_amount）
• 外键：<referenced_table_singular>_id（user_id、order_id）
• 索引：idx_<table>_<columns>（idx_orders_user_id_created_at）
• 约束：chk_<table>_<rule>、uq_<table>_<columns>、fk_<table>_<referenced>

---

规范化与反规范化

范式

范式	规则	违反示例
1NF	每列都存放原子值；不存在重复组	在单列中存储以逗号分隔的标签
2NF	每个非键列都依赖于整个主键	在复合键表中，某列只依赖于键的一部分
3NF	非键列不依赖于其他非键列	同时存储 city 和 zip_code，而邮政编码决定城市
BCNF	每个决定因素都是候选键	排课表中教室决定楼宇，但教室不是键

何时进行反规范化

规范化可以避免异常，但会增加 JOIN。应有选择地进行反规范化，并且仅在以下情况进行：

• 读操作繁重的工作负载占主导，且通过性能分析确认 JOIN 开销显著
• 报表表需要预聚合数据，否则就必须执行开销高昂的查询
• 缓存计算值可避免每次读取时都重新计算（例如，将 order_total 存储在订单行中）
• 面向文档的访问需要一次读取整个聚合

安全反规范化的规则：

1. 始终保留规范化的数据源。反规范化数据是派生缓存
2. 明确反规范化副本的更新方式和时机（触发器、应用事件、批处理作业）
3. 监控数据源与副本之间的偏差
4. 记录反规范化的原因及其服务的访问模式

---

选择数据库类型

类型	优势	适用场景
关系型（PostgreSQL、MySQL）	ACID 事务、复杂查询、成熟的工具、JOIN	数据结构化且存在关联、事务型工作负载、大多数 CRUD 应用
文档型（MongoDB、DynamoDB）	灵活的架构、嵌套数据、水平扩展	以整体为单位访问的聚合数据、快速演变的架构、按租户隔离
键值型（Redis、Memcached）	亚毫秒级读取、简单的数据模型	会话存储、缓存、计数器、速率限制
列族型（Cassandra、ScyllaDB）	高写入吞吐量、宽行、线性扩展	时间序列、IoT 遥测、写入密集型工作负载
图数据库（Neo4j、Neptune）	遍历查询、以关系为核心的数据	社交网络、推荐引擎、欺诈检测
时序型（TimescaleDB、InfluxDB）	针对带时间戳的数据优化、自动分区	指标、监控、金融行情数据

只有在不同部分的访问模式存在差异时，才有理由采用多模型持久化（在同一系统的不同部分使用不同数据库）。这并不是逃避深入学习某一种数据库的方式。

---

索引基础

索引可以加快读取，但会降低写入速度并增加存储开销。每个索引都必须有查询模式作为依据。

索引类型

类型	结构	最适用于
B-tree	平衡树、有序数据	等值和范围查询、ORDER BY、大多数通用索引场景
Hash	哈希表	仅适用于精确等值查找；不支持范围查询
GiST	通用搜索树	空间数据、几何查询、范围类型、最近邻查询
GIN	通用倒排索引	全文搜索、JSONB 包含查询、数组成员查询
BRIN	块范围索引	数据按自然顺序存储的大型表（时间戳、递增 ID）

复合索引设计

复合索引中列的顺序很重要。最左前缀规则意味着，针对 (a, b, c) 创建的复合索引支持按 (a)、(a, b) 或 (a, b, c) 进行筛选的查询，但不支持仅按 (b, c) 查询。

列排序指南：

1. 等值条件列在前（使用 = 比较的列）
2. 范围条件列在后（使用 >、<、BETWEEN 比较的列）
3. 等值条件列中，区分度最高的列在前

覆盖索引和部分索引

• 覆盖索引：包含查询所需的所有列，因此数据库只需读取索引。使用 INCLUDE（PostgreSQL），或将列添加到索引键中。
• 部分索引：只为符合条件的行创建索引，从而减少索引大小和写入开销。适用于查询大型表中的小部分数据（例如，WHERE status = 'pending'）。

详见 索引策略参考，了解详细的索引类型、EXPLAIN 分析和反模式。

---

架构演进

架构变更不可避免。关键在于这些变更是否会导致运行中的应用出现问题。

向后兼容的变更（安全）

• 添加新的可空列
• 添加新表
• 添加新索引（某些引擎可能会短暂锁表）
• 扩大列类型的范围（例如，从 VARCHAR(50) 改为 VARCHAR(100)）

破坏性变更（需要迁移策略）

• 重命名或删除列
• 以不兼容的方式更改列类型
• 在包含空值的现有列上添加 NOT NULL 约束
• 拆分或合并表

扩展-收缩模式

对于生产环境中的破坏性变更，可按以下步骤实现零停机：

1. 扩展： 在旧结构旁添加新结构
2. 迁移： 将数据从旧结构回填到新结构，并在过渡期间双写
3. 切换： 更新应用代码以使用新结构
4. 收缩： 确认没有任何内容引用旧结构后，将其删除

详见 迁移模式参考，了解零停机策略、回滚技巧和多工具示例。

---

分区与分片

表分区（单个数据库）

策略	工作方式	适用场景
范围	按值范围拆分行（例如按月）	时间序列数据、日志表、归档
列表	按离散值拆分行（例如按区域）	多租户数据、地理分区
哈希	根据某列的哈希值分发行	不存在自然范围时实现均匀分布

分片（多个数据库）

分片会将数据分布到单独的数据库实例中。只有在单实例优化（索引、缓存、只读副本）都已用尽后，才使用分片。

分片键选择标准：

• 高基数：具有许多不同的值，以便均匀分布
• 出现在大多数查询中：避免对所有分片进行分散-收集
• 稳定：创建后不会更改的值
• 无热点：不要按会集中写入的值分片（例如当前日期）

---

快速参考：常见设计错误

错误	后果	修复方法
没有外键约束	孤立行、数据不一致	除非有记录在案的理由，否则始终定义外键
索引过多	写入缓慢、浪费存储空间	仅为实际查询中 WHERE、JOIN、ORDER BY 使用的列创建索引
存储计算值但未制定刷新策略	数据过时、隐蔽的错误	对每项反规范化，都定义更新触发器、事件或批处理作业
对会变化的值使用 ENUM 类型	每增加一个新值都要迁移架构	改用带外键的查找表
以浮点数存储金额	舍入误差	使用 DECIMAL/NUMERIC，或将金额存储为整数分
缺少 created_at / updated_at 时间戳	无法审计，难以调试	默认在每张表中添加时间戳列
到处使用通用的 type + type_id 多态模式	没有引用完整性，查询复杂	先评估 STI、CTI 或独立表

---

参考文件

参考资料	内容
建模模式	多态关联（STI/CTI/TPT）、软删除、审计跟踪、时态数据、自引用树、JSON 列
索引策略	B-tree/hash/GiST/GIN 详解、复合索引设计、覆盖索引和部分索引、EXPLAIN 分析、反模式
迁移模式	基于版本与基于状态的迁移、扩展-收缩、数据迁移、回滚策略、多工具示例

---

与其他技能集成

场景	推荐技能
优化查询性能和缓存	performance
设计领域模型和聚合	clean-architecture
构建可公开数据库资源的 API	api-design
测试数据库交互	testing