3.1 数据库逻辑设计优化

3.1 数据库逻辑设计优化

本节摘要：逻辑设计决定数据怎么组织。本节讲清楚范式与反范式权衡、字段类型选择、主键设计、外键管理，让你设计出性能与规范兼顾的表结构。

范式与反范式

范式（Normal Form）：消除数据冗余，保证一致性。

• 1NF：原子性（字段不可分）。
• 2NF：非主键完全依赖主键（无部分依赖）。
• 3NF：非主键不传递依赖（无传递依赖）。
• BCNF：每个决定因素是候选键。

范式好处：冗余少、更新一致、空间省。坏处：关联多、JOIN 多、查询慢。

反范式：为查询性能冗余字段，违反范式。

• 冗余字段：如订单表冗余用户名，免 JOIN 查用户。
• 汇总字段：如商品表冗余销量，免每次 COUNT。
• 历史快照：如订单冗余下单时商品价格，防价格变动。

反范式好处：查询快（少 JOIN）。坏处：冗余、更新多处、一致性维护成本。

权衡：OLTP 高频小查询，适度反范式（冗余稳定字段）；OLAP 分析查询，重度反范式（宽表）。不要为范式牺牲性能，也不要为性能牺牲一致性——按场景权衡。

字段类型选择

类型影响存储、计算、索引性能：

1. 整数 vs 字符串

• INT/BIGINT 比 VARCHAR 快（整数比较快、索引小）。
• 用整数存 ID 而非字符串——如自增 ID 而非 UUID 字符串（UUID 36 字符，索引大、比较慢）。
• 若用 UUID，用 BINARY(16) 而非 VARCHAR(36)。

2. 定长 vs 变长

• CHAR(N) 定长，VARCHAR(N) 变长。
• 短且长度固定用 CHAR（如 state CHAR(2)），长且变化用 VARCHAR。
• CHAR 无需记录长度，稍快；VARCHAR 省 space。

3. 日期时间类型

• TIMESTAMP：4 字节，范围 1970-2038，时区转换。
• DATETIME：8 字节，范围大，无时区。
• PostgreSQL：TIMESTAMP（8 字节，无时区）/TIMESTAMPTZ（带时区）。
• 用合适类型——2038 后用 DATETIME，需时区用 TIMESTAMPTZ。

4. DECIMAL vs FLOAT

• 金额用 DECIMAL（精确），不用 FLOAT（浮点误差）。
• DECIMAL(15,2) 存金额——15 位整数 2 位小数。
• 科学计算用 FLOAT/DOUBLE。

5. TEXT/BLOB

• 大文本/二进制用 TEXT/BLOB，但：
• 不支持索引（或前缀索引）。
• 存储在额外页，查询慢。
• 考虑分离到独立表或对象存储。

6. ENUM

• 枚举值用 ENUM（如 status ENUM('active','inactive')）。
• 省空间（1-2 字节存索引而非字符串）。
• 但改枚举值要 ALTER TABLE，不灵活。频繁变用 VARCHAR+CHECK。

主键设计

主键影响索引、JOIN、复制性能：

1. 自增整数

• 自增 ID（AUTO_INCREMENT/SERIAL/IDENTITY）。
• 顺序插入——B+树顺序写，快。
• 索引小（4/8 字节）。
• JOIN 快（整数比较）。
• 缺点：可预测（爬虫）、分布式需协调。

2. UUID

• 全局唯一，分布式友好。
• 随机插入——B+树随机写，页分裂，慢。
• 索引大（16 字节 BINARY 或 36 字符字符串）。
• 改进：顺序 UUID（UUIDv7/ULID），顺序+唯一。

3. 雪花 ID

• 分布式自增（时间戳+机器+序列）。
• 趋势递增——B+树顺序写。
• 64 位整数——索引小。
• 适合分布式高并发。

4. 复合主键

• 多列主键（如 user_id + product_id）。
• 避免额外 ID 列。
• 但主键大、JOIN 复杂。通常用代理键（自增 ID）+ 唯一约束。

原则：单机用自增整数，分布式用雪花/顺序 UUID，避免随机 UUID 主键。

外键管理

外键：保证参照完整性，但影响性能：

