做后端这些年，跟 MySQL 打交道是每天的必修课。早期遇到业务要存一些格式不确定的数据，第一反应就是掏出一个 TEXT 字段往里塞 JSON 字符串，查询全靠 LIKE，修改全靠先把整个字符串读出来拼好再写回去。直到后来痛定思痛，把 MySQL 5.7 引入的原生 JSON 类型玩熟了，才发现以前的操作方式简直是拿着大刀绣花。这篇文章就把我从零开始踩坑、优化到最终在线上稳定跑了一两年的经验全部写出来，覆盖 JSON 类型的底层设计、常用函数、索引优化、排序技巧和实操案列，看完你基本就能在自己的项目里直接用起来。

文章适合这几类人看：被 JSON 字段折腾过的新手，想优化现有 JSON 查询性能的人，以及正在纠结"到底该不该用 JSON 类型"的架构师。内容不会跟你扯太多没用的理论，主要告诉你这东西在实战里怎么用、能踩到什么坑、如何让查询效率真正飞起来。

1. 为什么需要 JSON 列而不是"祖宗传下来的字符串列"

1.1 传统做法到底差在哪

如果你还没有在 MySQL 里正经用过 JSON 类型，那你大概率现在还在用 TEXT 或者 VARCHAR 存 JSON 字符串。平时存几条记录看着没啥问题，但等到数据量上来，或者前端要的东西稍微复杂一点点，这种方案的缺点就全暴露了。

首先，字符串列根本不认识 JSON 结构。你存进去的是"一串字符"，MySQL 完全不知道里面有个字段叫 user_id，也不知道 price 是数字还是字符串。所以你想查"所有 user_id 是 10086 的记录"，只能写LIKE '%10086%'。这种写法不光又慢又傻，还会误匹配到 100860、1008611 这些不该出现的值，想精确过滤几乎做不到。其次，字符串列不会校验 JSON 的合法性。应用层手一抖多写了个逗号，或者少了个引号，那玩意儿就存进去了，等到消费数据的时候才爆炸，排错排到想砸键盘。再有就是更新效率，你改 JSON 里的某一个键，传统方案必须把这个字段的整体内容从存储引擎里读出来，在应用层改完再整条 UPDATE 回去，一个好几 KB 的 JSON 每次只改一个小属性，白白消耗大量 IO 和网络开销。

这些问题不是靠"写代码小心一点"就能规避的，它们源自存储引擎对数据形态的认知差异。MySQL 从 5.7.8 开始原生支持 JSON 类型，就是专门来解决这些痛点的。

1.2 JSON 类型到底解决了什么问题

JSON 类型最本质的变化在于，MySQL 服务器把 JSON 文档在写入时就做了解析，同时用一种叫作 binary JSON 的二进制格式存储，而不是直接保存你传进来的文本。

当初官方做这个设计的核心诉求有三个。第一是合法性保证，写入时如果格式不对，直接报错拦下来，脏数据根本进不了表。第二是高效访问，二进制格式让 MySQL 可以直接定位到 JSON 文档内部的某个字段，不用把整个文档扫描一遍去找 key。这就好比你文件归档的时候就把每一份文件编好索引放在固定的格子里，要找哪一份直接按编号拿就行，不用从头翻到尾。第三是存储空间优化，JSON 二进制格式会自动处理 key 的存储、去重空格，对数字和字符串也有自己的一套存储方式，实际测下来很多场景比纯文本存 JSON 还要节省空间。

这里要特别强调一点，JSON 类型底层虽然后缀是"类型"，但它本质上还是以字符串（LONGTEXT）为载体存储的，MySQL 官方文档也明确写过这一点。理解了这个，你就不会奇怪为什么 JSON 列在 InnoDB 里其实不能直接建索引——至于怎么给 JSON 建索引，后面第三章详细讲。

1.3 该用和不该用的场景判断

JSON 类型不是万能药，我用下来的经验是：当业务字段确实具有"动态属性"特征时才值得用，如果每行记录的结构高度一致且字段永远固定，那赶紧去建普通表字段。

