开发者日记

外观

JSONB还是拆表：订单扩展字段建模成本实测 ​

问题背景：订单扩展字段为什么总是失控 ​

订单系统早期通常只有订单号、用户、金额、状态等固定字段。随着业务增长，营销来源、风控标签、发票信息、渠道快照、设备指纹等字段不断加入。此时团队常见有两种做法：一是把不确定字段放进 PostgreSQL 的 JSONB；二是按业务域拆成订单扩展表。

问题不在于 JSONB 能不能存，而在于“现在省事”会不会变成“以后很贵”。订单扩展字段往往具有三个特点：写入频率高、查询条件逐步增加、字段含义会变化。如果只凭“半结构化灵活”或“规范设计优雅”做决策，很容易在数据量达到百万、千万级后付出索引膨胀、锁表迁移、统计不准和查询退化的成本。

本文用一个可运行的 PostgreSQL 示例，对 JSONB 建模与拆表建模进行成本对比，并给出适合订单场景的选型判断。

两种订单表设计方案对比 ​

先建立基础订单表：

sql
-- 基础订单表：两种方案共享
CREATE TABLE orders (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_no TEXT NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- 便于按用户查询订单
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

方案一：JSONB 承载订单扩展字段 ​

sql
-- JSONB 方案：一个列容纳来源、渠道、发票、风控等非固定属性
CREATE TABLE orders_jsonb (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    ext JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- 高频查询字段使用表达式索引，而不是依赖整个 JSONB 的 GIN 索引
CREATE INDEX idx_orders_jsonb_source
ON orders_jsonb ((ext ->> 'source'));

CREATE INDEX idx_orders_jsonb_risk_level
ON orders_jsonb ((ext ->> 'risk_level'));

-- 适合任意键查询、包含查询，但写入和体积成本更高
CREATE INDEX idx_orders_jsonb_ext_gin
ON orders_jsonb USING gin (ext);

JSONB 的优势是上线快，新增键不需要 DDL，适合字段生命周期短、结构差异大的场景。例如不同渠道的订单扩展结构可能完全不同：直播渠道有直播间 ID，企业采购有合同号，跨境订单有报关信息。

但 JSONB 并不等于无模式。只要业务开始使用 ext ->> 'source' 做筛选、分组或排序，这个键事实上就已经成为模式的一部分，需要索引、类型约定和数据质量校验。

方案二：按业务域拆出扩展表 ​

sql
-- 拆表方案：把稳定、高频、需要约束的字段结构化
CREATE TABLE orders_marketing_ext (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    source TEXT NOT NULL,
    campaign_id BIGINT,
    coupon_code TEXT
);

CREATE TABLE orders_risk_ext (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    risk_level TEXT NOT NULL CHECK (risk_level IN ('low','medium','high')),
    device_id TEXT,
    reviewed BOOLEAN NOT NULL DEFAULT false
);

CREATE INDEX idx_order_marketing_source
ON orders_marketing_ext(source);

CREATE INDEX idx_order_risk_level_reviewed
ON orders_risk_ext(risk_level, reviewed)
WHERE reviewed = false;

拆表的优势是类型明确、约束可靠、统计信息更细、索引更可控。固定字段可以使用 CHECK、外键、非空、枚举或域类型，优化器也能基于普通列的统计信息选择更合理的执行计划。

它的成本是前期设计更重，新增字段需要迁移流程；如果拆得过细，还会引入大量 JOIN，把简单查询变成复杂查询。

查询与索引成本：灵活背后的隐性代价 ​

下面插入一批可复现实验数据：

sql
WITH generated_orders AS (
    INSERT INTO orders(order_no, user_id, amount, status)
    SELECT
        'NO' || lpad(g::text, 10, '0'),
        (random() * 100000)::bigint + 1,
        round((random() * 1000)::numeric, 2),
        CASE WHEN g % 10 = 0 THEN 'paid' ELSE 'created' END
    FROM generate_series(1, 100000) AS g
    RETURNING id
)
INSERT INTO orders_jsonb(order_id, ext)
SELECT
    id,
    jsonb_build_object(
        'source', CASE WHEN id % 3 = 0 THEN 'app' WHEN id % 3 = 1 THEN 'web' ELSE 'mini_program' END,
        'risk_level', CASE WHEN id % 20 = 0 THEN 'high' WHEN id % 5 = 0 THEN 'medium' ELSE 'low' END,
        'coupon_code', CASE WHEN id % 7 = 0 THEN 'NEWUSER' ELSE NULL END,
        'device_id', 'dev-' || ((random() * 10000)::int)
    )
FROM generated_orders;

JSONB 查询最常见的坑是把数值当文本比较：

sql
-- 能走表达式索引，但结果是文本比较，不适合金额、数量等数值字段
SELECT o.id, o.order_no
FROM orders o
JOIN orders_jsonb j ON j.order_id = o.id
WHERE j.ext ->> 'risk_level' = 'high';

-- 如果需要数值范围查询，应建立带类型转换的表达式索引
CREATE INDEX idx_orders_jsonb_amount_cents
ON orders_jsonb (((ext ->> 'amount_cents')::bigint));

SELECT order_id
FROM orders_jsonb
WHERE (ext ->> 'amount_cents')::bigint BETWEEN 10000 AND 20000;

使用 EXPLAIN 时要重点观察索引是否命中：

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id
FROM orders o
JOIN orders_jsonb j ON j.order_id = o.id
WHERE j.ext ->> 'source' = 'app';

如果看到 Seq Scan 或 GIN 索引选择性很差，通常说明查询键已经高频化，应考虑转为普通列。普通 B-Tree 索引对等值、范围、排序更稳定，而 GIN 索引更适合包含关系、键存在性和复杂 JSON 条件。

写入、更新与表膨胀成本 ​

JSONB 更新不能只修改内部某个键。即使执行的是：

sql
UPDATE orders_jsonb
SET ext = ext || jsonb_build_object('reviewed', true)
WHERE order_id = 1001;

PostgreSQL 仍然会写入新的整行版本，只是 JSONB 的二进制结构比普通文本 JSON 更适合操作。若扩展字段频繁更新，例如风控状态每秒变化、配送状态多次回写，JSONB 大对象会带来更高的 WAL、TOAST 和 vacuum 压力。

拆表方案可以把高频更新字段隔离到更小的表中：

sql
-- 高频状态独立成表，避免更新营销、发票等低频大字段
UPDATE orders_risk_ext
SET reviewed = true
WHERE order_id = 1001;

这张表行宽更小，HOT 更新机会更多，索引维护成本也更低。反过来，如果扩展字段只是订单创建时写入、之后很少读取，JSONB 的紧凑存储和低 DDL 成本反而更合适。

选型决策框架：按查询稳定性而不是字段数量判断 ​

新增订单扩展字段是否高频筛选、排序、统计？是否拆表或提升为列类型、约束、统计更稳使用 JSONB低频、多变、快照类数据

一个实用原则是：固定核心列保持结构化，不稳定扩展放入 JSONB，高频 JSONB 键定期“提升”为列。不要一开始就把所有属性拆成十几张表，也不要让所有查询长期依赖动态键。

可以按以下标准判断：

  • 字段是否出现在 WHERE、ORDER BY、GROUP BY 中；
  • 是否需要唯一约束、外键、非空或枚举校验；
  • 是否频繁更新，更新范围是单个属性还是整段快照；
  • 是否需要被 BI、报表或数据仓库稳定消费；
  • 不同业务线的字段结构是否长期不同。

从 JSONB 平滑迁移到扩展表 ​

当某个键已经被高频查询，可以使用双写、回填、校验、切换四步迁移，而不是直接锁表改结构。

sql
-- 1. 新建结构化扩展表
CREATE TABLE orders_source_ext (
    order_id BIGINT PRIMARY KEY REFERENCES orders(id),
    source TEXT NOT NULL
);

-- 2. 回填历史数据，并过滤异常值
INSERT INTO orders_source_ext(order_id, source)
SELECT order_id, ext ->> 'source'
FROM orders_jsonb
WHERE jsonb_typeof(ext -> 'source') = 'string'
ON CONFLICT (order_id) DO NOTHING;

-- 3. 建立索引后核对数量
CREATE INDEX idx_orders_source_ext_source
ON orders_source_ext(source);

SELECT count(*) AS jsonb_count
FROM orders_jsonb
WHERE jsonb_typeof(ext -> 'source') = 'string';

SELECT count(*) AS table_count
FROM orders_source_ext;

切换期间应用层可以同时写旧 JSONB 和新表，读流量逐步切到新表。核对一段时间后再停止旧字段写入。不要在业务低峰直接执行长时间 ALTER TABLE 后立刻改代码，因为回填、索引构建和统计信息收集都需要时间。

避坑总结：三个真实高频问题 ​

第一，不要滥用整个 JSONB 的 GIN 索引。常见现象是创建了 USING gin(ext)，但查询写成 ext ->> 'source' = 'app'，优化器仍可能选择顺序扫描。修正方法是为稳定查询创建表达式索引，例如 ((ext ->> 'source'))，只有键名和查询形态都不确定时再使用 GIN。

第二，不要忽略 JSONB 的类型陷阱。"100"、100、100.0 在 JSON 中语义不同，文本比较还会出现 '9' > '10'。修正方法是在写入层统一类型，数值查询使用 (ext ->> 'amount_cents')::bigint，并建立匹配的表达式索引；关键字段最好拆成普通列。

第三，不要把频繁更新的状态塞进大 JSONB。现象是表和 TOAST 体积快速增长、vacuum 压力升高、更新延迟波动。修正方法是把风控审核、履约状态等高频变化字段拆到独立小表，把创建后不变的渠道快照、原始报文留在 JSONB。

最终建议很明确：订单主表保留交易核心字段；短期实验、渠道差异、原始快照使用 JSONB；一旦字段承担稳定查询、约束或报表职责，就应通过扩展表结构化。这样既能保留迭代速度，也不会把灵活存储演变成长期性能债务。

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。

本文来自开发者日记（devdiary.cn）· 记录开发者实战笔记与免费在线工具箱

免责声明

本工具与文章内容仅用于学习测试场景，优化建议仅供参考，请勿直接用于生产环境，由此产生的风险自行承担。