表设计与字段选型

提出问题

建表是后端开发最基础也最容易踩坑的环节。一张表设计得好，后续查询、扩展、维护都顺畅；设计不好，索引堆上去也救不了 I/O 和存储。面试官问表设计，表面考 SQL 熟练度，实则在考察候选人对存储引擎底层行为（聚簇索引、行格式、内存分配）的理解——比如为什么主键用 UUID 会导致 InnoDB 页分裂？varchar(255) 和 varchar(1000) 在内存临时表里差多少？NULL 带来的存储开销具体在哪？这些问题踩过线上事故的人最清楚。

范式 vs 反范式：冗余字段的取舍

第三范式（3NF）要求非主键字段直接依赖主键，消除传递依赖。但全范式化意味着 JOIN 大量表，查询性能下降。反范式化通过冗余字段（如订单表直接存用户名而非 user_id）减少 JOIN，适合读多写少、对一致性要求不高的场景。

实际案例：订单详情页 JOIN 拖垮 MySQL

背景：某电商订单详情页，每次查询 JOIN 6 张表（订单主表、商品表、用户表、地址表、优惠券表、物流表），QPS 3000+ 时 MySQL CPU 冲到 80%。

分析：每条 JOIN 都要走聚簇索引回表、重复扫描，Using join buffer (Block Nested Loop) 频繁出现。6 张表 JOIN 下来，一次查询读 10+ 个数据页，Buffer Pool 命中率从 99% 降到 92%。

优化方案：将用户昵称、商品标题、商品缩略图 URL 三个字段冗余到订单主表，JOIN 减到 3 张。

-- 优化前：6 张 JOIN
SELECT o.*, u.nickname, g.title, g.thumb_url
FROM `order` o
JOIN `user` u ON o.user_id = u.id
JOIN `goods` g ON o.goods_id = g.id
JOIN `address` a ON o.address_id = a.id
JOIN `coupon` c ON o.coupon_id = c.id
JOIN `logistics` l ON o.logistics_id = l.id
WHERE o.id = 123456;

-- 优化后：冗余字段，3 张 JOIN
SELECT o.*, l.tracking_no, l.status
FROM `order` o
JOIN `logistics` l ON o.logistics_id = l.id
WHERE o.id = 123456;

效果：单查询耗时从 85ms 降到 12ms，CPU 从 80% 降到 30%。

反范式化的代价

踩坑记录：同一家电商，用户修改昵称后，订单详情页 30 分钟没更新，因为只改了 user 表，order 表里的冗余昵称没同步。最终方案：在用户昵称更新的业务方法里，异步 MQ 广播 OrderNicknameUpdateEvent，订单消费者收到后 update 该用户最近 100 条订单的冗余字段。

适用条件：

• 冗余字段更新频率 ≤ 1 次/天，查询频率 ≥ 1000 次/天
• 数据一致性可接受秒级延迟（MQ 异步更新）或最终一致
• 必须通过 MQ/CDC 确保冗余字段同步，不能依赖业务代码到处手动维护

字段类型选型：逐字节地抠

int vs tinyint vs enum vs varchar

字段	存储	范围	生产建议
tinyint unsigned	1 字节	0~255	状态码、性别、级别枚举，比 int 省 3 字节
smallint unsigned	2 字节	0~65535	端口号、短 ID
int unsigned	4 字节	0~42 亿	常规主键、普通 ID 不超 21 亿用 signed 也够
bigint unsigned	8 字节	0~1.8e19	雪花 ID、分库分表后的全局主键
enum	1~2 字节	255~65535 个值	不推荐线上用：ALTER TABLE ... MODIFY 要重建表，MySQL 8.0 加值要 ALTER TABLE ... ENUM(...)
varchar(n)	实际字符数 + 1~2 字节	n ≤ 65535 字节	可变长度，n 按业务最大字符数设，不要多给

varchar 长度陷阱：一个 Twitter 级别的教训

