一句话回答

表设计应从业务边界和查询模式出发，选择准确且紧凑的数据类型，建立稳定主键和必要约束，再围绕高频读写设计少量有效索引，同时提前考虑数据生命周期、并发一致性、扩展方式与安全变更。

好的表结构不是字段越少越好，也不是范式越高越好，而是在正确性、查询效率、写入成本和演进能力之间取得平衡。

表设计完整流程

梳理业务实体和不变量
        ↓
明确读写场景与数据规模
        ↓
设计主键、字段、状态和约束
        ↓
设计查询索引与并发控制
        ↓
规划归档、分区和扩展
        ↓
评审 DDL、压测并准备回滚

第一步：从业务问题开始

建表前先回答：

  • 这个表代表哪个业务实体或事件？
  • 谁负责创建、修改和删除它？
  • 哪些字段构成业务唯一性？
  • 主要查询条件、排序和分页方式是什么？
  • 写入频率、峰值 QPS 和未来三年数据量是多少？
  • 数据需要保存多久，是否需要审计和软删除？
  • 哪些字段必须保持强一致？
  • 是否存在多租户、分库分表和合规要求？

如果不了解查询和更新模式，只根据页面字段建表，后续索引和扩展通常会非常被动。

第二步：确定实体边界

订单、订单项、支付记录属于不同生命周期的实体，不应全部塞进一张超宽表。订单有多个商品明细，应拆为主表和明细表；支付可能多次尝试，也应独立记录每次支付事件。

orders          订单主信息
order_items     订单商品明细
payment_orders  支付请求与状态
payment_events  支付回调和状态变化记录

拆表不是越多越好。访问强相关、生命周期一致且始终一起读取的小字段可以放在同一表，避免无意义 JOIN 和维护成本。

第三步：主键怎么设计

InnoDB 按主键组织聚簇索引，二级索引叶子节点还会保存主键值。因此主键应尽量：

  • 唯一、非空、稳定不修改。
  • 类型短小，减少聚簇与所有二级索引空间。
  • 大致递增，降低随机插入导致的页分裂。
  • 不承载可能变化的业务含义。

自增 BIGINT 简单高效，但在分布式写入时需要协调。雪花 ID 等分布式 ID 可避免中心自增，却要解决时钟回拨、趋势递增和生成器可用性。

完全随机 UUID 字符串体积大、插入离散。若业务必须使用 UUID，可以考虑紧凑二进制存储和时间有序 UUID，但必须保持跨系统可读性与生成规范。

业务主键与数据库主键

订单号、身份证号或设备序列号可以有业务唯一约束，但不一定适合作为聚簇主键。常见做法是使用内部数值 ID 作为主键，再给业务标识建立唯一索引：

PRIMARY KEY (id),
UNIQUE KEY uk_tenant_order_no (tenant_id, order_no)

这样既保持物理结构稳定，又由数据库原子保证业务唯一性。

第四步：选择合适字段类型

数值类型

选择能够覆盖范围的最小类型，但不要为了省几个字节让未来溢出。金额推荐使用整数最小货币单位或 DECIMAL，不要使用 FLOAT/DOUBLE 表示需要精确计算的金额。

amount_cent BIGINT NOT NULL
-- 或
amount DECIMAL(18, 2) NOT NULL

字符串类型

VARCHAR 适合变长短文本，长度应基于业务上限。TEXT 适合长内容，但索引、临时表和返回成本更高。不要把日期、金额、布尔值全部保存成字符串。

字符集通常使用 utf8mb4。排序规则决定大小写、重音和比较方式，业务唯一键在选择排序规则前必须明确是否区分大小写。

时间类型

DATETIME 更像不随会话时区转换的日期时间；TIMESTAMP 存储和读取涉及时区转换且范围不同。系统必须统一时区策略，接口推荐传递带时区的标准时间格式。

不要只保存格式化字符串。创建时间、更新时间应由应用还是数据库维护，也要形成团队统一规范。

布尔与枚举状态

MySQL 的 BOOLEAN 本质上通常是 TINYINT(1) 的别名。状态字段可以使用小整数并在代码中维护枚举含义，但必须保留文档，避免出现无法解释的魔法数字。

数据库 ENUM 约束直观，但增加状态值需要 DDL，跨语言工具兼容性也要评估。状态频繁演进的系统更常使用小整数或短字符串。

JSON 字段

JSON 适合结构不稳定、低频查询的扩展属性，不应把所有业务字段都塞进 JSON。需要高频过滤、关联、唯一性或强约束的字段应显式建列。JSON 路径查询可配合生成列或多值索引，但复杂度和维护成本更高。

