MySQL JSON 字段怎样用生成列建立索引

我在给一张业务表加 JSON 扩展字段时，最先遇到的不是 JSON 语法，而是查询越来越依赖 payload 里的某个键，却没有一个稳定的索引入口。MySQL 的 JSON 列不能直接建立普通索引，实用做法是把经常参与过滤、排序的标量值提取成生成列，再在生成列上建立二级索引。

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

记住一条线索：JSON 负责保存文档，生成列负责把一个确定路径变成有类型的标量，索引负责服务查询。先决定“要查哪个键”，再决定生成列的类型和 VIRTUAL/STORED 形式。

下面的 SQL 是原创示例，图片是静态结构说明图，不是 MySQL 控制台截图，也不代表已经在某个实例上执行过。

先把要查询的 JSON 路径收窄

生成列不是给整份 JSON 文档做索引，而是为一个明确的路径提供索引入口。例如订单扩展信息中，customer.code 适合做字符串等值查询，priority 适合做数值范围查询。路径越明确，生成列表达式越容易维护。

-- JSON 只保存可扩展字段；真正高频查询的键单独规划路径
CREATE TABLE ticket_record (
    ticket_id BIGINT NOT NULL PRIMARY KEY,
    payload JSON NOT NULL
);

-- 先确认表达式的返回类型，再决定生成列使用字符还是数值类型
SELECT
    JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.code')) AS customer_code,
    CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.priority')) AS UNSIGNED) AS priority
FROM ticket_record;

这里有两个容易混淆的点：JSON_EXTRACT() 返回的是 JSON 值，字符串可能带 JSON 引号；数值路径如果直接按字符串处理，排序和范围比较也可能与业务含义不一致。因此生成列最好显式声明目标类型。

用生成列把 JSON 标量接出来

生成列的表达式会根据同一行的 JSON 内容计算值。字符串字段可以用 JSON_UNQUOTE(JSON_EXTRACT(...)) 去掉 JSON 字符串的引号；数值字段则用 CAST 转成整数或其他合适的数值类型。

-- VIRTUAL 不额外保存一份表字段值，但可以在它上面建立二级索引
ALTER TABLE ticket_record
    ADD COLUMN customer_code VARCHAR(64)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.code'))
        ) VIRTUAL,
    ADD COLUMN priority INT
        GENERATED ALWAYS AS (
            CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.priority')) AS UNSIGNED)
        ) VIRTUAL;

-- 生成列只负责提供可索引的标量，不把 JSON 文档改写成普通列
CREATE INDEX idx_ticket_customer_code ON ticket_record (customer_code);
CREATE INDEX idx_ticket_priority ON ticket_record (priority);

我更倾向于把生成列命名成业务语义，而不是重复 JSON 路径。这样查询、慢日志和执行计划里看到的是 customer_code，后续即使 JSON 路径调整，也更容易找到需要同步修改的 DDL 和 SQL。

字符串和数值要用不同的生成表达式

字符串查询最容易踩到 JSON 引号问题。MySQL 官方文档特别提醒：如果生成列来自返回带引号字符串的 JSON 函数，列定义中需要使用 JSON_UNQUOTE()，否则优化器在索引查找时可能无法按预期匹配去引号后的字符串。

-- 字符串路径要去掉 JSON 字符串的引号，避免索引值与业务文本不一致
ALTER TABLE ticket_record
    ADD COLUMN customer_name VARCHAR(80)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer.name'))
        ) STORED;

-- 数字路径要转成数值类型，范围比较才按数字语义进行
ALTER TABLE ticket_record
    ADD COLUMN retry_count INT
        GENERATED ALWAYS AS (
            CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.retry_count')) AS UNSIGNED)
        ) STORED;

-- 建索引前先确认列类型和长度符合真实取值范围
CREATE INDEX idx_ticket_customer_name ON ticket_record (customer_name);
CREATE INDEX idx_ticket_retry_count ON ticket_record (retry_count);

选择类型时不要只看当前样本：代码、短名称适合有限长度的 VARCHAR；计数、等级和时间戳应转换为对应数值或时间类型。把 JSON 文本原样放进很长的生成列，会让索引体积和写入成本一起上升。

查询时优先使用生成列名

最稳妥的查询方式是直接使用生成列名，因为查询意图和索引对象一一对应。优化器也能识别与生成列表达式相匹配的表达式，但表达式、返回类型和去引号方式要保持一致，不能一边用字符串 JSON 值，一边把生成列定义成数值。

-- 直接引用生成列，等值条件可以使用对应二级索引
SELECT ticket_id, payload
FROM ticket_record
WHERE customer_code = 'C-1001';

-- 数值生成列适合范围过滤；不要把 priority 当作 JSON 文本比较
SELECT ticket_id
FROM ticket_record
WHERE priority BETWEEN 2 AND 4
ORDER BY priority;

-- 用 EXPLAIN 查看优化器是否把候选索引纳入计划
EXPLAIN SELECT ticket_id
FROM ticket_record
WHERE customer_code = 'C-1001';

如果团队必须保留 JSON 路径表达式，也要把它写成与生成列定义等价的形式，并用 EXPLAIN 对真实数据分布检查。不要只因为“表上有索引”就断言查询一定会走索引；选择性、结果集大小和其他条件都会影响优化器判断。

