MUYI · 后端工程师的生产级知识体系

Appearance

数据库设计时主要注意哪些事情？ ​

声明：默认指 MySQL 的 InnoDB 存储引擎，以 MySQL 8.0 为主视角，涉及 5.7/8.0 行为差异处会单独标注。

表结构是系统的地基。数据量翻 100 倍之后还能不能顺畅地查询、变更、扩容，很大程度在建表那一刻就决定了。面试问"数据库设计注意什么"，是考你有没有被生产毒打过。下面按"规范 → 类型 → 主键 → 索引 → 大字段 → 反范式 → 扩展性"的顺序展开。

1 总览：设计目标 ​

  • 性能：写入顺序、索引可控、单表体量可控；
  • 可维护：命名统一、字段自解释、有注释、可平滑 DDL；
  • 可扩展：预留分片键与归档策略，避免后期推倒重来；
  • 一致性：null 语义清晰、冗余字段有明确的维护机制。

2 三范式与反范式的取舍 ​

  • 1NF 列原子性（不要在一个字段里塞逗号分隔的 id 列表——虽然 JSON 类型是受控的例外）；
  • 2NF 非主键列完全依赖主键（消除部分依赖）；
  • 3NF 非主键列不传递依赖主键（消除传递依赖）。

互联网业务的普遍做法：遵循 3NF 设计基础表，再按查询热点做定向反范式。冗余计数（点赞数）、冗余名称（订单里冗余商品名快照，还兼顾历史正确性）、宽表（报表/详情页）。反范式省掉 JOIN，代价是写入侧要维护一致性（事务内更新、或 binlog 异步对账）。冗余必须显式设计，而不是写着写着顺手复制的。

3 命名与基础规范 ​

  • 库表列名全小写 + 下划线分隔（Linux 上表名大小写敏感取决于 lower_case_table_names，统一小写最稳）。不用 MySQL 保留字（order、desc、groups 都是常踩的坑）；
  • 同一语义跨表同名：user_id 在所有表里都叫 user_id，不要 uid/buyer_id 混用；
  • 布尔语义用 is_xxx（tinyint 存 0/1）；时间用 create_time/update_time；
  • 每张表必备三件套：
