高性能数据库表该如何设计

0. 引言

表设计决定了数据库 80% 的性能上限——索引可以优化查询，但糟糕的 schema 无法靠索引救回来。本文从范式理论出发，给出 MySQL 8.0 下的数据类型选型、字段设计规范与实际案例，回答"这张表该怎么建"的完整问题。

1. 范式与反范式

1.1 范式理论

范式	要求	通俗解释
1NF	属性不可再分	每列原子：一个字段不存多个值（如"张三,李四"）
2NF	满足 1NF + 非主属性完全依赖主键	消除部分依赖：联合主键下，非主键列不能只依赖主键的一部分
3NF	满足 2NF + 非主属性不传递依赖主键	消除传递依赖：如"订单表存用户部门"（用户→部门传递依赖）
BCNF	每个决定因素都是候选键	3NF 的加强版，主属性内部的传递依赖也消除

范式的好处：数据冗余少、更新一致性好、节省存储。代价：查询需要多表 JOIN，关联变多、性能下降。

1.2 反范式：何时主动打破

常见反范式场景（权衡后保留冗余）：

场景	反范式做法	理由
订单列表页	订单表冗余"用户名/手机号"快照	避免高频列表查询 JOIN 用户表；用户改名不影响历史订单
统计计数	冗余计数列（如文章表冗余评论数）	避免每次 COUNT(*) 聚合
宽表数仓	多表合并为宽表	OLAP 场景查询即取，JOIN 代价不可接受

范式与反范式的取舍原则：

OLTP 在线交易：3NF 为主，适度冗余（冗余字段必须能容忍短暂不一致或由业务保证同步）；
OLAP 分析：反范式宽表为主；
永远不变的铁律：冗余字段要有明确的同步机制（事务内更新/异步任务补偿），否则必然产生脏数据。

图表渲染中…

2. 数据类型选型（8.0 视角）

2.1 数值类型

类型	存储	场景
TINYINT	1 字节	状态码、开关（-128~127）
SMALLINT	2 字节	小范围枚举
MEDIUMINT	3 字节	中范围计数
INT	4 字节	常规 ID、计数（-21 亿~21 亿）
BIGINT	8 字节	大 ID、雪花 ID、时间戳毫秒
DECIMAL(p,s)	变长	金额必须用 DECIMAL（精确十进制）
FLOAT/DOUBLE	4/8 字节	近似值，仅科学计算/经纬度等精度可容忍场景

int(3)/int(5) 的真相：int(3) 不是"3 位整数"，括号内只是显示宽度（配合 ZEROFILL 补零），存储与取值范围完全不变（都是 4 字节 32 位）。8.0.17+ 起显示宽度已被废弃，不再影响展示。设计时不需要写 int(n)。

2.2 字符串类型

类型	特点	建议
CHAR(n)	定长，不足补空格，0-255	定长码值：手机号？不——见下；状态码、固定长度标识
VARCHAR(n)	变长，1-65535 字节（含长度前缀）	绝大多数文本字段
TEXT/BLOB	大文本/二进制，独立存储（溢出页）	尽量少用：无法默认索引、排序开销大；大文本建议拆表或存对象存储

CHAR vs VARCHAR 选择：

长度固定且短（如 MD5 32 位、固定编码）→ CHAR；
长度可变（用户名、地址）→ VARCHAR；
手机号/身份证：虽然长度固定，但作为索引列时 VARCHAR 更省（CHAR 补空格比较有坑），且可能升级为可变，实际生产多用 VARCHAR(11)/VARCHAR(18)。

2.3 日期时间类型

类型	范围	存储	特点
DATE	1000-01-01 ~ 9999-12-31	3 字节	仅日期
TIME	-838:59:59 ~ 838:59:59	3 字节	时间段
DATETIME	同 DATE	8 字节（8.0 支持小数秒）	无时区，存啥显示啥
TIMESTAMP	1970-01-01 ~ 2038-01-19	4 字节	自动按会话时区转换

TIMESTAMP 的坑：

2038 年问题：4 字节上限 2038-01-19，长期系统慎用；
时区自动转换：写入/读取都按 time_zone 转换，多机房部署时同一值在不同时区读出不同；
8.0 中 TIMESTAMP 与 DATETIME 均支持自动初始化/更新（DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP），且 DATETIME 也支持带时区语义（DATETIME(6)）。

建议：新系统统一使用 DATETIME(3)（毫秒精度）存储业务时间，应用层统一 UTC 转换；或直接存 BIGINT 毫秒时间戳（便于跨库比较与排序）。

2.4 其他类型