推荐使用 JSON 的场景，最典型的就是存储第三方接口返回的原始数据。我们接支付渠道回调、物流轨迹、风控结果这类数据，各家渠道给的字段层次都不一样，而且随时可能新增字段，你要是为每个渠道建一张表，表结构会改到怀疑人生，用 JSON 字段存原始报文就非常合适。还有一种场景是产品端有可配置的扩展属性，比如电商类目属性，卖手机的字段是屏幕、处理器，卖衣服的字段是材质、版型，你不可能在商品表里把所有可能性都建出来，这种情况下 JSON 存这类异构属性也是标准答案。另外，系统埋点事件、AB 实验日志，这类数据量大但基本只写少读，而且读的时候往往是抽样分析，JSON 类型也完全能扛。

反过来，有些不适合的场景我也想直接说清楚。永远别用 JSON 存强关系型数据，比如订单明细、用户基本信息这种要频繁做 JOIN、外键关联的数据，JSON 会让一切关联查询都变成灾难。高频更新的核心业务数据也不建议用 JSON，理由后面第 4 章讲部分更新时会提到的 binlog 问题，MySQL 目前对 JSON 的部分更新其实有限制。最后还有一种情况，业务宁可字段名为 null 也要保证列存在，这时候用 JSON 只会模糊掉你对表结构的认知，不如老老实实加可空列。

2. 上线前必须吃透的 JSON 函数和路径语法

2.1 路径表达式基础，不然后面全乱

JSON 类型的使用几乎离不开路径表达式（JSON Path），它就是一套定位 JSON 内部节点的语法。MySQL 里的路径写法是$.开头，里面用点号取对象的 key，用下标取数组的元素。

比如有一个 JSON 假设是{"name": "张三", "tags": ["后端", "MySQL"], "address": {"city": "上海"}}，那$.name就定位到 "张三"，$.tags[0]定位到 "后端"，$.address.city定位到 "上海"。如果你的键名本身有点号或者特殊字符，比如 Kafka 里常见的键名带点的场景，可以用双引号包住键名：$."user.name"。

路径表达式里最影响效率的写法是用通配符*和**。$.*代表对象的所有成员，$**.price代表任意层级的 price 键。这种写法不是不能用，但服务器要递归遍历结构，性能开销大，能不用就不用，特别在 WHERE 条件里用了通配符基本就把索引优势全废了。

2.2 读取与提取：JSON_EXTRACT、->、->>

JSON_EXTRACT 是提取字段的元老级函数，用法是JSON_EXTRACT(json_doc, path)。我用同一个值演示一下：

SELECT JSON_EXTRACT('{"name": "张三", "age": 20}', '$.name'); -- 结果是 "张三"，注意这里带双引号 SELECT JSON_EXTRACT('{"name": "张三", "age": 20}', '$.age'); -- 结果是 20，不带引号，因为数字本身是数字类型

JSON_EXTRACT 返回的其实还是一个 JSON 文档，所以字符串类型的值会带着双引号。这在用来展示时非常别扭，于是出现了两个简写运算符。列名 -> path和JSON_EXTRACT(列名, path)完全等价，而列名 ->> path在 MySQL 5.7.13 之后提供了"去掉引号和转义"的版本。不信你试：

SELECT '{"name": "张三"}' -> '$.name'; -- "张三" SELECT '{"name": "张三"}' ->> '$.name'; -- 张三

所以在条件比较、排序、分组时，你要拿 JSON 里的字符串值和普通字符串比较，基本都用 ->>。反过来，如果你要提取数组、嵌套对象整体，用 JSON_EXTRACT 或者 -> 更能保留原始结构。

2.3 存在性判断与包含判断：JSON_CONTAINS、JSON_SEARCH、JSON_OVERLAPS

业务里最常见的 JSON 过滤需求是"看看我的 JSON 里有没有某个字段值"。这个场景核心函数是 JSON_CONTAINS，它的语义是"目标 JSON 里是否包含候选 JSON"。这个参数的顺序不知道坑了多少人，第一个参数是目标，第二个参数是候选。新手经常写反，结果死活查不出来。

