第 72 章 业务建模踩坑

📖 本篇简介：业务建模是把现实世界映射成表结构的过程，模型一旦建错，后面所有 SQL、索引、分库分表都是在错误的地基上盖楼。本章汇总业务建模阶段最容易踩的 11 个坑，覆盖一对一/多对多关系、状态机、软删除、冗余字段、流水表、币种、审计字段等典型问题。所有 SQL 示例基于 MySQL 8.0 语法。读完你能在需求评审阶段就发现那些"现在省时间、未来还技术债"的建模方案。

本章适合刚接手中型业务系统、需要参与表结构评审的工程师。建议配合第 42 章（建表与数据类型）、第 62 章（分库分表）一起阅读。

目录

• 坑 1：一对一关系硬塞进一张大宽表
• 坑 2：多对多关系不用中间表，硬塞逗号分隔字符串
• 坑 3：状态字段硬编码魔法数字，没有状态机
• 坑 4：软删除字段滥用导致唯一索引失效
• 坑 5：时间字段只存一个 datetime，不区分创建/更新/业务时间
• 坑 6：冗余字段不同步，主表与统计表数据打架
• 坑 7：账户余额直接 UPDATE 覆盖，不留流水
• 坑 8：金额字段不存币种，跨境业务翻车
• 坑 9：TEXT/JSON 大字段塞进主表拖慢查询
• 坑 10：用业务单号做主键与外键关联
• 坑 11：缺审计字段 created_atupdated_atcreated_by
• 速查表：业务建模红线一览
• 自测：这个模型合理吗

坑 1：一对一关系硬塞进一张大宽表

场景 / 错误示范：

-- 用户主表 + 用户详情表被合并成一张超宽表
CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  login_name VARCHAR(64),
  password_hash VARCHAR(128),
  -- 下面这些是"详情"字段，90% 的查询根本不需要
  real_name VARCHAR(64),
  id_card VARCHAR(32),
  birthday DATE,
  gender TINYINT,
  address VARCHAR(256),
  avatar_url VARCHAR(512),
  bio TEXT,                 -- 个人简介，可能几 KB
  id_card_front MEDIUMTEXT, -- 身份证正面图 base64，几十 KB
  id_card_back MEDIUMTEXT
);

为什么是坑：InnoDB 按主键聚簇存储，一行的所有列（含溢出页）物理上挨在一起。把不常用的详情、大字段塞进主表，会让每次 SELECT * FROM user WHERE id=1 都要读多个页；二级索引叶子节点也会因为行变大而装更少的记录，缓冲池命中率下降。

正确做法：

-- 正确：主表只放高频字段，详情拆到扩展表，按主键 1:1 拆分
CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  login_name VARCHAR(64) NOT NULL,
  password_hash VARCHAR(128) NOT NULL,
  status TINYINT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE user_profile (
  user_id BIGINT PRIMARY KEY,           -- 与 user.id 1:1，同时是外键
  real_name VARCHAR(64),
  id_card VARCHAR(32),
  birthday DATE,
  gender TINYINT,
  address VARCHAR(256),
  avatar_url VARCHAR(512),
  bio TEXT
);

加分点：垂直拆分的判断标准是"访问频率"。一个字段如果 80% 的查询都不读它，就该拆出去；详情页才 JOIN 一次，列表页完全不碰。

坑 2：多对多关系不用中间表，硬塞逗号分隔字符串

场景 / 错误示范：

-- 一个商品属于多个标签，开发图省事把标签 ID 拼成字符串
CREATE TABLE product (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128),
  tag_ids VARCHAR(256)    -- "1,5,8,12"，用逗号分隔
);

-- 想查"打了标签 5 的所有商品"
SELECT * FROM product WHERE tag_ids LIKE '%5%';
-- 错误：会把 tag_id=15、25 也查出来；而且完全用不上索引

为什么是坑：逗号分隔字符串违反第一范式，无法用外键约束、无法走索引、无法 JOIN，查"包含某标签"必须全表扫描 + 字符串拆分。数据量一大，这种查询就是慢查询；想删一个标签还得解析字符串再拼回去，极易出 bug。

正确做法：