sql
CREATE TABLE t_order (
  id           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  -- ... 业务字段 ...
  create_time  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  update_time  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';
  • 不使用外键与级联（互联网规范）：外键把一致性校验放进数据库。带来额外锁、级联风暴和分库分表障碍。一致性由应用层/事务保证（逻辑外键）；
  • 不使用 ENUM：加一个枚举值要 DDL；用 tinyint unsigned + 注释 或字典表。8.0 加列虽可 INSTANT，但 ENUM 改值仍属变更，不如 tinyint 灵活。

4 字段类型选择（最容易体现功力的部分） ​

4.1 选型速查表 ​

需求推荐避免/原因
主键/IDBIGINT UNSIGNED（或雪花 ID）UUID 字符串：空间大、插入随机导致页分裂
状态/类型/枚举TINYINT UNSIGNEDENUM（改值需 DDL）
金额DECIMAL(12,2)FLOAT/DOUBLE 有精度误差，钱不能算错
短字符串VARCHAR(N)，N 按真实上限无脑 VARCHAR(255)：影响排序内存与索引长度
定长编码（身份证 MD5 等）CHAR(32)VARCHAR 有 1~2 字节长度前缀
时间DATETIME（或 TIMESTAMP 见 4.3）VARCHAR 存时间无法按时间索引比较
大文本/二进制TEXT/BLOB，且垂直拆到附表与主表混放拖累扫描效率
标签/灵活属性JSON（5.7+）滥用 JSON 当万能字段：无法建常规索引、校验弱

4.2 整数与字符串细节 ​

  • INT 显示宽度（INT(11)）只影响 zerofill 展示，不限制取值范围，8.0 已弃用该语法（5.7 常见，升级时会被清理）；
  • 能用 UNSIGNED 就用：正整数范围翻倍且语义更清晰（如 ip2long 存 IP）；
  • VARCHAR(N) 的 N 是字符数，utf8mb4 下每字符最多 4 字节。索引长度限制默认 3072 字节（InnoDB DYNAMIC 行格式，5.7.7+/8.0 默认；更老版本是 767 字节）。所以 utf8mb4 单列索引最长约 768 字符；
  • ORDER BY varchar_col 时排序缓冲按定义长度预留内存，无脑 255 放大内存占用。

4.3 时间类型：DATETIME vs TIMESTAMP ​

维度DATETIMETIMESTAMP
存储8 字节（5.6.4+ 支持小数秒，最多 +3 字节）4 字节（有小数秒时 +3）
范围1000-01-01 ~ 9999-12-311970 ~ 2038-01-19（2038 问题）
时区存什么是什么，不做转换按 UTC 存储，读写自动做时区转换
默认值5.6+ 支持 DEFAULT CURRENT_TIMESTAMP同左；explicit_defaults_for_timestamp 在 8.0 默认 ON，不再隐式给首个 TIMESTAMP 列塞默认行为

经验：新系统一律 DATETIME，时区在应用层统一处理；存量 TIMESTAMP 在 2038 前要有迁移计划。

4.4 JSON 类型 ​

5.7 引入，8.0 增强（部分更新减少重写整份文档、->> 便捷取值）：

sql
ALTER TABLE t_order ADD COLUMN ext JSON;
SELECT ext->>'$.channel' FROM t_order;
CREATE INDEX idx_channel ON t_order ((CAST(ext->>'$.channel' AS CHAR(64)))); -- 8.0.13+ 虚拟列函数索引

定位：低频、结构多变的扩展属性；高频查询条件仍应落到普通列。8.0.13+ 可对 JSON 表达式建函数索引弥补检索。

5 主键设计：为什么强烈推荐有序主键 ​

InnoDB 是聚集索引组织表：数据行物理上按主键顺序存放在 B+ 树叶子节点。

自增/雪花主键插入（顺序写）          UUID 主键插入（随机写）
 [1][2][3][4][5][6][7][ ]           [7][2][500][9][88][3][1]
  新行总落在最后一页的空闲位置         每次插入都可能命中已满的页
  -> 顺序追加，页写满才开新页          -> 页分裂：把半页数据挪到新页 + 更新父节点
                                     -> 写放大、缓存命中率下降、空间碎片
  • 自增 BIGINT：最优写入性能；缺点是分库分表下会冲突、暴露业务量（可用随机起始值缓解）；
  • 雪花 Snowflake：趋势递增（高位时间戳），既保住顺序写又全局唯一。是分片场景标配（详见同目录《美团-Leaf》《雪花算法》两篇）；
  • UUID v4（随机）：主键场景最差选择——页分裂 + 36 字符空间 + 缓存局部性差。若必须用 UUID，至少用 UUID v7（时间有序，MySQL 侧仍占空间）或转换为有序形式；
  • 没有显式主键时，InnoDB 会选第一个"非空唯一索引"。再没有就生成隐藏的 6 字节行 ID（不可用、不可见，binlog 主从同步场景还有坑）。每表必须有显式主键。

6 索引设计规范 ​

  • 单表索引建议不超过 5~6 个、单个联合索引不超过 4~5 列。每个索引都是一棵独立的 B+ 树，拖累写入、占空间；
  • 联合索引顺序：等值列在前、范围列在后、排序列衔接；把最常用的过滤列放最左；
  • 区分度 COUNT(DISTINCT col)/COUNT(*) 低的列（性别/状态）不要单独建索引，要么进联合索引、要么靠其他条件先过滤；
  • 避免冗余：有 (a,b) 时 (a) 是冗余索引；(a) 和 (id,a) 也是（主键天然在二级索引尾部）；
  • 高频查询尽量设计成覆盖索引；
  • 给字符串列建前缀索引控制长度；8.0.13+ 可用函数索引覆盖带函数的查询；
  • 删索引先 INVISIBLE 灰度（8.0），确认无依赖再 DROP。

7 大字段与表结构拆分 ​

  • TEXT/BLOB 可能触发行溢出。超过约 768/4096 字节的部分存到溢出页。DYNAMIC 格式下只在本页留 20 字节指针。SELECT 未取大列也可能因为二级索引回表整行读取而受影响；
  • 常见拆法：主表保持窄（扫描快），t_order_detail_ext(order_id, content TEXT) 一对一垂直拆分；
  • 需要 WHERE content LIKE '%kw%' 的检索不要指望索引，走全文索引或 ES；
  • 临时表注意：含大字段/JSON 的查询在内存临时表放不下时落盘。8.0 TempTable 引擎改善了内存处理，但溢出仍存在。GROUP BY 大字段先取摘要值再 JOIN 回取内容。

8 NULL 的处理 ​

  • 建议列都 NOT NULL 并给默认值，理由：
    • COUNT(col)、SUM(col) 会忽略 NULL 行，聚合结果和直觉不符；
    • NULL 参与比较是三值逻辑（UNKNOWN），WHERE col != 'x' 查不出 NULL 行，易漏数据；
    • NOT IN (含 NULL 的子查询) 恒为空这类"埋雷"行为；
    • NULL 使索引统计和优化器估算更复杂；
  • 澄清一个常见误区：NULL 值本身可以被索引，IS NULL 能走索引，"NULL 不走索引"是错的。问题在语义和统计，不在能不能走；
  • 表示"没值"用 NOT NULL + 语义默认值（空串、0）或专门的状态列。

9 冗余、冷热与扩展性 ​

  • 预估容量：按 3~5 年增量估算行数与磁盘，评估单表是否到千万级、是否需要分表；
  • 分库分表要提前定分片键（通常选租户/用户维度），分片键进所有查询路径。跨分片 JOIN/聚合靠冗余宽表、数据仓库或绑定表；
  • 冷热分离：历史订单按时间归档到归档库/归档表，线上主表保持小而快；
  • 预留无侵入的扩展列（如 ext JSON）应对临时需求，但严控使用范围；
  • 长文本快照类需求（订单里的收货地址、商品名）按当时快照存储，不做外键关联。历史数据要还原当时的样子。

10 MySQL 8 视角的设计差异（版本加分项） ​

  • 默认字符集 utf8mb4 / collation utf8mb4_0900_ai_ci：5.7 默认 latin1。跨版本 JOIN 时 collation 不一致会导致索引失效，迁移后要统一核查（SHOW CREATE TABLE）；
  • 默认认证插件 caching_sha2_password：老客户端/驱动连不上，需调整用户插件或升级驱动（设计账号体系时考虑）；
  • Instant DDL：8.0.12 起 ADD COLUMN 可 INSTANT 秒级完成，8.0.29 扩展到 DROP COLUMN。5.7 时代大表 DDL 要 gh-ost/pt-osc。但"能秒加列"不等于可以随意改表，索引重建仍是重量级；
  • 数据字典改为 InnoDB 表：frm 文件消失，跨库元数据一致；
  • 降序索引、函数索引、Skip Scan（见《数据库索引失效总结》），为设计提供更多"用索引解决"的选项；
  • GROUP BY 不再隐式 ORDER BY：依赖隐式排序的老表/老 SQL 升级时要显式补 ORDER BY。

11 追问链（面试官会怎么往下问） ​

  • Q：为什么不用 UUID 做主键？ A：聚簇索引按主键有序存储，随机主键导致频繁页分裂、写放大、buffer pool 命中率下降。雪花/自增是趋势递增，顺序追加。
  • Q：为什么金额不能用 FLOAT/DOUBLE？ A：二进制浮点无法精确表示十进制小数，累加误差会算错账。DECIMAL 按字符串/定点存，或存最小单位分（BIGINT）。
  • Q：DATETIME 和 TIMESTAMP 选哪个？ A：范围与时区语义：TIMESTAMP 4 字节、自动时区转换，但有 2038 上限；DATETIME 8 字节、无时区语义。新系统 DATETIME + 应用层统一时区。
  • Q：为什么互联网规范禁用外键？ A：外键的约束检查与级联在高并发下放大锁竞争，且是分库分表的拦路虎；一致性交给应用事务。
  • Q：字段为什么建议 NOT NULL？ A：三值逻辑埋雷（!=、NOT IN 查不出 NULL）、聚合忽略 NULL、统计复杂；但 NULL 本身可走索引，别把理由说错。
  • Q：一张表最多能存多少数据？ A：技术上没有硬上限（受磁盘限制），工程经验单表千万行/10 GB 开始评估分表。真正信号是树高增长、缓存命中率与维护成本。
  • Q：TEXT 大字段为什么建议拆出去？ A：行溢出、扫描放大、内存临时表落盘；拆附表让主表窄，常用查询不受大字段拖累。

12 小结 ​

  • 设计顺序建议：先按 3NF 理清实体，再按查询热点做定向反范式；
  • 类型选型三条铁律：金额 DECIMAL、时间 DATETIME、状态 tinyint；字符串按真实长度，统一 utf8mb4；
  • 主键要"有序 + 全局唯一"：单库自增，分布式雪花；
  • 索引少而精：联合索引对齐查询，覆盖优先，删索引走 INVISIBLE 灰度（8.0）；
  • 列尽量 NOT NULL + 默认值，避开三值逻辑的坑；
  • 为未来留路：预估容量、定分片键、冷热分离、快照式冗余；
  • MySQL 8 的默认值变化（utf8mb4、认证插件、Instant DDL、GROUP BY 行为）是升级与设计时必须心里有数的差异点。

2026-10 依据主题重写（原文件为空壳）。