数据库设计指南：规范化、ERD、索引策略与 SQL vs NoSQL（2026）

简介：为什么数据库设计很重要

数据库是几乎所有应用程序的基础。一个设计良好的数据库模式（schema）能使查询高效、代码简洁、未来的变更易于管理。 而一个设计糟糕的模式则会带来数据完整性问题、性能瓶颈和级联技术债务，这些问题在后期极难修复。

数据库设计既是科学，也是艺术。科学部分涉及正式的规范化理论、关系代数和查询优化； 艺术部分则是理解访问模式、预见未来需求，以及知道何时因实际原因打破规则。

规范化：从原始数据到整洁的数据库模式

规范化是根据一系列正式规则（范式）组织关系型数据库的过程，目的是减少数据冗余并防止更新异常。 每个范式都建立在上一个范式的基础上。规范化为写操作的一致性而优化，而反规范化则以一定的冗余来换取读性能。

常见的规范化步骤：

• 第一范式（1NF）：每列只包含原子值，无重复组，每行可唯一标识。
• 第二范式（2NF）：满足 1NF，且所有非键属性完全依赖于整个主键（仅对复合主键有意义）。
• 第三范式（3NF）：满足 2NF，且所有非键属性直接依赖于主键，不存在传递依赖。
• BC 范式（BCNF）：3NF 的更严格版本，处理多个重叠候选键的边缘情况。

ERD 设计：实体关系图

实体关系图（ERD）是数据库模式的可视化蓝图。在编写 SQL 之前创建 ERD 可以防止设计错误， 并为开发人员、DBA 和利益相关者提供共同的语言。

ERD 包含三个基本构建块：

• 实体（Entities）：表示为表的现实世界对象（如用户、产品、订单）
• 属性（Attributes）：实体的属性，表示为列（如 user.email、product.price）
• 关系（Relationships）：实体之间的关联，由基数（一对一、一对多、多对多）表征

ERD 设计流程：识别实体 → 定义属性 → 确定关系与基数 → 定义主键 → 添加外键 → 应用规范化 → 与利益相关者评审验证。

主键与外键

键是关系数据库完整性的基石。选择正确的键策略对性能、简洁性和可扩展性有重大影响。

• 自增整数：最简单、最常见；紧凑，索引速度快；缺点是暴露业务数据量且非全局唯一。
• UUID v4：随机、全局唯一；不暴露 ID 枚举信息，适合分布式系统；缺点是更大（16字节）且随机插入会导致 B 树页面碎片化。
• UUID v7（推荐）：时间有序的 UUID，兼具全局唯一性和顺序性（无碎片化），可按时间排序。是大多数现代应用的最佳选择。
• 自然键：直接使用有意义的列（如 ISO 国家代码）；慎用，因为自然键可能会随时间变化。

外键约束通过 ON DELETE CASCADE（删除父记录时级联删除子记录）、ON DELETE SET NULL（设置为 NULL）或 ON DELETE RESTRICT（阻止删除，默认行为） 来控制引用完整性。务必为所有外键列手动添加索引——数据库不会自动创建。

索引策略

索引是提升查询性能最有效的工具。了解不同索引类型的工作原理和使用时机，是优秀数据库设计与卓越数据库设计的分水岭。

• B 树索引（默认）：支持等值查询、范围查询和 ORDER BY；是最通用的索引类型。
• 复合索引：列的顺序至关重要——复合索引 (a, b) 可用于仅查询列 a 的情况，但不能用于仅查询列 b 的情况（前导列规则）。等值列放在范围列之前。
• 部分索引：只对满足条件的行建立索引（如只索引 status = 'active' 的用户），更小、更快。
• 覆盖索引：将查询所需的所有列包含在索引中，数据库无需回表即可完整回答查询。
• GIN 索引：用于全文搜索和 JSONB 包含查询。
• BRIN 索引：适用于具有自然顺序的超大表（如日志、时序数据），远比 B 树小。

始终使用 EXPLAIN ANALYZE 验证索引是否被实际使用。定期检查并删除未使用的索引——它们会拖慢写操作而不带来任何读取收益。 在 PostgreSQL 中，使用 CREATE INDEX CONCURRENTLY 在不锁表的情况下添加索引。

SQL 与 NoSQL 的选择

在系统设计中最关键的决策之一是选择关系型（SQL）还是非关系型（NoSQL）数据库。两者都有合理的使用场景，现代应用通常同时使用两者。

选择 SQL 的场景：数据有明确关系、需要 ACID 事务、需要复杂的多表 JOIN 查询、数据完整性至关重要（银行、电商订单）。

选择 NoSQL 的场景：需要跨多台服务器水平扩展、数据非结构化或高度可变（文档存储）、需要极高写入吞吐量（时序数据、日志）、需要灵活的 schema 演进。

大多数现代应用两者都会用到：SQL 用于核心事务数据，NoSQL 用于缓存、会话或分析。

关系建模：一对多与多对多

正确建模关系是数据库设计中最实用的技能。

• 一对多（1:N）：外键始终放在关系的"多"一侧。例如，一个用户拥有多篇文章，则 posts 表中有 user_id 外键。
• 多对多（M:N）：需要一个联结表（也称为桥接表或关联表），其中包含指向两个相关表的外键。联结表的主键通常是两个外键的复合键。联结表还可以携带关于关系本身的附加数据（如注册时间、成绩）。
• 自引用关系：表可以通过外键引用自身来表示层级数据（如类别的子类别、员工的上级）。可使用递归 CTE（WITH RECURSIVE）高效地遍历层级结构。

性能优化

数据库性能优化是一个迭代过程：先建立良好的 schema 设计，然后测量，再优化。切勿在未经测量的情况下过早优化。

• 使用键集分页（Keyset Pagination）代替 OFFSET：OFFSET 随页码增大而变慢；键集分页性能恒定。
• 避免 SELECT *：只获取需要的列，尤其要避免拉取大型 TEXT/BYTEA 列。
• 存在性检查用 EXISTS 而非 COUNT：EXISTS 在找到第一个匹配项后即停止扫描。
• 批量插入：将多条插入合并为一条多行 INSERT 语句，或使用 PostgreSQL 的 COPY 命令进行大批量加载。
• 物化视图：将耗时的聚合结果预计算并存储，查询时直接读取，定期刷新。

事务与 ACID 属性

ACID 代表原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）：

• 原子性：事务要么完全成功，要么完全回滚——永远不会有部分更新。
• 一致性：数据库从一个有效状态转变为另一个有效状态，所有约束均得到满足。
• 隔离性：并发事务互不干扰（通过隔离级别控制：READ COMMITTED、REPEATABLE READ、SERIALIZABLE）。
• 持久性：事务一旦提交，数据就通过预写日志（WAL）在系统崩溃后依然存活。

PostgreSQL 默认隔离级别为 READ COMMITTED，适用于大多数 OLTP 应用。对于报表或需要一致快照的场景，使用 REPEATABLE READ。对于金融系统或需要最高正确性的场景，使用 SERIALIZABLE。

最佳实践