数据库设计与建表规范手册

LukeLi
3 阅读5分钟

一、 命名规矩：拒绝“猜谜语”

良好的命名是代码自解释的第一步。

  1. 全小写 + 下划线： 禁止使用驼峰命名（如 UserName），一律使用 user_name。

    • 原因： 不同操作系统对 MySQL 大小写敏感性不同，全小写能规避兼容性坑。

  2. 见名知意： 禁用缩写（除非是 org, dept 这种公认的）。禁止使用 table1, data_01。

  3. 表名复数 vs 单数： 建议使用单数。例如使用 user 而非 users。

  4. 模块前缀： 表名建议带上业务模块前缀。

    • 示例： oms_order (订单模块), pms_product (商品模块)。

二、 字段选型：够用、精准、高效

  1. 主键 (Primary Key)：

    • 强制： 每个表必须有主键。
    • 推荐： 使用 BIGINT UNSIGNED（无符号大整型），对应 Java 的 Long。
    • 规矩： 禁止使用 UUID 或字符型作为主键，会严重拖慢 B+ 树索引插入效率。

  2. 金钱与精度：

    • 强制： 涉及金钱、费率、精度要求高的数值，必须使用 DECIMAL 类型。
    • 严禁： 使用 FLOAT 或 DOUBLE。

  3. 字符串长度：

    • 规矩： VARCHAR(N) 中的 N 代表字符数。
    • 常用： 用户名、标题类用 VARCHAR(64) 或 VARCHAR(128)；长文本用 TEXT。

  4. 所有字段尽量 NOT NULL：

    • 原因： NULL 字段会使索引统计变得复杂，且在 Java 中容易引发 NullPointerException。

三、 结构规矩：“五金件”必带

为了方便审计和后期运维，每个表必须包含以下 5 个字段：

字段名类型默认值描述
idBIGINT自增 / 分布式 ID主键
create_timeDATETIMECURRENT_TIMESTAMP数据创建时间
update_timeDATETIMEON UPDATE CURRENT_TIMESTAMP数据最后修改时间
is_deletedTINYINT(1)0逻辑删除标识（0正常，1已删）
versionINT0乐观锁版本号

四、 索引规矩：快而不乱

  1. 唯一索引 (Unique Index)： 业务上有唯一性要求的字段（如手机号、订单号），必须建唯一索引。不要指望代码校验，数据库是最后防线。
  2. 索引个数： 单表索引建议不超过 5 个。索引越多，写操作越慢。
  3. 最左匹配原则： 建立联合索引 (a, b, c) 时，查询条件必须包含 a 才能触发索引。

五、 关联规矩：代码大于外键

  1. 禁止使用物理外键 (Foreign Key)：

    • 原因： 物理外键在分库分表时无法迁移，且在大并发下会造成级联死锁。
    • 规矩： 关联关系由 Java 业务代码层维护。

  2. 禁止三表以上 JOIN：

    • 如果需要关联多个表，建议在代码里分步查询，或通过字段冗余、缓存来解决。

六、 Java 映射规矩 (针对 MyBatis/JPA)

  1. POJO 匹配： 数据库 is_deleted 映射到 Java 实体类必须是 Boolean deleted（注意：Java 规范建议布尔值不带 is 前缀，框架会自动处理）。
  2. 时间映射： 统一使用 java.time.LocalDateTime。
  3. 大字段警示： 表中如果有 TEXT 或 BLOB 字段，查询时严禁 SELECT *，必须按需取字段，否则会瞬间塞满内存。

七、 进阶避坑：那些代价昂贵的“常识”

1. 逻辑删除与唯一索引的冲突（必看！）

  • 背景： 很多表要求某个字段（如 phone）唯一，同时表里有 is_deleted 字段。

  • 坑点： 如果用户 A 删除了账号（is_deleted=1），新用户 B 用同一个手机号注册时，由于数据库里已经有一个 phone='138...' 的记录，唯一索引会报错，导致无法注册。

  • 规矩： * 方案 A： 唯一索引包含删除标识，建立复合索引 UNIQUE(phone, is_deleted)。但注意，这样只能支持“删除一次”，再删第二次又会冲突。

    • 方案 B（推荐）： 删除时将 is_deleted 置为 主键 ID，未删除时为 0。索引设为 UNIQUE(phone, is_deleted)。

2. 这里的 DEFAULT NULL 是万恶之源

  • 规矩： 严禁字段不写默认值且允许为 NULL。

  • 原因： * NULL 会让 COUNT(column) 的结果不符合预期（不统计 NULL）。

    • 在复合索引中，NULL 会让索引统计信息变得极其复杂，导致优化器选错索引。
    • 要求： 数字型默认 0，字符型默认空字符串 ''。

3. 索引失效的“隐形杀手”：类型不匹配

  • 场景： 字段 user_id 是 VARCHAR 类型，但 Java 代码传参是 Long。
  • 后果： 触发 MySQL 的隐式类型转换，导致全表扫描（索引失效） 。
  • 规矩： 实体类类型必须与数据库类型严格对齐。

4. 关于 COMMENT 的硬性要求

  • 规矩： 每个字段必须有 COMMENT。如果字段是枚举值（如 status），必须在注释里写清楚：0:下线, 1:上线, 2:待审批。
  • 原因： 数据库是唯一的真理来源。不要让后来者去翻你的 Java 代码猜 1 是什么意思。

八、 数据库变更“军规”

这部分是给新人的行为准则，防止他们由于操作不当把库搞挂：

  1. 严禁在大表（万级以上）执行 ALTER TABLE：

    • 加字段、改索引会锁表。必须找 DBA 或使用 pt-online-schema-change 等工具。

  2. DML 语句必须带 WHERE：

    • UPDATE 和 DELETE 之前，先写成 SELECT 跑一遍，确认范围。

  3. 禁止在程序中使用 SELECT *：

    • 只取需要的列。这不仅是为了节省网络带宽，更是为了索引覆盖（Index Covering），让查询直接在索引树上完成，不回表。

💡 给新人的温馨提示：

“建表不仅是给数据库看的，更是给未来的同事（包括三个月后的你自己）看的。每一个 COMMENT（注释）都要写清楚这个字段是干嘛的，枚举值 0 和 1 分别代表什么。没有注释的数据库字段，就是埋在项目里的地雷。

好的数据库设计就像房子的地基：

  1. 规范：是为了让别人能看懂。
  2. 类型：是为了让性能最高。
  3. 索引：是为了让查询最快。
  4. 冗余/拆分：是为了让扩展性更强。

请记住：所有的性能优化，最廉价也最高效的方式，永远是在建表那一刻完成的。 ”

avatar
文章
阅读
粉丝