MySQL JSON 字段的合理使用边界

CR 时跟同事吵了一架

上周做订单模块的代码评审，同事设计的新表里有个 ext_data 字段，类型 json，里面塞了发票信息、优惠券信息、配送偏好、渠道来源，一共十几个子字段。他的理由是"这些字段每个订单不一定都有，建十几个列太浪费"。

我在评论里提了反对意见，他回了一句"MySQL 8 原生支持 JSON 类型，还有索引，官方推荐的"。这句话对也不对，我花了点时间整理了一下自己的看法，顺便做了几组测试。

先说我同意的部分

JSON 类型确实不是洪水猛兽，我自己也在用。下面这几种场景我觉得用它完全合理：

• 字段稀疏且不可预期：比如不同渠道的订单带过来的扩展参数完全不同，你不可能穷举建列；
• 只存不查的快照：下单时的商品快照、当时的风控结果明细，这些写进去基本只用于事后查看和展示；
• 结构还在变：产品每周都要加字段，用 JSON 可以避免每周一次 DDL。这个在项目早期很有用。

我们订单表里的 snapshot 字段就是 JSON，存下单瞬间的商品信息（名称、图片、规格、价格），用了两年没出问题，因为从来没人拿它做查询条件。

问题出在"要拿它查询"

同事那个设计里，ext_data 里放了 invoice_type（发票类型）和 channel（渠道来源）。财务那儿每周要按发票类型导出报表，运营要按渠道分析。这两个字段注定要进 WHERE 条件。

我做了个测试，500 万行数据的订单表，三种方案对比：

-- 方案 A：JSON 字段，无索引
SELECT * FROM orders
WHERE ext_data->>'$.channel' = 'douyin';

-- 方案 B：JSON 字段 + 生成列索引
ALTER TABLE orders
  ADD COLUMN channel VARCHAR(32)
      GENERATED ALWAYS AS (ext_data->>'$.channel') STORED,
  ADD INDEX idx_channel (channel);
SELECT * FROM orders WHERE channel = 'douyin';

-- 方案 C：普通列
ALTER TABLE orders ADD COLUMN channel VARCHAR(32), ADD INDEX idx_channel(channel);
SELECT * FROM orders WHERE channel = 'douyin';

结论很明确：JSON 字段不做索引就是全表扫描，做了生成列索引性能跟普通列基本一样，但表更大、DDL 更麻烦。

如果已经确定某个字段要用于查询，那它一开始就该是个普通列。生成列索引是在"已经用了 JSON 且现在需要查询"这个既成事实下的补救手段，不是设计目标。

多值索引：JSON 数组查询的唯一解

有一种场景 JSON 确实有独特优势：一对多的标签。比如商品有多个标签，不想建中间表的时候，可以存成数组：

{"tags": ["包邮", "7天无理由", "现货"]}

MySQL 8.0.17 开始支持多值索引，可以建在函数索引上：

ALTER TABLE products
  ADD INDEX idx_tags ((CAST(tags_json->'$.tags' AS CHAR(32) ARRAY)));

SELECT * FROM products
WHERE '包邮' MEMBER OF (tags_json->'$.tags');

实测 300 万商品表，查询 tags 包含"包邮"的记录，无索引 6.2s（全表），加了多值索引后 0.031s。这个场景多值索引是唯一能在不建中间表的前提下解决的办法。

但要注意限制：多值索引一个字段只能建一个，而且数据类型必须是数组。如果你既要按标签查又要按标签数量排序，那还是老实建中间表。

几个我踩过的坑

更新是全量重写

这个是我觉得最需要提前知道的。JSON 字段的修改在 InnoDB 层面是"把整个 JSON 值读出来、改、再整体写回去"，不是原地修改。所以 JSON 越大，改一个字段的代价越高。

我们有个表存了 2.3KB 的 JSON，做 JSON_SET(ext, '$.status', 1) 更新，单行更新 binlog 记录 2.4KB。同样的更新如果拆成普通列，binlog 只有 60 字节。高频更新的字段放 JSON 里，会让 binlog 暴涨、主从延迟变大。我们线上因此出现过 40 秒的主从延迟。

类型陷阱

JSON 里的 1 和 "1" 是不同类型，比较时行为不同：

-- 存入 {"level": 1}（数字）
SELECT * FROM t WHERE ext->>'$.level' = '1';    -- 能查到
SELECT * FROM t WHERE ext->>'$.level' = 1;       -- 也能查到（隐式转换）

-- 存入 {"level": "1"}（字符串）
SELECT * FROM t WHERE ext->>'$.level' = 1;       -- 能查到，但索引失效

问题在于写入端如果类型不统一（Java 里一会儿 put("level", 1) 一会儿 put("level", "1")），查询行为就不一致。而且生成列索引的类型是固定的，存了字符串进去，WHERE channel = 5 这种比较会导致索引失效。写入 JSON 的类型必须在代码层面统一。

没有约束

普通列可以有 NOT NULL、CHECK、外键、默认值。JSON 里的字段什么都没有，空值、错值、缺字段全靠应用层保证。我们出过一次事故：某个版本的客户端漏传了 JSON 里的 amount 字段，写入时没报错，结算时读到 null，导致一批订单金额算成 0。

如果要用 JSON，建议加 CHECK 约束兜底：

ALTER TABLE orders
  ADD CONSTRAINT chk_ext_amount
  CHECK (JSON_EXTRACT(ext_data, '$.amount') IS NOT NULL);

MySQL 8.0.16 开始 CHECK 约束是真的生效的（之前只是语法糖），这个能用。

大小限制和文档混乱

JSON 列最大不能超过 max_allowed_packet（默认 64MB），实际生产中超过 1MB 的 JSON 就该警惕了。我们有个日志表存了 800KB 的 JSON，查询时网络传输和反序列化开销已经很明显，后来拆成了子表。

我给同事的建议

最后我们是这么定的：

• channel、invoice_type 这两个要查要统计的，改成普通列；
• 配送偏好这种只在订单详情页展示的，留在 ext_data；
• 渠道带过来的不可预期参数，留在 ext_data，但加个文档说明大概有哪些；
• 代码里定义常量类枚举 JSON 里的 key，别到处写字符串字面量。

最后一条是我的习惯：JSON 字段没有 schema，唯一的"文档"就是代码里的常量。我们建了个 OrderExtKeys 类集中管理，改 key 名的时候 IDE 能直接搜到。

写在后面

现在回头看，《MySQL JSON 字段的合理使用边界》本身不算多难，难的是线上真出问题那十分钟里的判断。经验都是这么来的。

参考

• 官方文档：database-sql
• Baeldung 上的相关实战
• StackOverflow 上的相关讨论