-- 正确：标准多对多中间表
CREATE TABLE tag (
  id BIGINT PRIMARY KEY,
  name VARCHAR(64) NOT NULL UNIQUE
);

CREATE TABLE product_tag (
  product_id BIGINT NOT NULL,
  tag_id     BIGINT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (product_id, tag_id),
  KEY idx_tag (tag_id)          -- 反向查询"某标签下所有商品"走这个索引
);

加分点：中间表还可以挂额外属性，比如"打标签的时间、打标签的人"，这些在逗号字符串里根本存不下。

坑 3：状态字段硬编码魔法数字，没有状态机

场景 / 错误示范：

-- 订单表 status 字段，代码里到处是这种判断
-- if (order.status == 1) { ... } else if (order.status == 2) { ... }
-- 没人记得 1 是待支付还是待发货，2 是已支付还是已取消

CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  status INT NOT NULL    -- 注释只写了个"状态"
);

为什么是坑：魔法数字散落在几百个 Java/JS 文件里，改一个状态要全局搜索；新同事接手要靠口口相传。更糟的是状态流转没有约束，代码里可能出现"已取消 → 已支付"这种业务上不允许的跳转，直接造成资金事故。

正确做法：

-- 正确 1：用 TINYINT + 详尽 COMMENT，状态集合放应用层枚举
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  status TINYINT NOT NULL DEFAULT 0
    COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消 5退款中 6已退款'
);

-- 正确 2：再建一张状态流转表，把合法跳转显式化
CREATE TABLE order_status_transition (
  from_status TINYINT,
  to_status   TINYINT,
  PRIMARY KEY (from_status, to_status)
);
INSERT INTO order_status_transition VALUES
  (0,1),(0,4),(1,2),(1,5),(2,3),(5,6),(5,1);

加分点：状态机应该在应用层用枚举 + switch 实现，所有跳转必须经过统一入口，禁止在业务代码里直接 order.status = 3。

坑 4：软删除字段滥用导致唯一索引失效

场景 / 错误示范：

-- 用户表要求 login_name 唯一，但又要"软删除"保留数据
CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  login_name VARCHAR(64) NOT NULL,
  is_deleted TINYINT NOT NULL DEFAULT 0,
  UNIQUE KEY uk_login (login_name)
);

-- 用户 A 被软删：is_deleted=1
-- 新用户注册同名 login_name='alice' -> 唯一索引冲突，注册不了

为什么是坑：唯一索引不知道"软删除"这种业务概念，它眼里只要 login_name 相同就冲突。结果是被删过的名字再也注册不了，或者开发被迫放弃唯一索引，改成应用层校验，并发下又出重复数据。

正确做法：

-- 正确 1：把删除时间戳拼进唯一索引，软删后原记录 login_name 被"让出"
CREATE TABLE user (
  id BIGINT PRIMARY KEY,
  login_name VARCHAR(64) NOT NULL,
  deleted_at DATETIME NULL DEFAULT NULL,   -- NULL 表示未删除
  UNIQUE KEY uk_login (login_name, deleted_at)
);
-- 未删除：deleted_at 全是 NULL，MySQL 唯一索引里 NULL 不参与冲突判断，多个 NULL 共存
-- 删除时：UPDATE user SET deleted_at=NOW() WHERE id=1
-- 这样 login_name='alice' 可以存在多条 deleted_at 不同的记录

加分点：MySQL 唯一索引对 NULL 的处理是"多个 NULL 不算冲突"，这正好被软删除场景利用；不要用 is_deleted TINYINT 拼唯一索引，因为 0 和 0 仍然冲突。

坑 5：时间字段只存一个 datetime，不区分创建/更新/业务时间

场景 / 错误示范：

-- 订单表只建了一个 create_time
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32),
  create_time DATETIME    -- 这是下单时间？支付时间？发货时间？
);
-- 运营问："昨天有多少订单支付成功？"开发只能翻日志猜

为什么是坑：业务系统里"时间"至少有三类：记录创建时间、记录更新时间、业务发生时间（支付/发货/完成）。混为一谈后，做报表、做审计、做客服查询都要靠猜；等业务量上来，补字段要 ALTER 大表，成本极高。

正确做法：

