★ FEATURED ARTICLE 做了这么多年后端我越来越发现一个规律凡是要长期维护的业务系统一定会长出结构不够用的时候。最常见的痛苦就是产品说咱这个对象要加个属性而你低头一看数据库表里整整齐齐躺着二十多个字段下面还有三四张扩展表在等着接收这些临时新增的数据。MySQL从5.7开始原生支持了JSON类型再到8.0版本把JSON_TABLE、多值索引这些能力逐步补齐现在已经完全能支撑起大多数半结构化业务场景。这篇文章我就从底层存储到实际查询、从建索引到踩坑把MySQL JSON类型这件事彻底说透让你看完之后敢在自己的项目里直接用也敢跟别人解释清楚为什么它效率能飞起。1. 为了一张结构天天变的表我最后不得不选JSON先说个真实场景。早期我做电商后台商品表一开始只卖标准商品标题、价格、库存、品牌、上下架状态。后来业务要区分新旧程度、保修期、发票类型再后来要支持图书的ISBN、服装的尺码表、数码产品的参数列表。你不可能每次来一个新属性就往主表加一列——那样过半年主表就有四五十个字段而且大部分行里这些列全是NULL。于是多数团队会走这么几条路但每条都让人难受。第一条路叫预留可扩展字段就是在主表里放一个或者几个varchar(1000)之类的列用来存扩展属性。这么做最大问题是格式完全失控没有人校验JSON是不是合法的字段值到底是字符串还是数字也没人管到了查询的时候你就得在SQL里写LIKE %xxx%慢且不说等节点多了以后想维护根本无从下手。第二条路叫EAV纵表Entity-Attribute-Value就是商品一份表、属性名一份表、属性值一份表。它能存任意结构也能通过JOIN查出来但代价相当大。每个商品要拿几十行甚至几百行属性拼回来复杂度是乘法级别的。数据量一旦过了百万光是查一个商品的完整属性这个动作就可能要走好几毫秒实时场景根本跑不动。第三条路就是直接引入一个真正的JSON列。它不是简单的一串字符而是会被MySQL解析、校验、而且能用SQL函数去检索的半结构化数据容器。JSON类型的出现本质上是在关系严格性和结构灵活性之间加了一个可调的中间档位你可以不给它预定字段但在写入时它必须是一个合法JSON也支持你用专用的JSON函数从里面取某个键、查某个值。对变动频繁但查询路径相对稳定的场景这个自由度和可控性的组合恰恰是最划算的。当然我不是让你把全表都改成JSON。常量的、参与联表JOIN的、会被频繁修改的字段继续留在普通列里。JSON列的定位是那些确实结构松散、不必要参与复杂JOIN的信息比如商品参数、用户画像、设备上报的原始日志、第三方回调里的payload。想明白这个边界再用JSON你才会真正感到省心。2. 别被表象骗了JSON类型在InnoDB里根本不是一串普通文本很多人有个误区以为JSON列就是个语法加强版varchar其实它在引擎层已经变成了二进制格式。当你执行INSERT的时候MySQL会做三件你看不见的事先校验这段内容的JSON合法性然后解析成内部二进制表示再按某种规则压缩存储。这个二进制格式通常也被叫作文档序列化格式它不是原样保存文本而是把键名排序、把重复键去掉、把数字和字符串区分类型最终存成一个紧凑的内部结构体。既然序列化成二进制了MySQL就能在读取的时候只抽取其中一部分而不需要把整段字符串读出来再用程序解析。举个例子一个JSON对象有20个键但你只是查询其中的某个值引擎可以利用解析好的结构直接定位到对应键值虽然目前依然是把整列读入内存后由JSON函数做路径提取但比起你在应用层拿全文去解析还是省掉了很多无谓的字符操作。更重要的是写入时会做实时校验非法JSON会在插入那一刻直接报错不需要等业务代码去catch。JSON类型在存储上有几个硬性限制你需要记住。最直观的是最大存储大小4GB受限于max_allowed_packet和实际内存日常使用足够键名的长度受utf8mb4字符集限制但实际中几乎不会被用到上限JSON里的对象键会被去重如果同一层出现两个相同键后面的会覆盖前面键的顺序也会被重新编排所以不要期望JSON字段里的顺序跟你写入顺序一致。它的类型体系包括对象、数组、字符串、数字、布尔、null这6种都存在内部类型标记里查询的时候JSON函数能区分出来不会像字符串那样全混在一起。那么问题来了既然已经是二进制存储为什么我还要建议大家不要图省事把JSON放在varchar里区别在于三点。第一varchar不校验你把一串乱码存进去MySQL也不拦你等到程序读出来JSON.parse直接抛错再回头查是哪行脏数据搞到凌晨都不一定查完。第二varchar没有路径提取能力虽然字符串也有LOCATE/SUBSTRING_INDEX但在复杂JSON嵌套结构里写那种语句维护成本极高基本等同于手写解析器。第三JSON列在更新的时候只改路径上的值虽然引擎层面最终还是重写整列但至少有JSON_SET这类专用语法支持应用层代码会清爽得多。提示JSON类型按utf8mb4_bin排序也就是二进制排序规则严格区分大小写。如果你需要忽略大小写去检索JSON里的某个字符串值需要在取出值之后显式用LOWER函数做转换别指望JSON比较自动帮你做大小写归一。3. 从建表到写入JSON类型最常见的操作命令一次讲清先把最基础的建表语句列出来你直接复制就能跑。假设我们要建一张用户标签表用来存放用户身上动态变化的各类标签属性。CREATE TABLE user_tag ( id INT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, tag_info JSON NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP, KEY idx_user_id (user_id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;写入JSON有几种常见方式。最简单的是直接传字符串字面量只要字符串符合JSON规范MySQL就会自动校验并转换INSERT INTO user_tag (user_id, tag_info) VALUES (1, {level: vip, tags: [数码, 极客], points: 3288}), (2, {level: normal, tags: [美食], points: 88});如果你在代码里拿到的是一个对象想拼SQL那推荐用JSON_OBJECT这个函数生成一方面避免手写字符串引号转义出错另一方面也更容易看清结构INSERT INTO user_tag (user_id, tag_info) VALUES (3, JSON_OBJECT(level, vip, tags, JSON_ARRAY(运动, 户外), points, 1024));把数组放到JSON对象里时用JSON_ARRAY是最稳妥的。还有一种特殊技巧如果你有一批散装数据要拼成一个JSON数组可以用JSON_ARRAYAGG把多行记录聚合成一个JSON数组这个在报表统计时很好用。而对应的JSON_OBJECTAGG可以把多行记录聚合成一个JSON对象。更新JSON字段时有四组函数最容易混淆我做了个对比表函数行为典型使用场景JSON_SET存在则更新不存在则插入最常用覆盖式写入JSON_INSERT仅当键不存在时插入保留已有值避免覆盖JSON_REPLACE仅当键存在时替换不新增未知键JSON_REMOVE删除指定路径的键按条件移除属性举个例子UPDATE user_tag SET tag_info JSON_SET(tag_info, $.level, svip) WHERE user_id 1; UPDATE user_tag SET tag_info JSON_SET( tag_info, $.tags, JSON_ARRAY_APPEND(JSON_EXTRACT(tag_info, $.tags), $, 摄影) ) WHERE user_id 1;这里有个细节值得提醒JSON_SET里的第三个参数如果直接传摄影它会被当成字符串加进去而不会自动理解成JSON字符串值但如果你要用循环方式往数组里塞就需要先JSON_EXTRACT把原数组取出来再用JSON_ARRAY_APPEND追加最后再写回去。整个过程看着多了一步但它能够保证原数组里的其他元素不被破坏。如果业务里频繁执行这种读-改-写你要注意它本质上是读和写两个操作高并发下要对行锁和更新顺序有预期必要的时候在事务里配合条件去更新。4. 查询JSON的正确姿势路径表达式和函数别用错JSON查询的核心是路径表达式。路径表达式统一以$开头帮助你在JSON文档里定位到某个节点。常见的语法有这么几类$表示整个JSON文档$.level提取顶层的level键$.tags[0]提取tags数组的第一个元素$.tags[*]匹配数组的所有元素$.**.name表示在任意深度下找name键这个搜索式写法在嵌套结构里很实用$.store.book[0].title这种连点就是多级嵌套的路径路由配合路径表达式最核心的提取函数是JSON_EXTRACT以及它的两个简写形式-和-。新手最容易在这两个简写上面翻车我把核心差异说清楚写法等价于返回值特点tag_info-$.levelJSON_EXTRACT(tag_info, $.level)返回带引号的JSON字符串如viptag_info-$.levelJSON_UNQUOTE(JSON_EXTRACT(...))返回不带引号的字符串如vip所以如果你看到WHERE tag_info-$.level vip查不到数据不要慌大概率是因为左侧返回的是带双引号的vip你拿不带引号的vip去比较连接不上。正确的写法是tag_info-$.level vip。这个坑网上每天都在被问我当年也栽过一次。条件判断类的函数里JSON_CONTAINS负责判断目标值是否存在于指定路径中。特别提醒一点JSON_CONTAINS在判断数组元素时目标也要写成JSON字符串格式不然会变成比对整个数组值而不是比对某个元素。判断某个路径是否存在可以用JSON_CONTAINS_PATH。如果在数组里想要找包含某个字符串的元素的位置可以用JSON_SEARCH但它做的是类似全文搜一样的遍历开销相对大在千万级数据里用来做高频精确匹配是不合适的。把JSON数据“炸”回关系结构的最佳利器是JSON_TABLE。它的用法就是把一个JSON数组按行展开每一行变成一张临时表的记录然后你可以查它、过滤它、和外层表JOIN。看个例子SELECT ut.user_id, t.tag FROM user_tag ut, JSON_TABLE(ut.tag_info, $.tags[*] COLUMNS (tag VARCHAR(50) PATH $)) AS t WHERE ut.user_id 1;这种写法看着像是在逗号后面挂了一个子查询其实JSON_TABLE生成了一个关系型的虚拟表MySQL之后可以对这个结果做正常的筛选、排序、聚合。它特别适合把JSON里的数组转成标准的多行结果集和报表类需求天然契合。要注意的是JSON_TABLE在MySQL 8.0.14起才支持完整功能如果你还在用5.7建议尽早升级不然就只能用老办法在应用层循环解析了。5. 效率飞起的真正关键生成列把JSON索引进普通索引JSON本身不能被直接建普通B树索引这一点让很多人头疼。为什么不能建因为InnoDB的索引页是按列值组织排序的一个JSON文档可能很大很长直接用整个文档建索引既不现实也没意义。正确的思路是把JSON里需要频繁查询的那个值提取成一个虚拟列再在这个虚拟列上建索引。这个提取出来的列在MySQL里叫生成列Generated Column声明方式是在建表时用AS子句加一个表达式。生成列分两种VIRTUAL和STORED。VIRTUAL生成列不占用额外的物理存储每次读取的时候即时计算STORED生成列会把计算结果落盘占用真实存储空间。查询过滤和排序时MySQL能直接利用VIRTUAL列上的索引因为InnoDB支持在虚拟列上建辅助索引。如果这个值还会被别的地方频繁参与聚合计算用STORED一次计算省得每次检索都重复算。最经典的写法是这样的ALTER TABLE user_tag ADD COLUMN level VARCHAR(20) GENERATED ALWAYS AS (tag_info-$.level) VIRTUAL; ALTER TABLE user_tag ADD INDEX idx_level (level);建完索引之后原来那种写法不用改甚至会让WHERE tag_info-$.level vip自动走索引。不过要注意函数提取之后的类型是字符串如果你在原JSON里存的是数字希望按数值范围过滤那生成列要显式转类型比如CAST(tag_info-$.points AS UNSIGNED)然后在生成列上建索引这样WHERE points BETWEEN 100 AND 500才能真正利用索引不会触发隐式转换导致索引失效。这里还有几个细节值得注意。第一生成列上的索引对等值查询和最左前缀匹配都是有效的但如果你在生成列上套了一层函数比如WHERE LOWER(level) vip还是会失效。第二数组类型的查询MySQL 8.0.17以后专门给了多值索引Multi-Valued Index可以在JSON数组字段上直接建索引用于加速WHERE JSON_CONTAINS(tag_info-$.tags, 数码)这类数组包含查询。第三如果你要按JSON里的日期排序建议生成列直接定义为DATE类型而不是把字符串存进去不然字符串按字典序排序会把2024-09-01排在2024-09-02前面还好一旦涉及月份跨位数字排序结果就会乱。6. 一次真实场景对比从全表扫描到索引命中效率差了多少我拿一个线上用过的商品属性场景来做对比实验数据量不算大10万行但足够说明问题了。表结构是这样CREATE TABLE sku_attrs ( id INT PRIMARY KEY AUTO_INCREMENT, sku_id BIGINT NOT NULL, attrs JSON NOT NULL, created_at DATETIME DEFAULT CURRENT_TIMESTAMP );attrs字段里的数据大概是这个风格{brand: a1, season: 2024, color: black, size: [M, L], price: 299}现在业务接口有个筛选条件按品牌等于a1且价格小于200去查SKU。我们先记住这个查询的等价条件如果没做任何索引执行计划会显示typeALL也就是全表扫描estimated rows接近10万。在机械硬盘上跑一次可能要600~800毫秒在SSD上也要120毫秒左右这数据看着好像还能忍但一旦并发到了上百QPSCPU和IO马上告警。接下来的改造就是加一个价格虚拟列并建索引ALTER TABLE sku_attrs ADD COLUMN price DECIMAL(10,2) GENERATED ALWAYS AS (CAST(attrs-$.price AS DECIMAL(10,2))) STORED; ALTER TABLE sku_attrs ADD INDEX idx_brand_price ( (attrs-$.brand), price);这里用了函数索引语法实际上MySQL 8.0.13以后支持在函数表达式上直接建索引也可以写成显式虚拟列再建普通索引我两种方式都试过效果基本一致。再跑一遍同样的查询执行计划里type会变成refkey显示idx_brand_pricerows大约只扫几百行单查询耗时直接掉到1毫秒以内。从600毫秒到1毫秒这个提升不是靠某个神奇参数就是靠把查询路径变成索引路径。这也解释了为什么我一直强调JSON本身不是慢而是你如果拿它当普通字符串去扫描当然慢一旦通过生成列把高频条件提升到索引层面MySQL的优化器就会像对待普通列一样对待它。7. 那些说起来没人提、踩进去全是坑的边界问题JSON类型用顺手了之后我特别想提醒几个边界场景这些地方处理不好开发体验会断崖式下跌。生成列和默认值这两个点藏了不少老版本限制。MySQL 8.0.13之前JSON列不允许设置默认值如果你有每一行都要带一个缺省空对象的需求只能靠应用层写入时补。升级到8.0.13以后可以给JSON列加表达式默认值但条件是表达式必须是一个字符串常量或者括号包裹的函数直接写{}这类字符串是可以过的。另一个陷阱是生成列表达式里不能引用JSON里不存在的路径否则该列会变成NULL同时还会导致插入的时候被当成功但其实查询结果不是你要的——排查起来非常难受。JSON_SEARCH和JSON_CONTAINS_PATH的性能问题也要正视。JSON_SEARCH做的是全文档递归遍历复杂度是线性的数据一大、字段一深每次查询都要把整列都扫一遍。如果你的业务要高频判断某个值在不在数组里正确的做法就是上面说的多值索引或者把数组的值也抽成一张关系表来做精确匹配。把SUM、COUNT这种高频聚合也压在JSON上初看能省一张表但跑几轮压测你就能感受到差距。隐式类型转换也是个高频地雷。JSON_EXTRACT返回的永远是带引号的JSON字符串如果你拿它去跟数字比较MySQL可能把两边都转成浮点数后再比也可能走不进索引。老老实实用CAST去显式转或者干脆在生成列定义时就把类型写好比在查询里临时转换要稳定得多。另一个跟类型相关的是排序ORDER BY JSON_EXTRACT(attrs, $.created_at)默认按字符串排序在固定格式下基本能用但一旦有脏数据就把顺序打乱同样建议生成DATE类型列后再排序。还需要考虑的是写入成本。JSON字段有一个特点只要更新了路径下的任何一个值整列的内容基本都会重写。也就是说如果你经常对某个值做很小幅度的UPDATE虽然SQL写起来很方便但底层依然要把整个文档重新序列化、重新存储。所以对于那种需要高频修改部分内容的对象特别是超过几十KB的大文档JSON并不一定比拆表合适这是方便和性能的取舍点得提前想清楚。我之前还看到有人试图在JSON内部字段上建外键答案是权限之外的不可能。MySQL的外键只能建于实际列之间JSON内部的键依然是文档的一部分无法被异构表引用。同理JSON内部也没法建全文索引做了全文索引的VARCHAR列可以模糊搜索JSON列本身没有这个能力。如果你要搜的是JSON中的一个值而且范围很广规范化出来一张表也许才是正确的终点。8. 规范化还是JSON一张表一个判断口诀文章快收尾了我知道很多人心里还悬着那个终极问题到底什么时候该把数据拆成关系表什么时候放心用JSON我给自己的判断口诀是如果这个属性的取值范围和含义会经常变化而且几乎不需要和其他表做JOIN那就考虑JSON如果它要参与统计、排序、区间过滤、多表关联并且结构相对固定就必须规范化成普通列。边界情况是结构会变但查询必须是精确等值这时生成列加索引的JSON方案仍然很高效可以直接用。还有一个来自实践的建议千万别一上来把整个业务对象都塞进JSON。我会把对象里的核心标识字段提取成普通列比如sku_id、user_id、order_no这些字段要做索引、要JOIN、要全局唯一约束真正动态的部分比如参数明细、偏好列表、原始扩展属性才放进JSON里。这样查询的入口由结构化列承担JSON只是附加的信息载体表的可读性和查询性能都能保持在可控范围。如果你是从5.7往8.0迁移的有一个额外的红利8.0对JSON支持更完善了JSON_TABLE成熟多值索引能用函数索引也允许直接在表达式上建。升级的时候记得把旧版本的JSON函数都测一遍特别是那些靠字符串拼接去构造JSON的低级写法8.0的校验更严格但总体迁移成本不高换来的是查询写法的简洁度和执行效率的双提升。最后分享一个我自己的小技巧调试JSON路径写错了发现查询结果和预想对不上我会先跑一条SELECT JSON_EXTRACT(attrs, $.price) FROM sku_attrs LIMIT 5看看取出来的值带不带引号、格式是什么再决定下一步是加CAST还是改路径。这能节省大量排查时间比直接改SQL然后反复试要靠谱得多。希望这篇能把你在JSON类型上的疑惑一站式解决下次在项目里再遇到结构总变的属性你可以底气十足地说用JSON再加个生成列效率飞起。
阅读完成 · 觉得有帮助？
