大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
前两天刷到一个面试题：
“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字符串有本质区别：
| 对比维度 | VARCHAR/TEXT存JSON | 原生JSON类型 |
|---|---|---|
| 存储格式 | 纯文本字符串 | 优化的二进制格式 |
| 数据验证 | 不验证，脏数据也能进 | 自动验证，非法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。
八、小结
| 坑 | 症状 | 解决方案 |
|---|---|---|
| 坑1：函数查询 | 全表扫描 | 虚拟列+B-Tree索引 / 函数索引 |
| 坑2：数组查询 | 包含查询无法索引 | 多值索引 + MEMBER OF |
| 坑3：字符集不一致 | 索引“假装”在工作 | 统一字符集，直接用虚拟列名 |
| 坑4：JSON过大 | 外部存储，额外IO | 保持<1KB，超10KB拆分 |
| 坑5：频繁更新 | 页分裂，空间膨胀 | 应用层改完再写回，定期OPTIMIZE |
JSON类型出来快10年了，不是不能用，是要用对地方、建对索引。
下次往表里加JSON字段之前，先问自己三个问题：
这个字段会频繁作为查询条件吗？→ 会就建虚拟列索引
这个字段会频繁更新吗？→ 会就考虑拆出去
这个字段需要约束吗？→ 需要就走普通列
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
