次數據庫 JSON 字段的深度使用實踐

本文結合生產環(huán)境真實場景，從「場景選型→語法實戰(zhàn)→性能優(yōu)化→避坑指南」全鏈路拆解數據庫 JSON 字段的深度使用，內容可直接落地到 MySQL/PostgreSQL（兩款主流數據庫）。

一、為什么要深度使用 JSON 字段？

先明確核心場景 —— 不是所有場景都適合用 JSON，這是「抽絲剝繭」的第一步：

適用場景（選對才是關鍵）

非結構化 / 半結構化數據：如用戶畫像（多維度、易擴展）、配置項（字段不固定）、日志明細

快速迭代的業(yè)務：避免頻繁 DDL（改表結構），比如運營活動的自定義參數

一對多輕量關聯：如weibo.com/ttarticle/p/show?id=2309405281549024624840訂單的多規(guī)格屬性（顏色、尺寸、材質），無需單獨建關聯表

不適用場景（避坑前提）

需頻繁索引 / 排序 / 聚合的核心字段（如訂單金額、用戶 ID）

數據量超大（千萬級以上）且需高頻查詢的場景

事務強一致性要求的核心業(yè)務數據

二、實戰(zhàn)：MySQL vs PostgreSQL JSON 字段核心用法

1. 基礎準備（建表）

MySQL（5.7 + 支持 JSON，8.0 + 增強）

sql

-- 訂單擴展表：存儲訂單的自定義規(guī)格、用戶備注等

