7AA7 博客
登录 / 注册

JSON 字段与半结构化数据的存储取舍

Cache 孙 UID:133
2026-09-19 20:50
22 0 0

现代关系库都提供了原生 JSON 类型：MySQL 的 JSON、PostgreSQL 的 jsonb。它让”先上线再说”变得非常容易——不用改表结构就把业务属性塞进去了。问题是这类字段用久了，往往会变成查询瓶颈的来源，而改造成本随数据量增长。

什么时候 JSON 字段是合理的

属性稀疏且差异大。不同品类的商品有不同的规格：手机有屏幕尺寸和内存，衣服有尺码和面料，图书有 ISBN 和页数。如果用 EAV（实体-属性-值）表存储，一次查询要 join 三张表；如果每种品类建一张表，维护成本高。JSON 字段在这种场景下是自然的表达。

结构频繁变动且不由本系统决定。接收第三方回调时，body 里有什么字段不受自己控制。用 JSON 保留原始报文，后续再按需解析，本身就是合理的防御。

整体读写、不拆开用。配置项、表单快照、模板定义这类数据，存储和读取都是整个对象，不需要对内部字段做过滤。存成 JSON 就是把对象序列化之后的结果直接放进去。

CREATE TABLE product_form_config (
  id          BIGINT PRIMARY KEY,
  tenant_id   BIGINT NOT NULL,
  form_schema JSON   NOT NULL,     -- 整体读写，从不按内部字段查询
  updated_at  DATETIME NOT NULL
);

什么时候是错的

判断标准很直接：是否需要按 JSON 内部的某个字段做高频过滤、排序、聚合、或与其他表关联。答案是”是”的话，JSON 就应该换成正式的列。

-- 反模式：每次列表查询都要扫描整个 JSON 字段
SELECT * FROM orders
WHERE JSON_EXTRACT(payload, '$.region') = 'cn-north'
ORDER BY JSON_EXTRACT(payload, '$.score') DESC
LIMIT 20;

问题包括：无法用普通索引、每次过滤都要解析整个文档、类型语义不确定（数字 1 和字符串 “1” 是不同的 JSON 值）、无法做外键约束、无法建统计信息。随着表变大，这类查询会稳定地变差。

为什么普通索引用不上

B+Tree 索引的本质是对”列值”建立有序映射，磁盘上的页按 key 的值排序。

payload 这一列的值是整个 JSON 文档的二进制（MySQL 实际存储的是一种便于按路径访问的二进制格式，但仍是一个整体 doc）。

索引 idx_payload:
  '{"region":"cn-north","score":88,...300字节...}'  ->  rowid 1
  '{"region":"cn-south","score":12,...320字节...}'  ->  rowid 2

这个有序序列是按完整文档文本排序的，而不是按 $.region 排的。想要用 $.region 做定位，必须先对每个文档执行路径求值，得到标量之后才能比较——而索引里没有存这些标量，存储引擎也没有办法把”求路径结果”这个计算下推到索引的二分查找过程中。

所以上面的查询只能：

  1. 全表（或按其他条件过滤后剩下的所有行）扫描。
  2. 对每行的 payload 执行 JSON_EXTRACT。
  3. 解析二进制/文本，做字符串比对。
  4. 结果集再排序。

时间复杂度是 O(N)，且常数远大于普通列比较——解析 JSON 本身是 CPU 密集操作。

生成列 + 索引

MySQL 支持从 JSON 路径派生出虚拟的或存储的列，再对它建索引：

ALTER TABLE orders
  ADD COLUMN region VARCHAR(32)
      GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(payload, '$.region'))) STORED,
  ADD INDEX idx_region_created (region, created_at);

或者更新版本里更简洁的写法：

ALTER TABLE orders
  ADD COLUMN score INT
      GENERATED ALWAYS AS (payload->>'$.score') STORED,
  ADD INDEX idx_score (score);

->> 等价于 JSON_UNQUOTE(JSON_EXTRACT(...))，去掉引号才是真正的值。这一步很重要：如果直接用 ->，取出来的是带引号的 JSON 字符串，与整数比较不会走索引。

生成之后查询就可以用普通索引了：

SELECT * FROM orders WHERE region = 'cn-north' ORDER BY created_at DESC LIMIT 20;

EXPLAIN SELECT * FROM orders WHERE region = 'cn-north'G
-- type: ref
-- key: idx_region_created
-- ref: const

限制需要提前知道：

  • 表达式必须确定性。NOW()、RAND()、UUID() 这类结果不确定的函数不能用于生成列。包含不确定函数的子查询也不行。
  • 不能跨行引用，不能引用自增列的值（自增发生在行插入时，可能晚于生成列计算）。
  • VIRTUAL 与 STORED 的差别：VIRTUAL 不占存储，读取时计算，索引必须显式建（MySQL 会对 virtual 列的实现是在索引里物化该值）；STORED 占用实际存储，写入时计算并落盘。对这个列建了索引的情况下，两者差异不大，因为索引里都会存一份值。
  • 类型由表达式决定，通常要显式 CAST 才能得到想要的类型：
ALTER TABLE orders
  ADD COLUMN score DECIMAL(10,2)
      GENERATED ALWAYS AS (CAST(payload->>'$.score' AS DECIMAL(10,2))) STORED;

不 CAST 的话，优化器看到的是个长文本或者 double，用于范围判断时可能得到非预期结果。

  • 修改源 Data 列的写入路径会变慢：每次写入含有该 JSON 列的INSERT/UPDATE，都要重新计算所有 STORED 生成列并更新它们对应的索引。这在高频写入的表上是实打实的成本。

PostgreSQL 的做法在思路上类似，但用的是表达式索引，不需要新增列：

