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

文章目录1. 引言当业务需要“无限”扩展字段2. 方案回顾JSON 列存储的优点与痛点2.1 为什么选 JSON 列2.2 痛点JSON 内部字段无法直接使用索引3. 虚拟列把 JSON 字段“抽”出来变成真实列3.1 创建虚拟列提取 JSON 值3.2 虚拟列的类型3.3 VIRTUAL vs STORED 如何选择4. 虚拟列索引让 JSON 查询走 B 树4.1 在虚拟列上创建索引4.2 内部原理4.3 JSON 字段不好含指定创建虚拟列和索引的键索引情况一个简单的验证5. 进阶扩展虚拟列与真实列的联合索引5.1 创建联合索引5.2 查询如何使用联合索引5.3 注意事项虚拟列必须确定5.4 更复杂的例子虚拟列 真实列范围查询6. 最佳实践与避坑指南6.1 什么时候应该用虚拟列索引6.2 虚拟列对写入性能的影响6.3 如何选择 VIRTUAL 还是 STORED6.4 虚拟列上可以使用其他索引类型吗6.5 查询时别忘了用虚拟列7. 总结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_noPO123456;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_noPO123456;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 值作为普通索引条目存在。一个简单的验证假设我们有三行数据idextend_info1{customer_po_no: PO123, other: abc}2{color: red}无 customer_po_no3{customer_po_no: PO456}添加虚拟列后三行的虚拟列值分别为行1PO123行2NULL行3PO456在虚拟列上建立索引后索引条目大致为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_id1001ANDv_customer_po_noPO123456;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 还是 STOREDVIRTUAL节省空间适合大多数场景。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 的扩展性又解决了慢查询问题。”