一个订单表从 12 列长到 40 列。新需求来一次加两列:发票信息、物流单号、优惠券、渠道来源、退款原因、会员等级、直播间 id……其中一半字段只在特定订单上有值,大部分行是 NULL。后来又有人往表里加了一个 JSON 字段,把整个商品快照塞进去,查起来用 LIKE 扫 JSON 文本,越来越慢。
表设计的问题不会立刻爆发,而是在半年后以「慢查询」「字段含义没人说得清」「改一列怕影响十个功能」的形式出现。这篇是 SQL 系列的最后一篇,回到设计阶段:范式到底在防什么、什么时候该拆、什么时候该冗余、JSON 能用到什么程度、什么时候才需要考虑分库分表。
场景走查:40 列的订单表怎么长出来的
每一次加列都有理由:做发票要发票抬头,做物流要运单号,做营销要优惠券 id。问题是这些需求的答案分布不同:发票信息只有企业订单才有,物流信息只有发货后才有,优惠券只有部分订单有。把它们全塞进一张表,就得到大量 NULL。
NULL 多本身不是罪,但它带来三个麻烦:行变宽、索引变大、字段语义模糊(「这个 NULL 是没填,还是不适用?」)。更麻烦的是,四十个字段里很难看出哪些属于「订单主体」,哪些属于「履约」「支付」「营销」,改一个字段要翻十处代码。
范式说人话:一张表只管一个主题
第一范式(1NF)要求每一列都是不可再分的最小单位,不能把「北京,上海」塞进一个字段当多值用。第二范式(2NF)要求每一行由主键唯一确定,不能出现「这行的主键是订单 id,但客户名取决于客户 id」这种部分依赖。第三范式(3NF)要求非主键列不能依赖其他非主键列,比如订单表里存了「客户城市」,而城市其实由客户决定。
背定义没意义,实用版就一句话:一张表只管一个主题,属于别的主题的数据只存引用,不存副本。客户信息归客户表,商品信息归商品表,订单表只存「谁、买了什么、多少钱、什么状态」。
但范式不是教条。报表、列表页、快照场景里,适度冗余能省掉 JOIN 和实时计算。关键是知道自己在冗余什么、为什么冗余、怎么保证一致。
什么时候该拆
「可选属性」最值得拆。发票、物流、退款这三类信息,只有部分订单有,拆成独立表后,主表保持窄,查询列表时不用加载它们;需要详情时再按订单 id 关联。代价是详情页多一次 JOIN,但通常可以接受。
「一对多」不能靠加列解决。见过 item1_name, item1_price, item2_name, item2_price 这种设计,加到第 5 个商品就崩了。正确做法是明细表加 order_id 外键,一行一个商品,数量不限。
「大字段」也要小心。把 5KB 的 JSON 快照放在订单主表里,每行都变宽,Buffer Pool 能缓存的页数变少,列表查询即使只取两列,也可能因为行太宽而变慢。拆到扩展表或对象存储,主表只存引用。
什么时候该合、该冗余
反范式不是「随便抄一份」。合理的冗余通常有明确目的:
| 场景 | 冗余什么 | 代价与兜底 |
|---|---|---|
| 订单列表要显示客户名 | 订单表存 customer_name 快照 | 客户改名后历史订单显示旧名,需接受或异步刷新 |
| 商品价格会变,订单要保留下单时价格 | 明细表存 price_snapshot | 这是业务要求,不是冗余错误 |
| 报表按城市聚合,每次都 JOIN 太慢 | 订单表存 city,或建汇总表 | 城市变更需回刷,汇总表要定时更新 |
| 高频读取的计数 | 单独计数表或缓存 | 要接受短暂不一致,或有对账补偿 |
判断标准:这份冗余是「业务快照」(下单时的价格、收货地址)还是「可以重新算出来的缓存」(客户当前城市)。前者应该冗余,因为历史事实不能变;后者要谨慎,因为源数据一变,冗余就过期。
还有一种「合」是为了减少 JOIN 次数。把一对一的两张表合成一张,能少一次关联;但前提是它们生命周期一致、查询总是一起出现。如果一边频繁更新、一边很少读,合并反而让写入更重。
JSON 字段:能不用就不用,用就要有边界
MySQL 5.7 起支持 JSON 类型,8.0 增强了 JSON 函数和部分索引能力。它适合存「结构不固定、整块读写、不参与复杂查询」的数据,比如第三方回调的原始报文、商品详情快照、埋点扩展属性。
它不适合当「万能列」:把所有可能变化的字段都塞进 JSON,查询时用 ->> 取值过滤。JSON 内部字段默认没有索引,每次过滤都要解析整块 JSON。数据量大时,这比多建几列慢得多。
如果确实要按 JSON 里的某个字段查询,做法是建生成列再建索引:
CREATE TABLE t_json (
id INT PRIMARY KEY AUTO_INCREMENT,
snapshot JSON NOT NULL,
sku VARCHAR(30) GENERATED ALWAYS AS (
JSON_UNQUOTE(JSON_EXTRACT(snapshot, '$.sku'))
) STORED,
KEY idx_sku (sku)
) ENGINE=InnoDB;
EXPLAIN SELECT id FROM t_json WHERE sku = 'A-100';
实测 type=ref、key=idx_sku、rows=1。生成列把 JSON 里的值「物化」成普通列,索引就能正常工作。MySQL 8.0.13 起还支持函数索引,可以直接给表达式建索引;8.0.17 起支持 JSON 数组的多值索引。版本不同,能力不同,别把新特性当成所有环境都有。
WHERE JSON_UNQUOTE(JSON_EXTRACT(snapshot, '$.sku')) = 'A-100',优化器也匹配到了生成列索引。但这种自动匹配依赖表达式完全等价,换个写法就可能失效。显式生成列 + 索引更可控,也更容易被后来的人看懂。JSON 的另一个边界是更新成本。修改 JSON 里的一个键,InnoDB 可能重写整块文档;频繁更新的字段不适合放 JSON。还有校验:JSON 类型只保证语法合法,不保证业务字段齐全,重要字段仍要单独成列并加约束。
翻车现场:把筛选字段塞进 JSON,20 倍慢还查不准
某商品表把「规格、颜色、标签」全塞进一个 JSON 列,前端要按 SKU 筛选。第一版 SQL 是 WHERE snapshot LIKE '%"A-100"%',第二版改成 JSON_UNQUOTE(JSON_EXTRACT(snapshot, '$.sku')) = 'A-100'。在 1998 行、平均每块 JSON 1.72 KB 的演示表上实测:
| 写法 | type / key | rows | 最优耗时 | 中位耗时 |
|---|---|---|---|---|
snapshot LIKE '%"A-100"%' | ALL / NULL | 1998 | 24.69 ms | 44.38 ms |
JSON_EXTRACT(snapshot,'$.sku')='A-100' | ALL / NULL | 1998 | 17.23 ms | 18.42 ms |
生成列 + 索引后按 sku 查 | ref / idx_sku | 1 | 亚毫秒 | 亚毫秒 |
前两条都是 type=ALL、rows=1998:JSON 里的字段没有索引,只能逐行解析整块文档。LIKE 版看起来「也能查」,但它匹配的是整块文本,描述里出现同样的字符串也会命中,分不清是 SKU 还是别的位置;JSON_EXTRACT 版语义准确,代价仍是全表解析。两者都随行数线性变慢,和「索引定位一行」不是一个量级。
加上生成列索引后(建法见上一节),同一条查询变成 type=ref、key=idx_sku、rows=1,从「扫 1998 行解析 JSON」变成「定位一行」。代价是写入时多维护一个生成列和一棵索引,但筛选是高频操作,这笔账划算。
结论不是「JSON 不能用」,而是「要筛选的字段不能只存在 JSON 里」。用 JSON 之前先问:这个字段会不会出现在 WHERE、ORDER BY、GROUP BY?会,就单独建列,或者至少建生成列索引;不会,只是整块读取展示,再放 JSON。
主键、状态、软删除与审计字段
主键用自增 BIGINT 或有序 ID,理由在索引原理篇讲过。状态字段用短字符串或 TINYINT,并在代码里维护枚举;不要用中文长文本当状态,索引大且容易拼错。状态流转如果需要历史,单独建流水表,别在主表只保留当前值。
软删除(is_deleted)能保留数据,但会让每个查询都多一个条件,也会让唯一索引变复杂(删掉的手机号能不能再注册?)。如果业务允许,优先用「归档表 + 物理删除」或「状态字段」,而不是给所有表加 is_deleted。
软删除和唯一约束的冲突有个常见解法:把唯一索引建成 (phone, deleted_flag),未删除行 deleted_flag 统一存 0,已删除行存自己的 id 或删除时间戳。这样活着的手机号仍然只能有一个,删掉的行因为标记不同不会互相冲突,同一手机号也能重新注册。代价是索引多一列、查询要带上标记,代码里必须统一约定,不能有的地方存 0 有的地方存 NULL。
审计字段(created_at、updated_at)几乎每张表都该有。时间统一用 UTC 存储或统一用 DATETIME 存业务时区,别一半 TIMESTAMP 一半 DATETIME 混着用,排查时会对不上。
分库分表的信号
不要因为「听说单表超过 500 万行就要分」就提前分。单表能扛多少,取决于行宽、索引数量、查询模式、硬件和缓存命中率。真正该考虑分片的信号是:单表数据量和索引已经超出单机磁盘/内存合理范围;写入 QPS 接近单库上限;大表 DDL 窗口长到无法接受;备份恢复时间影响业务。
分片会带来一串新问题:跨分片 JOIN 没了、事务要分布式、唯一 ID 要全局生成、分页和聚合要应用层合并、扩容要数据迁移。它能晚点做就晚点做,先尝试归档历史数据、读写分离、冷热分离、加缓存。
SHOW SLAVE STATUS\G(8.0.22 起推荐 SHOW REPLICA STATUS),盯 Seconds_Behind_Master(新名 Seconds_Behind_Source);再查 performance_schema.replication_applier_status_by_worker,看是哪个 worker 在等、等多久。正常应该看到:写入高峰时延迟上升、大事务和 DDL 期间延迟明显拉长、写入回落后延迟逐步归零。具体字段名、输出格式和阈值以你的版本为准,标记为待确认。迁移策略:别一步到位
表结构变更最安全的方式是「双写 + 灰度 + 可回滚」。加新表时,先双写新旧两处,读仍然走旧表;用对账脚本比对差异;确认无误后切读新表;观察一段时间再停旧写。整个过程要有开关,出问题能立刻切回。
拆表时还要考虑历史数据迁移。大批量迁移分批进行,每批一个短事务,记录进度,支持断点续跑;迁移期间新数据双写;迁移完成后做全量对账。别在一个长事务里搬几百万行,锁和 undo 会把库拖垮。
现象对照
| 现象 | 真实原因 | 第一步查什么 |
|---|---|---|
| 订单表字段越来越多,没人说得清 | 没有按主题拆表,可选属性全塞进主表 | 列出一半以上为 NULL 的列,评估拆扩展表 |
| 列表查询越来越慢 | 主表行太宽,或大字段拖累 Buffer Pool | 看平均行宽和大字段占比,考虑拆大字段 |
| 同一客户信息在几十张表里不一致 | 到处冗余客户名/城市,没有同步机制 | 找出冗余列,确认哪份是权威数据 |
| JSON 字段查询慢 | 没有生成列/函数索引,每次解析整块 JSON | EXPLAIN 看 key 是否为空 |
| 改一列要动十几个功能 | 表职责不清,多个业务共享同一张宽表 | 梳理字段归属,按主题拆分 |
| 单表写入接近瓶颈 | 数据量、索引数量、热点行竞争 | 看写入 QPS、行宽、索引数,再判断是否分片 |
| 按某个业务字段筛选很慢 | 该字段只在 JSON 里,没有生成列索引 | 看 EXPLAIN 的 key 是否为空,评估补列或生成列 |
| 详情页 JOIN 了五六张表 | 表拆得太细,访问模式一致的数据被分开 | 看这些表是否总是一起读,考虑合并一对一表 |
| 迁移后数据对不上 | 双写期间部分路径漏写,或迁移脚本中断 | 跑全量对账,检查双写覆盖率和迁移进度 |
边界与代价
拆表会增加 JOIN 和代码复杂度,合表会牺牲职责清晰和写入隔离。没有「正确」的范式等级,只有和当前业务匹配的取舍。一个小项目把表拆成二十张,维护成本可能高于收益;一个大系统把所有东西塞进三张宽表,后期改不动。
冗余一定要有权威数据源和同步策略。下单快照这类「历史事实」可以永久保留;客户当前城市这类「可重算数据」最好实时查或异步刷新,别让过期副本误导业务。
拆表还要算运维成本:每多一张表,备份、迁移、监控、权限、对账都要多一份。表拆得越细,跨表一致性的代码就越多,出问题时定位链路也越长。评估方案时,把「查询变快多少」和「维护复杂多少」放在一起算,而不是只算前者;后者往往半年后才显形。
JSON 不是 schema-less 的免死金牌。它降低的是「加字段」的成本,抬高的是「查询、约束、迁移」的成本。用之前先问:这个字段需要被筛选、排序、统计吗?需要的话,老老实实建列。
小结
表设计的核心不是背范式,而是分清主题、访问模式和一致性要求。一对多、多对多、可选属性、大字段该拆;历史快照该冗余;可重算的缓存慎冗余。JSON 适合整块读写、不参与复杂查询的数据,要查询就建生成列索引。分库分表是最后手段,先归档、缓存、读写分离。
表结构不是给当前需求用的,是给未来半年用的。多问一句「这个字段半年后会在哪里被筛选、被排序」,比事后加索引、拆表、迁移都便宜。
当天可以自己验证的练习:
- 把订单表里常年为 NULL 的列列出来,试着拆成一张扩展表,对比查询行宽。
- 建一张 JSON 表,用生成列给
$.sku建索引,验证EXPLAIN走ref。 - 把商品快照塞进订单主表,对比列表查询的扫描页数和耗时。
- 写一个双写对账 SQL,比较新旧两张表的行数和关键字段差异。
- 估算一张表的行宽 × 行数 × 索引数,判断是否接近单机容量。
这个系列到这里就收尾了。回头看,十二篇其实围绕三件事:数据怎么放(表、主键、约束、范式)、数据怎么取(查询、JOIN、聚合、索引、执行计划)、数据怎么保证正确(事务、锁、慢查询排查)。语法只是入口,真正决定系统稳不稳的,是这些取舍有没有想清楚。下一篇可以回到关系数据库是什么,用现在的理解重读一遍,会发现那些最基础的概念,恰好是所有问题的落点。