MySQL 生成列配合索引的 JSON 字段检索

MySQL 检索 JSON 中固定路径的标量字段，可以先用生成列表达式提取值，再为生成列建立二级索引。应用查询直接引用生成列名，能够把路径、SQL 类型和查询接口集中在表结构里；建立索引提供了访问选择，不等于每条查询都会选用它。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/create-table-secondary-indexes.html

以下以 MySQL 8.4、InnoDB 和原创的消息档案示例说明。SQL 供读者在独立开发库理解结构，没有在本机执行，也没有实测耗时或执行计划。

先定义来源字段和字符串契约

假设业务把消息来源写入 JSON 文档 payload 的 $.channel，例如字符串 partner。先约定它是最多32字符的短字符串代码，而不是任意对象、数组或数字。JSON 类型能约束文档语法，却不会替应用确定每条路径的业务类型。

这篇示例只解决单个字符串的等值查询。一个文档里有多个渠道的数组时，不能直接套用“一个生成列对应一个标量”的模型。若该字段已经成为核心关联键，也值得评估直接使用普通列，以免长期维护两套含义。

把固定 JSON 路径映射成有类型的生成列

-- 在独立开发库新建示例表；不要直接对现有生产表照搬DDL。
CREATE TABLE message_archive_demo (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  payload JSON NOT NULL,
  -- 先按固定路径提取JSON字符串，再移除字符串的引号。
  channel_code VARCHAR(32)
    CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
    GENERATED ALWAYS AS (
      JSON_UNQUOTE(JSON_EXTRACT(payload, '$.channel'))
    ) VIRTUAL,
  PRIMARY KEY (id),
  -- 索引对象是有界字符串生成列，并非整份JSON文档。
  INDEX idx_channel (channel_code)
) ENGINE=InnoDB;

JSON_EXTRACT 指定路径，JSON_UNQUOTE 处理提取出的字符串引号，channel_code 则确定 SQL 侧的长度与比较规则。示例显式写出排序规则，避免让读者把 JSON 文本与 SQL 字符串比较看成同一种行为；业务若要求不同的大小写、重音或尾随空格语义，应按实际规则选择并测试。

VIRTUAL 不意味着索引没有存储成本。InnoDB 对虚拟生成列建立二级索引时，索引中仍保存生成值，写入也要维护相关索引。若改用 STORED，需要评估生成列本身的存储成本；不能只凭名字判断哪种方案一定更快。

查询直接引用生成列名

-- 原创样本只写基础JSON列，生成列由数据库按表达式计算。
INSERT INTO message_archive_demo (payload) VALUES
  (JSON_OBJECT('channel', 'partner', 'subject', 'invoice_notice')),
  (JSON_OBJECT('channel', 'direct', 'subject', 'status_notice')),
  (JSON_OBJECT('channel', 'partner', 'subject', 'delivery_notice'));

-- 字符串条件对应VARCHAR生成列，不向生成列重复包裹提取函数。
SELECT id, channel_code, payload
FROM message_archive_demo
WHERE channel_code = 'partner'
ORDER BY id
LIMIT 50;

这里的等值条件使用字符串参数，返回字段包括 id 和 payload。idx_channel 与 channel_code 的定义相连，但返回整份 payload 仍可能需要访问基表数据，不能因为 WHERE 提到了索引列就声称查询已被完全覆盖。

优化器可以识别某些与生成列定义匹配的表达式并考虑其索引，但表达式与结果类型的匹配存在条件。应用若始终自己拼写路径、转换和比较，很容易出现与结构定义不一致的变体。直接引用 channel_code 能减少这种重复，索引最终是否被选择仍取决于真实数据和成本判断。

异常输入要回到字段约定处理

如果应用需要“缺失与 JSON null 一律归为未分类”，应先设计清晰的归一化表达式和输入策略，再迁移结构。本文的简洁表达式没有实现这个额外规则，图示也不代表它已经具备字段类型校验能力。

更新、归档与结构清理保持同一份定义

后续更新 payload 时，生成列按表达式反映该行的内容，相关索引需要维护。应用不应给 channel_code 再赋一份手工计算值，否则会把生成列与普通冗余列混淆。批量更新渠道或回填历史文档之前，应评估写入量、索引维护与业务窗口。

归档若保留原始 JSON，可以保留来源信息；重新导入到新结构时，必须确认路径与比较规则仍符合约定。清理一个不再使用的检索字段时，先梳理依赖 channel_code 的查询和报表，再安排索引、生成列的结构变更。删除主记录与删除索引是不同动作，不要为了去掉检索能力而删除业务文档。

相关问题

给JSON列直接加普通索引可以替代生成列吗？

本文采用的是标量提取后的间接索引，普通索引的对象为 channel_code。它不代表已经给文档内所有路径建立索引。

生成列有索引，为什么小样本仍可能全表扫描？

可用索引与被选用索引是两回事。小表或匹配比例很高时，成本判断可能偏向扫描；不要用几条示例记录推导生产性能保证。

每个JSON键都应建一个生成列吗？

优先选择稳定且确实用于筛选、连接或排序的标量字段。不断增加生成列和索引会扩大结构与写入维护负担，字段选择应来自真实查询需求。