对齐多值索引 CAST 路径与 MEMBER OF、JSON_CONTAINS 查询路径

MySQL 多值索引不只是“给 JSON 列加一个索引”。真正决定它能否被识别的，是索引表达式里的 JSON path、CAST(... AS ... ARRAY) 类型，以及查询谓词右侧使用的 path 是否对应同一组数组元素。最稳妥的写法是：索引和查询都明确写出同一个数组路径，例如都使用 profile->'$.labels'；不要让索引落在 $.labels，查询却直接把整列 profile 交给 MEMBER OF()。

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

先对齐路径，再检查元素类型，最后看 EXPLAIN。如果 possible_keys 里完全没有多值索引，优先排查表达式不一致；如果索引可选但没有被选中，再讨论选择性和成本。

• 索引表达式：固定 JSON 数组路径，并用正确的 SQL 数组类型 CAST。
• 查询表达式：MEMBER OF()、JSON_CONTAINS() 或 JSON_OVERLAPS() 继续指向同一条 path。
• 计划判断：只看一次 EXPLAIN 不够，至少区分“没有候选索引”和“候选索引成本不划算”。

先把索引表达式写成可复用的路径契约

假设 customer_profiles.profile 保存类似 {"labels":[42,51]} 的 JSON 文档，业务只查询 labels 数组里的无符号整数。多值索引的关键不是索引整列，而是用 JSON path 选出数组，再把同类型标量转换成 SQL 数组：

-- 只索引 profile.labels 数组中的无符号整数
CREATE INDEX idx_profile_labels
ON customer_profiles (
  (CAST(profile->'$.labels' AS UNSIGNED ARRAY))
);

-- 查询也固定使用同一条 JSON path，避免把整列 profile 当成数组
SELECT profile_id, profile
FROM customer_profiles
WHERE 42 MEMBER OF(profile->'$.labels');

CAST 的数组类型是索引的一部分。这里索引的是 UNSIGNED 数组，所以查询常量也应按无符号整数语义使用；不要一边把数组元素按数值建索引，一边把业务谓词改写成另一种字符串路径或另一种元素类型。MySQL 官方将多值索引实现为面向 JSON 数组的二级索引，一个数据行可以对应多个索引记录。

让 MEMBER OF 和 JSON_CONTAINS 指向同一条 path

路径一致后，三类常见谓词才有机会进入多值索引的候选匹配。单值成员查询适合 MEMBER OF()；需要判断一个数组候选集时，可使用 JSON_CONTAINS()；只要两个数组存在交集，则可使用 JSON_OVERLAPS()。它们不是互相等价的业务条件，但都必须把目标数组定位到 profile->'$.labels'。

-- 单个无符号标签：右侧明确定位到 labels 数组
SELECT profile_id
FROM customer_profiles
WHERE 42 MEMBER OF(profile->'$.labels');

-- 候选数组包含多个标签：JSON_CONTAINS 的目标 path 不能省略
SELECT profile_id
FROM customer_profiles
WHERE JSON_CONTAINS(
  profile->'$.labels',
  CAST('[42, 51]' AS JSON)
);

-- 只要求存在任意交集：仍然使用同一目标 path
SELECT profile_id
FROM customer_profiles
WHERE JSON_OVERLAPS(
  profile->'$.labels',
  CAST('[42, 51]' AS JSON)
);

容易误判的写法是 WHERE 42 MEMBER OF(profile)，或者让索引使用 $.labels 而查询改用 $.attributes[*].id。即使两条路径在业务数据上“看起来都能找到标签”，对优化器来说它们也不是同一个索引表达式。不要只把 JSON_CONTAINS 换成 MEMBER OF；先比较目标 JSON path，再比较 CAST 类型和谓词含义。

用 EXPLAIN 区分路径不匹配和成本放弃

排查时先看同一条查询的 possible_keys、key、访问类型和估算行数。若 possible_keys 为 NULL，或者没有出现 idx_profile_labels，先回到索引表达式与 WHERE 谓词逐字符对照；这通常不是“表太小”的成本问题。若 possible_keys 已包含索引，但 key 仍为 NULL，才进入选择性、数据分布和回表成本的判断。

-- 只观察优化器是否识别到同名多值索引，不把输出当成固定运行结果
EXPLAIN
SELECT profile_id, profile
FROM customer_profiles
WHERE 42 MEMBER OF(profile->'$.labels');

多值索引是二级索引，不能作为覆盖索引；命中索引后仍可能回表读取完整 JSON。若标签值出现在绝大多数记录中，优化器也可能认为全表访问更便宜。此时“没有使用索引”不一定表示路径写错，但要先确认索引确实出现在候选集合里。

把空数组、类型和设计边界写进排障清单

还要记住多值索引的实际边界：它不支持排序、范围扫描或覆盖索引，也不能直接替代规范化的关联表。本文只处理“索引表达式与查询 JSON path 不一致”这一类故障；如果数组对象结构本身需要拆分、去重或维护外键，应另行评估数据模型。

相关问题

为什么整列 JSON 查询能返回结果，却没有使用多值索引？

能返回正确结果只说明谓词语义成立，不说明它与索引表达式匹配。多值索引需要从索引定义的 JSON path 中取数组元素，查询直接指向整列时通常没有可匹配的同一表达式。

JSON_CONTAINS 的候选数组必须和索引数组完全一样吗？

不需要值集合完全相同，但目标 path 要对应索引的数组路径，候选 JSON 的业务含义也要保持一致。包含和重叠的判断条件不同，不能为了复用索引而混用。

EXPLAIN 没选索引时要不要立刻 FORCE INDEX？

不建议先强制。先区分 path 不匹配、元素类型不一致和选择性过低，再用受控环境评估计划；强制索引不能修复错误的 JSON path，也可能放大回表成本。

小结：多值索引排障的第一步是把 CAST 里的 JSON path 与 MEMBER OF、JSON_CONTAINS 或 JSON_OVERLAPS 的目标 path 对齐。确认候选索引出现后，再根据选择性、空数组、回表和谓词语义判断最终计划是否合理。