CREATE INDEX idx_orders_region ON orders ((payload->>'region'));
CREATE INDEX idx_orders_score  ON orders (((payload->>'score')::numeric));

查询时必须写与索引完全一致的表达式才会命中：

-- 命中
SELECT * FROM orders WHERE payload->>'region' = 'cn-north';
-- 不命中：表达式形式不一致
SELECT * FROM orders WHERE payload->'region' ->>0 = 'cn-north';

函数索引的适用场景

MySQL 8.0.13 起支持真正的函数索引（functional index），可以直接对表达式建索引而不用先加生成列：

CREATE INDEX idx_region ON orders ((CAST(payload->>'$.region' AS CHAR(32))));

适用场景：

  • 表达式只在一两个查询里用到，不想为它永久加一列。
  • 表达式涉及多个 JSON 路径的组合（CONCAT(payload->>'$.a', '-', payload->>'$.b')）。
  • 迁移期临时方案：业务还在用旧查询方式，先用函数索引止血，之后再配合改造。

限制与生成列类似：表达式必须确定性；不能直接对函数索引做部分索引（PostgreSQL 支持 PARTIAL INDEX 加 WHERE 条件，这是另一个话题）。

-- PostgreSQL 的部分索引，只索引活跃订单，索引体积很小
CREATE INDEX idx_active_orders ON orders ((payload->>'region'))
  WHERE status = 'ACTIVE';

更新的代价

MySQL 的 JSON 列在内部存的是一棵可以在原地进行部分修改的树结构。按路径更新时，理论上可以只改对应的节点：

-- 只改一个标量字段，不重建整个文档
UPDATE orders SET payload = JSON_SET(payload, '$.status', 'PAID') WHERE id = 1;

但这只是”存储层可能可以不重写”，实际上有几个必然的代价：

  1. 整行仍要写。InnoDB 的最小写单位是页，更新任何列都会导致所在的整行被重写，产生一条 undo 和 redo 记录。更新一个 JSON 里的一个字段，与更新一个普通列，在 redo/undo 层面成本接近。
  2. 文档变大后会触发页分裂。 JSON_SET 增加一个原本不存在的 key，文档体积变大，可能放不进原来的页面位置，引发页迁移。
  3. 所有关联索引都要更新。如果该 JSON 列上有基于它的生成列索引或函数索引，这个更新要把那些索引项也改一遍。
  4. binlog 里记录的是整行。ROW 格式下不管改的是 JSON 里的一个字段还是整行，binlog 记录的都是完整的行镜像，导致从库回放成本、网络传输成本都按整行计算。这是 JSON 字段在高频更新场景下的一个隐性成本。

JSON_REMOVE 或 JSON_SET 到较小的值通常不会立即回收空间，需要在业务的低峰做一次重建，或者依赖后续的行更新逐步复用。

隐式转换陷阱

CREATE TABLE t (payload JSON);
INSERT INTO t VALUES ('{"num": 1, "str": "1", "flag": true}');

JSON 有自己的类型系统：数字、字符串、布尔、null、对象、数组。JSON_EXTRACT 返回的是 JSON 类型；JSON_UNQUOTE 或 ->> 返回的是 MySQL 的字符串。

SELECT payload->'$.num'  = 1       FROM t;   -- 期望为 1，实际可能不按常规预期比较
SELECT payload->>'$.num' = 1       FROM t;   -- 字符串 '1' 与数字 1 比较，发生类型转换
SELECT payload->'$.flag' IS TRUE   FROM t;

陷阱通常出现在：

  • ->> 取出的永远是字符串，WHERE payload->>'$.amount' > 100 会先转成数值再比较，类型转换让列上的生成列索引失效（如果生成列是 VARCHAR 类型，比较时会转成 number，索引不能用）。
  • 大小写和引号：'{"a":"X"}' 里 ->>'$.a' 得到 X，而 JSON_COMPACT 之类函数得到的可能是 "X" 带引号。
  • 数字精度：JSON 里的大整数（超过 DOUBLE 精度）取出来可能失真，需要 CAST(... AS UNSIGNED) 明确处理。
  • null 与 SQL NULL 不同：JSON 的 null 是有效值，JSON_EXTRACT 返回的结果是 JSON null，而不是 SQL 的 NULL；用 IS NULL 判断会失效，要用 JSON_TYPE(...) = 'NULL'。
SELECT JSON_TYPE(payload->'$.nothing') AS t;   -- NULL（SQL NULL，路径不存在）
SELECT JSON_TYPE(payload->'$.nullval') AS t;   -- 'NULL'（JSON null）

演进路径怎么走

从 JSON 起步再拆表，适合业务早期形态不确定、无法预知字段清单的场景。演进步骤：

  1. JSON 字段承载全部扩展属性。
  2. 识别出开始被高频用于过滤或排序的 1-2 个路径。
  3. 对这些路径加生成列索引，保持 JSON 结构不变，查询改成用生成列。
  4. 数据量再增长后，把这些字段提升为正式的列，写一个后台任务回填历史数据，应用侧双读一段时间。
  5. 回填完成且校验通过后，移除 JSON 里的对应 key（或者保留作为历史存档，不再写入）。

一开始就建正式列，适合schema 已经清晰、字段会被用于查询过滤的场景。成本是需要一次迁移，但避免了后期的重构。

两种路径没有绝对优劣，取舍点在于字段要不要参与查询。写多少次不重要，重要的是它会不会出现在 WHERE、ORDER BY、GROUP BY、JOIN 里。设计阶段能回答这个问题，方案就明确；回答不了，就先放 JSON，但要留好加索引与后续拆列的接口。

数据库 # JSON# 生成列# 索引
🔁 0
746浏览量
22赞
0评论
0参与用户
15 分钟阅读时间

评论（0）

还没有评论，来抢沙发。