-- 判断 json_doc 中的 name 是不是等于 张三 SELECT JSON_CONTAINS(json_doc, '"张三"', '$.name') FROM tb; -- 注意！字符串作为候选 JSON 时必须自己带双引号 -- 判断数组里有没有某个值 SELECT JSON_CONTAINS(json_doc, '"MySQL"', '$.tags'); -- 对应 json_doc = '{"tags": ["后端", "MySQL"]}' 时返回 1 -- 判断数组和数组有没有交集 SELECT JSON_CONTAINS(json_doc, '["后端", "Java"]', '$.tags'); -- 只要目标数组同时包含"后端"和"Java"两个元素才返回 1

JSON_SEARCH 则是全文搜索 JSON 文档里某个值的位置，返回的是路径字符串。它支持模糊匹配，比如JSON_SEARCH(json_doc, 'one', '%张%')返回第一个包含"张"的值的路径，JSON_SEARCH(json_doc, 'all', '%张%')返回所有匹配路径组成的数组。这个函数挺好用，但要提醒你一句，JSON_SEARCH 因为要遍历整个文档，性能开销不小，更适合数据量小的场景或者一次性分析任务，不要放在高频接口的 WHERE 条件里。

还有个 8.0.17 之后才有的 JSON_OVERLAPS，专门用来判断两个 JSON 数组是否有交集：

SELECT JSON_OVERLAPS('["a", "b"]', '["b", "c"]'); -- 返回 1 SELECT JSON_OVERLAPS('["a", "b"]', '["c", "d"]'); -- 返回 0

这个函数在做"用户是否命中任意一个标签"这种查询时非常顺手，而且如果配合多值索引使用，效率会非常好。

2.4 增删改：JSON_SET、JSON_INSERT、JSON_REPLACE、JSON_REMOVE

有些同学会天真地以为 JSON 函数只有查没有改，其实不是。MySQL 出了四个 DML 类 JSON 函数，它们的区别非常微妙，我直接列表格给你看清楚：

它们的执行方式都是"基于原有 JSON 生成一个新的 JSON 文档"，并不会直接在原存储上原地修改字段。这一点要尤其注意，特别是后面讲部分更新的时候。写的时候函数会返回新文档，你必须把这个结果更新回去才生效，比如：

-- 给 json_doc 设置一个 version 键，如果已存在就更新它 UPDATE tb SET json_doc = JSON_SET(json_doc, '$.version', '2.1') WHERE id = 1;

JSON_ARRAY_APPEND 和 JSON_ARRAY_INSERT 是专门操作数组的扩展函数，前者向后追加，后者往指定下标插值。这几个函数组合起来，基本上覆盖了你对 JSON 字段的日常增删改需求。

2.5 JSON_TABLE，让 JSON 也能像表一样被 JOIN

JSON_TABLE 是 MySQL 8.0 加入的函数，个人认为是处理 JSON 查询里最强大、也最容易被忽略的利器。它的作用是把 JSON 数组里的每个元素映射成一行虚拟表数据，这样你就能对 JSON 内部的数组做展开查询、聚合统计、JOIN 操作。

举个例子，我有张订单表，每条订单里的 items 字段存的是商品明细数组：

SELECT o.order_id, t.product_name, t.price FROM orders o, JSON_TABLE(o.items, '$[*]' COLUMNS ( product_name VARCHAR(50) PATH '$.name', price DECIMAL(10,2) PATH '$.price' )) AS t;

JSON_TABLE 第一个参数是要解析的 JSON 字段，第二个参数是行路径，$[*]表示遍历所有数组元素。COLUMNS 里定义了每一字段要映射的路径和类型。这段 SQL 跑完，商品明细就从 JSON 数组展开成了普通行，接下来想怎么聚合、怎么排序、怎么 JOIN 都行。这个函数是我在生产上最常用的 JSON 处理手段，建议所有 8.0 用户都熟练掌握。

3. JSON 列如何建立索引才能让查询真正飞起来

3.1 为什么 JSON 字段本身加不了索引