-- 正确：每张表固定带 created_at / updated_at，业务时间单独建字段
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32) NOT NULL,
  -- 系统时间
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- 业务时间，每个关键动作一个字段，默认 NULL
  paid_at     DATETIME NULL,
  shipped_at  DATETIME NULL,
  finished_at DATETIME NULL,
  cancelled_at DATETIME NULL
);

加分点：报表统计一律基于业务时间（paid_at 算支付订单），不要用 created_at 代替，否则"刚创建未支付"的订单会被算进支付额。

坑 6：冗余字段不同步，主表与统计表数据打架

场景 / 错误示范：

-- 订单表冗余了用户昵称，免得每次 JOIN user
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  user_nick VARCHAR(64)   -- 冗余字段，下单时把昵称拷过来
);

-- 用户后来改了昵称，user 表已经更新，但历史订单里的 user_nick 还是老的
-- 运营导出订单列表，看到的是老昵称；客服查用户，看到的是新昵称，两边对不上

为什么是坑：冗余字段是为了性能，但必须明确"以谁为准"。如果是"快照型冗余"（下单时的地址、昵称，永远不改），要在字段名上标 _snapshot；如果是"镜像型冗余"（要跟随主表更新），必须有可靠的同步机制，否则就是数据不一致。

正确做法：

-- 正确 1：快照型冗余，命名上明确"历史快照，永不更新"
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  user_nick_snapshot VARCHAR(64),   -- 下单时的昵称，历史订单就该长这样
  address_snapshot VARCHAR(256)
);

-- 正确 2：镜像型冗余，通过 binlog 订阅（Canal/Debezium）异步同步
-- user 表昵称变更 -> Canal 监听 binlog -> 批量回写 trade_order.user_nick
-- 并在业务上接受"秒级延迟"，不要强一致

加分点：能不冗余就不冗余；必须冗余时，先用一句话写清楚"这个字段谁来更新、什么时候更新、不一致怎么办"，写不清楚就别冗余。

坑 7：账户余额直接 UPDATE 覆盖，不留流水

场景 / 错误示范：

-- 账户表只有 balance 一个字段，所有变动都直接覆盖
CREATE TABLE account (
  user_id BIGINT PRIMARY KEY,
  balance DECIMAL(12,2)
);

UPDATE account SET balance = balance - 100 WHERE user_id = 1;
-- 月底对账：运营问"这 100 块扣到哪去了？"——查不到，历史被覆盖了

为什么是坑：资金类业务必须"余额可追溯"。直接 UPDATE 覆盖后，出了客诉、出了对账差异，根本无法复盘是哪笔订单扣的钱。监管要求支付类企业必须保留完整流水，不留流水等于合规风险。

正确做法：

-- 正确：余额表 + 流水表双写
CREATE TABLE account (
  user_id BIGINT PRIMARY KEY,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0   -- 当前余额，由流水汇总而来
);

CREATE TABLE account_flow (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  change_amount DECIMAL(12,2) NOT NULL,      -- 正=入账，负=出账
  balance_after DECIMAL(12,2) NOT NULL,      -- 变动后余额，用于对账
  biz_type VARCHAR(32) NOT NULL,             -- ORDER_REFUND / CONSUME / RECHARGE
  biz_no VARCHAR(64) NOT NULL,               -- 关联业务单号
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user_time (user_id, created_at)
);
-- 所有变动必须先写流水，再更新余额；余额 = SUM(流水)

加分点：流水表只 INSERT 不 UPDATE/DELETE，余额表是流水的"物化视图"；任何对账差异都可以通过 SUM(flow) 与 balance 对比定位。

坑 8：金额字段不存币种，跨境业务翻车

场景 / 错误示范：

-- 订单表只有一个 amount 字段
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  amount DECIMAL(12,2)    -- 这是人民币？美元？欧元？
);
-- 国内订单 amount=100，美国订单 amount=100，财务报表一加：200，
-- 实际 100 人民币 + 14 美元 = 约 200 人民币，对不上

为什么是坑：只要业务可能出海、接海外支付、做跨境结算，不存币种就是定时炸弹。更隐蔽的是汇率：即使存了币种，不存下单时的汇率快照，事后也无法复算当时的人民币金额。

正确做法：