NULL 还是默认值

NULL 表示未知或不适用，与空字符串、0 的业务含义不同。不应为了“避免 NULL”给未知日期填 1970-01-01，这会污染统计和逻辑。

必须存在的字段使用 NOT NULL 并提供合理写入路径；可选字段允许 NULL。默认值必须有真实业务意义，不能只为通过插入校验。

第五步：设计状态与生命周期

订单状态不只是一个数字，还代表合法状态机：

CREATED → PAID → FULFILLED → COMPLETED
    └────────────→ CANCELLED

更新时使用条件和版本号避免并发覆盖：

UPDATE orders
SET status = 'PAID', version = version + 1
WHERE id = ?
  AND status = 'CREATED'
  AND version = ?;

受影响行数为 0 时要区分状态已经变化、版本冲突和记录不存在。

软删除怎么设计

软删除便于恢复和审计，但会让所有查询增加过滤条件，也会影响唯一约束、索引选择性和数据量。常见字段包括 deleted_at 或 is_deleted。

若业务要求“删除后可以重新注册同一账号”，唯一索引必须考虑软删除语义。简单把 is_deleted 放入唯一索引可能仍难支持多次删除历史，应使用归档表、版本标识或重新定义唯一键。

软删除不是备份，敏感数据还可能有法规要求的真正擦除流程。

第六步：索引围绕查询设计

假设核心查询是：

SELECT id, order_no, amount_cent, created_at
FROM orders
WHERE tenant_id = ?
  AND status = ?
  AND created_at >= ?
ORDER BY created_at DESC, id DESC
LIMIT 50;

可以评估联合索引：

KEY idx_tenant_status_time
    (tenant_id, status, created_at, id)

字段顺序要综合等值条件、范围、排序、选择性和索引复用。高选择性字段并不总要放最前，例如多租户系统常先放 tenant_id 以隔离访问范围。

索引设计原则

  • 为高频、重要且能显著缩小范围的查询建索引。
  • 联合索引优先于多个无法有效组合的单列索引。
  • 避免重复索引，例如已有 (a, b) 时 (a) 可能冗余，但仍需结合索引大小与查询验证。
  • 控制索引数量，每个索引都会增加写入、日志、缓存和存储成本。
  • 长字符串可评估前缀索引，但它可能无法覆盖查询，也影响区分度。
  • 定期根据真实使用统计清理无效索引，删除前先观察与灰度。

第七步：是否需要外键

外键能在数据库层保证引用完整性，但会增加写入检查、锁顺序和跨表变更复杂度。在单体或一致性要求高的系统中很有价值；在分库分表和高吞吐系统中常由应用与离线校验维护。

不能因为“不用外键”就放弃完整性。必须有明确的应用事务、补偿、数据校验和孤儿数据治理机制。

第八步：审计字段与版本字段

常见基础字段：

created_at DATETIME(3) NOT NULL,
updated_at DATETIME(3) NOT NULL,
created_by BIGINT NULL,
updated_by BIGINT NULL,
version INT NOT NULL DEFAULT 0

是否记录操作人取决于审计要求。高价值状态变化建议额外保存事件表，不要只依赖主表最后状态，因为主表无法还原完整变化过程。

