MySQL JSON 数组字段如何建立可用索引

我在订单表里遇到过一种很容易误判的慢查询：attributes 是 JSON，里面的 product_ids 是数组，业务却经常按“是否包含某个商品”筛选。直接给 JSON 列建普通索引行不通；更合适的做法是让 InnoDB 建立多值索引，把数组里的每个标量元素映射成二级索引记录。

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

JSON 数组要做成员检索，可以使用 CAST(... AS ... ARRAY) 定义多值索引；查询侧优先使用 MEMBER OF()、JSON_CONTAINS() 或 JSON_OVERLAPS()。如果数组元素是复杂对象，或者查询需要排序、覆盖索引，应该重新评估关系表方案。

先判断：数组适合多值索引还是拆表

先看查询是不是“一个订单是否包含某个标量值”。例如 [101, 205, 309] 这样的商品 ID 数组，适合按成员查找；如果数组元素是带价格、数量、有效期的对象，或者经常要按元素排序、统计，索引并不能替代订单明细表。

用 CAST(... AS ... ARRAY) 建立多值索引

下面把订单表中的商品 ID 数组定义为无符号整数数组。索引表达式使用额外的一层括号，这是函数表达式索引的写法；ARRAY 告诉 MySQL 将数组中的同类型标量作为多个索引值处理。

-- 先保证表使用 InnoDB，并把数组元素限定为无符号整数
CREATE TABLE orders (
    id BIGINT NOT NULL PRIMARY KEY,
    attributes JSON NOT NULL,
    -- 生产表已有数据时，可先用 ALTER TABLE 添加下面的索引
    INDEX idx_product_ids ((CAST(attributes->'$.product_ids' AS UNSIGNED ARRAY)))
) ENGINE=InnoDB;

-- 已有表的增量写法：索引路径必须与查询语义一致
ALTER TABLE orders
    ADD INDEX idx_product_ids (
        (CAST(attributes->'$.product_ids' AS UNSIGNED ARRAY))
    );

这不是把整个 JSON 文档压成一个普通键，而是让一行数组拥有多个二级索引记录。数组为空时不会产生索引项；数组内出现 JSON null，或者元素无法按声明类型转换，都应在上线前作为数据质量问题处理。

让查询写法和索引表达式对上

最直观的成员查询是 MEMBER OF()。数组是数字数组时，左侧也应保持数字语义；不要把数字写成含义不明的字符串，再期待类型转换帮你兜底。

-- 查询某个订单是否包含商品 205，使用成员语义
SELECT id, attributes
FROM orders
WHERE 205 MEMBER OF (attributes->'$.product_ids');

-- 多个候选值有交集时，可用 JSON_OVERLAPS 表达“至少命中一个”
SELECT id
FROM orders
WHERE JSON_OVERLAPS(attributes->'$.product_ids', CAST('[205, 411]' AS JSON));

-- 先观察优化器是否把候选索引列为 possible_keys 并实际选中
EXPLAIN SELECT id
FROM orders
WHERE 205 MEMBER OF (attributes->'$.product_ids');

JSON_CONTAINS() 也可表达数组包含关系，但参数形状要与 JSON 类型一致。最终是否使用索引仍由数据分布、统计信息和查询成本决定，所以关注 possible_keys 与 key，不要仅凭建表语句判断“已经优化”。

上线前看清空数组、类型和写入代价

多值索引解决的是“数组里有没有这个值”，不是 JSON 查询的万能加速器。它不能用于排序，也不能作为覆盖索引；一个复合索引最多包含一个多值键部分。数组越长，插入和更新时需要维护的索引记录越多，批量修改 JSON 字段尤其要留意锁和日志压力。

上线前我会按这张清单复查：数组是否只存一种标量类型；空数组是否与“不存在”有明确业务含义；索引字段是否确实是 InnoDB；生产数据是否可能包含 JSON null；查询是否固定使用成员、包含或重叠语义；以及是否需要保留一个可回退的关系表迁移路径。

常见问题

JSON 列能直接写 INDEX(attributes) 吗？

不能把它当作普通标量列直接索引。单值 JSON 属性可以考虑生成列索引；数组成员检索应优先评估多值索引。

空数组为什么查不到索引命中？

空数组不会生成索引记录，因此它没有可供多值索引扫描的成员。需要区分空数组的业务语义时，应额外设计状态字段或关系表。

建了索引但 EXPLAIN 没选它怎么办？

先确认查询函数、路径和类型与索引表达式一致，再检查数据选择性与统计信息；如果查询还要排序、聚合或返回大量列，多值索引可能不是合适方案。