MySQL multi-valued index 怎样为 JSON 数组建立多值索引

当一行记录的 JSON 字段保存多个邮编、标签或商品编号时，普通索引无法直接把数组里的每个元素变成可检索键。MySQL 8.0.17 及以上的 InnoDB 提供了 multi-valued index：用 CAST(... AS ... ARRAY) 把 JSON 数组映射为 SQL 类型数组，同一行的多个元素分别建立索引记录，再用 JSON 包含类条件查询。

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

最小可用方案是：确认 JSON 路径确实返回同类型数组，用 CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY) 建立 InnoDB 多值索引，然后让 MEMBER OF()、JSON_CONTAINS() 或 JSON_OVERLAPS() 表达与该数组的包含关系。它适合成员查询，不是排序、覆盖查询或任意 JSON 表达式的通用索引。

• 输入边界：被索引的列必须是 JSON，路径表达式要能返回数组，数组元素类型要与 CAST 类型一致。
• 查询入口：优化器可以为 MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS 这三类条件使用多值索引。
• 运行取舍：一个数据行可能产生多条索引记录，空数组不产生索引记录，建索引也不支持在线创建。

JSON 数组为什么会成为查询瓶颈

假设客户资料中的 zipcode 是一个 JSON 数组。没有合适的索引时，查询“包含 94507 的客户”只能逐行读取 custinfo，再判断数组内容。数据量变大后，瓶颈不在 JSON 语法，而在每次查询都要把候选行带入过滤阶段。

CREATE TABLE customers (
  id BIGINT NOT NULL PRIMARY KEY,
  custinfo JSON NOT NULL
) ENGINE = InnoDB;

INSERT INTO customers (id, custinfo) VALUES
  (1, '{"user":"Jack","zipcode":[94582,94536]}'),
  (2, '{"user":"Jill","zipcode":[94568,94507,94582]}'),
  (3, '{"user":"Bob","zipcode":[94477,94507]}');

-- 这里的数组元素都是无符号整数，后面的 CAST 类型必须保持一致
SELECT id, custinfo
FROM customers
WHERE 94507 MEMBER OF (custinfo->'$.zipcode');

这条条件表达的是“标量 94507 是否属于数组”。在规模较小时全表过滤也许足够；当数组查询成为固定入口时，可以把数组元素的查找职责交给多值索引。

用 CAST 和 ARRAY 创建多值索引

最常用的增量改造写法是 ALTER TABLE ... ADD INDEX。表达式外层需要额外括号，ARRAY 不是普通查询中的 CAST 类型，而是创建多值索引时用于声明“数组元素按指定 SQL 类型索引”的关键部分。

-- 用 UNSIGNED 统一邮编数组的索引元素类型
ALTER TABLE customers
  ADD INDEX idx_customer_zipcodes (
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY))
  );

-- 单个数据行可以对应多个邮编索引键
EXPLAIN SELECT id, custinfo
FROM customers
WHERE 94507 MEMBER OF (custinfo->'$.zipcode');

也可以在 CREATE TABLE 时直接声明，或者使用 CREATE INDEX idx_customer_zipcodes ON customers (...)。如果还要按普通列缩小范围，可以把普通键部分和一个多值键部分组成复合索引，但一个多值索引中只能有一个多值键部分。

查询条件要与数组语义匹配。单值成员判断用 MEMBER OF()，候选 JSON 数组或对象的包含关系可用 JSON_CONTAINS()，两个数组是否有交集可用 JSON_OVERLAPS()。索引是否被采用仍应以当前数据分布和 EXPLAIN 输出为准，不要只因为索引存在就假设查询一定走它。

上线前必须接受的索引取舍

多值索引把一行记录的数组元素拆成多个索引记录，因此写入和更新会随着数组元素数量增加而增加索引维护成本。它不是把 JSON 文档整体复制一份，而是为同一聚簇记录保存多个可定位的键。

查询计划与后续改进怎么判断

创建索引后，先用与业务完全相同的 JSON 条件查看 EXPLAIN，关注访问类型、候选键和实际过滤路径；不要把一个改写成普通标量比较的条件，拿来代替真实查询判断。若索引没有被采用，优先检查三件事：路径是否与索引表达式一致，CAST 类型是否与数组值一致，条件是否属于支持的 JSON 搜索函数。

当数组长度持续增长时，多值索引仍然可能比全表解析更适合成员查询，但它会放大写入维护量。下一步可以按访问模式拆分高频成员字段，限制数组长度，或者把需要排序、聚合和覆盖读取的数据迁移到关系表；不要仅靠继续堆叠 JSON 索引解决数据模型问题。

常见问题

普通 JSON 列能直接建 multi-valued index 吗？

不能把 JSON 文档整体当成普通索引键。要在 JSON 列上的数组表达式中使用 CAST(... AS ... ARRAY)，并让表达式返回适合索引的数组元素类型。

为什么创建了索引却没有使用？

先核对 JSON 路径、CAST 类型和查询函数是否对应，再用真实 SQL 执行 EXPLAIN。优化器还会结合数据分布与成本选择访问路径，索引存在不等于每次查询都必须使用。

多值索引适合给数组排序吗？

不适合。官方限制包括不支持排序、范围扫描和索引覆盖；它的核心价值是把数组成员查询变成可利用索引的包含判断。