一张订单表示例

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL,
    tenant_id BIGINT UNSIGNED NOT NULL,
    order_no VARCHAR(32) NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status TINYINT UNSIGNED NOT NULL,
    amount_cent BIGINT UNSIGNED NOT NULL,
    version INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME(3) NOT NULL,
    updated_at DATETIME(3) NOT NULL,
    deleted_at DATETIME(3) NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_tenant_order_no (tenant_id, order_no),
    KEY idx_tenant_user_time (tenant_id, user_id, created_at, id),
    KEY idx_tenant_status_time (tenant_id, status, created_at, id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;

这只是示例，不能脱离真实查询直接复制。比如若从不按用户查询，第二个索引就是额外写入负担。

宽表、冗余和范式取舍

第三范式减少重复与更新异常，适合事务系统的基础建模。适度冗余可以减少高频 JOIN，例如订单中保存下单时的商品名称快照，因为历史订单需要保留当时信息，而不是跟随商品表变化。

冗余字段必须明确：

  • 权威数据源是谁？
  • 什么时候同步？
  • 失败如何补偿？
  • 是否允许短暂不一致？

没有维护机制的冗余会逐渐变成不可信数据。

大字段如何处理

图片、视频和大型文件通常存对象存储，数据库只保存地址、摘要和元数据。超长文本若很少读取，可以拆到扩展表，避免主表单行过大影响 Buffer Pool 和列表扫描。

拆表后读取需要额外查询，应根据访问比例决定。不要为了形式上的“瘦表”给每次请求增加大量随机 IO。

分区表、分库与分表

数据量大不代表立刻分库分表。优先检查索引、归档、冷热分离和单机规格。过早拆分会引入分布式事务、跨分片查询、全局 ID、扩容迁移和运维复杂度。

需要拆分时应先找到稳定分片键：

  • 查询是否总能携带分片键？
  • 数据和流量是否均匀？
  • 是否存在超级租户或热点用户？
  • 跨分片聚合和分页如何处理？
  • 未来如何增加分片并迁移？

MySQL 分区表主要服务数据管理与分区裁剪，不等价于跨实例分库分表。分区键还受唯一索引等规则限制，使用前要验证真实收益。

数据归档与保留期限

为日志、订单和审计数据定义在线保留周期。旧数据可以迁移到历史库、ClickHouse 或对象存储。归档任务应按主键或时间小批量执行，并验证源与目标数据数量、校验和及恢复能力。

没有生命周期规划的表最终会无限增长，使索引、备份、DDL 和故障恢复越来越慢。

表结构变更安全

生产 DDL 前要确认：

  • MySQL 版本支持的 Online/Instant DDL 能力。
  • 是否需要复制整表或长时间持有元数据锁。
  • 表大小、磁盘临时空间和预计执行时间。
  • 对主从复制和备份窗口的影响。
  • 应用是否兼容新旧结构同时存在。
  • 失败后的回滚或前滚方案。

推荐采用“扩展—迁移—切换—清理”模式：先添加兼容字段，应用双读或双写迁移，验证后切换，最后再删除旧结构。删除字段属于高风险不可逆操作，必须先备份并观察。

常见设计反模式

  1. 用一个 ext1/ext2/ext3 表示含义不断变化的字段。
  2. 所有字段都使用 VARCHAR，失去类型校验和高效比较。
  3. 用逗号分隔字符串保存多值关系，无法可靠约束和索引。
  4. 一个状态字段混合支付、履约、退款等多个维度。
  5. 只建单列索引，不考虑实际组合查询和排序。
  6. 在高频表保存巨大 JSON，却经常按 JSON 内部字段筛选。
  7. 直接使用随机长字符串作为所有二级索引携带的主键。
  8. 无归档、无审计、无版本字段，也没有表结构说明。

核心考点清单

  • 表设计从业务不变量和读写模式开始，而不是从页面字段开始。
  • 主键应唯一、稳定、短小并尽量趋势递增；业务唯一性由唯一索引保证。
  • 类型要准确且紧凑，金额不用浮点，时间和 JSON 都要有明确策略。
  • 索引围绕高价值查询设计，并同时计算写入和存储成本。
  • 软删除、冗余、外键、分表没有统一答案，必须说明一致性与维护机制。
  • 数据生命周期和安全 DDL 是表设计的一部分，不是上线后的补丁。

高频追问与参考回答

追问 1：为什么建议主键趋势递增？

相邻新记录更可能写入相近叶子页，减少随机页访问与频繁页分裂。纯随机主键会让插入分散，还会放大所有二级索引的空间成本。

追问 2：字段是不是都应该 NOT NULL？

不是。必须存在且有明确默认语义的字段适合 NOT NULL；未知或不适用应使用 NULL，而不是伪造 0、空字符串或特殊日期。关键是业务语义一致。

追问 3：订单金额为什么不能用 double？

二进制浮点无法精确表示许多十进制小数，连续计算会产生误差。金额使用整数最小单位或明确精度的 DECIMAL。

追问 4：什么时候可以使用 JSON？

适合低频查询、结构变化快且不参与关键约束的扩展属性。高频过滤、关联、排序和唯一性字段应显式建列。

追问 5：表达到多少数据量必须分表？

没有固定行数阈值。要看行宽、索引、查询模式、写入速率、备份恢复和 DDL 时间。单表问题应先通过证据确认，再选择归档、分区或分片。

追问 6：联合索引字段顺序怎么定？

综合等值过滤、范围、排序、选择性和覆盖需求，并考虑多条高频 SQL 的复用。不能只背“选择性最高放最前”。

追问 7：为什么不建议把所有字段放在一张宽表？

低频大字段会占用 Buffer Pool 和 IO，多个生命周期混合会增加更新冲突和维护难度。但拆分也会增加查询成本，应依据访问模式，而不是机械拆表。

追问 8：不用数据库外键如何保证一致性？

通过应用事务、唯一约束、幂等、补偿任务和定期数据校验共同保证。若这些机制不存在，单纯取消外键只是放弃完整性保护。

机制全景图

「MySQL 数据库表应该怎么设计？」的实现链路如下，节点可与后面的源码和运行证据逐一对应。

flowchart LR
    A["识别实体与不变量"]
    A --> B["选择字段类型和主键"]
    B --> C["设计约束与索引"]
    C --> D["评估写放大和演进"]
    D --> E["用真实数据验证"]

源码与实现定位

入口 阅读重点
information_schema.columns/statistics 字段与索引实际定义
information_schema.innodb_tablespaces 表和索引空间

源码或系统表应按上表顺序追踪：先确认入口实际走到哪条路径，再用运行时数据验证，而不是仅凭类名或配置推测。

参数配置与可复现实验

CREATE TABLE orders (
 id BIGINT PRIMARY KEY, order_no VARCHAR(32) NOT NULL,
 UNIQUE KEY uk_order_no(order_no)
) ENGINE=InnoDB;

生成生产分布数据，测平均/P99 行宽、索引体积、插入吞吐和在线 DDL 时间，而不是空表评审。

验证步骤与预期结果

1. 固定输入和基线

先在没有故障注入的环境执行上述配置，固定数据规模、并发度、运行时版本和预热时间。以「索引/数据比」为主基线，记录值应满足「按读写目标」；同时保存 行平均与 P99 大小、索引总量/数据量，使后续变化能够回到同一时间轴比较。

2. 从实现入口确认路径

在「information_schema.columns/statistics」确认请求确实进入「字段与索引实际定义」对应的实现，再沿「information_schema.innodb_tablespaces」观察「表和索引空间」。如果入口路径都未命中，就不应继续调整下游参数，而应先检查调用条件、版本或路由是否与假设一致。

3. 注入本文特有的失败模式

优先复现「用 varchar 存所有类型」，并把单一变量逐级放大，直到「索引/数据比」越过「>1.5示例」。随后再分别验证「没有唯一约束只靠应用判重」和「索引覆盖所有字段导致写放大」，三类故障分开执行，避免多个变量同时变化而无法归因。

4. 执行止损和根因修复

第一轮只应用「稳定字段类型化」，确认它能控制影响范围；第二轮应用「不变量落唯一/外键约束」，验证核心链路恢复；最后落实「DDL 先影子表/灰度演练」，消除同类问题再次出现的条件。每一步都保留变更前后数据，不用“感觉变快了”替代测量。

5. 通过退出条件

实验只有同时满足三项才算通过：「索引/数据比」回到「按读写目标」、「唯一冲突」回到「与业务重复一致」、「DDL 锁等待」回到「灰度预算内」，并且业务结果差异为零。若性能恢复但结果不一致，仍应视为失败；若指标恢复后很快再次越线，则说明只完成了临时止损，没有消除根因。

量化基线

指标 样例基线/口径 风险线 结论
索引/数据比 按读写目标 >1.5示例 索引过多/过宽
唯一冲突 与业务重复一致 无约束却重复 补唯一键
DDL 锁等待 灰度预算内 阻塞写入 改 online/分批

这些数值是实验口径或示例告警线，不是可复制到所有系统的固定答案；上线阈值应由本系统稳态、峰值和故障演练共同确定。

事故复盘：订单表增加万能 JSON 后查询失控

团队把所有新字段写入 JSON，短期避免 DDL，但筛选、约束和索引逐渐困难，字段语义也无人治理。将稳定高频字段提升为类型化列，保留低频扩展区并建立 Schema 版本后恢复可维护性。

失败模式 首要证据 第一处置动作
用 varchar 存所有类型 行平均与 P99 大小 稳定字段类型化
没有唯一约束只靠应用判重 索引总量/数据量 不变量落唯一/外键约束
索引覆盖所有字段导致写放大 页分裂与写放大 DDL 先影子表/灰度演练

发布与回滚检查点

  • 发布前：确认「information_schema.columns/statistics」对应实现和上述配置在目标版本仍然有效，并保存「索引/数据比」基线。
  • 灰度中：同时观察 行平均与 P99 大小、索引总量/数据量、页分裂与写放大；任一指标越过表中风险线，就停止继续扩量。
  • 回滚时：先执行「稳定字段类型化」控制影响，再回退代码或参数；涉及持久状态时必须额外核对结果差异。
  • 发布后：至少覆盖一个完整峰值周期，确认「用 varchar 存所有类型」没有再次出现，才关闭变更观察窗口。

设计边界与工程取舍

表结构是长期数据契约；正确类型、约束和访问路径应优先于短期开发便利，任何冗余都要明确维护者和修复方式。