做后端这些年跟 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两个元素才返回 1JSON_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_SET新增这个键值对用新值覆盖旧值JSON_INSERT新增这个键值对忽略新值保留旧值JSON_REPLACE忽略新值什么都不做用新值覆盖旧值JSON_REMOVE什么都不做删除这个键它们的执行方式都是基于原有 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 也能像表一样被 JOINJSON_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) ) ENGINEInnoDB;这里的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) VIRTUAL3.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、INTMySQL 在生成虚拟列时就会做类型转换索引里存储的也是排序后的数值问题就自动消失了。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) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;这里有一个值得指出的设计细节我把 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的第一个参数如果是整列 JSONMySQL 优化器虽然能识别但多值索引匹配的路径不够明确。明确指定 tags 路径可以确保优化器正确选择 idx_tags 索引。如果要做标签交集判断比如既含 hot 又含 new 标签的人群用 JSON_CONTAINS 会要求同时匹配两个值如果用 JSON_OVERLAPS语义就变成至少匹配一个。这两种语义对应完全不同的业务筛选别搞混了。4.3 性能对比和调优实录我自己在差不多 80 万行数据的表上做了一轮对比给几个真实的数字感受一下查询方式执行时间未优化执行时间优化后按 user_id 时间范围查最近事件520ms 全表扫8ms 走复合索引按 sku_id 精确查某商品行为数460ms 全表扫12ms 走虚拟列索引按标签查所有 hot 事件1.2 秒全表扫 Json 解析15ms 走多值索引这三个数字是我同一台测试机、同一个数据量下实测的可以看出只要把索引建对查询效率都是数量级的提升。结合 EXPLAIN 来看最直观的变化是 type 列从 ALL 变成了 ref或者 rangeExtra 里也不再出现 Using filesort。调优过程中我还发现了一个容易忽视的问题如果虚拟列的定义和查询里的表达式写法不一致优化器有时候不会自动识别这是同一个表达式导致索引失效。比如表里生成列定义用的是event_payload - $.sku_id你查询时却写JSON_UNQUOTE(JSON_EXTRACT(event_payload, $.sku_id))虽然语义一样但 MySQL 不一定能自动等价。所以请务必保持查询里 JSON 提取表达式和建表时生成列的定义严格一致这是 90% 的人索引失效的原因。4.4 常见问题排查与避坑清单最后把我在生产上踩过的坑和看到的高频问题整理成一个速查表遇到直接对号入座问题/报错原因解决方案JSON 格式错误Invalid JSON text字符串里有多余逗号、单引号、转义问题先用 JSON_VALID() 函数验证格式用 JSON_QUOTE() 帮你转义查询结果带了双引号JSON_EXTRACT 返回的是 JSON 文档类型字符串值会带引号SELECT 展示用 -条件比较也尽量用 -JSON_CONTAINS 永远查不到参数顺序写反或字符串候选忘记加双引号第一个参数是目标第二个参数是候选字符串候选要写成张三生成列创建报错 3102表达式不是确定性的生成列表达式里不要用存储函数、用户变量、NOW() 等多值索引查询不生效没有使用 JSON_CONTAINS/JSON_OVERLAPS 等识别函数多值索引只服务于数组 JSON 函数普通比较不走索引ORDER BY 数字排序错误用 - 提取后是字符串字典序和数值序不一致生成列定义为 DECIMAL 或 INT使索引里存的是数值JSON 字段更新后 binlog 很大JSON_SET 实际整体替换了 JSON 文档小字段可以用 JSON_SET 部分更新超大 JSON 建议拆列或归档8.0 之前版本不支持多值索引/JSON_TABLE版本过旧生产建议至少升到 8.0.17 以上并开启完整的 JSON 功能这里重点多说两句 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 类型真正厉害的地方不在于能存或者能查而在于它把数据库的存储能力和文档型数据库的灵活性做了一个非常务实的结合。你把每个能预见的查询字段建成虚拟列去索引把动态扩展部分放心地交给 JSONMySQL 就能在很多看起来不适合关系型数据库的业务里继续坚挺很久。如果在看文章的你也正在犹豫要不要把 TEXT 里的 JSON 字符串迁到原生 JSON 类型我的建议是——大胆迁但把函数、路径、虚拟列、多值索引这套组合拳先练熟迁完之后的效率提升会非常明显。