-- 正确：金额 + 币种 + 汇率快照三件套
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,
  currency_code CHAR(3) NOT NULL DEFAULT 'CNY',   -- ISO 4217：CNY/USD/EUR
  original_amount DECIMAL(12,2) NOT NULL,         -- 原币金额
  exchange_rate DECIMAL(12,6) NOT NULL DEFAULT 1, -- 下单时对 CNY 的汇率
  cny_amount DECIMAL(12,2) GENERATED ALWAYS AS
    (original_amount * exchange_rate) STORED       -- 生成列，便于报表
);

加分点：币种字段用 CHAR(3) 存 ISO 4217 代码，不要用中文"人民币"；汇率快照必须随订单落库，不能事后查汇率表重算。

坑 9：TEXT/JSON 大字段塞进主表拖慢查询

场景 / 错误示范：

-- 商品表把商品详情 HTML、图片 JSON、规格 JSON 全塞进来
CREATE TABLE product (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128),
  price DECIMAL(10,2),
  detail_html MEDIUMTEXT,     -- 商品详情页 HTML，几十 KB
  images_json JSON,           -- 图片列表
  spec_json JSON              -- 规格参数
);
-- 列表页 SELECT id,name,price FROM product 时，
-- InnoDB 仍然可能因为行过大而读多个页

为什么是坑：InnoDB 行格式为 DYNAMIC 时，TEXT/BLOB 的前 768 字节或 20 字节指针会留在聚簇索引页，其余存到溢出页。但只要行里有大字段，缓冲池里一页能装的行数就变少，列表查询的缓存命中率下降。

正确做法：

