当前位置：首页 > 文章列表 > 数据库 > MySQL > MySQL JSON 数组字段如何建立可用索引

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

来源：17golang原创 2026-09-12 14:44:58 0浏览收藏

我在订单表里遇到过一种很容易误判的慢查询：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()。如果数组元素是复杂对象，或者查询需要排序、覆盖索引，应该重新评估关系表方案。

要点速览

普通 JSON 列不能直接建立普通索引，数组成员检索对应的是多值索引。
索引定义中的数组路径、SQL 类型和查询表达式必须保持一致。
空数组没有索引项，多值索引不支持排序，也不能成为覆盖索引。

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

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

场景	建议	原因
标量数组，按是否包含查询	多值索引	一个数组元素对应一条索引记录
数组对象需要组合条件	拆成关系表	对象内部字段不适合直接当成员索引
需要排序、覆盖查询或统计	优先关系模型	多值索引不支持排序和覆盖索引

MySQL JSON 数组索引结构示意：orders 表的 attributes 字段通过 JSON 路径和 CAST 数组类型连接到 InnoDB 多值索引 — 图1：JSON 数组字段、数组路径、CAST 数组类型与多值索引之间的静态结构示意图，不是数据库控制台截图。

用 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，不要仅凭建表语句判断“已经优化”。