当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL JSON 路径查询的生成列索引设计

MySQL JSON 路径查询的生成列索引设计

来源：17golang原创 2026-10-16 22:20:24 0浏览收藏

JSON 字段很适合承载变化较大的业务属性，但当查询开始频繁使用固定路径时，只写 JSON_EXTRACT(payload, '$.order_id') 往往不够。更稳妥的做法是把路径投影成明确类型的生成列，再为这列建立索引。MySQL 优化器还可以识别与生成列定义完全匹配的表达式，因此查询不一定要直接写生成列名。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/

设计重点只有一句话：先确定 JSON 路径最终要参与什么比较，再让生成列表达式、查询表达式和结果类型保持一致。字符串路径通常要去掉 JSON 引号；数值路径则应显式转换为数值类型。

JSON_EXTRACT 取值时先确定结果类型

JSON_EXTRACT() 返回的是 JSON 值；路径不存在时会得到 NULL，同一个路径也可能因为业务数据不规范而出现字符串、数字或空值。直接把它塞进索引，容易让比较类型和索引键的含义变得模糊。

如果路径表示订单号、租户编号这类数值，生成列应明确为整数；如果路径表示业务编码，则用字符串并配合 JSON_UNQUOTE() 去掉 JSON 字符串外层引号。不要把数组路径当成单值等值查询的候选索引列，数组包含关系应另行评估多值索引或查询方案。

JSON_EXTRACT 路径、去引号和标量生成列的关系说明图 — 图1：结构说明图，查看 JSON 路径、结果类型与生成列之间的关系；这是静态说明图，不是运行截图。

用生成列把高频路径投影出来

下面的表把订单号和客户编码从 payload 中提取出来。订单号使用无符号整数，客户编码使用去引号后的字符串。示例只表达索引设计，不代表已经在本机执行。

CREATE TABLE orders (
    id BIGINT UNSIGNED PRIMARY KEY,
    payload JSON NOT NULL,
    order_id BIGINT UNSIGNED GENERATED ALWAYS AS (
        -- 数值路径先去掉 JSON 引号，再转换为可比较的整数
        CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.order_id')) AS UNSIGNED)
    ) STORED,
    customer_code VARCHAR(32) GENERATED ALWAYS AS (
        -- 字符串路径去掉 JSON 引号，避免索引键保留引号
        JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code'))
    ) STORED,
    INDEX idx_orders_order_id (order_id),
    INDEX idx_orders_customer_code (customer_code)
);

选择 STORED 是为了让生成值随行保存，读查询时直接使用索引键；它会增加写入和更新时的计算与存储成本。若两个条件经常同时出现，可以再按真实过滤顺序设计联合索引，而不是为每个可能路径无节制地加索引。

让查询表达式与索引定义保持一致

MySQL 优化器使用生成列索引时，关注的是表达式是否相同以及结果类型是否相同。下面两类查询都保留了生成列定义中的路径、函数和转换方式；字符串条件尤其不要随意省略 JSON_UNQUOTE() 或改变比较类型。

-- 数值条件保持与 order_id 生成列相同的提取和转换
SELECT id, payload
FROM orders
WHERE CAST(JSON_UNQUOTE(JSON_EXTRACT(payload, '$.order_id')) AS UNSIGNED) = 10086;

-- 字符串条件保持去引号后的结果类型
SELECT id, payload
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code')) = 'CUST-001';

-- 用执行计划观察优化器是否考虑对应生成列索引
EXPLAIN SELECT id
FROM orders
WHERE JSON_UNQUOTE(JSON_EXTRACT(payload, '$.customer_code')) = 'CUST-001';

如果查询写成另一种等价但类型不同的表达式，或者把算术/函数顺序改掉，优化器可能无法把它映射到生成列索引。排查时先看 possible_keys 和 key，再确认路径字符串、函数嵌套和字面量类型没有漂移。