表结构设计

一、范式

范式解决的是数据冗余导致的更新异常。

范式	要求	违反的后果
1NF	每列都是不可再分的原子值	把 `"DIAB100 MYOP"` 塞一个字段里，只能全表扫描
2NF	在 1NF 基础上，非主属性完全依赖于候选键（消除对联合主键的部分依赖）	主键 `(订单号, 商品号)` 的表里放"客户名"，客户改名要更新多行
3NF	在 2NF 基础上，非主属性不传递依赖于候选键	员工表里放"部门名"（依赖于部门号，部门号依赖于员工号），部门改名要更新多行
BCNF	每个决定因素都必须是候选键	3NF 的加强，处理主属性之间的依赖

一句话记忆：1NF 列不可分，2NF 不能部分依赖，3NF 不能传递依赖。

三种更新异常：

插入异常：新部门还没有员工，就无法录入部门信息；
删除异常：删掉最后一个员工，部门信息也跟着没了；
更新异常：部门改名要更新成千上万行，漏改就出现数据不一致。

反范式

生产中常常有意冗余换取查询性能：

-- 订单列表要显示商品名，但商品表在另一个库
ALTER TABLE order_item ADD COLUMN product_name VARCHAR(100);  -- 冗余快照

反范式的判断标准：

✅ 冗余的是历史快照（下单时的商品名、价格），业务上本来就不应该跟着源表变；
✅ 冗余的是极少变化的数据（如国家名），且能接受最终一致；
✅ 冗余的是统计值（评论数、点赞数），实时 COUNT 代价太高；
❌ 单纯"懒得写 JOIN"而冗余频繁变化的数据——一致性维护成本远高于省下的那个 JOIN。

二、字段设计规范