生产案例：某社交 App 用户简介字段定义 varchar(5000)，线上实际 99% 的用户简介 ≤ 200 字。但内存临时表（Using temporary）分配固定大小的 VARCHAR(5000)，导致文件排序（Using filesort）时每行占 5000 字节。一次 10 万条用户简介按粉丝数排序的查询，内存临时表直接溢写到磁盘，排序耗时 8 秒。

修复：缩到 varchar(500)，内存临时表大小缩为 1/10，排序耗时降到 500ms。

原理：MySQL 内存临时表使用 MEMORY 引擎，VARCHAR(n) 按 n 字符数 × 字符集最大字节数（utf8mb4 为 4 字节）分配固定长度。varchar(5000) 在 utf8mb4 下每行占 20000 字节，10 万行占 2GB。tmp_table_size 默认 16MB，瞬间溢出。

结论：varchar 长度紧贴业务最大长度，不要为了「万一以后」多给 10 倍。多给一个 0，内存临时表膨胀 10 倍。

时间字段选型

类型	字节	范围	时区	生产建议
datetime	5~8	1000-01-01 ~ 9999-12-31	不关心时区，存原值	推荐，无 2038 问题
datetime(3)	6~8	同上，毫秒精度	不关心时区	推荐，记录创建/更新时间
timestamp	4	1970-01-01 ~ 2038-01-19	自动转 UTC，受时区影响	有 2038 年问题，新项目不推荐
bigint	8	自定毫秒/秒戳	完全由程序控制	跨时区/跨语言场景可选

生产方案：统一用 datetime(3) 记录创建/更新时间，不做时区转换，应用层显示时转本地时区。timestamp 的 2038 年问题不是段子，某金融系统 2022 年还在用 timestamp，审计要求数据保存到 2050 年，被迫做 ALTER TABLE 大表迁移，锁表 3 小时。

主键设计：自增 vs 雪花 vs UUID

InnoDB 是聚簇索引表，数据按主键顺序物理排列。主键选择直接影响写入性能。

自增主键：最安全的选择

