1. 从一次线上事故说起JSON字段查询慢到超时的排查过程先讲个我自己的经历。去年初接手一个内容管理系统的性能优化系统里有个article表其中ext_info字段是 JSON 类型存了一堆文章扩展属性包括作者签名、阅读权限、展示模板之类。上线初期数据量只有几十万一切正常。结果半年后数据涨到 500 万行运营同学开始频繁提工单——后台按“作者标签”筛选文章时接口动不动就超时 30 秒以上。当时后台的查询 SQL 长这样SELECT * FROM article WHERE ext_info-$.author_label 深度干货 ORDER BY create_time DESC LIMIT 20;单看这条 SQL索引该建的都建了——create_time有索引author_label在 MySQL 5.7 里没法直接建普通索引所以只能靠全表扫描。500 万行数据每行还要解析 JSON 字符串取字段不慢才怪。这个案例基本把 MySQL JSON 模糊查询的核心问题全暴露出来了JSON 字段的查询性能、索引利用、以及“模糊匹配”到底该怎么做。这半年里我陆续把 MySQL 5.7 和 8.0 的 JSON 查询方案都试了一遍也踩了不少坑这篇就把完整经验和盘托出。先明确一个概念MySQL 里对 JSON 字段做“模糊查询”传统 LIKE 的思路不完全适用因为 JSON 是一个完整的文档结构。你要么先把 JSON 解析成虚拟列再走索引要么用JSON_CONTAINS、JSON_SEARCH这类专用函数要么从设计层面直接避免“在 JSON 里做模糊匹配”这个需求。这几种方案各有各的适用场景下面逐个拆解。2. 为什么不能直接在 JSON 字段上用 LIKE背后的解析机制很多刚开始接触 JSON 字段的同学会写出这样的 SQLSELECT * FROM article WHERE ext_info LIKE %深度干货%;这条 SQL 在数据量小的时候能跑出结果但有两个致命问题第一它会把 JSON 的键名也匹配进去。比如你存的 JSON 是{author_label: 深度干货, template_id: author_deep},那author_deep里面包含deepLIKE %deep%也能匹配上但实际上这条记录的author_label根本不是“深度干货”。这在业务上就是误报。第二它会匹配到 JSON 的结构符号。比如搜索%: 深度干货%像是 JSON 的引号、冒号、花括号参与了匹配。一旦 JSON 里出现嵌套对象比如{作者: {标签: 深度干货}}你根本分不清匹配到的是哪个层级、哪个字段。更关键的问题在性能层面。MySQL 存储 JSON 字段时内部使用二进制格式binary json存储并不是普通的文本字符串。当你用LIKE去匹配时MySQL 必须先把这个二进制 JSON反序列化成文本再执行字符串匹配。每行都要做一次反序列化500 万行就是 500 万次 JSON 解析这就是前面说的超时 30 秒的直接原因。注意即使你只在WHERE ext_info LIKE %keyword%前面加了其他等值条件过滤到 1000 行这 1000 行依然需要逐行反序列化 JSON。等值条件能缩小扫描范围但无法消除 JSON 解析的开销。从执行计划上也能看到问题。用EXPLAIN查看type是ALL也就是全表扫描rows估算直接接近全表数据量。就算你给 JSON 字段建了普通索引也没用——普通索引是按整个 JSON 文档的二进制值排序的但你查询条件是子字符串匹配完全用不上。所以在 MySQL 里处理 JSON 模糊查询第一步要接受一个事实LIKE 不是 JSON 查询的正确答案它只适用于你已经把 JSON 拆成独立字段的表结构。JSON 查询必须走专门的路子。3. 三种主流实现方案对比谁适合等值匹配谁适合真正模糊搜索针对 JSON 字段的查询MySQL 官方和社区主要演进出了三种方案我按推荐程度和适用场景列个表方案核心思路模糊匹配能力索引利用适用版本适用场景虚拟列 普通索引Generated Column用-把 JSON 字段提取成虚拟列再在虚拟列上建索引支持、LIKE prefix%但对%keyword%效果有限支持前缀匹配索引MySQL 5.7等值查询、前缀模糊查询、范围查询JSON_CONTAINS/JSON_SEARCH在 JSON 文档内部按路径或值搜索前者等值匹配后者支持子串匹配无法使用索引依赖全文索引替代MySQL 5.7 / 8.0小数据量、精确匹配 JSON 内数组等全文索引Full-Text Index对 JSON 字段内的文本内容做分词索引支持自然语言搜索、布尔搜索能匹配子串使用全文索引加速匹配MySQL 5.7需额外配置大数据量下对 JSON 文档内文本做搜索先看第一个方案虚拟列加索引。它的原理很简单MySQL 5.7 开始支持GENERATED COLUMN你可以把 JSON 中某个字段提取出来存储为一个虚拟列然后在这个虚拟列上建普通索引。查询时如果条件直接命中虚拟列优化器就能走索引。举个例子把ext_info-$.author_label提取成author_label_virtual列ALTER TABLE article ADD COLUMN author_label_virtual VARCHAR(50) GENERATED ALWAYS AS (ext_info-$.author_label) STORED, ADD INDEX idx_author_label (author_label_virtual);这里有一个关键选择虚拟列建STORED还是VIRTUAL。STORED会把值持久化到磁盘占空间但查询更快VIRTUAL不占额外存储但每次查询都要实时计算提取。对频繁查询的字段建议用STORED代价是多一点磁盘空间换来索引直接可用。对很少查询的字段用VIRTUAL更省空间。建好之后查询就变成SELECT * FROM article WHERE author_label_virtual 深度干货;这种写法完全走索引实测 500 万行数据下等值查询从 30 秒降到 10 毫秒级别效果立竿见影。但虚拟列方案对模糊查询的支持有限。如果你写WHERE author_label_virtual LIKE %干货%索引就失效了因为%干货%是中间匹配普通 BTREE 索引只能优化前缀匹配即干货%这种。实际业务中如果模糊查询的比例很高虚拟列并不能彻底解决问题。第二个方案JSON_CONTAINS和JSON_SEARCH。这两个函数是在 JSON 内部按结构搜索不需要预先提取虚拟列适合临时性查询。比如-- 精确匹配 JSON 对象中的某个值 SELECT * FROM article WHERE JSON_CONTAINS(ext_info, 深度干货, $.author_label); -- 在 JSON 文档中搜索包含指定字符串的值 SELECT * FROM article WHERE JSON_SEARCH(ext_info, one, %干货%) IS NOT NULL;JSON_SEARCH的one参数表示只要找到第一个匹配项就返回可以用all返回所有匹配路径。但这两个函数的问题一样无法利用索引必须全表扫描并逐行解析 JSON。在数据量过百万之后性能会断崖式下降只适合做数据修复、后台临时查询这种低频操作不适合放在用户请求的关键链路上。第三个方案全文索引才是真正为“模糊搜索”设计的。MySQL 5.7 开始支持对 JSON 字段建全文索引底层用 InnoDB 的全文索引机制对 JSON 内的文本内容分词建立索引。ALTER TABLE article ADD FULLTEXT INDEX ft_article_ext (ext_info);查询时用MATCH ... AGAINSTSELECT * FROM article WHERE MATCH(ext_info) AGAINST(深度干货 IN NATURAL LANGUAGE MODE);全文索引能处理分词、词频排序但有一个很烦的限制它默认按英文空格分词中文是连续的汉字串没有空格所以 MySQL 自带的分词器对中文的支持很差。比如你搜索“深度干货”MySQL 可能把它整个当成一个 token或者被默认最小 token 长度默认 3 个字符过滤掉。中文场景下必须启用 ngram 全文解析器ALTER TABLE article ADD FULLTEXT INDEX ft_article_ext (ext_info) WITH PARSER ngram;ngram 解析器会把中文按 N-gram 切分例如ngram_token_size2时会把“深度干货”切成“深度”、“度干”、“干货”三个二元组。搜索时也按相同方式切分这样就能匹配了。但 ngram 会显著增加索引体积且误匹配率偏高实际使用中需要调参和验证。把三种方案放在一起看实际情况里往往是组合拳核心查询字段用虚拟列加索引保证等值性能模糊搜索需求通过全文索引兜底JSON_SEARCH只用于低频管理操作。4. 虚拟列方案实战从设计到索引失效排查全记录前面说了虚拟列方案是首选但它不是建完就万事大吉。我把从设计到上线过程中踩过的坑完整记录一下。4.1 提取字段时的路径写法坑JSON 路径表达式经常写错尤其是字段名带下划线、嵌套层级深的时候。比如 JSON 是{ author: { name: 张三, label: 深度干货 } }提取name字段的路径是$.author.name但如果你用-而不是-拿到的值会带双引号虚拟列里存的可能是张三而不是张三。-- 错误带双引号 ADD COLUMN author_name VARCHAR(50) GENERATED ALWAYS AS (ext_info-$.author.name) STORED; -- 正确不带双引号去掉外层引号 ADD COLUMN author_name VARCHAR(50) GENERATED ALWAYS AS (ext_info-$.author.name) STORED;这个坑非常隐蔽因为查询WHERE author_name 张三时如果列里存的是张三查询结果为空但不会报错。排查方式很简单建完列之后先SELECT author_name FROM article LIMIT 5看一下实际值确认有没有多余的双引号。4.2 虚拟列类型尽量和实际数据对齐虚拟列定义时如果不显式声明类型MySQL 会按 JSON 值的类型推断但很多场景下推断结果不符合预期。比如 JSON 里存的是数字score: 95虚拟列建VARCHAR类型时查询WHERE score 90会走字符串比较结果可能出乎意料——95 90按字符串比较是成立的但100 90字符串比较反而不成立。正确做法是显式指定类型ADD COLUMN score_virtual DECIMAL(5,2) GENERATED ALWAYS AS (ext_info-$.score) STORED;这样 MySQL 会做隐式类型转换数字比较才符合直觉。类型尽量和业务字段的真实语义对齐不要偷懒全用 VARCHAR。4.3 索引失效的一个高频场景虚拟列加索引后查询却没用上索引的情况很多最常见的是查询条件里写了JSON_EXTRACT(ext_info, $.author_label) 深度干货而不是author_label_virtual 深度干货。前者是直接在 JSON 上执行函数优化器认为无法使用虚拟列索引直接走全表扫描后者才是命中虚拟列。用EXPLAIN一眼就能看出来EXPLAIN SELECT * FROM article WHERE author_label_virtual 深度干货;正常情况type应该是ref或const。如果看到ALL检查你 SQL 里是不是直接写了 JSON 表达式而不是虚拟列。4.4 前缀模糊查询的索引利用技巧虚拟列虽然对%keyword%无能为力但对keyword%这种前缀查询是可以走索引的。MySQL 的 BTREE 索引天然支持范围扫描LIKE 干货%会被优化成 干货 AND 干饮按排序规则计算上界的形式走索引效率很高。所以如果业务里的“模糊查询”其实更多是“前缀搜索”比如搜索作者名、文章标题以某关键字开头虚拟列方案完全够用不需要上全文索引。5. JSON_SEARCH 的正确使用姿势和性能边界JSON_SEARCH是 MySQL 5.7 引入的 JSON 搜索函数很多教程一笔带过实际用起来有不少讲究。5.1 三种搜索类型JSON_SEARCH(json_doc, one_or_all, search_str, [escape_char], [path] ...)函数的核心参数是第二个参数它决定返回什么参数值含义返回值one找到第一个匹配项就返回该值的完整 JSON 路径如$.author.labelall返回所有匹配项所有匹配路径组成的 JSON 数组如[$.author.label, $.tags[0]]第三个参数search_str支持通配符%匹配任意多个字符_匹配单个字符。需要转义时用第四个参数指定转义字符默认是\。举个例子-- 在 ext_info 中任意位置搜索包含干货的值返回第一个匹配路径 SELECT JSON_SEARCH(ext_info, one, %干货%) AS matched_path FROM article WHERE id 100;如果匹配到了返回值是类似$.author.label这样的路径如果没匹配到返回NULL。所以IS NOT NULL就能当布尔判断用。5.2 性能边界实测我这边的测试表500 万行JSON 字段平均约 1.2KBJSON_SEARCH单次查询耗时在 700ms 到 3s 之间波动具体取决于 JSON 大小和目标字符串的分布。对比虚拟列加索引的 10ms差距是两个数量级。所以JSON_SEARCH只建议用在以下场景数据量在十万行以内查询频率低比如后台手动检索、定时任务清洗数据无法预知 JSON 内部结构需要按值全局搜索的场景反过来如果某个 JSON 字段的业务查询频率很高任何时候第一个想到的都应该是把该字段提取成虚拟列而不是依赖JSON_SEARCH。5.3 一个容易混淆的坑JSON_CONTAINS 的相等语义JSON_CONTAINS(target, candidate, path)检查目标 JSON 是否包含指定的候选 JSON。这里候选参数必须是一个合法的 JSON 值字符串要带引号-- 错误写法 WHERE JSON_CONTAINS(ext_info, 深度干货, $.author.label); -- 正确写法候选值必须是 JSON 字符串格式 WHERE JSON_CONTAINS(ext_info, 深度干货, $.author.label);这个引号问题也是经典报错点。JSON_CONTAINS做的是精确匹配不是子串匹配。如果 JSON 里存的是“深度干货|热点”JSON_CONTAINS无法匹配到“干货”必须用JSON_SEARCH才行。这两者的定位完全不同别混用。6. MySQL 8.0 的新选择多值索引对 JSON 数组的优化如果你用的是 MySQL 8.0还有一个利器多值索引Multi-Valued Index。它在 MySQL 8.0.17 引入专门解决 JSON 数组元素查询的索引问题。场景是这样的ext_info里有一个标签数组{ tags: [深度, 干货, MySQL, 性能优化] }之前的方案里想查出所有包含“干货”标签的记录用JSON_CONTAINS(ext_info-$.tags, 干货)是能做等值匹配但没法走索引。有了多值索引可以把tags数组里的每个元素都当成一个索引条目。建索引的语法ALTER TABLE article ADD INDEX idx_tags ((CAST(ext_info-$.tags AS UNSIGNED ARRAY)));注意多值索引的表达式必须用CAST(... AS ... ARRAY)包一层。上面例子是数值数组如果是字符串数组用CHAR ARRAYALTER TABLE article ADD INDEX idx_tags ((CAST(ext_info-$.tags AS CHAR(20) ARRAY)));查询时用JSON_CONTAINS或者MEMBER OFMySQL 就能使用多值索引-- 方式一MEMBER OF用途更直观 SELECT * FROM article WHERE 干货 MEMBER OF (ext_info-$.tags); -- 方式二JSON_CONTAINS同样可以命中多值索引 SELECT * FROM article WHERE JSON_CONTAINS(ext_info-$.tags, 干货);实测下来在百万级数据上查 JSON 数组包含关系多值索引把原本 2 秒级别的JSON_CONTAINS查询降到了 20 毫秒以内。这是目前 MySQL 处理 JSON 数组等值匹配的最优解。但多值索引同样有一个限制它只支持等值匹配和部分范围匹配不支持子串模糊匹配。想查tags中“干”开头的内容依然要回到全文索引或者JSON_SEARCH。7. 中文模糊查询的硬骨头ngram 全文索引的配置与调优中国的业务场景几乎绕不开中文搜索而 MySQL 默认的全文分词器对中文支持极差ngram 插件是唯一可靠方案。7.1 启用 ngram 解析器MySQL 5.7.6 之后内置了 ngram 全文解析器无需额外安装插件建索引时指定即可ALTER TABLE article ADD FULLTEXT INDEX ft_ext (ext_info) WITH PARSER ngram;也可以在建表时指定CREATE FULLTEXT INDEX ft_ext ON article(ext_info) WITH PARSER ngram;7.2 ngram_token_size 的取舍ngram 的核心参数是ngram_token_size表示分词的最小单元长度。默认值是 2也就是说“深度干货”会被切为“深度”、“度干”、“干货”三个 bigram。这个参数不能在建索引时动态指定必须在 MySQL 配置文件my.cnf中设置后重启[mysqld] ngram_token_size2不同取值的影响很大token_size分词示例优点缺点1深、度、干、货匹配粒度最细单字也能搜索引体积最大误匹配率高2深度、度干、干货平衡方案的默认值双字词之间可能出现无用二元组3深度干、度干货索引体积小精确度高低于 3 个字符的关键词搜不到我自己的实践经验是文本内容偏短小于 50 字时用 2偏长文本用 3减少误匹配。但token_size是全局参数一个实例只能设置一个值所以要在部署前想清楚主要业务的平均文本长度。注意修改ngram_token_size后已有的全文索引必须删除重建否则不会生效。这又是一个容易踩的坑。7.3 全文索引的查询写法全文索引查询用MATCH ... AGAINST支持三种模式自然语言模式IN NATURAL LANGUAGE MODE按相关度排序布尔模式IN BOOLEAN MODE支持、-、*等操作符查询扩展模式先用自然语言查结果再根据结果中的词汇做二次扩展实际业务中布尔模式最灵活比如要求包含“干货”但不包含“注水”SELECT * FROM article WHERE MATCH(ext_info) AGAINST(干货 -注水 IN BOOLEAN MODE);布尔模式还支持前缀匹配用通配符*SELECT * FROM article WHERE MATCH(ext_info) AGAINST(干* IN BOOLEAN MODE);7.4 全文索引与虚拟列索引的组合策略全文索引无法替代等值查询虚拟列索引也无法解决模糊搜索二者是互补关系。我最终的线上方案是频繁等值查询的 JSON 字段状态、标签、类型提取虚拟列并建 BTREE 索引需要模糊搜索的文本字段用 ngram 全文索引低频全局搜索用JSON_SEARCH兜底涉及 JSON 数组包含关系的用多值索引这个组合在 500 万行数据上跑等值查询稳定在 10ms模糊搜索在 100ms 到 300ms 之间完全满足业务要求。8. 如果 JSON 字段成了性能瓶颈该考虑反范式设计了最后说一个很多人不爱听但必须讲的观点JSON 字段不是万能良药它是在关系模型和文档模型之间的妥协。当你在 JSON 里频繁做模糊查询时与其纠结索引方案不如考虑把高频查询字段彻底从 JSON 里提出来做成普通列。这跟“虚拟列”不一样虚拟列是逻辑上提取、物理上还是要走 JSON 解析STORED 虚拟列会持久化但数据冗余在表里而“反范式设计”是业务上直接把字段放到表里单独建索引、单独维护。怎么判断该不该拆我的经验标准有三个查询频率高这个 JSON 字段被查询的次数远高于写入次数字段独立性高它不和其他 JSON 字段强耦合单独存在也不违和模糊/等值查询是刚需不是偶尔查一次而是核心筛选条件满足这三个条件就该考虑新建独立列。比如前文的author_label完全可以设计成article表的独立字段查询、索引、统计报表都方便很多JSON 里保留一份用于展示扩展信息。当然反范式设计也有代价写入时需要同时更新 JSON 和独立列维护逻辑变复杂如果 JSON 是文档快照性质不能改变语义拆出去反而破坏结构。所以这属于“结构性优化”需要评估后再动。9. 实操总结不同业务场景的最终选型建议把上面的分析浓缩成一张决策表方便直接套用业务需求推荐方案最大数据量参考主要限制JSON 等值匹配字符串/数值虚拟列 BTREE 索引千万级以上需要预先知道查询路径JSON 数组元素包含多值索引MySQL 8.0千万级仅支持等值/部分范围中文/英文全文模糊搜索ngram 全文索引百万到千万级需要调ngram_token_size误匹配需处理低频全 JSON 内容搜索JSON_SEARCH十万级以内全表扫描不适合大表高频高频且字段独立直接拆成独立列反范式无明确上限写逻辑复杂需权衡最后分享一个排查心法遇到 JSON 查询慢第一步别急着调索引先确认查询条件能不能改成等值、能不能命中虚拟列。只要把 80% 的高频查询从全表扫描转向索引扫描系统的性能问题基本就解决了一大半。剩下的模糊搜索需求按数据量量级再决定上 ngram 还是保持低频兜底。就我个人经验而言MySQL 的 JSON 功能这几年迭代很快8.0 的多值索引、以及未来版本对 JSON 的持续优化让“在关系数据库里用文档模型”这件事变得越来越顺手。但工具再强设计上的权衡始终躲不掉——JSON 字段里到底放什么、不放大什么决定了三年后你是轻松加个索引还是痛苦重构表结构。这个选择的优先级比任何查询优化技巧都高。
