用 JSON 列存储扩展字段后，如何优雅地支持高频查询？MySQL 虚拟列 + 联合索引实战指南

1. 引言：当业务需要“无限”扩展字段

在 ToB 系统中，客户经常有各种稀奇古怪的自定义字段需求。今天 A 客户要存“鞋子尺码”，明天 B 客户要存“喜欢的颜色”，后天 C 客户又加个“紧急程度”。如果每来一个新需求就改表加列，不出半年你的表就会变成“千列奇观”。更可怕的是，每次加列都要发布新版本，DBA 也会被你烦死。

于是我们选择了JSON 列——在表中加一个extend_infoJSON 字段，把所有客户自定义属性都塞进去。这样，无论客户想要多少字段，我们都能轻松应对。但新的问题来了：客户经常要根据他们的自定义字段查询，比如“我要查所有鞋子尺码 42 的订单”，直接对 JSON 字段查询会触发全表扫描，性能堪忧。

本文将介绍 MySQL 5.7+ 提供的虚拟列（Generated Column）技术，结合B+ 树索引，优雅地解决 JSON 字段的查询性能问题，并进一步扩展到虚拟列与真实列的联合索引，让你的扩展字段查询也能“嗖嗖”快。

2. 方案回顾：JSON 列存储的优点与痛点

2.1 为什么选 JSON 列？

CREATETABLEpo_order(idBIGINTPRIMARYKEY,po_noVARCHAR(50)NOTNULL,-- 真实列，如订单号customer_idBIGINTNOTNULL,-- 真实列extend_info JSON-- 自定义扩展字段);

优点：

• 无限扩展：客户加字段，只需要修改 JSON 内容，无需改表结构。
• 语义清晰：JSON 里的 key 直接表达业务含义，比如{"shoe_size": 42, "color": "red"}，排障时一目了然。
• 代码友好：应用层可以用 Map 或嵌套对象直接映射。

2.2 痛点：JSON 内部字段无法直接使用索引

假设客户经常根据extend_info里的customer_po_no（客户内部单号）查询：

SELECT*FROMpo_orderWHEREextend_info->>'$.customer_po_no'='PO123456';

extend_info本身是一个长文本字段，上述查询实际上会对每一行的 JSON 进行解析，然后比较。如果表很大，这就是全表扫描，性能灾难。

3. 虚拟列：把 JSON 字段“抽”出来变成真实列

MySQL 5.7 引入了生成列（Generated Column），也叫虚拟列。它可以把 JSON 中的某个属性“提取”出来，变成一个虚拟的列。这个列的值不是真实存储的，而是通过计算得到的，但我们可以像普通列一样查询它。

3.1 创建虚拟列提取 JSON 值

ALTERTABLEpo_orderADDCOLUMNv_customer_po_noVARCHAR(50)GENERATED ALWAYSAS(extend_info->>'$.customer_po_no')VIRTUAL;

解释：

• ->>是 MySQL 的 JSON 提取运算符，extend_info->>'$.customer_po_no'会提取 JSON 中customer_po_no的值并返回字符串。
• GENERATED ALWAYS AS (...)定义这是一个生成列。
• VIRTUAL表示该列不占用物理存储空间，每次读取时动态计算（也可以选择STORED实际存储，后面会讨论）。

3.2 虚拟列的类型

虚拟列的数据类型必须与提取的表达式兼容。例如提取字符串，就用VARCHAR；提取整数，可以用BIGINT。

3.3 VIRTUAL vs STORED 如何选择？

• VIRTUAL：不占用物理存储，每次读取时计算。适合读取频率一般、计算开销小的场景。
• STORED：实际存储计算后的值，占用额外空间，但读取时无需计算。适合读取极其频繁，或计算开销大的场景。

对于 JSON 提取，一般用VIRTUAL即可，因为提取开销很小。如果这个虚拟列被大量查询，而且你愿意用空间换时间，可以考虑STORED。

4. 虚拟列索引：让 JSON 查询走 B+ 树

有了虚拟列，我们就可以像普通列一样在上面建索引了！

4.1 在虚拟列上创建索引

CREATEINDEXidx_v_customer_po_noONpo_order(v_customer_po_no);

现在，查询可以自动使用这个索引：

-- 注意：查询条件要用虚拟列，而不是直接对 JSON 用 ->>SELECT*FROMpo_orderWHEREv_customer_po_no='PO123456';

MySQL 优化器会识别到v_customer_po_no上有索引，从而使用 B+ 树索引快速定位。底层的 JSON 数据依然优雅，但查询性能已与传统列无异。

4.2 内部原理

虚拟列索引本质上是一个普通索引，索引的键值来自虚拟列的计算结果。当插入或更新数据时，MySQL 会自动计算虚拟列的值，并维护索引。因此，对 JSON 内部字段的查询就变成了索引查找，避免了全表扫描。

4.3 JSON 字段不好含指定创建虚拟列和索引的键

在添加虚拟列v_customer_po_no时，如果某行数据的 JSON 字段extend_info中不包含customer_po_no键，那么使用extend_info->>'$.customer_po_no'提取的值会返回NULL。因此，该虚拟列在这一行上的值就是NULL。

索引情况