VIRTUAL 和 STORED 怎么选

MySQL 生成列默认是 VIRTUAL。虚拟列不把计算结果作为表字段额外物化，但可以建立二级索引；读取没有命中索引、又需要直接计算该值时，仍可能产生计算成本。STORED 会把生成值物化到表中，读取更直接，但占用更多表空间，写入或更新 JSON 时也要维护这份值。

我通常先用 VIRTUAL 加二级索引解决明确的过滤需求，只有在读取派生值本身很多、表达式确实偏重，或数据模型需要稳定物化结果时，才改为 STORED。这个选择不是“哪个更快”的固定答案，而是读、写、空间三项成本的交换。

缺失键、NULL 和类型异常要提前定规则

JSON 路径不存在时，提取结果可能是 SQL NULL；如果值为空字符串、JSON null 或者不能转换成目标数值类型，它们在索引排序和筛选中的表现并不相同。生成列上线前要先决定这些值是允许缺失、统一为默认值，还是直接拒绝写入。

-- 用 NULLIF 把空字符串视为缺失值，避免把空值当成有效业务编码
ALTER TABLE ticket_record
    ADD COLUMN region_code VARCHAR(16)
        GENERATED ALWAYS AS (
            NULLIF(
                JSON_UNQUOTE(JSON_EXTRACT(payload, '$.region.code')),
                ''
            )
        ) VIRTUAL;

-- 统计生成列的 NULL 分布，帮助判断索引选择性和数据质量
SELECT
    COUNT(*) AS total_rows,
    SUM(region_code IS NULL) AS missing_region,
    COUNT(DISTINCT region_code) AS distinct_region
FROM ticket_record;

如果生成列用于唯一约束或租户隔离，缺失值的规则更要写进设计说明。否则同一条查询在“键不存在”和“键存在但为空”之间可能得到难以解释的边界结果。

上线前按三层检查索引是否值得保留

建立索引只是 DDL 完成，不等于查询已经得到收益。我会按“定义、计划、分布”三层检查：先看生成列表达式和数据类型，再用 EXPLAIN 看候选索引，最后确认真实数据的选择性是否足够。

-- 第一层：确认生成列的表达式、类型和 VIRTUAL/STORED 属性
SHOW CREATE TABLE ticket_record;

-- 第二层：确认目标查询是否把生成列索引作为候选
EXPLAIN SELECT ticket_id
FROM ticket_record
WHERE customer_code = 'C-1001';

-- 第三层：观察取值分布；低基数字段未必适合单列索引
SELECT customer_code, COUNT(*) AS row_count
FROM ticket_record
GROUP BY customer_code
ORDER BY row_count DESC
LIMIT 20;

如果 EXPLAIN 没有选择索引，先不要急着强制索引。检查查询是否真的使用了生成列定义中的去引号和类型转换，再看条件返回的行数、统计信息和其他联合条件。只有明确知道优化器为什么选择另一条路径时，才考虑索引提示或调整索引设计。

一份可直接改造的最小模板

下面把最常见的字符串等值查询压缩成一份模板：JSON 保存原始扩展信息，生成列暴露稳定的业务键，索引只覆盖这个键。

-- 先保留 JSON 原文，再暴露一个有限长度、可查询的业务键
CREATE TABLE audit_event (
    event_id BIGINT NOT NULL PRIMARY KEY,
    detail JSON NOT NULL,
    actor_code VARCHAR(48)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(detail, '$.actor.code'))
        ) VIRTUAL,
    INDEX idx_audit_actor_code (actor_code)
);

-- 查询直接使用生成列名，语义和索引对象保持一致
SELECT event_id, detail
FROM audit_event
WHERE actor_code = 'operator-17';

这份模板的关键不在于把所有 JSON 键都做成生成列，而在于只为稳定、高频、值得筛选的键付出索引成本。低频查询、结构经常变化或返回长数组的路径，不适合机械地复制这个模式。

总结

MySQL JSON 字段需要索引时，可以按“提取标量—声明类型—建立生成列索引—用 EXPLAIN 复查”的顺序处理。字符串路径记得 JSON_UNQUOTE，数值路径显式 CAST；VIRTUAL 节省物化空间，STORED 换取读取便利，最终要结合读写比例和字段长度决定。生成列应服务一个明确查询任务，不要把整份 JSON 文档无差别搬进索引。

相关问题

JSON 列可以直接建普通索引吗？

不能按普通列的方式直接索引整列 JSON。常见做法是提取需要查询的标量到生成列，再在生成列上建立索引。

为什么字符串生成列经常要写 JSON_UNQUOTE？

JSON 函数返回的字符串可能带 JSON 引号。生成列中去掉引号，才能让索引值和业务字符串比较保持一致，也更容易被优化器识别为匹配表达式。

VIRTUAL 一定比 STORED 快吗？

不一定。VIRTUAL 减少表内物化空间，但未被索引覆盖的读取可能需要重新计算；STORED 增加存储和写入维护成本，却可能让读取更直接。应按真实读写比例和表达式复杂度选择。