前面已经埋了个伏笔，JSON 列底层是 LONGTEXT 存储，所以不能像普通字段那样直接CREATE INDEX idx_json ON tb(json_col)。就算能加，这样的索引对 JSON 查询也没有意义，因为你要定位的是某一个内部字段，比如$.user_id，索引必须建立在"表达式的值"上，而不是整个 JSON 文档上。

MySQL 给 JSON 建索引的标准姿势是虚拟列（生成列） + 常规索引。这个虚拟列从 JSON 中提取某个值，并且把它当作普通列一样去建索引。这里大多数同学第一次接触会有点绕，我拆细一点讲。

3.2 虚拟列（生成列）的正确姿势与两个版本差异

你可以在建表时定义一列，该列的值是通过表达式从其他列计算得来的，这种列就叫生成列（Generated Column）。它有两种选项，VIRTUAL（默认）表示这个列不实际占用磁盘存储，读取时实时计算；STORED 表示实际存储，占磁盘但读取快。

给 JSON 建索引时，我强烈建议用 VIRTUAL 虚拟列加索引。因为索引本身就是把该列的值物化了一份存在索引结构里，查询走索引时直接能取到，根本不依赖真实存储，所以没必要再浪费一份磁盘。代价是，如果你SELECT *恰好又没走索引要全表扫，每一行都会实时计算一下虚拟值。但查询能走到索引的场景，这个代价不会被触发，整体收益很可观。

建表时的完整写法长这样：

CREATE TABLE user_events ( id INT PRIMARY KEY AUTO_INCREMENT, event_body JSON NOT NULL, user_id INT GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_body, '$.user_id'))) VIRTUAL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, INDEX idx_user_id (user_id) ) ENGINE=InnoDB;

这里的JSON_UNQUOTE(JSON_EXTRACT(event_body, '$.user_id'))等价于event_body ->> '$.user_id'，不过我教你留意一点，生成列的表达式必须是确定性的，也就是同一个输入必须始终产生同一个输出，官方为此要求表达式里不能用存储函数、用户变量这些不确定的东西。最常见的报错是Error Code: 3102，提示表达式不是确定性的，或者Error Code: 3759，提示生成列的值超范围，都需要对应去查。

对 MySQL 8.0 来说，还有更简洁的写法，因为 8.0.13 之后的生成列可以直接用->>运算符：

user_id INT GENERATED ALWAYS AS (event_body ->> '$.user_id') VIRTUAL

3.3 多值索引，解决数组场景的索引需求

虚拟列方案适合 JSON 里存简单对象的情况，但如果前端存的是数组，比如用户标签["后端", "MySQL"]，你很难用虚拟列搞明白。因为虚拟列提取的是一整块数组值，不是里面单个元素，这时候想要"查所有包含 MySQL 标签的用户"，虚拟列就失效了。

MySQL 从 8.0.17 开始引入了多值索引（Multi-Valued Index），专门解决 JSON 数组的索引问题。语法长这样：

CREATE INDEX idx_tags ON user_events ((CAST(event_body ->> '$.tags' AS UNSIGNED ARRAY)));

或者针对字符串数组：

CREATE INDEX idx_tags ON user_events ((CAST(event_body->'$.tags' AS CHAR(20) ARRAY)));

核心就是你在表达式外面套了一层CAST(... AS ... ARRAY)，让 MySQL 知道你对数组的每个元素都要建索引。多值索引生效的查询必须用特定的 JSON 函数，比如JSON_CONTAINS或者JSON_OVERLAPS，普通=匹配数组整体是走不了这个索引的，这点要特别记牢。

在我实际测的场景里，一个几十万行的标签查询表，不用多值索引走全表扫大概 400ms，建了多值索引之后直接掉到个位数毫秒。效果是非常夸张的。

3.4 排序的正确打开方式

看到排序这个需求，第一反应肯定是ORDER BY event_body->>'$.created_at'，但直接这样排会在临时表做 filesort，性能差。正确做法是让排序也走生成列索引。

你在建虚拟列的时候顺便给它建索引，然后排序查询里就用那个虚拟列字段，MySQL 优化器会直接选择索引有序扫描，避免临时表和文件排序：