• INSERT/UPDATE/DELETE 检查外键约束，增加开销。
• 加锁——父表更新锁子表，可能死锁。
• 复制——外键影响复制性能。

权衡：

• OLTP 强一致性——用外键（数据完整重要）。
• 高并发/分布式——不用外键，应用层保证一致性（性能优先）。
• OLAP——通常不用外键（分析查询，不更新）。

替代：应用层校验 + 唯一约束 + 逻辑外键（无约束但逻辑关联）。

表设计原则

1. 小表原则

• 单表不宜过大——千万行级考虑分区，亿级考虑分片。
• 列不宜过多——宽表更新锁多列，考虑垂直拆分。

2. 冷热分离

• 热数据（频繁访问）和冷数据（历史归档）分表。
• 如订单表（热）+ 订单历史表（冷），冷表归档。

3. 适度冗余

• 稳定字段冗余（如用户名，很少变）。
• 易变字段不冗余（如用户余额，频繁变）。

4. 预留扩展

• 字段命名清晰，预留扩展字段（如 ext_json）。
• 避免频繁 ALTER TABLE（大表 ALTER 慢）。

⚠️ 常见误读：以为"严格范式最好"。范式保证一致性但 JOIN 多查询慢。OLAP 重度反范式（宽表），OLTP 适度反范式（冗余稳定字段），按场景权衡。

💡 关键直觉：逻辑设计——范式（1NF/2NF/3NF/BCNF 消冗余保一致）vs 反范式（冗余字段/汇总/快照提查询），权衡（OLTP 适度反范式稳定字段、OLAP 重度反范式宽表）。字段类型——整数比字符串快（自增 ID 而非 UUID 字符串，UUID 用 BINARY(16)）、CHAR 定长/VARCHAR 变长、TIMESTAMP(4字节1970-2038)/DATETIME(8字节)、DECIMAL 金额（不用 FLOAT）、TEXT/BLOB 不索引分离存储、ENUM 省空间但不灵活。主键——自增整数（顺序写快索引小）、UUID（随机写慢索引大，用顺序 UUIDv7/ULID）、雪花（分布式趋势递增）、复合主键（通常代理键+唯一约束）。外键——OLTP 用（强一致）、高并发/分布式不用（应用层校验）、OLAP 不用。表原则——小表（千万分区亿分片）、冷热分离、适度冗余稳定字段、预留扩展避免频繁 ALTER。

逻辑设计要点

• 范式与反范式：范式（1NF 原子/2NF 完全依赖/3NF 无传递/BCNF 决定因素是候选键）消冗余保一致但 JOIN 多慢；反范式（冗余字段/汇总字段/历史快照）提查询但冗余更新多处。权衡：OLTP 适度反范式稳定字段，OLAP 重度反范式宽表。
• 字段类型：整数比字符串快（自增 ID 而非 UUID 字符串，UUID 用 BINARY(16)）、CHAR 定长短字段/VARCHAR 变长长字段、TIMESTAMP(4字节1970-2038时区)/DATETIME(8字节无时区)/PG TIMESTAMPTZ、DECIMAL 金额（不用 FLOAT 浮点误差）、TEXT/BLOB 不索引额外页分离存储、ENUM 省空间但不灵活（频繁变用 VARCHAR+CHECK）。
• 主键设计：自增整数（顺序插入 B+树顺序写快、索引小、JOIN 快，缺点可预测）、UUID（全局唯一分布式，随机插入页分裂慢，用顺序 UUIDv7/ULID）、雪花 ID（时间戳+机器+序列，趋势递增顺序写，64 位索引小，分布式高并发）、复合主键（通常代理键+唯一约束）。
• 外键管理：保证参照完整性但增开销（检查约束/加锁/复制），OLTP 强一致用外键，高并发/分布式不用（应用层校验+唯一约束+逻辑外键），OLAP 不用。
• 表设计原则：小表（千万分区亿分片）、列不宜多（宽表垂直拆分）、冷热分离（热表+历史表）、适度冗余（稳定字段冗余易变不冗余）、预留扩展（ext_json 避免频繁 ALTER）。

字段类型的隐性成本清单

