MySQL 5.7 vs 8.0：JSON 索引实战对比，你真的用对了吗？

随着业务系统越来越灵活，越来越多开发者选择在 MySQL 中使用 JSON 类型存储半结构化数据。但你知道吗？MySQL 5.7 和 8.0 对 JSON 的索引支持存在重大差异！本文带你通过真实案例，彻底搞懂两者的区别与最佳实践。

背景：为什么用 JSON？

在用户画像、日志记录、配置项管理、动态表单等场景中，数据结构经常频繁变更。如果每次变更都要修改表结构，不仅成本高，还容易出错。
于是，JSON 类型成了很多团队的“灵活存储”首选：

1
2
3
4
CREATE TABLE user_profiles (
    id INT PRIMARY KEY,
    data JSON
);

但问题来了：如何高效查询 JSON 字段中的某个值？

MySQL 5.7 的 JSON 索引：虚拟列 + 普通索引

MySQL 5.7 虽然支持 JSON 类型，但 不支持直接对 JSON 字段创建索引。
解决方案是：使用“虚拟生成列（Virtual Generated Column）”提取 JSON 路径，再对该列建索引。

案例：查询用户所在城市

假设 JSON 结构如下：

1
2
3
4
5
6
7
{
  "name": "张三",
  "address": {
    "city": "杭州",
    "district": "西湖区"
  }
}

我们要按 city 查询用户。

步骤 1：添加虚拟列

1
2
3
ALTER TABLE user_profiles 
ADD COLUMN city VARCHAR(100) 
GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.address.city'))) VIRTUAL;

JSON_UNQUOTE 用于去掉 JSON 字符串值的双引号。

步骤 2：为虚拟列建索引

1
CREATE INDEX idx_city ON user_profiles(city);

步骤 3：查询（可命中索引）

1
SELECT * FROM user_profiles WHERE city = '杭州';

✅ 有效，但需额外维护虚拟列，表结构变复杂。

MySQL 8.0 的革新：原生 JSON 索引（函数索引）

MySQL 8.0 引入了 函数索引（Functional Index），允许直接对表达式或函数结果建索引。
更重要的是，8.0.17+ 支持直接对 JSON 路径创建索引，无需虚拟列！

同样需求：查询用户所在城市

直接创建 JSON 路径索引（8.0.17+）

1
CREATE INDEX idx_city ON user_profiles ((CAST(data->'$.address.city' AS CHAR(100))));

注意：data->'$.xxx' 是 JSON_EXTRACT(data, '$.xxx') 的简写。
CAST(... AS CHAR(100)) 是必须的，因为函数索引要求结果是标量类型且长度确定。

查询（同样可命中索引）

1
2
SELECT * FROM user_profiles 
WHERE data->>'$.address.city' = '杭州';

->> 是 JSON_UNQUOTE(JSON_EXTRACT(...)) 的简写，直接返回字符串。

✅ 无需额外列！表结构更干净，维护成本更低。

性能对比（实测参考）

在百万级数据测试中：

方案 查询耗时（ms） 是否走索引 表结构复杂度
5.7 虚拟列索引 ~15ms ✅ 较高（需维护列）
8.0 函数索引 ~12ms ✅ 低（无额外列）
无索引全表扫描 ~1200ms ❌ 低

注：实际性能受数据分布、服务器配置等影响，但趋势一致。

迁移建议

  • 若你仍在使用 MySQL 5.7：虚拟列方案稳定可靠，但需谨慎管理列与索引的一致性。
  • 升级到 MySQL 8.0：强烈推荐使用原生 JSON 索引，简化设计，提升可维护性。
  • 注意兼容性：8.0 的 CAST(... AS CHAR(N)) 中 N 要足够大，避免截断导致索引失效。

小结

特性 MySQL 5.7 MySQL 8.0
JSON 类型支持 ✅ ✅
JSON 路径查询 ✅ ✅
原生 JSON 索引 ❌ ✅（8.0.17+）
实现方式 虚拟列 + 普通索引 函数索引（直接索引表达式）
维护成本 较高 低

灵活 ≠ 牺牲性能。正确使用 JSON 索引，既能享受 schema-free 的自由，又不失查询效率。

🎁 互动话题
你们在项目中用过 MySQL 的 JSON 类型吗？遇到过哪些坑？欢迎在评论区分享！

🔔 关注我们，获取更多数据库、后端开发实战干货！