企业官网建设流程全解析

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 字段的同学会写出这样的 SQL：

SELECT * FROM article WHERE ext_info LIKE '%深度干货%';

这条 SQL 在数据量小的时候能跑出结果，但有两个致命问题：

第一，它会把 JSON 的键名也匹配进去。比如你存的 JSON 是{"author_label": "深度干货", "template_id": "author_deep"},那author_deep里面包含deep，LIKE '%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 官方和社区主要演进出了三种方案，我按推荐程度和适用场景列个表：

先看第一个方案，虚拟列加索引。它的原理很简单：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 ... AGAINST：

SELECT * 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_size=2时会把“深度干货”切成“深度”、“度干”、“干货”三个二元组。搜索时也按相同方式切分，这样就能匹配了。但 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] ...)函数的核心参数是第二个参数，它决定返回什么：

第三个参数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.2KB），JSON_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 ARRAY：

ALTER TABLE article ADD INDEX idx_tags ((CAST(ext_info->'$.tags' AS CHAR(20) ARRAY)));

查询时用JSON_CONTAINS或者MEMBER OF，MySQL 就能使用多值索引：

-- 方式一：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_size=2

不同取值的影响很大：

我自己的实践经验是：文本内容偏短（小于 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 全文索引与虚拟列索引的组合策略

全文索引无法替代等值查询，虚拟列索引也无法解决模糊搜索，二者是互补关系。我最终的线上方案是：

1. 频繁等值查询的 JSON 字段（状态、标签、类型），提取虚拟列并建 BTREE 索引
2. 需要模糊搜索的文本字段，用 ngram 全文索引
3. 低频全局搜索用JSON_SEARCH兜底
4. 涉及 JSON 数组包含关系的，用多值索引

这个组合在 500 万行数据上跑，等值查询稳定在 10ms，模糊搜索在 100ms 到 300ms 之间，完全满足业务要求。

8. 如果 JSON 字段成了性能瓶颈：该考虑反范式设计了

最后说一个很多人不爱听但必须讲的观点：JSON 字段不是万能良药，它是在关系模型和文档模型之间的妥协。当你在 JSON 里频繁做模糊查询时，与其纠结索引方案，不如考虑把高频查询字段彻底从 JSON 里提出来，做成普通列。

这跟“虚拟列”不一样，虚拟列是逻辑上提取、物理上还是要走 JSON 解析（STORED 虚拟列会持久化，但数据冗余在表里）；而“反范式设计”是业务上直接把字段放到表里，单独建索引、单独维护。

怎么判断该不该拆？我的经验标准有三个：

1. 查询频率高：这个 JSON 字段被查询的次数远高于写入次数
2. 字段独立性高：它不和其他 JSON 字段强耦合，单独存在也不违和
3. 模糊/等值查询是刚需：不是偶尔查一次，而是核心筛选条件

满足这三个条件，就该考虑新建独立列。比如前文的author_label，完全可以设计成article表的独立字段，查询、索引、统计报表都方便很多，JSON 里保留一份用于展示扩展信息。

当然，反范式设计也有代价：写入时需要同时更新 JSON 和独立列，维护逻辑变复杂；如果 JSON 是文档快照性质（不能改变语义），拆出去反而破坏结构。所以这属于“结构性优化”，需要评估后再动。

9. 实操总结：不同业务场景的最终选型建议

把上面的分析浓缩成一张决策表，方便直接套用：

最后分享一个排查心法：遇到 JSON 查询慢，第一步别急着调索引，先确认查询条件能不能改成等值、能不能命中虚拟列。只要把 80% 的高频查询从全表扫描转向索引扫描，系统的性能问题基本就解决了一大半。剩下的模糊搜索需求，按数据量量级再决定上 ngram 还是保持低频兜底。

就我个人经验而言，MySQL 的 JSON 功能这几年迭代很快，8.0 的多值索引、以及未来版本对 JSON 的持续优化，让“在关系数据库里用文档模型”这件事变得越来越顺手。但工具再强，设计上的权衡始终躲不掉——JSON 字段里到底放什么、不放大什么，决定了三年后你是轻松加个索引还是痛苦重构表结构。这个选择的优先级，比任何查询优化技巧都高。