数据库表设计指南

本文基于某互联网公司内部 MySQL 设计开发与使用规范整理，结合通用方法论与典型业务场景展开。所有示例采用脱敏后的虚构电商系统 mall_*（mall_user / mall_order / mall_ticket），不涉及任何生产数据。

一、为什么需要”表设计规范”

MySQL 相比 Oracle、SQL Server，内核优化器有明显短板。这意味着同一句 SQL 在数据量上来之后，可能从毫秒级退化到分钟级——而绝大多数慢 SQL 的根因都不在 SQL 本身，而在表结构设计得不合理。

规范要解决的核心矛盾：

可读性 vs 性能：人能看懂的命名（user_name）和机器最高效的存储（uname varchar(32)）往往是冲突的
一致性 vs 灵活性：用外键约束保证一致性最省心，但在分布式场景下会成为扩展的枷锁
当前需求 vs 未来扩展：订单表今天 1 万行，明天可能 10 亿行；今天能 join 的表，明天可能要分库分表

本章后续所有规则，本质都是这三组矛盾的取舍答案。

二、为什么大公司不建议使用外键

外键（Foreign Key）是数据库自带的”强一致性”工具：插入子表时自动检查父表存在，删除父表时自动级联子表。教科书会告诉你这是好东西，但生产环境几乎一致地禁用它。原因有四层：

2.1 性能开销：每行写入都要多走一次校验

InnoDB 在 INSERT/UPDATE/DELETE 涉及外键列时，会自动执行 SELECT 检查父表对应行是否存在，并加共享锁。这等于让每一次写入都偷偷多了一次读。

在订单系统里，订单明细 mall_order_item 的 order_id 关联 mall_order.id。每写一条明细都要查一次订单表，TPS 高的场景下这笔开销是真实可见的。我们曾在线下测试：同等硬件，纯业务校验比外键约束快 30%-50%。

2.2 锁扩散：从行锁升级到间隙锁

外键检查会触发 InnoDB 的间隙锁（Gap Lock）。原本你只想锁 order_id=100 这一行，外键约束会顺手锁住 order_id 在某个范围内的所有”间隙”，防止有”幻读”行插入进来。结果：

单条更新可能阻塞同表其他事务的批量插入
死锁概率显著上升
主从复制延迟加大（从库回放时锁等待更久）

2.3 扩展性：分库分表的第一道墙

这是最致命的一条。订单表跑到 5 亿行，必然要走分库分表（按 user_id 哈希到 64 个库）。外键约束无法跨库生效——一旦分库，外键就变成了”摆设但又消耗性能的拖累”。

更糟的是迁移过程：原来单体库带外键，要拆出去时必须先把外键全删了才能动工，等于在高峰业务期做了一次”有损改造”。

2.4 运维成本：DBA 的第一杀手

外键约束会让很多日常运维动作变得危险甚至不可执行：

TRUNCATE 父表？子表的外键会直接报错
批量导入历史数据？必须先临时禁用外键，否则几条/秒的速度能让你哭
主从切换？外键相关的锁等待可能让从库永远追不上主库

结论：完整性约束由应用层事务 + 代码校验实现。代价是多写几行 if，但换来的是性能、扩展性、运维自由度，值。

三、基础规范速查（来自规范文档）

下面这些是高频踩坑点，做成速查表方便查阅。

3.1 命名与字符集

库名：业务系统名_子系统名，不超过 20 字符（如 mall_op）
表名：业务前缀_实体名，小写下划线（如 mall_order）
字段名：同表名规则，必须显式 NOT NULL，必要时给 DEFAULT
强制：库/表/列字符集统一 utf8mb4，排序集 utf8_general_ci
强制：存储引擎一律 InnoDB（事务、行锁、MVCC、宕机恢复）

3.2 数据类型选择

场景	推荐	禁忌	原因
主键	`bigint unsigned auto_increment`	UUID、字符串	自增 ID 顺序写入，page 不分裂；UUID 随机写性能差 5-10 倍
状态/枚举	`tinyint(1)` + comment 注释	`enum`、`set`、`bit`	业务变更时改 enum 要 DDL，改 tinyint 是改业务代码
文本	`varchar(32)`、`varchar(64)`、`varchar(255)`	`text`、`blob`	varchar 变长存储省空间；text/blob 加载占内存
金钱	`bigint`，单位”分”	`decimal`、`float`	浮点有精度问题；int 性能高、对账无歧义
时间	`datetime` / `timestamp`	`int` 时间戳	直读可读性好；2038 年问题注意 timestamp 边界
手机号	`varchar(20)`	`bigint`	国际区号、`+` 号、用户输入习惯
IPv4	`int unsigned`（用 `INET_ATON()`）	`char(15)`	4 字节 vs 15 字节，索引效率差 3 倍

