生成列配合 JSON 索引怎么配置或排查

MySQL 的 JSON 列不能像普通字符串列那样直接建立普通索引。实际项目中更稳妥的做法是：把固定 JSON path 提取成一个有明确 SQL 类型的生成列，再给这个生成列建索引。查询时优先使用生成列，并用 EXPLAIN 检查是否真的命中。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/

先把 JSON 路径和返回类型定死

假设订单表的 profile 保存用户属性，查询经常按城市筛选。JSON 文档中的 city 是字符串，生成列就应该声明为字符串；如果把数字当字符串索引，比较和排序都可能出现不符合预期的结果。

CREATE TABLE customer_order (
    id BIGINT UNSIGNED PRIMARY KEY,
    profile JSON NOT NULL,
    city VARCHAR(64)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city'))
        ) STORED,
    INDEX idx_customer_order_city (city)
);

-- 用 JSON 文档写入城市，生成列会按固定路径得到可索引值
INSERT INTO customer_order (id, profile) VALUES
(1, '{"city":"Hangzhou","level":3}'),
(2, '{"city":"Shenzhen","level":2}');

这里的关键不是列名，而是表达式的稳定性：JSON_EXTRACT() 取出 JSON 值，JSON_UNQUOTE() 把字符串的 JSON 引号去掉，最后由 VARCHAR(64) 规定索引值的类型。路径不存在时，生成列通常得到 NULL，这应当纳入数据约束和业务判断。

用生成列把 JSON 查询变成普通索引查询

已经存在的表可以用 ALTER TABLE 增加生成列和索引。读多写少、表达式较简单时，VIRTUAL 可减少额外存储；如果需要让生成值持久化，或表达式计算成本较高，可以选 STORED。两者都可以被索引，真正要权衡的是写入维护成本、存储空间和计算开销。

ALTER TABLE customer_order
    ADD INDEX idx_customer_order_city_v2 (
        (JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')))
    );

-- 生产环境更容易维护的写法：先命名生成列，再检查它的定义
SHOW CREATE TABLE customer_order;

上面的表达式索引适合快速验证；长期维护通常更推荐显式命名生成列，因为字段定义、类型和索引名都能在表结构中直接看到。若使用表达式索引，查询表达式也要保持与索引表达式兼容，不能随意换路径、换返回类型或再包一层不同的函数。

EXPLAIN 没用上索引时按三层排查

查询不要只看“索引已经创建”。先用生成列名过滤，再看执行计划：

EXPLAIN SELECT id
FROM customer_order
WHERE city = 'Hangzhou';

-- 重点观察 key 是否为目标索引，rows 是否明显小于全表规模
SHOW INDEX FROM customer_order;

如果 key 是 idx_customer_order_city，通常说明优化器选择了该索引；type 至少应从全表扫描方向改善，rows 则是优化器估算需要检查的行数。若 key 仍为 NULL，先排查查询是否绕过了生成列、字面量类型是否一致，再确认统计信息和数据分布。

第二层是表达式：用 SELECT id, city, JSON_UNQUOTE(JSON_EXTRACT(profile, '$.city')) FROM customer_order LIMIT 5; 对照生成列与原 JSON 的结果。第三层是数据分布：城市取值极少、表很小或过滤选择性很差时，优化器主动选择全表扫描并不一定是配置错误。

几个容易误判的边界

第一，JSON path 变更就是数据模型变更。应用从 $.city 改到 $.shipping.city 后，旧生成列不会自动理解新路径，需要同步变更定义并评估索引重建。第二，数组包含多个值时，普通生成列只适合提取一个标量；要查数组成员，应另行评估 InnoDB 多值索引。第三，索引会增加写入和存储成本，只有反复出现且选择性合适的过滤条件才值得建立。

相关问题

JSON 列能不能直接写 INDEX(profile)？

普通 JSON 列不能直接这样建立常规索引。应提取可比较的标量到生成列，或在适用版本和表达式条件下使用函数表达式索引。

生成列应该选 VIRTUAL 还是 STORED？

先看存储和计算的取舍。VIRTUAL 少占持久化存储但读取时计算，STORED 需要保存生成值但读取更直接；两者都要结合写入量和查询量测试。

EXPLAIN 的 key 是索引名但查询还是慢，怎么办？

继续看 rows、过滤选择性、回表列和排序/连接部分。命中索引只说明访问路径的一部分，不代表整个 SQL 已经优化完成。