在现代后端开发中，为了应对多变的业务需求（比如电商商品的动态属性、用户自定义配置、IoT 设备上报的异构数据），我们经常会在 MySQL 表中设计一个JSON类型的字段。
存的时候很方便，不需要频繁改表结构（DDL）。但问题来了：当你需要根据 JSON 里面的某个 Key 进行搜索时，性能会极其低下。
比如SELECT * FROM products WHERE attributes->'$.color' = 'Red'。
这条 SQL 会导致 MySQL 进行全表扫描，并实时解析每一行的 JSON 字符串。当数据量达到百万级时，接口直接超时。
别担心，MySQL 5.7+ 引入的虚拟列 (Generated Columns)结合索引，就是解决这个问题的“屠龙刀”。
1. 核心原理：把 JSON 里的值“提取”出来建索引
MySQL 的 B+ 树索引是基于具体的列建立的，它无法直接深入到 JSON 对象的内部结构中去建立索引（虽然 MySQL 8.0 引入了 Multi-Valued Indexes 用于数组，但常规 Key-Value 还是依赖虚拟列）。
解决方案逻辑：
- 创建虚拟列：告诉 MySQL，“我想要 JSON 里的
color字段，你帮我自动提取出来，作为一个假的（虚拟的）列放在表里”。 - 建立索引：对这个“虚拟列”建立标准的 B+ 树索引。
- 查询加速：当你查询这个虚拟列（或对应的 JSON 路径）时，MySQL 直接走索引，速度瞬间提升至 O(\log N)。
什么是“虚拟” (VIRTUAL)？
- 不占空间：虚拟列的数据不存储在磁盘上，它是实时计算的（元数据）。
- 索引占空间：但是，加在虚拟列上的索引是实实在在存储在磁盘上的 B+ 树。
2. 实战演练：从全表扫描到索引查找
假设我们有一张商品表 (products)，其中features是 JSON 字段：
CREATETABLEproducts(idINTPRIMARYKEYAUTO_INCREMENT,nameVARCHAR(100),features JSON-- 存储如 {"color": "Red", "size": "XL", "weight": "1kg"});阶段一：痛苦的慢查询
我们要找所有“红色”的商品：
SELECT*FROMproductsWHEREfeatures->'$.color'='Red';- Explain 结果:
type: ALL(全表扫描)。 - 耗时:假设有 100 万行数据，耗时可能超过 2 秒。
阶段二：引入虚拟列与索引
我们不需要修改业务代码插入逻辑，只需要执行 DDL：
-- 1. 添加虚拟列 `v_color`-- GENERATED ALWAYS AS (...) 定义提取逻辑-- VIRTUAL 表示这是虚拟列，不占用数据行空间ALTERTABLEproductsADDCOLUMNv_colorVARCHAR(20)GENERATED ALWAYSAS(features->>'$.color')VIRTUAL;-- 2. 给虚拟列加索引CREATEINDEXidx_v_colorONproducts(v_color);(注：->>操作符等同于提取 JSON 值并去除引号，非常适合用于索引)
阶段三：极速查询
现在我们再查一次：
-- 方式 A：直接查虚拟列 (推荐，最明确)SELECT*FROMproductsWHEREv_color='Red';-- 方式 B：查 JSON 路径 (MySQL 5.7+ 优化器通常能自动识别并走索引)SELECT*FROMproductsWHEREfeatures->>'$.color'='Red';- Explain 结果:
type: ref,key: idx_v_color。 - 耗时:毫秒级（ms）。
3. 三大实战场景
场景一：电商多规格商品 (SKU)
电商系统中，不同品类的商品属性差异巨大。手机有“屏幕尺寸”、“内存”，衣服有“颜色”、“尺码”。
- 传统做法：建立庞大的属性表 (
EAV模型)，查询需要多次JOIN，极慢。 - JSON 做法：属性存 JSON。
- 优化：针对高频筛选属性（如“颜色”、“品牌”），建立虚拟列索引。冷门属性不建索引。
场景二：物联网 (IoT) 设备上报
不同型号的传感器上报的数据格式不同。A 型号上报{temp: 20, humid: 40}，B 型号上报{voltage: 220, status: "ok"}。
- 需求：快速查询所有
status: "error"的设备日志。 - 优化：
ADD COLUMN v_status VARCHAR(10) GENERATED ALWAYS AS (data->>'$.status') VIRTUAL;+INDEX。
场景三：用户自定义标签 (Tags)
CRM 系统中，运营人员会给用户打各种标签。
- 数据：
tags: {"vip": true, "churn_risk": "high"} - 需求：找出所有高流失风险的用户。
- 优化：利用虚拟列提取风险等级并建立索引。
4. 进阶：VIRTUAL vs STORED
在定义生成列时，有两种模式可选：
VIRTUAL(默认):
- 列数据不存磁盘，每次读取时实时计算（除非走了索引）。
- 写入快（插入数据时不需要写这列的数据）。
- 适合：绝大多数场景，特别是需要通过索引加速查询的场景。
STORED:
- 列数据写入磁盘，和普通列一样。
- 读取快（不需要实时计算），但写入慢（多一次写磁盘 I/O）。
- 适合：提取逻辑非常复杂（耗 CPU），且读多写少的场景。
5. 总结
不要因为“JSON 查询慢”就放弃 JSON 的灵活性，也不要为了性能强行把所有字段拆成普通列。
MySQL 的JSON + 虚拟列 + 索引方案，是一种完美的**“折中艺术”**：
- 它保留了 NoSQL 的灵活（Schemaless）。
- 它拥有了 SQL 的强劲性能（B+ Tree）。