3.3 主键与索引

强制：主键 id bigint unsigned auto_increment，禁止更新
业务标识（订单号、用户 ID）做 unique key，不当主键
索引命名：pk_ / uniq_ / idx_ 前缀
联合索引字段不超过 5 个，把区分度最高的放最前
单表索引不超过 10 个，避免冗余索引（如已有 key(a,b)，则 key(a) 冗余）

3.4 必须有的字段

所有核心表必须带：

1
2
3

`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
`is_deleted`  tinyint(1) NOT NULL DEFAULT 0 COMMENT '逻辑删除标记',

这是排查问题的”生命线”——线上 bug 复现、慢 SQL 定位、数据修复都靠它。

四、典型业务表设计

以下示例采用虚构电商系统 mall_*，脱敏处理。

4.1 用户表 `mall_user`

CREATE TABLE `mall_user` (
  `id`           bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '物理主键',
  `user_id`      bigint unsigned NOT NULL COMMENT '业务用户ID，对外暴露',
  `username`     varchar(64)     NOT NULL DEFAULT '' COMMENT '用户名',
  `nickname`     varchar(64)     NOT NULL DEFAULT '' COMMENT '昵称',
  `email`        varchar(128)    NOT NULL DEFAULT '' COMMENT '邮箱',
  `phone`        varchar(20)     NOT NULL DEFAULT '' COMMENT '手机号',
  `password_hash`varchar(128)    NOT NULL DEFAULT '' COMMENT '密码哈希(BCrypt)',
  `status`       tinyint(1)      NOT NULL DEFAULT 1 COMMENT '状态:1正常,0冻结,2注销',
  `register_source` tinyint(1)   NOT NULL DEFAULT 0 COMMENT '注册来源:0未知,1Web,2App,3小程序',
  `last_login_at` datetime       DEFAULT NULL COMMENT '最后登录时间',
  `create_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_deleted`   tinyint(1)      NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_id` (`user_id`),
  UNIQUE KEY `uniq_phone` (`phone`),
  UNIQUE KEY `uniq_email` (`email`),
  KEY `idx_create_time` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户主表';

设计要点：

物理主键 id 与业务主键 user_id 分离——user_id 对外暴露、可加密、可变；id 内部使用、永不变
手机号、邮箱、用户名各加 unique key，支持多种登录方式
password_hash 而非 password：哪怕数据库泄露也无法反推
不存 gender、birthday 这类低频查询字段到大表——必要时垂直拆到 mall_user_profile

4.2 订单表 `mall_order`

订单是电商最核心的表，也是最容易跑成”大表”的那张。