-- 正确：主表只放列表查询字段，详情拆到扩展表
CREATE TABLE product (
  id BIGINT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  status TINYINT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE product_detail (
  product_id BIGINT PRIMARY KEY,
  detail_html MEDIUMTEXT,
  images_json JSON,
  spec_json JSON
);
-- 列表页只查 product；详情页才 JOIN product_detail

加分点：JSON 字段虽然灵活，但不要把它当"万能列"，能拆成关系表就拆；JSON 上的函数索引（MySQL 8.0）可以解决局部查询需求，但写入成本高。

坑 10：用业务单号做主键与外键关联

场景 / 错误示范：

-- 用订单号当主键
CREATE TABLE trade_order (
  order_no VARCHAR(32) PRIMARY KEY    -- '2026100700012345'
);

CREATE TABLE order_item (
  id BIGINT PRIMARY KEY,
  order_no VARCHAR(32),               -- 外键，每行带 32 字节字符串
  sku_id BIGINT,
  qty INT
);
-- 所有二级索引叶子节点都带着 32 字节的 order_no，空间放大严重

为什么是坑：InnoDB 聚簇索引按主键物理排序，业务单号通常是"时间 + 序列"，虽然趋势递增，但字符串本身比 BIGINT 占空间；更糟的是每个二级索引叶子节点都要带一份主键值，二级索引空间被白白放大。业务单号一旦要改（合规要求、系统迁移），主键一改全库联动。

正确做法：

-- 正确：代理键 BIGINT 自增或雪花 ID 做主键，业务单号加唯一索引
CREATE TABLE trade_order (
  id BIGINT PRIMARY KEY,                  -- 代理键，聚簇索引
  order_no VARCHAR(32) NOT NULL UNIQUE,   -- 业务单号，走唯一二级索引
  ...
);

CREATE TABLE order_item (
  id BIGINT PRIMARY KEY,
  order_id BIGINT NOT NULL,               -- 外键用代理键，8 字节
  sku_id BIGINT,
  qty INT,
  KEY idx_order (order_id)
);

加分点：关联表之间一律用代理键关联，业务单号只在对外展示和对账时使用；这是为了让"业务规则变化"不触动"存储结构"。

坑 11：缺审计字段 created_at/updated_at/created_by

场景 / 错误示范：

-- 一张业务表，没有任何审计字段
CREATE TABLE coupon (
  id BIGINT PRIMARY KEY,
  name VARCHAR(64),
  discount DECIMAL(10,2)
);
-- 出问题时：这张券谁建的？什么时候建的？谁改的？——全不知道

为什么是坑：审计字段不是"锦上添花"，是出事时唯一的追溯线索。客服要查"这张券什么时候发的"、合规要查"这条数据谁改的"、运维要按时间做增量归档，没有审计字段全部抓瞎。

正确做法：

-- 正确：每张业务表固定带三件套 + 操作人
CREATE TABLE coupon (
  id BIGINT PRIMARY KEY,
  name VARCHAR(64) NOT NULL,
  discount DECIMAL(10,2) NOT NULL,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by  BIGINT NOT NULL,                 -- 创建人用户 ID
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by  BIGINT NOT NULL,                 -- 最后修改人
  is_deleted  TINYINT NOT NULL DEFAULT 0
);
-- 这五个字段建议在公司建表模板里默认带上，不用业务方每次想

加分点：核心业务表（订单、账户、权限）还应加一张操作流水表，记录每次 UPDATE 前后的值，而不仅是 updated_by。

速查表：业务建模红线一览

| 建模决策 | 错误做法 | 正确做法 |

|---|---|---|

| 1:1 关系 | 全塞一张宽表 | 主表高频 + 扩展表低频大字段 |

| 多对多 | 逗号分隔字符串 | 独立中间表 + 双向索引 |

| 状态机 | 魔法数字散落在代码 | TINYINT + 枚举 + 状态流转表 |

| 软删除 | is_deleted 拼唯一索引 | deleted_at 时间戳拼唯一索引 |

| 时间字段 | 一个 create_time 走天下 | created_at/updated_at + 业务时间分列 |

| 冗余字段 | 拷过来就不管 | 明确快照型 vs 镜像型，写清同步机制 |

| 资金余额 | UPDATE 覆盖 | 余额表 + 只追加流水表 |

| 跨境金额 | 只存 amount | amount + currency + exchange_rate 快照 |

| 大字段 | TEXT/JSON 塞主表 | 拆到详情扩展表 |

| 主键 | 业务单号当主键 | BIGINT 代理键 + 业务号唯一索引 |

| 审计 | 啥都没有 | created_at/by + updated_at/by + is_deleted |

自测：这个模型合理吗

题 1：为什么多对多关系不能用逗号分隔字符串？

<details>

<summary>参考答案</summary>

违反第一范式，列里存多个值无法用外键约束、无法走索引、无法 JOIN；查"包含某标签"必须全表扫描 + 字符串解析。正确做法是建独立中间表 product_tag(product_id, tag_id)，并在两列上各建索引支持双向查询。

</details>

题 2：软删除与唯一索引冲突怎么解？

<details>

<summary>参考答案</summary>

用 deleted_at DATETIME NULL 代替 is_deleted TINYINT，唯一索引建在 (login_name, deleted_at) 上。未删除记录 deleted_at 为 NULL，MySQL 唯一索引不认为多个 NULL 冲突；删除时把 deleted_at 设为当前时间戳，原 login_name 被"让出"，新用户可以注册同名。

</details>

题 3：为什么账户余额不能直接 UPDATE 覆盖？

<details>

<summary>参考答案</summary>

资金类业务必须可追溯，直接覆盖后历史变动丢失，客诉与对账无法复盘。正确做法是"余额表 + 流水表"双写：所有变动先 INSERT 一条流水（含变动后余额），再更新余额表；余额本质上是流水的物化汇总，任何差异都可以通过 SUM(flow) 重算定位。

</details>

题 4：代理键与业务主键怎么选？

<details>

<summary>参考答案</summary>

关联表之间一律用 BIGINT 代理键（自增或雪花 ID）做主键，因为 8 字节定长、趋势递增、二级索引叶子节点携带成本低；业务单号（订单号、手机号）加唯一二级索引用于对外展示与对账。这样业务规则变化（订单号改版）不会触动存储结构。

</details>

题 5：冗余字段有哪两种，分别怎么维护？

<details>

<summary>参考答案</summary>

快照型冗余（下单时的昵称、地址）：拷过来就永不更新，字段名加 _snapshot，反映"历史那一刻"。镜像型冗余（要跟随主表变化）：通过 binlog 订阅（Canal/Debezium）异步同步，业务上接受秒级延迟，不要强求强一致。拿不准是哪种时，宁可不冗余。

</details>

下一篇预告：第 73 章 大数据量场景踩坑