还在用MySQL 5.7？JSON查询从850ms到3ms，升级+虚拟列索引就够了

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

前两天刷到一个面试题：

“MySQL的JSON字段支持索引吗？”

下面一堆回答：“支持啊，MySQL 8.0开始支持JSON索引了。”

这个回答对了一半。

准确地说：MySQL 8.0引入了多值索引（Multi-Valued Index），但它是为JSON数组设计的。对于普通JSON字段的单个属性查询，你需要的是虚拟列索引，而不是直接在JSON字段上建索引。

如果搞混了这两者，就会遇到这样的情况：明明“建了索引”，但查询还是慢到怀疑人生。

今天不讲面试题，直接说5个生产环境里真实踩过的坑，以及对应怎么解决。

一、MySQL JSON的前世今生

MySQL 5.7.8开始引入原生JSON类型，和用VARCHAR/TEXT存JSON字符串有本质区别：

二、坑1：直接对JSON字段用函数查询

症状：用JSON_EXTRACT、->、->>在WHERE条件里查JSON内部字段，数据量一大就慢。

原理：MySQL的B+树索引不识别JSON路径表达式。直接对JSON字段用JSON_EXTRACT查询，优化器只能走全表扫描。

正确方法：用虚拟列（Generated Column）+ B-Tree索引。

MySQL 5.7：

-- 创建虚拟列
ALTER TABLE user_events 
ADD COLUMN event_type VARCHAR(32) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(event_data, '$.event_type'))) VIRTUAL;

-- 在虚拟列上建索引
CREATE INDEX idx_event_type ON user_events(event_type);

MySQL 8.0+更简洁，支持直接创建函数索引：

ALTER TABLE user_events 
ADD INDEX idx_event_type((CAST(event_data->>'$.event_type' AS CHAR(32))));

实测数据：10万条数据，JSON_EXTRACT全表扫描约850ms，虚拟列+索引约3ms——提升280倍。

三、坑2：JSON数组查询索引用不上

症状：JSON里存了数组，想查“数组中包含某个值”的记录，怎么写都慢。

原理：虚拟列只能提取单个值（如$.tags[0]），无法处理数组的“包含”查询。

正确方法：MySQL 8.0.17开始支持多值索引（Multi-Valued Index） ，把JSON数组的每个元素作为独立的索引项存入B+树。

建索引：

ALTER TABLE t_config 
ADD INDEX idx_phone((CAST(extras->'$.phone' AS UNSIGNED ARRAY)));

查询：

-- 推荐写法
SELECT * FROM t_config WHERE '13800138000' MEMBER OF(extras->'$.phone');

-- 或
SELECT * FROM t_config 
WHERE JSON_CONTAINS(extras->'$.phone', '13800138000');

效果：多值索引让JSON数组查询从全表扫描变成索引范围扫描，性能提升上千倍。

注意：多值索引只能作为联合索引的最后一列。

四、坑3：字符集和排序规则不一致

症状：虚拟列建了、索引也建了，EXPLAIN显示用了索引，但查询还是慢。

原理：JSON_EXTRACT返回的字符串排序规则和虚拟列定义的排序规则不一致时，MySQL无法使用索引。

正确方法：

方案一：统一字符集和排序规则
建表时统一用utf8mb4和utf8mb4_0900_ai_ci（或utf8mb4_general_ci）。

方案二：查询时直接用虚拟列名，别用JSON_EXTRACT

-- ✅ 正确：直接用虚拟列
SELECT * FROM user_events WHERE event_type = 'page_view';

-- ❌ 错误：还在用JSON_EXTRACT
SELECT * FROM user_events 
WHERE JSON_EXTRACT(event_data, '$.event_type') = 'page_view';

排查工具：

SHOW FULL COLUMNS FROM user_events LIKE 'event_type';
SELECT CHARSET(JSON_EXTRACT(event_data, '$.event_type')) FROM user_events LIMIT 1;

五、坑4：JSON过大导致外部存储

症状：JSON字段不算太大（几KB），但查询越来越慢。

原理：MySQL存储JSON时，小于1KB的JSON内联存储在行记录中，超过这个阈值则存储在外部页，每次查询需要额外的磁盘IO。

正确方法：

• 保持JSON文档在1KB以下，能内联就内联

• 单条JSON超过10KB不拆分，会导致临时表溢出磁盘，查询性能断崖式下降

• 定期审查JSON字段大小，该拆就拆

• 对频繁查询的JSON路径，一定建虚拟列索引

六、坑5：频繁更新JSON导致页分裂

症状：JSON字段频繁更新，表越来越大，性能持续下降。

原理：JSON_SET、JSON_REPLACE等更新操作虽然支持局部修改（只重写变化的部分），但如果修改导致JSON文档变长，可能触发InnoDB的页分裂——数据页一分为二，产生存储碎片，空间利用率下降。

正确方法：

• 尽量避免频繁的小幅更新导致JSON不断变长

• 如果更新频率高，考虑先读取、在应用层修改对象、再全量写回，减少页分裂次数

• 定期执行OPTIMIZE TABLE回收碎片空间

• 高更新频率的核心字段不要塞JSON，用普通列

七、JSON使用的决策框架

什么场景适合用JSON：

✅ 动态属性/扩展字段（不同记录字段差异大，无法预定义列）
✅ 配置信息（结构相对稳定，查询不频繁）
✅ 缓存数据（从其他系统同步过来的半结构化数据）

什么场景不适合用JSON：

❌ 需要关联查询的核心业务字段 → 拆成普通列+外键
❌ 需要NOT NULL、UNIQUE等约束的字段 → 走普通列
❌ 频繁作为WHERE、ORDER BY、JOIN条件的字段 → 走普通列+索引
❌ 频繁更新的字段 → 走普通列，避免页分裂

一句话决策规则：核心数据走普通列，扩展属性才用JSON。

八、小结

JSON类型出来快10年了，不是不能用，是要用对地方、建对索引。

下次往表里加JSON字段之前，先问自己三个问题：

1. 这个字段会频繁作为查询条件吗？→ 会就建虚拟列索引

2. 这个字段会频繁更新吗？→ 会就考虑拆出去

3. 这个字段需要约束吗？→ 需要就走普通列

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~