MySQL 表设计

表设计是 MySQL 学习里最容易被低估的部分。很多慢 SQL、数据不一致、后期扩展困难，不是 SQL 写错了，而是表结构一开始就没有设计好。

一句话理解：

表设计的目标不是“能存进去”，而是“能长期正确、高效、可演进地存和查”。

设计流程

flowchart TD
    A["理解业务对象"] --> B["识别实体和关系"]
    B --> C["确定字段含义"]
    C --> D["选择数据类型"]
    D --> E["设计主键"]
    E --> F["设计唯一约束"]
    F --> G["根据查询设计索引"]
    G --> H["考虑状态、时间、审计字段"]
    H --> I["评审容量和扩展"]

跳过流程的后果：

问题	后果
字段含义不清	一个字段被多个业务复用，维护困难
类型随便选	存储浪费、排序错误、索引变大
没有唯一约束	只能靠代码防重，最终可能出现重复数据
主键随机	聚簇索引页分裂更频繁
不按查询设计索引	上线后慢 SQL 集中出现

字段类型选择

数值

场景	推荐	原因
主键 ID	bigint	容量足够，适合长期增长
状态	tinyint	占用小，适合枚举
金额	decimal 或 bigint 分	避免浮点误差
数量	int / bigint	根据业务上限选择

金额不要用 double：

create table payment_wrong (
  id bigint primary key,
  amount double not null
);

create table payment_right (
  id bigint primary key,
  amount_cent bigint not null
);

原因：浮点数是近似值，支付、退款、对账必须精确。

字符串

类型	适合	不适合
char	固定长度，如性别、固定编码	长短差异大的文本
varchar	用户名、手机号、订单号	超大文本
text	长内容、备注	高频查询条件、排序字段

索引字段不要过长。varchar(1000) 建索引会让索引页容纳的 key 变少，树更高，缓存命中更差。

时间

推荐保存业务时间：

created_at datetime not null,
updated_at datetime not null

如果涉及跨时区系统，要统一约定：

1. 数据库存 UTC，展示层转用户时区。
2. 或系统统一使用业务所在地时区。

不要一个系统里有的用 UTC，有的用本地时间，否则排查订单、日志、对账会非常痛苦。

主键设计

InnoDB 是聚簇索引，主键叶子节点保存整行数据。主键设计会影响整张表的物理组织。

flowchart TD
    A["主键选择"] --> B["影响聚簇索引顺序"]
    B --> C["影响插入是否顺序"]
    C --> D["影响页分裂和空间碎片"]
    B --> E["影响二级索引大小"]

推荐主键：

id bigint primary key auto_increment

原因：

1. 短：二级索引叶子节点会保存主键，主键越短二级索引越小。
2. 稳定：主键不应该频繁修改。
3. 递增：减少随机插入导致的页分裂。

UUID 作为主键的问题：

问题	原因
插入更随机	新记录可能插入到 B+Tree 中间页
页分裂更多	中间页满了要分裂
二级索引变大	每个二级索引叶子都保存主键值
缓存效率差	随机写更难命中热点页

如果必须使用全局唯一 ID，可以用雪花算法、号段模式、数据库发号器等生成趋势递增 ID。

唯一约束

唯一约束不是为了查询快，首要作用是保证数据正确。

create table users (
  id bigint primary key auto_increment,
  mobile varchar(20) not null,
  username varchar(50) not null,
  unique key uk_mobile (mobile)
) engine = InnoDB default charset = utf8mb4;

为什么不能只靠代码判断手机号是否重复：

sequenceDiagram
    participant A as 请求A
    participant B as 请求B
    participant DB as MySQL
    A->>DB: 查询手机号不存在
    B->>DB: 查询手机号不存在
    A->>DB: 插入手机号
    B->>DB: 插入同一手机号

并发下两个请求都可能先查到“不存在”。唯一索引由数据库保证最终不能重复。

Null 和默认值

尽量使用 not null 并给合理默认值：

status tinyint not null default 1,
remark varchar(255) not null default ''

null 的问题：

问题	说明
语义复杂	null 表示未知，不等于空字符串或 0
SQL 判断特殊	要用 is null，不能用 = null
业务判断复杂	代码里要额外处理空值
索引统计复杂	可能影响优化器估算

不是说绝对不能用 null。如果业务上确实表示“未知”，比如 paid_at 未支付时为空，可以使用。

宽表和窄表

订单主表不要什么都塞进去：

create table orders (
  id bigint primary key auto_increment,
  order_no varchar(64) not null,
  user_id bigint not null,
  status tinyint not null,
  amount_cent bigint not null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_order_no (order_no),
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

订单扩展信息可以拆出去：

create table order_extra (
  order_id bigint primary key,
  receiver_address varchar(500) not null,
  invoice_info json null,
  remark varchar(500) not null default ''
) engine = InnoDB default charset = utf8mb4;

拆表原因：

1. 高频列表查询不需要大字段。
2. 主表行更短，一页能放更多行。
3. Buffer Pool 能缓存更多热点数据。
4. 大字段变更不影响主表查询效率。

反范式设计

范式强调减少冗余，反范式允许为了查询效率保留冗余字段。

订单表保存用户名称：

create table orders (
  id bigint primary key auto_increment,
  user_id bigint not null,
  user_name varchar(50) not null,
  amount_cent bigint not null,
  created_at datetime not null,
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

这样订单列表不用每次 join 用户表。但要接受一个事实：用户改名后，历史订单里的 user_name 是否跟着变，要由业务规则决定。

商业设计示例：支付流水

create table payment_record (
  id bigint primary key auto_increment,
  pay_no varchar(64) not null,
  order_no varchar(64) not null,
  user_id bigint not null,
  channel tinyint not null,
  status tinyint not null,
  amount_cent bigint not null,
  request_id varchar(64) not null,
  paid_at datetime null,
  created_at datetime not null,
  updated_at datetime not null,
  unique key uk_pay_no (pay_no),
  unique key uk_request_id (request_id),
  key idx_order_no (order_no),
  key idx_user_created (user_id, created_at)
) engine = InnoDB default charset = utf8mb4;

关键点：

字段或索引	原因
pay_no 唯一	支付流水号不能重复
request_id 唯一	防止接口重试导致重复支付
amount_cent	金额用分保存，避免浮点误差
paid_at null	未支付时确实没有支付时间
idx_user_created	支持用户查询支付记录

面试标准回答

MySQL 表设计要从业务对象、字段含义、数据类型、主键、唯一约束、索引和容量演进几个方面考虑。InnoDB 使用聚簇索引，主键会影响数据的物理组织，所以主键一般要短、稳定、趋势递增，避免随机 UUID 导致页分裂和二级索引膨胀。唯一约束要交给数据库保证，不能只靠代码防重。字段尽量 not null 并设置合理默认值，金额不要用浮点数，时间字段要统一时区约定。高频字段和大字段可以拆表，必要时为了查询效率做反范式冗余，但要明确一致性维护规则。

关联学习：SQL 基础、存储结构、索引。