CREATE TABLE `order_ext` (

? `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主鍵',

? `order_id` BIGINT UNSIGNED NOT NULL COMMENT '訂單ID',

? `ext_info` JSON NOT NULL COMMENT 'JSON擴展字段',

? `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

? PRIMARY KEY (`id`),

? UNIQUE KEY `uk_order_id` (`order_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='訂單擴展信息表';

PostgreSQL（9.4 + 支持 JSON，9.5 + 支持 JSONB）

sql

-- 推薦用JSONB（二進制存儲，支持索引，性能更好）

CREATE TABLE order_ext (

? id BIGSERIAL PRIMARY KEY,

? order_id BIGINT NOT NULL UNIQUE,

? ext_info JSONB NOT NULL,

? create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

COMMENT ON TABLE order_ext IS '訂單擴展信息表';

COMMENT ON COLUMN order_ext.ext_info IS 'JSON擴展字段';

2. 核心操作（增刪改查）

（1）插入 JSON 數據

sql

-- MySQL

INSERT INTO order_ext (order_id, ext_info)

VALUES (1001, '{

? "goods_spec": {"color": "紅色", "size": "XL", "version": "尊享版"},

? "user_note": "工作日送貨",

? "payment_ext": {"coupon_id": 58, "discount": 20.5}

}');

-- PostgreSQL（JSONB需注意語法，單引號轉義）

INSERT INTO order_ext (order_id, ext_info)

VALUES (1001, '{

? "goods_spec": {"color": "紅色", "size": "XL", "version": "尊享版"},

? "user_note": "工作日送貨",

? "payment_ext": {"coupon_id": 58, "discount": 20.5}

}'::JSONB);

（2）查詢：精準提取 JSON 字段（核心深度用法）

表格

需求MySQL 語法PostgreSQL 語法

提取一級字段SELECT ext_info->'user_note' AS user_note FROM order_ext WHERE order_id=1001;SELECT ext_info->'user_note' AS user_note FROM order_ext WHERE order_id=1001;

提取二級字段SELECT ext_info->'goods_spec'->'color' AS color FROM order_ext WHERE order_id=1001;SELECT ext_info->'goods_spec'->'color' AS color FROM order_ext WHERE order_id=1001;

提取值（去引號）SELECT ext_info->>'user_note' AS user_note FROM order_ext WHERE order_id=1001;SELECT ext_info->>'user_note' AS user_note FROM order_ext WHERE order_id=1001;

按 JSON 字段篩選SELECT * FROM order_ext WHERE ext_info->'payment_ext'->>'coupon_id' = '58';SELECT * FROM order_ext WHERE ext_info->'payment_ext'->>'coupon_id' = '58';

復雜篩選（數組）-- 假設ext_info有tags數組：["促銷", "新品"]

SELECT * FROM order_ext WHERE JSON_CONTAINS(ext_info->'tags', '"促銷"');

SELECT * FROM order_ext WHERE ext_info @> '{"tags": ["促銷"]}'::JSONB;

（3）修改：更新 JSON 指定字段

sql

-- MySQL：更新一級字段

UPDATE order_ext

SET ext_info = JSON_SET(ext_info, '$.user_note', '周末送貨')

WHERE order_id=1001;

-- MySQL：更新二級字段

UPDATE order_ext

SET ext_info = JSON_SET(ext_info, '$.goods_spec.size', 'XXL')

WHERE order_id=1001;

-- PostgreSQL：更新JSONB字段

UPDATE order_ext

SET ext_info = ext_info || '{"user_note": "周末送貨"}'::JSONB

WHERE order_id=1001;

-- PostgreSQL：更新二級字段

UPDATE order_ext

SET ext_info = jsonb_set(ext_info, '{goods_spec,size}', '"XXL"')

WHERE order_id=1001;

（4）刪除：移除 JSON 指定字段

sql

-- MySQL

UPDATE order_ext

SET ext_info = JSON_REMOVE(ext_info, '$.user_note')

WHERE order_id=1001;

-- PostgreSQL

UPDATE order_ext

SET ext_info = ext_info - 'user_note'

WHERE order_id=1001;

-- PostgreSQL刪除二級字段

UPDATE order_ext

SET ext_info = ext_info #- '{goods_spec,version}'

WHERE order_id=1001;

3. 性能優(yōu)化：JSON 字段索引（深度使用的核心）

沒有索引的 JSON 字段查詢就是「全表掃描」，這是深度使用必須解決的問題：

MySQL（8.0.17 + 支持 JSON 多值索引）

sql

-- 1. 對JSON一級字段創(chuàng)建虛擬列+索引（兼容低版本）

ALTER TABLE order_ext

ADD COLUMN coupon_id INT GENERATED ALWAYS AS (ext_info->'payment_ext'->>'coupon_id') STORED;

CREATE INDEX idx_order_ext_coupon_id ON order_ext(coupon_id);

-- 2. 多值索引（適用于JSON數組）

CREATE INDEX idx_order_ext_tags ON order_ext((CAST(ext_info->'tags' AS JSON)))

WHERE ext_info->'tags' IS NOT NULL;

PostgreSQL（JSONB 專屬 GIN 索引）

sql

-- 1. 全局GIN索引（支持所有JSON字段的查詢）

CREATE INDEX idx_order_ext_ext_info ON order_ext USING GIN (ext_info);

-- 2. 局部索引（指定字段，性能更優(yōu)）

CREATE INDEX idx_order_ext_coupon_id ON order_ext ((ext_info->'payment_ext'->>'coupon_id'));

-- 3. 路徑索引（針對固定JSON路徑）

CREATE INDEX idx_order_ext_goods_color ON order_ext USING GIN (ext_info->'goods_spec');

三、深度使用避坑指南（生產踩坑總結）

1. 數據類型坑

MySQL：JSON 字段提取的值默認是字符串，需手動轉換類型（如CAST(ext_info->>'discount' AS DECIMAL(10,2))）

PostgreSQL：JSONB 存儲會自動去重鍵，若 JSON 有重復鍵（如{"name":"a", "name":"b"}），僅保留最后一個

2. 性能坑

避免對大 JSON 字段做全字段查詢 / 更新，只操作需要的子字段

千萬級數據不要用 JSON 做聚合查詢（如 SUM/COUNT），提前抽成物理列

MySQL JSON 字段不支持主鍵 / 外鍵，核心關聯字段不要放 JSON 里

3. 維護坑

定期清理 JSON 中的無效字段（如廢棄的配置項），減少存儲體積

對 JSON 結構做版本控制，避免前端 / 后端解析不一致

不要在 JSON 中存儲超大文本（如超過 10KB），建議拆分到單獨表

4. 事務 / 一致性坑

JSON 字段的更新是「全字段覆蓋」，高并發(fā)下需加行鎖（SELECT ... FOR UPDATE）

避免在 JSON 中存儲需強一致性的數據（如訂單狀態(tài)），物理列更可靠

四、最佳實踐總結（落地建議）

分層存儲：核心字段（訂單 ID、金額）用物理列，擴展字段（規(guī)格、備注）用 JSON

索引策略：低頻查詢用虛擬列 / 局部索引，高頻查詢用 GIN / 多值索引

解析規(guī)范：后端統一封裝 JSON 解析工具類，避免硬編碼 JSON 路徑

監(jiān)控告警：監(jiān)控 JSON 字段的查詢耗時、存儲大小，超過閾值及時優(yōu)化

測試驗證：上線前做 JSON 結構校驗（如 JSON_SCHEMA），避免臟數據

五、實戰(zhàn)案例：JSON 字段優(yōu)化前后對比

場景

訂單擴展表 1000 萬數據，按coupon_id查詢 JSON 字段，優(yōu)化前耗時 5.2 秒，優(yōu)化后耗時 0.03 秒：

表格

優(yōu)化方式耗時原理

無索引全表掃描5.2s遍歷所有行解析 JSON

MySQL 虛擬列 + 索引0.05s預計算字段，走 B + 樹索引

PostgreSQL GIN 索引0.03sJSONB 專屬索引，快速匹配

總結

選型核心：JSON 字段適合存儲非核心、易擴展的半結構化數據，核心字段仍用物理列；

性能關鍵：MySQL 靠「虛擬列 + 索引」、PostgreSQL 靠「JSONB+GIN 索引」解決 JSON 查詢性能問題；

避坑重點：注意數據類型轉換、控制 JSON 大小、避免高并發(fā)下的全字段更新，做好結構版本控制。

?著作權歸作者所有,轉載或內容合作請聯系作者
【社區(qū)內容提示】社區(qū)部分內容疑似由AI輔助生成，瀏覽時請結合常識與多方信息審慎甄別。
平臺聲明：文章內容（如有圖片或視頻亦包括在內）由作者上傳并發(fā)布，文章內容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務。

相關閱讀更多精彩內容

友情鏈接更多精彩內容