逻辑设计的微观决策里，字段类型是最容易被轻视的一项，给一份隐性成本清单。过宽的主键：主键会被复制进每个二级索引，字符串主键比整型主键让所有索引体积膨胀数倍，索引内存效率与扫描速度全线下滑；分布式场景需要全局 ID 时，有序的整型族方案优于随机字符串。可空字段的坑：空值在索引与统计里的行为与直觉不同（部分优化器对空值列的估算偏差），非必要不设空、用默认值与哨兵值替代。浮点与定点：金额用浮点是事故预定（精度丢失的对账灾难），定点数或以最小单位存整数是铁律。超长字段：行溢出到独立页意味着一次行读取变两次 IO，大文本要么压缩、要么分表（主表瘦身上策）。枚举与字典表：枚举改动要改表结构，字典表只插数据，高频变更的枚举用字典表更稳。这份清单的每一行都对应着真实的故障复盘——类型选对的收益是隐性的（什么都没发生），选错的代价是显性的（半夜的告警电话），权衡的天平从一开始就倾向认真的人。

主键策略的深水区

逻辑设计的收官谈主键——它比看起来深。业务主键 vs 代理主键：用业务字段（身份证号、订单号）当主键，业务规则变更时（订单号格式调整）就是数据库的地动山摇；代理主键（自增或全局 ID）与业务解耦，代价是多一列索引空间——主流实践是代理主键加业务唯一索引，两全。分布式环境的主键：单库自增在分库后冲突，常用解法是号段分配（每次取一段）或雪花族算法（时间戳加机器加序列）；雪花族的隐性坑是时钟回拨（生成重复或错乱 ID），部署时要配时钟守护，这个坑踩过的人不多但都刻骨。主键与插入模式：有序主键让插入永远发生在 B 树最右侧（页顺序写、缓存友好），随机主键（如无序哈希）让插入散布全树（页分裂频繁）——第 2 节的页物理学在这里落地为选型建议。三条深水区合起来的选型决策树：单库小系统用自增足矣；分库系统按运维能力选号段或雪花；无论哪种，业务字段一律另建唯一索引。主键是表的心脏，值得设计会上最认真的十分钟。

设计评审的检查表

逻辑设计的知识浓缩成一张评审检查表，新表上线前逐项过。主键与标识：代理主键还是业务主键，分布式环境 ID 方案与时钟依赖确认。字段类型：金额定点化、时间统一时区、状态列有无枚举值文档、大字段是否分表。约束完整性：非空与默认值齐备、外键策略（物理外键还是应用层维护）与团队规范一致、唯一约束覆盖业务不变量（业务上的唯一就该有唯一索引兜底）。命名与文档：表与列的命名风格统一、每张表有注释说明用途与负责人、枚举值有字典。预留设计：软删除与审计字段（创建更新时间、操作人）是否按团队标准带上——它们不影响今天的性能，但影响半年后每一次排查的速度。检查表的价值不是形式主义，是"设计的隐含假设显性化"——评审桌上多问十分钟，上线后就少一次"这个字段当时为什么这么设计"的考古。

范式与业务的翻译练习

逻辑章收官做一个"范式与业务"的翻译练习——设计能力的最终形态是把业务语言实时翻译成结构约束。业务说"一个用户可以有多个收货地址，但只能有一个默认地址"——翻译成结构：地址表带用户外键加默认标志，加"每用户仅一条默认"的约束（部分库的表达式唯一索引，或应用层加事务保证）。业务说"订单金额以下单时为准，商品调价不影响历史订单"——翻译：订单表存金额快照而非引用商品表价格——这不是反范式，是业务语义的正确建模（快照字段本就该固化）。业务说"支持用户注销后数据保留一年再删除"——翻译：软删除标志加计划任务，删除路径要覆盖所有关联表（外键的级联策略此时要显式设计）。三个翻译例子的共性：好的逻辑设计不是范式的机械应用，是业务规则的忠实映射——每个业务不变量都要在结构里找到承载（约束、快照、标志位），找不到承载的规则将来必然靠人肉补丁维护，而人肉是会离职的。

收尾一句：逻辑设计的所有技巧，最后都汇成"让结构替人记住规则"这一件事——约束记不变量、快照记历史语义、类型记边界；结构没记住的规则，最终都会变成某次深夜故障复盘里的"当时怎么没考虑到"。设计每一列时多问一句"这个业务规则靠什么承载"，就是最好的设计习惯。