-- 建虚拟列时顺便建索引 event_time DATETIME GENERATED ALWAYS AS (event_body ->> '$.event_time') VIRTUAL, INDEX idx_event_time (event_time) -- 排序时直接用虚拟列 SELECT * FROM user_events ORDER BY event_time DESC;

这里有个特别实用的技巧：如果你要把 JSON 里的数字排序，直接用->>提取出来是字符串，数字 2 会排在 10 后面。这时候虚拟列就一定要定义成正确的数值类型，比如DECIMAL、INT，MySQL 在生成虚拟列时就会做类型转换，索引里存储的也是排序后的数值，问题就自动消失了。

4. 实战：用户行为扩展属性表从设计到上线

4.1 表结构设计与写入

纸上谈兵再多，不如把完整案例跑一遍。我举个非常典型的场景：业务方要记录用户在小程序里的行为，每次行为对应的扩展属性千奇百怪，有的带页面路径，有的带商品 sku，有的只带一个来源渠道。这类需求最适合 JSON。

我设计一张 events 表，核心字段就四个：id、user_id、event_type、event_payload，其中 payload 就是 JSON。同时为了查询效率，我为高频使用的字段创建虚拟列。

CREATE TABLE events ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, event_type VARCHAR(32) NOT NULL, event_payload JSON NOT NULL, enter_time DATETIME GENERATED ALWAYS AS (event_payload ->> '$.enter_time') VIRTUAL, sku_id BIGINT GENERATED ALWAYS AS (event_payload ->> '$.sku_id') VIRTUAL, channel VARCHAR(16) GENERATED ALWAYS AS (event_payload ->> '$.channel') VIRTUAL, INDEX idx_user_time (user_id, enter_time), INDEX idx_channel (channel), INDEX idx_sku (sku_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

这里有一个值得指出的设计细节：我把 user_id 和 enter_time 构建了一个复合索引，因为业务上最常见的查询就是"取某人在某段时间内的行为记录"，这是一个典型的范围+等值组合，复合索引可以让过滤一步到位。

写入时直接用 INSERT 传 JSON 字符串即可，但注意一定要是合法的 JSON，列名上的类型约束会帮你做校验：

INSERT INTO events (user_id, event_type, event_payload) VALUES (1001, 'view_item', '{"enter_time": "2025-06-01 10:00:00", "sku_id": 887766, "channel": "wechat", "tags": ["hot", "new"]}'), (1002, 'add_cart', '{"enter_time": "2025-06-01 10:05:00", "sku_id": 556677, "channel": "app", "tags": ["new"]}'), (1003, 'view_item', '{"enter_time": "2025-06-01 10:10:00", "sku_id": 887766, "channel": "h5", "tags": ["hot"]}');

4.2 核心查询需求逐条拆解

第一个高频需求，查用户 1001 最近的埋点记录，并且只要前 20 条。这个查询要走之前建的 idx_user_time 复合索引：

SELECT user_id, event_type, event_payload FROM events WHERE user_id = 1001 ORDER BY enter_time DESC LIMIT 20;

注意这里 ORDER BY 用的就是虚拟列，user_id 等值 + enter_time 排序，完全被复合索引覆盖，执行计划里会显示 Using index condition，效率非常好。

第二个需求，统计 sku 887766 在当天被 view 了多少次。sku_id 走了虚拟列索引：

SELECT COUNT(*) FROM events WHERE event_type = 'view_item' AND sku_id = 887766 AND enter_time >= '2025-06-01 00:00:00' AND enter_time < '2025-06-02 00:00:00';

第三个需求是数组场景，查"所有带 hot 标签的事件"。这就到了 JSON_CONTAINS 和多值索引的主场。先在表上建多值索引：

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

然后查询：

SELECT COUNT(*) FROM events WHERE JSON_CONTAINS(event_payload->'$.tags', '"hot"');

这里要说明一个比较隐蔽的坑：我用的是event_payload->'$.tags'而不是整个event_payload，因为JSON_CONTAINS的第一个参数如果是整列 JSON，MySQL 优化器虽然能识别，但多值索引匹配的路径不够明确。明确指定 tags 路径，可以确保优化器正确选择 idx_tags 索引。

如果要做标签交集判断，比如"既含 hot 又含 new 标签的人群"，用 JSON_CONTAINS 会要求同时匹配两个值；如果用 JSON_OVERLAPS，语义就变成"至少匹配一个"。这两种语义对应完全不同的业务筛选，别搞混了。

4.3 性能对比和调优实录

我自己在差不多 80 万行数据的表上做了一轮对比，给几个真实的数字感受一下：

这三个数字是我同一台测试机、同一个数据量下实测的，可以看出只要把索引建对，查询效率都是数量级的提升。结合 EXPLAIN 来看，最直观的变化是 type 列从 ALL 变成了 ref（或者 range），Extra 里也不再出现 Using filesort。

调优过程中我还发现了一个容易忽视的问题：如果虚拟列的定义和查询里的表达式写法不一致，优化器有时候不会自动识别这是同一个表达式，导致索引失效。比如表里生成列定义用的是event_payload ->> '$.sku_id'，你查询时却写JSON_UNQUOTE(JSON_EXTRACT(event_payload, '$.sku_id'))，虽然语义一样，但 MySQL 不一定能自动等价。所以请务必保持查询里 JSON 提取表达式和建表时生成列的定义严格一致，这是 90% 的人索引失效的原因。

4.4 常见问题排查与避坑清单

最后把我在生产上踩过的坑和看到的高频问题整理成一个速查表，遇到直接对号入座：

这里重点多说两句 JSON 更新和 binlog 的问题。MySQL 8.0 的 JSON 文档其实有一个"部分更新"的优化，当用 JSON_SET 只改一个小键时，如果满足"原文档未压缩、更新时没有触发重新序列化"等条件，InnoDB 能够只记录变更部分。但条件是挺苛刻的，一旦文档经过压缩，或者更新键的路径结构太长，就又会走整体替换。所以你在代码里 JSON_SET 一个几百字节的文档和几十 KB 的文档，压力天差地别。我有一个经验是，如果 JSON 字段承载的数据已经膨胀到几十 KB 以上，里面还包含高频更新的属性，就说明这个字段应该拆出来了，不要硬塞在 JSON 里。

还有个高频问题，可能困扰很多人：查询SELECT * FROM events时，那几万行 JSON 数据全量返回，没有任何实际问题。但如果你的 JSON 字段里有超大数组、几 MB 的原始报文，千万不要不做限制地SELECT *，应用服务器内存分分钟被打爆。线上习惯是只 SELECT 需要的虚拟列和普通列，把 JSON 大字段留给按需查询的场景。

另外一个关于排序规则的小坑，如果 JSON 里存的文本有中文，虚拟列和索引的字符集排序规则最好和查询条件完全一致。我遇到过表里 utf8mb4_general_ci 建索引，程序里拿 utf8mb4_unicode_ci 做排序对比，结果查出来顺序不符合预期。统一字符集和排序规则，这类玄学问题就不会找上门。

最后说一个 JSON 相关事务的注意点。前面说的 JSON_SET 部分更新，在事务里会有行锁和间隙锁的连带行为，以及 Undo Log 的额外记录。不要在高并发路径上对一个热点 JSON 字段反复做细分修改，这种业务应当把热点字段单独建列，换成普通类型更新，避免 JSON 结构的解析和重放开销放大成锁竞争。

整篇文章写到这里，把我日常用 MySQL JSON 类型的大部分经验都倒出来了。我个人在实际操作中最深的体会是，JSON 类型真正厉害的地方不在于"能存"或者"能查"，而在于它把数据库的存储能力和文档型数据库的灵活性做了一个非常务实的结合。你把每个能预见的查询字段建成虚拟列去索引，把动态扩展部分放心地交给 JSON，MySQL 就能在很多"看起来不适合关系型数据库"的业务里继续坚挺很久。如果在看文章的你也正在犹豫要不要把 TEXT 里的 JSON 字符串迁到原生 JSON 类型，我的建议是——大胆迁，但把函数、路径、虚拟列、多值索引这套组合拳先练熟，迁完之后的效率提升会非常明显。