CREATE TABLE `mall_order` (
  `id`              bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id`        bigint unsigned NOT NULL COMMENT '业务订单号',
  `user_id`         bigint unsigned NOT NULL COMMENT '下单用户ID',
  `order_status`    tinyint(1)      NOT NULL DEFAULT 0 COMMENT '0待支付,1已支付,2已发货,3已完成,4已取消,5退款中',
  `pay_amount`      bigint unsigned NOT NULL DEFAULT 0 COMMENT '支付金额,单位分',
  `pay_type`        tinyint(1)      NOT NULL DEFAULT 0 COMMENT '0未支付,1微信,2支付宝,3银行卡',
  `pay_time`        datetime        DEFAULT NULL,
  `receiver_name`   varchar(64)     NOT NULL DEFAULT '',
  `receiver_phone`  varchar(20)     NOT NULL DEFAULT '',
  `receiver_addr`   varchar(255)    NOT NULL DEFAULT '',
  `order_source`    tinyint(1)      NOT NULL DEFAULT 0 COMMENT '0未知,1Web,2App,3小程序',
  `remark`          varchar(255)    NOT NULL DEFAULT '',
  `create_time`     datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`     datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_deleted`      tinyint(1)      NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_order_id` (`order_id`),
  KEY `idx_user_status_ctime` (`user_id`, `order_status`, `create_time`),
  KEY `idx_ctime` (`create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';

订单明细单独建表 mall_order_item（订单与订单明细 1:N，永远拆开）：

CREATE TABLE `mall_order_item` (
  `id`           bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_id`     bigint unsigned NOT NULL COMMENT '关联mall_order.order_id',
  `product_id`   bigint unsigned NOT NULL,
  `sku_id`       bigint unsigned NOT NULL DEFAULT 0,
  `product_name` varchar(128)    NOT NULL DEFAULT '',
  `quantity`     int unsigned    NOT NULL DEFAULT 1,
  `unit_price`   bigint unsigned NOT NULL DEFAULT 0 COMMENT '单价,单位分',
  `total_amount` bigint unsigned NOT NULL DEFAULT 0,
  `create_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_order_id` (`order_id`),
  KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细';

设计要点：

金额一律 bigint 单位”分”——99.99 元存 9999，规避浮点精度、对账无歧义、节省一半存储
联合索引 (user_id, order_status, create_time)：覆盖”我的订单列表”最高频查询，按时间倒序
收货人信息冗余到订单表：用户改了默认地址不影响历史订单可读
pay_type=0 而非 NULL：业务上”未支付”就是 0，避免聚合偏差
订单主表 1 亿行就考虑按 user_id 分库分表，订单明细跟着走

4.3 工单表 `mall_ticket`

工单（Ticket）是客服系统的核心实体，与订单的关键区别：状态机更复杂、协作性更强、历史可追溯性要求更高。

CREATE TABLE `mall_ticket` (
  `id`           bigint unsigned NOT NULL AUTO_INCREMENT,
  `ticket_no`    varchar(32)     NOT NULL COMMENT '工单编号,业务可读,如TK202606300001',
  `user_id`      bigint unsigned NOT NULL COMMENT '提单用户',
  `order_id`     bigint unsigned NOT NULL DEFAULT 0 COMMENT '关联订单(0表示无)',
  `category`     tinyint(1)      NOT NULL DEFAULT 0 COMMENT '1售后,2投诉,3建议,4咨询',
  `priority`     tinyint(1)      NOT NULL DEFAULT 1 COMMENT '1低,2中,3高,4紧急',
  `status`       tinyint(1)      NOT NULL DEFAULT 0 COMMENT '0待受理,1处理中,2已解决,3已关闭,4升级',
  `assignee_id`  bigint unsigned NOT NULL DEFAULT 0 COMMENT '当前处理人客服ID',
  `title`        varchar(128)    NOT NULL DEFAULT '',
  `description`  text            COMMENT '问题描述',
  `first_reply_time` datetime    DEFAULT NULL COMMENT '首次响应时间',
  `resolved_time`    datetime    DEFAULT NULL,
  `closed_time`      datetime    DEFAULT NULL,
  `create_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `is_deleted`   tinyint(1)      NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_ticket_no` (`ticket_no`),
  KEY `idx_assignee_status` (`assignee_id`, `status`),
  KEY `idx_user_ctime` (`user_id`, `create_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单主表';

-- 工单流转历史,记录每一步状态变化
CREATE TABLE `mall_ticket_history` (
  `id`           bigint unsigned NOT NULL AUTO_INCREMENT,
  `ticket_id`    bigint unsigned NOT NULL,
  `from_status`  tinyint(1)      NOT NULL DEFAULT -1 COMMENT '变更前状态,-1表示初始',
  `to_status`    tinyint(1)      NOT NULL,
  `operator_id`  bigint unsigned NOT NULL DEFAULT 0,
  `operator_type` tinyint(1)     NOT NULL DEFAULT 0 COMMENT '1用户,2客服,3系统',
  `comment`      varchar(512)    NOT NULL DEFAULT '',
  `create_time`  datetime        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ticket_id` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='工单流转历史';

设计要点：

工单编号 ticket_no 业务可读（如 TK202606300001）：客服沟通、用户截图都方便，比纯数字 ID 友好
状态变化全留痕：mall_ticket_history 单独成表，永不删——审计和复盘都要靠它
几个时间字段分离（首响/解决/关闭）：客服 SLA 考核的硬指标，单独字段方便统计
优先级与状态分两个字段：状态管流程走到哪，优先级管谁先处理

4.4 日志表 `mall_user_login_log`

日志表是规范里最容易被忽视、但跑起来最容易爆的那张。

CREATE TABLE `mall_user_login_log_202606` (
  `id`           bigint unsigned NOT NULL AUTO_INCREMENT,
  `user_id`      bigint unsigned NOT NULL DEFAULT 0,
  `login_type`   tinyint(1)      NOT NULL DEFAULT 0 COMMENT '1密码,2短信,3微信,4OAuth',
  `login_result` tinyint(1)      NOT NULL DEFAULT 0 COMMENT '1成功,0失败',
  `fail_reason`  varchar(64)     NOT NULL DEFAULT '',
  `client_ip`    int unsigned    NOT NULL DEFAULT 0 COMMENT 'INET_ATON存储',
  `device`       varchar(64)     NOT NULL DEFAULT '' COMMENT 'UA摘要',
  `create_time`  datetime(3)     NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '毫秒精度',
  PRIMARY KEY (`id`),
  KEY `idx_user_ctime` (`user_id`, `create_time`),
  KEY `idx_ctime_result` (`create_time`, `login_result`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户登录日志-202606分区';

日志表的设计原则：

按月分表：mall_user_login_log_YYYYMM，写死在表名里——比 MySQL 原生 partition 更可控，迁移归档都方便
定期清理/归档：保留 180 天线上、超过的转冷存储（OSS/HDFS）
datetime(3) 毫秒精度：登录日志对时间精度敏感（排查撞库攻击）
不存原始 UA 全文：存 UA 摘要（设备型号 + 操作系统版本），原始 UA 单独打日志系统
失败原因单独字段：高频失败原因（密码错、账号锁、验证码错）走 enum，省空间
写入优先：日志表几乎不读，禁用外键和复杂索引，写入路径只保留 (user_id, create_time) 和 (create_time, login_result) 两个常用查询索引

五、容易踩的坑与”为什么”

5.1 自增 ID 会不会耗尽？

int unsigned 上限 42 亿，跑得快的订单表几年就能撞。强制用 bigint unsigned，上限 1844 亿亿，你一辈子用不完。

5.2 为什么”业务主体字段不要当主键”？

订单号如果按日期生成（如 2026063000001），新订单写入是几乎随机的——InnoDB 主键是聚簇索引，随机写入会导致频繁的 page split 和随机磁盘 I/O，性能下降 5-10 倍。

正确做法：自增 id 做主键（聚簇索引顺序写），业务号做 unique key。

5.3 为什么禁止 `select *`？

网络传输浪费：100 列的表只用到 3 列，剩下 97 列照样走网卡
索引失效：SELECT * 几乎一定走回表，覆盖索引（covering index）的优化就用不上
schema 演进埋雷：表加字段后，SELECT * 的代码如果做了字段顺序假设（如 rs[0]），直接崩

5.4 为什么”事务里 SQL 不超过 5 个”？

事务持有锁的时间越长，并发能力越差。规则背后的逻辑：

锁等待时间长 → 后续事务排队 → TPS 下降
慢事务容易引起主从延迟
大量回滚代价高（binlog、undo log 都要写）

外部调用（HTTP、Redis、文件）必须移出事务——这是 90% 线上事故的根因。

5.5 为什么大事表 alter 要审核？

ALTER TABLE 在 InnoDB 会锁整个表（哪怕你只加一列），期间所有读写都被阻塞。100 万行的表还好，1 亿行的表加一个字段，业务可能要停 30 分钟。

规范要求走”低峰期 + 临时表方案”（pt-online-schema-change 或 gh-ost），保证 alter 期间业务不阻塞。

六、给团队的一份自检清单

新表上线前，对照下面这张表过一遍：

检查项	是否通过
主键 `id bigint unsigned auto_increment`，且不可更新	☐
核心表带 `create_time` / `update_time` / `is_deleted`	☐
无外键、无触发器、无存储过程、无视图	☐
字符集 `utf8mb4`，存储引擎 `InnoDB`	☐
字段 NOT NULL + DEFAULT	☐
状态字段 `tinyint(1)` + comment	☐
金额字段 `bigint` 单位”分”	☐
索引命名规范，个数 ≤ 10	☐
表有 comment	☐
大表（>100W 行）alter 已走审核	☐

七、最后

数据库表设计是写一次、改十年的事。前期多花一周打磨结构，后期省下的是无数个凌晨三点被叫起来修数据的夜晚。

规范不是束缚，而是把团队里最优秀工程师的经验沉淀成共识——让新人第一天写出的表，不会比老司机差太多。

愿你少踩坑，多睡觉。

参考资料：某互联网公司《MySQL 设计开发&使用规范》（内部文档）

数据库表设计指南

一、为什么需要”表设计规范”

二、为什么大公司不建议使用外键

2.1 性能开销：每行写入都要多走一次校验

2.2 锁扩散：从行锁升级到间隙锁

2.3 扩展性：分库分表的第一道墙

2.4 运维成本：DBA 的第一杀手

三、基础规范速查（来自规范文档）

3.1 命名与字符集

3.2 数据类型选择

3.3 主键与索引

3.4 必须有的字段

四、典型业务表设计

4.1 用户表 mall_user

4.2 订单表 mall_order

4.3 工单表 mall_ticket

4.4 日志表 mall_user_login_log

五、容易踩的坑与”为什么”

5.1 自增 ID 会不会耗尽？

5.2 为什么”业务主体字段不要当主键”？

5.3 为什么禁止 select *？

5.4 为什么”事务里 SQL 不超过 5 个”？

5.5 为什么大事表 alter 要审核？

六、给团队的一份自检清单

七、最后

4.1 用户表 `mall_user`

4.2 订单表 `mall_order`

4.3 工单表 `mall_ticket`

4.4 日志表 `mall_user_login_log`

5.3 为什么禁止 `select *`？