MySQL 生成列怎样把 JSON 路径变成可索引字段

如果订单表把扩展属性放在 JSON 列里，查询一多，最容易遇到的症状就是：路径写得出来，普通索引却建不在 JSON 文档本身；即使已经建了生成列，EXPLAIN 也未必显示它被使用。解决思路是把稳定的 JSON 路径提取成有明确 SQL 类型的生成列，再给这个生成列建立二级索引。

JSON 列不能直接按普通列方式建立索引。对于经常参与等值、范围或排序条件的标量路径，应先提取为生成列（或使用等价的函数索引表达式），再用 EXPLAIN 判断查询表达式和生成列定义是否匹配。

官方资料：https://dev.mysql.com/doc/refman/8.4/en/

下面用 MySQL 8.4 的订单扩展属性举例，重点放在一个字段：payload 中的 $.customer_id。示例代码是文章中的设计示意，未在本文制作过程中连接数据库执行。

先把 JSON 路径和目标类型说清楚

一个 JSON 路径能不能成为索引字段，第一关不是语法，而是它是否代表稳定的标量。客户编号适合转换为无符号整数；客户昵称适合转换为字符串；数组或整个对象则不适合直接当作普通 B-Tree 标量索引来设计。

CREATE TABLE orders (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    payload JSON NOT NULL,
    -- 把客户编号定义成明确的数值类型，避免索引比较时发生隐式转换。
    customer_id_gen BIGINT UNSIGNED
        GENERATED ALWAYS AS (
            JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED)
        ) VIRTUAL,
    INDEX idx_orders_customer_id (customer_id_gen)
) ENGINE = InnoDB;

这里的 payload 仍保存完整文档，customer_id_gen 只是从固定路径得到的可查询字段。VIRTUAL 表示值不单独存放，但 InnoDB 可以为虚拟生成列建立二级索引；如果需要把计算结果也存储下来，可以改用 STORED，代价是写入和存储空间都会增加。

把生成列命名出来还有一个好处：业务查询可以明确表达“按客户编号过滤”，而不是让每个调用方各自拼一份 JSON 路径。图中的分组展示了文档、类型转换和索引之间的静态关系。

数值路径优先使用明确的 JSON_VALUE 类型

对数字字段，生成列表达式应尽量让结果类型在定义处就确定下来。下面的 RETURNING UNSIGNED 让客户编号按数值比较，而不是把 JSON 文本或带引号的结果当成字符串比较。

-- 适用于稳定存在的数值路径；缺失路径时结果可为 NULL。
ALTER TABLE orders
    ADD COLUMN customer_id_gen BIGINT UNSIGNED
        GENERATED ALWAYS AS (
            JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED)
        ) VIRTUAL,
    ADD INDEX idx_orders_customer_id (customer_id_gen);

如果表里已经有同名生成列，不要重复执行这段变更；实际迁移时应先按现有表结构拆成可回滚的 DDL。路径缺失、JSON 文档不符合预期或数值无法转换时，必须按照项目的 SQL 模式和数据约束处理，不要把“索引已经建成”误认为所有脏数据都能自动被修复。

字符串字段则要注意 JSON 函数返回的引号语义。例如 JSON_EXTRACT(payload, '$.status') 得到字符串 JSON 值时，索引列定义通常需要配合 JSON_UNQUOTE()，让生成列保存真正的字符串：

CREATE TABLE order_events (
    id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    payload JSON NOT NULL,
    -- JSON_UNQUOTE 去掉 JSON 字符串的外层引号，便于按文本索引比较。
    status_text VARCHAR(32)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(payload, '$.status'))
        ) VIRTUAL,
    INDEX idx_order_events_status (status_text)
) ENGINE = InnoDB;

这和数值路径的设计原则相同：先确认业务类型，再决定生成列类型和表达式。不要因为两个路径都来自 JSON 就复用同一套字符串或数字转换逻辑。

生成列和查询表达式要保持同一语义

第一次排查“索引没命中”时，我会先把生成列定义和 WHERE 条件并排看。MySQL 优化器可以识别与索引生成列定义匹配的表达式，但匹配要求比较严格：表达式应保持一致，结果类型也要一致。只把 f1 + 1 改成 1 + f1，或把数字表达式拿去和字符串比较，都可能失去这种匹配。

-- 推荐：查询表达式和生成列保持同一路径、同一返回类型。
EXPLAIN
SELECT id
FROM orders
WHERE JSON_VALUE(payload, '$.customer_id' RETURNING UNSIGNED) = 1001;