当你在虚拟列上创建索引时，MySQL 的 B+ 树索引是允许存储 NULL 值的。所有 NULL 值在索引中会被集中存储（通常放在最前面或最后面）。因此：

• 对于常规查询WHERE v_customer_po_no = 'PO123456'，索引只会扫描匹配 ‘PO123456’ 的键值，NULL 值的行不会被涉及，查询依然能高效利用索引。
• 对于查询WHERE v_customer_po_no IS NULL，索引同样可以用于快速定位所有 NULL 值的行（因为 NULL 在索引中有序存储）。

所以，缺失键并不会破坏索引的有效性，也不会导致查询错误。索引依然正常工作，只是 NULL 值作为普通索引条目存在。

一个简单的验证

假设我们有三行数据：

添加虚拟列后，三行的虚拟列值分别为：

• 行1：'PO123'
• 行2：NULL
• 行3：'PO456'

在虚拟列上建立索引后，索引条目大致为：NULL, 'PO123', 'PO456'（实际顺序取决于存储引擎）。

查询WHERE v_customer_po_no = 'PO123'会通过索引快速定位到行1，行2和行3不会干扰。查询WHERE v_customer_po_no IS NULL则会定位到行2。

5. 进阶扩展：虚拟列与真实列的联合索引

很多场景下，查询条件不仅包括虚拟列，还涉及真实列。例如：“查询某客户（真实列customer_id）的某个客户内部单号（虚拟列v_customer_po_no）”。

5.1 创建联合索引

我们可以创建一个包含真实列和虚拟列的联合索引：

CREATEINDEXidx_customer_po_noONpo_order(customer_id,v_customer_po_no);

5.2 查询如何使用联合索引

SELECT*FROMpo_orderWHEREcustomer_id=1001ANDv_customer_po_no='PO123456';

MySQL 可以利用联合索引，先按customer_id筛选，再按v_customer_po_no精确匹配，效率极高。

5.3 注意事项：虚拟列必须确定

虚拟列的定义必须确定性的（DETERMINISTIC）。也就是说，给定相同的输入（同一行数据），虚拟列的计算结果必须相同。JSON 提取显然是确定性的，所以没问题。

另外，虚拟列不能作为联合索引的前导列？不，它可以放在任何位置。但要注意，如果虚拟列的计算开销较大，放在前面可能会影响索引维护性能，但一般 JSON 提取开销很小。

5.4 更复杂的例子：虚拟列 + 真实列范围查询

假设我们要查询客户 1001 的订单，并且订单金额（存在 JSON 里）大于 1000。我们可以先提取金额为虚拟列，然后建立联合索引(customer_id, v_amount)，查询就能高效执行。

6. 最佳实践与避坑指南

6.1 什么时候应该用虚拟列索引？

• 高频查询的 JSON 字段：如果某个 JSON 属性经常出现在WHERE条件中，就应该为其创建虚拟列和索引。
• 查询性能敏感：如果数据量大，不能忍受全表扫描。
• 值有规律：虚拟列适合提取标量值（字符串、数字），不适合提取复杂嵌套结构。

6.2 虚拟列对写入性能的影响

创建索引就会增加写入开销，虚拟列索引也不例外。每次INSERT/UPDATE都需要计算虚拟列的值并更新索引。如果虚拟列较多，写入性能会下降。建议只为真正高频查询的属性建立虚拟列索引。

6.3 如何选择 VIRTUAL 还是 STORED？

• VIRTUAL：节省空间，适合大多数场景。
• STORED：如果查询极其频繁且不希望每次计算，或者需要对该列进行GROUP BY、ORDER BY等操作（STORED 列上可以建立索引，效果和普通列一样），可以考虑 STORED。但注意，STORED 列会占用存储空间。

6.4 虚拟列上可以使用其他索引类型吗？

可以！除了 B+ 树索引，你还可以在虚拟列上建立唯一索引、全文索引等，只要类型匹配。

6.5 查询时别忘了用虚拟列

最容易犯的错误：写 SQL 时仍然直接对 JSON 字段用->>，而忽略了已创建的虚拟列。这样索引不会生效。查询条件必须使用虚拟列本身。

7. 总结

通过JSON 列 + 虚拟列 + 索引，我们既能享受 JSON 的无限扩展性，又能保证高频查询的性能。当客户需要更多自定义字段时，我们只需在业务层处理 JSON，数据库层面通过虚拟列索引确保查询不崩。如果再结合真实列和虚拟列的联合索引，几乎可以覆盖所有业务查询场景。

这套方案已经在很多大型 ToB 系统中落地，优雅地解决了“扩展字段查询慢”的难题。希望本文能给你带来启发，下次面试被问到“JSON 字段如何优化查询”时，你可以自信地回答：“用虚拟列，再加索引，必要时联合索引，搞定！”

面试话术参考：
“在 ToB 录单系统中，我推荐使用 JSON 列存储自定义字段。针对高频查询，我采用 MySQL 虚拟列（Generated Column）提取 JSON 中的字段，并建立 B+ 树索引。如果查询涉及真实列和虚拟列的组合条件，我还会创建联合索引。这样既保持了 JSON 的扩展性，又解决了慢查询问题。”