-- 推荐：自增主键，写入顺序递增，B+ 树叶子节点只追加，不分裂
CREATE TABLE `order` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `status` tinyint unsigned NOT NULL DEFAULT 0,
  `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_order_no` (`order_no`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

优点：写入顺序递增，B+ 树叶子节点只在末尾追加，不会触发页分裂（page split），写入性能最高。

缺点：分布式场景下多个应用实例同时写入需要全局协调，分库分表后可能产生冲突。

雪花 ID：分布式场景的折中

CREATE TABLE `order` (
  `id` bigint unsigned NOT NULL COMMENT '雪花 ID',
  `order_no` varchar(32) NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

雪花 ID 结构（64 位）：

比特位	41 位	10 位	12 位
含义	时间戳（毫秒）	机器 ID	序列号

趋势递增：因为时间戳在高位，批次写入的数据在主键顺序上是递增的，写入性能接近自增主键。但跨批次（如服务器重启后）可能产生少量回退，导致少量页分裂——每 10 万条写入约 5~10 次页分裂，可以接受。

UUID：为什么不能做主键

UUID v4（完全随机）：

-- 反面教材：线上真实案例
CREATE TABLE `log` (
  `id` char(36) NOT NULL,  -- UUID v4
  `content` text,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

真实踩坑：某日志系统用 UUID v4 做主键，日写入 500 万条。运行 3 个月后，表空间 12GB，实际数据只有 6GB——碎片率 50%。每次写入要插入随机位置，B+ 树频繁页分裂（page split），索引碎片化严重。OPTIMIZE TABLE 重建表花了 2 小时，期间阻塞写入。

原因：InnoDB 数据页默认 16KB，每页存约 200 条记录（按每行 80 字节算）。UUID 随机写入时，新记录落在一个几乎满的页上 → 该页分裂为两个 50% 满的页 → 原页 50% 空间浪费。持续分裂 → 碎片率 30%~50%。

UUID v7（时间排序）：MySQL 8.0+ 支持 UUID_TO_BIN()，通过 UUID_TO_BIN(uuid(), 1) 生成时间排序的 UUID，写入顺序接近雪花 ID。但应用层需要额外处理，不如直接上雪花 ID。

主键方案	顺序性	写入性能	页分裂概率	存储空间	分布式友好
自增 bigint	严格递增	最高	0	8 字节	需协调
雪花 ID	趋势递增	接近自增	每 10 万条 5~10 次	8 字节	天然支持
UUID v4	完全随机	差	每写入都可能	36 字节字符(16 字节二进制)	天然支持
UUID v7	趋势递增	较高	少量	同上	天然支持，8.0+ 可用

联合主键与二级索引

面试考点：InnoDB 二级索引的叶子节点存的是主键值。主键越大，二级索引占用的空间越大。

-- 表有 10 个二级索引，主键用 bigint(8 字节) vs UUID(36 字节)
-- 每个二级索引条目多存 28 字节
-- 1000 万条 × 10 个索引 × 28 字节 = 2.8GB 额外空间

结论：主键越小，二级索引越省空间。这是为什么自增 int/bigint 比 UUID 在空间上优势更明显的原因。

NULL 的代价：比你以为的多

InnoDB 行格式解读（COMPACT / DYNAMIC）：

行格式：
  [变长字段长度列表][NULL 位图][固定字段][可变字段...]

• NULL 位图：每 8 个可为 NULL 的字段占 1 字节，标记每个字段是否为 NULL
• 即使字段值为 NULL，位图位仍然存在，只是没有实际数据
• IS NULL 查询不能走索引下推（ICP），因为索引条目里没有 NULL 位图信息
• IS NOT NULL 同样不能走索引下推

生产案例：某日志表 deleted_at 字段定义为 datetime DEFAULT NULL，查询 WHERE deleted_at IS NULL 走不了索引下推，每次回表 10 万行。改为 deleted_at datetime NOT NULL DEFAULT '1970-01-01' 并加 WHERE deleted_at = '1970-01-01'，查询走索引，耗时从 800ms 降到 15ms。

结论：能用 NOT NULL + DEFAULT 值 就别让字段可为 NULL。DEFAULT '' 比 DEFAULT NULL 好，DEFAULT 0 比 DEFAULT NULL 好。

其他设计实践

字符集统一用 utf8mb4

utf8 在 MySQL 里是假的 utf8，最多 3 字节，存不了 emoji（😂）和部分生僻字。utf8mb4 才是真正的 4 字节 UTF-8。

-- 正确
CREATE TABLE ... DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

踩坑：某社交 App 用户昵称字段用 utf8，用户输入 emoji 后报错 Incorrect string value: '\xF0\x9F...'，最终全表 ALTER 改字符集，大表 500 万行锁住 40 分钟。

数字类型用 unsigned 扩大上限

int 范围 -21 亿到 21 亿，ID 不可能为负，所以用 int unsigned 把上限扩大到 42 亿。同理 tinyint unsigned 范围 0~255 而不是 -128~127。

不要用 text/blob 做主键或索引的一部分

text 和 blob 不支持前缀索引之外的索引，ORDER BY text_column 用不上索引，GROUP BY text_column 只能用文件排序。日志内容、大文本字段单独建表，外键关联。

总结

1. 范式化设计 → 按热点查询反范式化，冗余字段必须通过 MQ/CDC 机制同步，不能靠业务代码手动维护
2. 字段类型按实际需求选，varchar 长度紧贴业务最大长度，多给一个 0 内存临时表膨胀 10 倍
3. 时间字段用 datetime(3)，避免 2038 问题，应用层做时区转换
4. 主键用自增 bigint 或雪花 ID，UUID v4 坚决不用，碎片率可达 50%
5. 字段尽量 NOT NULL，IS NULL/IS NOT NULL 走不了索引下推，查询性能差 10 倍以上
6. 字符集统一用 utf8mb4，避免 emoji 报错
7. 主键越小越好，二级索引叶子节点存主键，主键大 4 倍，二级索引也大 4 倍

参考

MySQL 官方文档 InnoDB Row Formats | Data Type Storage Requirements | MySQL 8.0 UUID Support | MySQL 8.0 memcached temporary table