-- 也可以直接查询生成列，让意图更明显。
EXPLAIN
SELECT id
FROM orders
WHERE customer_id_gen = 1001;

对字符串路径，生成列定义中的 JSON_UNQUOTE() 也很关键。若生成列只写 JSON_EXTRACT()，某些直接对 JSON 函数结果的比较仍可能被识别，但把一个普通字符串条件映射到索引查找时，缺少去引号会让匹配变得不稳定。工程上更容易维护的做法是统一表达式，并在代码评审中把路径、返回类型和比较常量一起看。

用 EXPLAIN 判断 possible_keys 和 key

建好索引之后，判断它是否真的参与当前查询，至少要看两个字段：

如果 possible_keys 中出现 idx_orders_customer_id，而 key 也选中了它，说明当前计划至少把这个索引纳入并采用了。若两列都为空，优先检查路径是否写错、生成列是否真的带索引，以及查询表达式的返回类型是否发生变化。

-- 只查看执行计划字段，不把预期结果当成本文已执行的输出。
EXPLAIN
SELECT id, payload
FROM orders
WHERE customer_id_gen BETWEEN 1000 AND 2000;

-- 用 SHOW CREATE TABLE 对照生成列的原始表达式与索引定义。
SHOW CREATE TABLE orders;

MySQL 文档还说明，优化器可能在查询没有直接引用生成列名称时，根据匹配表达式使用生成列索引；但这不是鼓励随意改写表达式的理由。为了让迁移、ORM 和手写 SQL 都更稳定，常用条件最好直接引用生成列，或集中封装完全相同的 JSON 表达式。

VIRTUAL 和 STORED 怎么取舍

VIRTUAL 不把生成值作为表数据另存一份，但索引仍然要保存自己的索引条目；适合表达式简单、基础 JSON 文档变化频繁、又希望减少额外表存储的场景。STORED 在插入和更新时计算并保存生成值，读取路径更直观，但会增加表和索引的写入维护成本。

选择时可按下面三点判断：

• 路径是否稳定：字段名和数据类型长期不变，才值得固定成生成列。
• 读写比例如何：读多写少且表达式较重时，STORED 可能更容易解释；写入密集时先评估维护成本。
• 是否真的用于过滤：只在 SELECT 列表展示、很少参与 WHERE、ORDER BY 或 GROUP BY 的路径，不要为了“看起来完整”批量建索引。

生成列和索引都不是免费的缓存。每多一个 JSON 路径，就多一份 DDL、数据约束、索引统计和变更记录。高频查询可以先选一个最稳定的路径做试点，再用真实查询计划决定是否继续扩展。

容易踩到的五个边界

• 把数组当单值。 一个路径返回多个数组元素时，不应直接套用客户编号这种标量索引模型；先明确查询是否需要多值索引或拆表设计。
• 忽略字符串引号。 JSON_EXTRACT 返回字符串 JSON 值时，生成列定义通常要写 JSON_UNQUOTE。
• 查询常量类型不一致。 生成列是 UNSIGNED，却在应用层把参数绑定成带格式的字符串，可能让表达式匹配和选择性判断变差。
• 只看 possible_keys。 最终是否使用看 key；两个字段都不是业务结果，仍要结合查询计划和数据分布理解。
• 重复创建同一列。 迁移脚本需要明确执行前提和回滚方案，不要把文章示例直接反复投产。

相关问题

JSON 列能不能直接建索引？ MySQL 8.4 文档给出的常规做法是通过生成列提取需要索引的标量；不要把完整 JSON 文档直接当普通 B-Tree 列来处理。

必须使用 STORED 才能索引吗？ 对 InnoDB 来说，虚拟生成列也可以有二级索引；STORED 主要改变生成值的存储和写入计算方式，应该按读写负载选择。

为什么 JSON_EXTRACT 写对了，key 还是空？ 先核对路径、生成列数据类型、是否写了 JSON_UNQUOTE，以及 WHERE 表达式和生成列定义是否保持一致，再用 SHOW CREATE TABLE 对照索引定义。

归纳起来，这个方案的核心不是“给 JSON 加一层名字”，而是把需要参与检索的路径固定成一个有明确类型的关系字段：文档保留原始事实，生成列表达查询语义，二级索引负责候选定位，EXPLAIN 负责告诉你当前计划是否真的采用了它。