类型	说明
JSON	8.0 原生 JSON：`->`/`->>` 提取、`JSON_TABLE()` 展开为关系表、多值索引（8.0.17+）、`CHECK (json_valid(col))` 校验。适合灵活配置、埋点明细
枚举 ENUM	紧凑但修改枚举需 DDL、排序按定义顺序而非字面值，新项目建议用 TINYINT + 字典表
生成列 GENERATED	8.0 支持函数索引（`CREATE INDEX ON t ((LOWER(col)))`）与不可见索引（`INVISIBLE`），详见索引篇
布尔	MySQL 无原生 BOOLEAN，用 TINYINT(1)

3. 字段设计规范

3.1 通用规范

主键：8.0 推荐 BIGINT UNSIGNED AUTO_INCREMENT 或雪花/分布式 ID；不建议 UUID 直接做主键（无序、聚簇索引页分裂严重），可用 UUID 转 BIGINT 或改用随机后缀；
NOT NULL + DEFAULT：所有字段尽量 NOT NULL（NULL 使索引统计与比较复杂化，且占额外空间），业务无值用空串/0/明确默认值；
冗余越小越好：能复用字段不新增，警惕"同义不同名"（user_id vs uid）；
大字段独立：TEXT/BLOB/超长 VARCHAR 拆出主表，避免行溢出拖慢主键查询；
预留字段是反模式：reserve1/reserve2 会让代码与字段语义脱节，8.0 的 ALTER TABLE ... ADD COLUMN 是 INSTANT 操作（秒级），需要时再加。

3.2 命名规范

表名：业务模块前缀 + 名词复数（order_detail、user_account_log），全小写下划线；
字段：小写下划线，动词+名词（is_deleted、created_at、updated_at）；
索引：idx_字段名（普通）、uk_字段名（唯一）、fk_（外键）；
统一保留字段：id（主键）、created_at、updated_at、is_deleted（逻辑删除，8.0 可用生成列配合唯一索引做软删唯一约束）。

3.3 InnoDB 表注意事项

必须显式指定主键：否则 InnoDB 用第一个非空唯一索引，再不行隐式生成 6 字节 rowid（无法利用聚簇索引优势、复制与备份混乱）；
字符集统一 utf8mb4（8.0 默认）：utf8mb4 是真正的"完整 UTF-8"（4 字节，支持 emoji 与生僻字），utf8 只是 3 字节子集；排序规则用 utf8mb4_0900_ai_ci（8.0 新排序，更快更准）；
行格式：8.0 默认 DYNAMIC（大字段溢出页存储），不要再手动指定 COMPACT；
分区表谨慎：8.0 分区限制多（唯一键必须含分区键），优先用分表/归档替代分区。

4. 实战案例

4.1 IP 地址存储

sql

-- ❌ 错误：字符串存 IP 浪费 3 倍空间且无法范围查询优化
ip VARCHAR(15)
-- ✅ 正确：INT UNSIGNED 4 字节，配合 INET_ATON/INET_NTOA
ip INT UNSIGNED
SELECT INET_ATON('192.168.1.1');   -- 3232235777
SELECT INET_NTOA(3232235777);      -- '192.168.1.1'
-- 8.0 还支持 INET6_ATON/INET6_NTOA（BINARY(16)，存 IPv6）

4.2 时间戳与状态

sql

CREATE TABLE user_order (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL COMMENT '业务订单号（唯一）',
  user_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL COMMENT '金额，分转元',
  status TINYINT NOT NULL DEFAULT 0 COMMENT '0待支付 1已支付 2已取消',
  pay_time DATETIME(3) NULL,
  created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_id (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户订单表';

4.3 表大小与频率分层

级别	数据量	设计策略
小表	< 100 万行	常规设计，索引适度
中表	100 万 ~ 1000 万行	索引精细化、避免大字段、冷热分离
大表	> 1000 万行	归档（分区/分表）、读写分离、按时间分表
超大表	> 1 亿行	分库分表（见《如何突破单库性能瓶颈》）

5. 小结

范式解决"更新一致性"，反范式解决"查询性能"，OLTP 以 3NF 为主、局部反范式；
数据类型是隐藏的性能杠杆：INT 家族按需选、金额必 DECIMAL、字符串 CHAR/VARCHAR 分场景、时间戳统一 DATETIME(3) 或 BIGINT；
8.0 新特性（JSON 原生支持、函数索引、不可见索引、INSTANT DDL）让"先建后用"更从容，不再需要预留字段；
规范即生产力：显式主键、NOT NULL、utf8mb4、统一命名，是团队协作的底线。

下一章讲解高性能索引设计：B+ 树原理、聚簇索引/二级索引、覆盖索引与索引下推、8.0 索引新特性。