CREATE TABLE `order` (
    `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
    `order_no`    VARCHAR(32)     NOT NULL                COMMENT '业务订单号',
    `user_id`     BIGINT UNSIGNED NOT NULL                COMMENT '用户 ID',
    `amount`      DECIMAL(12,2)   NOT NULL DEFAULT 0.00   COMMENT '金额（元）',
    `status`      TINYINT         NOT NULL DEFAULT 0      COMMENT '0待付 1已付 2取消',
    `is_deleted`  TINYINT         NOT NULL DEFAULT 0      COMMENT '逻辑删除',
    `created_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at`  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP
                                  ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uk_order_no` (`order_no`),
    KEY `idx_user_created` (`user_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='订单表';

要点：

能小则小：TINYINT(1B) / INT(4B) / BIGINT(8B)。字段越小，单页容纳的行越多，B+ 树越矮，Buffer Pool 缓存的行越多。
尽量 NOT NULL 并给默认值：NULL 需要额外的 null 标志位，让索引统计和比较逻辑复杂化，也是无数 bug 的来源。
金额一律 DECIMAL，或用 BIGINT 存"分"。绝不用 FLOAT / DOUBLE。
时间用 DATETIME（TIMESTAMP 有 2038 问题）；created_at / updated_at 用数据库默认值自动维护。
字符集 utf8mb4，排序规则 8.0 默认 utf8mb4_0900_ai_ci（不区分大小写和重音，速度快）；需要区分大小写用 utf8mb4_0900_as_cs，需要 emoji 精确比较用 utf8mb4_bin。
每个字段写 COMMENT，枚举值的含义必须写清楚。
单表字段不超过 30 个左右，大字段（TEXT / JSON）垂直拆到附属表。
业务主键与代理主键分离：用自增/雪花 id 做主键，业务号（订单号）建唯一索引。

三、约束

约束	说明
`PRIMARY KEY`	非空 + 唯一，InnoDB 中同时决定物理存储顺序
`UNIQUE`	唯一，允许多行为 `NULL`
`NOT NULL`	非空
`DEFAULT`	默认值
`CHECK`	MySQL 8.0.16 起才真正生效，之前的版本会解析但忽略
`FOREIGN KEY`	外键，保证引用完整性

互联网业务通常不用外键

外键会带来：写入时的额外检查与锁（父表加锁影响并发）、级联操作难以预测、分库分表后完全失效、DDL 变更受限。

主流做法是在应用层保证引用完整性，数据库只留索引。但要清楚这是一个取舍——在数据一致性极其重要、并发不高的内部系统里，外键仍然是好东西。

逻辑删除 is_deleted 也是取舍：它保留了数据但让所有查询都要带 AND is_deleted = 0，唯一索引也必须包含该列才能正确工作（否则删除后无法重新插入相同的值）。数据量大时应定期归档而非无限保留。

四、分区表

单表按规则水平切分成多个物理文件，但对上层仍是一张表。

CREATE TABLE log (
    id         BIGINT      NOT NULL AUTO_INCREMENT,
    created_at DATETIME    NOT NULL,
    content    TEXT,
    PRIMARY KEY (id, created_at)          -- 分区键必须包含在主键中
) PARTITION BY RANGE (TO_DAYS(created_at)) (
    PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION pmax    VALUES LESS THAN MAXVALUE
);

分区类型：RANGE（最常用，按时间）、LIST、HASH、KEY。

分区表最大的限制

表上所有唯一索引（含主键）都必须包含分区键的全部列。

这条限制经常直接否决分区方案：想按 created_at 分区，主键就必须是 (id, created_at)，而这会让 WHERE id = ? 的唯一性保证失效（不同分区可能有相同 id）。

分区适合的场景其实很窄：按时间分区的日志/流水表，靠 DROP PARTITION 秒级删除历史数据（比 DELETE 快几个数量级）。如果查询条件带不上分区键，会触发全分区扫描，比不分区还慢——用 EXPLAIN 的 partitions 列确认分区裁剪是否生效。

五、分库分表

什么时候才该分

经验阈值：单表超过千万行或单表数据文件超过 20~50GB，且已经做完索引优化、冷数据归档、读写分离之后仍然扛不住。

分库分表是最后手段，它引入的复杂度远超多数团队的预期。

拆分方式：

垂直拆分：按业务模块拆库（用户库、订单库），按字段冷热拆表（主表 + 详情表）。简单、收益明确，应优先做。
水平拆分：同一张表按分片键拆到多个库表。真正解决单表容量问题，复杂度也最高。

分片键选择是水平拆分的核心决策：

必须是绝大多数查询都会带上的字段（如订单表用 user_id 而不是 order_id，因为查询多是"我的订单"）；
分布要均匀，避免热点；
取模（user_id % 64）扩容时要迁移大量数据，一致性哈希或范围分片 + 路由表更利于扩容；
需要按非分片键查询时，建立异构索引表（把 order_no → user_id 的映射单独存一张表或放进 ES）。

随之而来的问题：

问题	常见方案
跨库 JOIN	冗余字段、应用层聚合、绑定表（同一分片键的表放同一库）
分布式事务	尽量避免；必须要时用 TCC / 本地消息表 / Seata，追求最终一致
全局唯一 ID	雪花算法、号段模式（美团 Leaf）、`AUTO_INCREMENT` 步长错开
跨库分页排序	各库取前 N 条再归并；深分页只能限制页数或用 ES
跨库聚合统计	走离线数仓 / OLAP 引擎（ClickHouse、Doris），不要在 OLTP 库上跑

中间件：ShardingSphere（客户端/代理均可）、MyCat、Vitess。

六、DDL 变更

ALTER TABLE 在大表上是高危操作。

MySQL 5.6 起支持 Online DDL（ALGORITHM=INPLACE），加列、加索引等操作不再阻塞 DML；8.0 起加列还支持 ALGORITHM=INSTANT（只改元数据，秒级完成，仅限在表末尾加列等有限场景）。

ALTER TABLE t ADD COLUMN c INT, ALGORITHM=INSTANT;   -- 失败则报错，不会悄悄退化为 COPY
ALTER TABLE t ADD INDEX idx_c (c), ALGORITHM=INPLACE, LOCK=NONE;

Online DDL 也不是完全无锁

即使 LOCK=NONE，DDL 开始和结束时仍需要短暂的 MDL 写锁。如果此时有一个长事务持有 MDL 读锁，DDL 会等待，而排在 DDL 后面的所有普通查询都会被阻塞——瞬间打满连接数。

因此：执行 DDL 前必须先确认没有长事务（查 information_schema.innodb_trx），并给 DDL 设置超时（8.0 的 SET lock_wait_timeout = 5）。

超大表变更建议用 pt-online-schema-change 或 gh-ost：它们通过"建影子表 → 拷贝数据 → 增量同步 → 原子改名"完成，可随时暂停，对线上影响可控。

回到 👉 知识体系目录

# 表结构设计

# 一、范式

# 反范式

# 二、字段设计规范

# 三、约束

# 四、分区表

# 五、分库分表

# 六、DDL 变更