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

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 字符串外层引号。不要把数组路径当成单值等值查询的候选索引列，数组包含关系应另行评估多值索引或查询方案。

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

下面的表把订单号和客户编码从 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，再确认路径字符串、函数嵌套和字面量类型没有漂移。

用 EXPLAIN 和写入成本做最后取舍

索引设计完成后，重点观察访问类型是否从全表扫描转为更合适的索引访问，以及 possible_keys、key 是否出现预期索引。不要只因为某个 JSON 路径出现在 WHERE 中就马上建索引：低选择性字段、很少查询的字段和频繁更新的 JSON 文档，都可能让索引收益抵不过维护成本。

实践中可以先为一个高频、选择性较好的标量路径建立生成列索引，再用代表性 SQL 查看计划；如果条件组合稳定，再考虑联合索引。路径缺失产生的 NULL、类型混杂、过长字符串和数据清洗规则，都应在业务约束中先处理清楚。

常见问题

为什么 JSON_EXTRACT 查得到，但索引没有被使用？

优先检查生成列表达式与查询表达式是否完全匹配，尤其是 JSON_UNQUOTE()、显式类型转换和比较字面量的类型；然后再看选择性、统计信息和优化器最终选择。

生成列应该用 VARCHAR 还是数值类型？

按业务比较方式选择：参与范围、排序或数值等值比较的字段使用数值类型；业务编码即使只包含数字，也应按编码语义保留字符串，避免前导零被改变。

参考资料

https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

https://dev.mysql.com/doc/refman/8.4/en/generated-column-index-optimizations.html

https://dev.mysql.com/doc/refman/8.4/en/json.html