数据库设计黄金规范：从命名到索引的系统化最佳实践
Hyman 2025/11/6 实践总结最佳
# 1. 引言
本规范旨在统一数据库设计标准，保障数据一致性、完整性、性能与可维护性，适用于所有业务系统的数据库设计（如 MySQL、PostgreSQL 等关系型数据库），需结合业务场景灵活落地。
# 2. 命名规范
# 2.1 核心原则
统一采用 “小写字母 + 下划线” 命名法，禁止混合大小写、特殊字符（如
#、$）或数据库关键字（如select、order）；所有命名需体现业务含义，避免无意义缩写或数字标识（如
index1、t1）；表名、字段名、索引名长度控制在 30 字符内（避免超出数据库命名长度限制）。
# 2.2 各对象命名规范
| 对象类型 | 规范要求 | 正例 | 反例 | 反例问题 |
|---|---|---|---|---|
| 表名 | 业务模块前缀 + 核心含义（如user_、order_） | user_info（用户信息表）、order_detail（订单明细表） | UserInfo（混合大小写）、order（无模块前缀 + 关键字） | 协作理解成本高，需转义关键字 |
| 字段名 | 核心含义 + 统一后缀（如时间用_at、状态用_status） | user_id（用户 ID）、created_at（创建时间） | uid（无意义缩写）、CreatedTime（驼峰命名） | 新人需额外文档理解，命名不统一 |
| 索引名 | 类型标识（idx_普通索引 /uni_唯一索引）+ 字段组合 | idx_user_id_status（用户 ID + 状态普通索引）、uni_user_phone（用户手机号唯一索引） | index2（无含义）、user_index（无类型标识） | 维护时无法快速判断索引用途 |
| 视图名 | 前缀view_+ 业务含义 | view_user_order（用户订单关联视图） | user_order_view（前缀不统一） | 无法快速区分视图与表 |
# 3. 结构设计规范
# 3.1 范式与反范式平衡
优先遵循第三范式：消除数据冗余与传递依赖（如用户手机号仅存于
user_info，不冗余到order_detail）；非核心场景适当反范式：高频查询表（如商品详情页）可冗余关联字段（如
product表冗余category_name），减少 JOIN 操作，需通过定时任务 / 消息队列同步数据。
# 3.2 表拆分与扩展
| 规范要求 | 正例 | 反例 | 反例问题 |
|---|---|---|---|
| 大表拆分 | 订单表按时间分表：order_202501、order_202502（单表数据≤1000 万） | 单表存储 5 亿订单数据 | 查询耗时从 100ms 增至 5s，索引失效 |
| 字段扩展 | 预留扩展字段需注明用途：ext1 VARCHAR(64) COMMENT '预留：用户等级标识' | 无注释的ext1、ext2 | 后续维护无法判断字段用途，易滥用 |
| 附属表拆分 | 大字段（如用户头像、文章内容）拆分至附属表：user_avatar（存头像路径） | 主表user_info含avatar BLOB字段 | 主表查询时加载大字段，性能下降 |
# 4. 字段设计规范
# 4.1 通用字段设计原则
优先选择 “最小且合适” 的数据类型（如状态用
TINYINT，不用INT）；非必要字段不设
NOT NULL，关键字段（如user_id）设默认值或非空约束；禁止用字符串存储日期、金额、状态等结构化数据。
# 4.2 常用字段专项规范
# 4.2.1 时间类常用字段
| 字段名 | 数据类型 | 适用场景 | 正例 | 反例 | 设计原则 |
|---|---|---|---|---|---|
created_at | datetime（时区同步用timestamp） | 数据创建时间（用户注册、订单生成） | created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP | create_time VARCHAR(20)（存2025-11-06） | 统一用_at后缀，禁止字符串存储，设默认值 |
updated_at | datetime | 数据最后更新时间（订单状态修改） | updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP | update_time（无自动更新） | 设 “更新时自动填充”，避免手动维护 |
deleted_at | datetime | 软删除标记（替代物理删除） | deleted_at datetime DEFAULT NULL（未删除为NULL） | is_delete TINYINT(1)（仅存是否删除） | 保留删除时间便于追溯，不用布尔型单独存储 |
expired_at | datetime | 过期时间（优惠券、会员有效期） | expired_at datetime NOT NULL | expire_date DATE（仅存日期，无时间） | 需精确控制过期时间时用datetime |
# 4.2.2 状态类常用字段
| 字段名 | 数据类型 | 适用场景 | 正例 | 反例 | 设计原则 |
|---|---|---|---|---|---|
status | TINYINT UNSIGNED | 通用状态（订单：0 = 未支付，1 = 已支付） | status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '0=未支付，1=已支付，2=已取消' | status VARCHAR(10)（存 “未支付”） | 用数字枚举 + 注释，禁止字符串存储 |
audit_status | TINYINT UNSIGNED | 审核状态（文章：0 = 待审核，1 = 通过） | audit_status TINYINT UNSIGNED NOT NULL DEFAULT 0 | check_status（命名不统一） | 业务场景 +_status，避免自定义模糊命名 |
is_valid | TINYINT(1) | 数据有效性（0 = 无效，1 = 有效） | is_valid TINYINT(1) NOT NULL DEFAULT 1 | is_active/is_enable（混用） | 布尔型状态统一用is_+形容词 |
# 4.2.3 关联类常用字段
| 字段名 | 数据类型 | 适用场景 | 正例 | 反例 | 设计原则 |
|---|---|---|---|---|---|
user_id | 与user_info.id一致（如BIGINT） | 关联用户表 | user_id BIGINT NOT NULL COMMENT '关联user_info.id' | uid INT（缩写 + 类型不匹配） | 关联表名 +_id，类型与被关联主键一致 |
parent_id | 与当前表id一致 | 自关联（分类上下级、评论回复） | parent_id BIGINT DEFAULT 0 COMMENT '父分类ID，0=顶级' | pid（缩写） | 自关联固定用parent_id |
# 4.2.4 标识 / 描述类常用字段
| 字段名 | 数据类型 | 适用场景 | 正例 | 反例 | 设计原则 |
|---|---|---|---|---|---|
id | BIGINT UNSIGNED | 表主键（自增） | id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY | user_id（主键名不统一） | 单表主键固定用id，自增 + 无符号 |
uuid | CHAR(36) | 全局唯一标识（分布式系统） | uuid CHAR(36) NOT NULL COMMENT '全局唯一UUID' | uuid VARCHAR(50)（长度冗余） | 用CHAR(36)（UUID 标准长度） |
remark | VARCHAR(512) | 备注（订单备注、操作说明） | remark VARCHAR(512) DEFAULT '' COMMENT '用户订单备注' | remark TEXT（大字段滥用） | 内容≤1000 字用VARCHAR，避免TEXT |
# 5. 索引设计规范
# 5.1 核心原则
主键必设（优先自增
BIGINT，避免UUID导致索引碎片化）；单表索引数量≤5 个（过多索引导致写入性能下降 30%+）；
联合索引遵循 “最左前缀原则”，高频查询字段放左侧。
# 5.2 索引设计
| 场景 | 正例 | 反例 | 反例问题 |
|---|---|---|---|
| 高频查询 | 登录验证：uni_user_phone（手机号唯一索引） | 为user_info的gender（性别，区分度低）建索引 | 扫描行数占比高，索引无效 |
| 联合索引 | 订单查询：idx_user_id_created_at（用户 ID + 创建时间） | idx_create_at_user_id（高频字段在后） | 无法命中 “用户 ID + 时间范围” 查询 |
| 避免失效 | 时间范围查询：created_at BETWEEN '2025-11-01' AND '2025-11-06' | DATE_FORMAT(create_at, '%Y-%m') = '2025-11' | 函数操作导致索引失效，全表扫描 |
# 6. 完整性约束规范
| 约束类型 | 规范要求 | 正例 | 反例 | 反例问题 |
|---|---|---|---|---|
| 主键约束 | 单表唯一主键，自增BIGINT | id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY | 无主键或用UUID做主键 | 无主键导致数据无法唯一定位，UUID影响性能 |
| 外键约束 | 核心表（如order关联user）用外键，高频更新表（如社交好友表）应用层校验 | user_id BIGINT NOT NULL REFERENCES user_info(id) | 所有表均无外键，应用层未校验 | 数据不一致（如删除用户后残留订单） |
| 唯一约束 | 需去重字段（手机号、商品编码）加唯一索引 | uni_user_phone（手机号唯一索引） | 业务层判断去重，无数据库约束 | 高并发下导致重复数据 |
| CHECK 约束 | 字段合法性校验（如年龄 1-120 岁） | age TINYINT UNSIGNED CHECK (age BETWEEN 1 AND 120) | 仅应用层校验年龄 | 绕过应用层操作时插入非法数据 |
# 7. 性能与安全规范
| 规范维度 | 要求 | 正例 | 反例 | 反例问题 |
|---|---|---|---|---|
| 存储引擎 | 优先InnoDB（支持事务、行锁），日志表可MyISAM | user_info用InnoDB，sys_log用MyISAM | 混合引擎（user用InnoDB，order用MyISAM） | 跨表事务不支持，数据一致性风险 |
| 敏感数据 | 加密存储（密码哈希，手机号脱敏） | 密码：SHA-256加盐哈希；手机号：`SUBSTR (phone,1,3) | ||
| 分库分表 | 数据量超 1000 万规划拆分（按时间、地域、用户 ID 哈希） | 订单表按用户 ID 哈希分 3 库 10 表 | 单库单表存储 5 亿数据 | 连接超时，查询无法响应 |
# 8. 规范落地建议
设计前校验：使用 “数据库设计校验清单”（含命名、字段类型、索引数量等），避免遗漏；
定期评审：每季度检查线上表结构，删除冗余索引、清理无用字段；
工具辅助：用 SQL lint 工具（如
sqlfluff）自动检测命名与类型规范，集成到 CI/CD 流程；文档同步：表结构变更需更新文档，记录字段含义、约束规则及变更原因。