MySQL JSON 数组检索要不要建多值索引：从 MEMBER OF 到不能排序的边界

先给结论：如果 JSON 列保存的是标签、地区编号或权限编号数组，查询目标是“数组里是否包含某个值”，MySQL 8 的 InnoDB 多值索引值得考虑；如果目标是按数组内容排序、分页，或者按对象里的数值排序，它就不是万能索引。最容易验证的入口是 MEMBER OF() 配合 EXPLAIN。

• 成员筛选：多值索引可以让数组元素分别参与查找。
• 类型一致：索引表达式的类型要和实际元素一致。
• 排序边界：多值索引解决“找哪些行”，不自动解决“按哪个值排”。

1. 先把数组查询和排序问题分开

假设表中有一列 profile，内容类似 {"tags":[3,8,13],"score":91}。先确认业务真正要查的是标签成员，而不是对整段 JSON 做模糊匹配：

SELECT id, profile
FROM user_profile
WHERE 8 MEMBER OF (profile->'$.tags');

MEMBER OF() 判断一个标量是否是 JSON 数组元素，字符串 "8" 与数字 8 也不是同一种值。若要判断两个数组有交集，可以考虑 JSON_OVERLAPS()；若要求数组包含另一数组的全部元素，则是另一种语义。先固定语义，才能决定索引表达式。

2. 为数组元素创建多值索引

多值索引的关键是把数组路径放进类型转换表达式。下面示例按无符号整数标签建立索引：

CREATE INDEX idx_profile_tags
ON user_profile (
  (CAST(profile->'$.tags' AS UNSIGNED ARRAY))
);

它不是给每一行 JSON 只存一个键，而是为数组中的元素建立多个索引记录，并仍然指向同一行。因此空数组不会产生可扫描的索引条目，类型错误或混用数字字符串也会让预期命中变得不可靠。生产表上执行前，先用少量样本确认数组路径、元素类型和空值规则。

3. 用 MEMBER OF() 和 EXPLAIN 验证筛选段

索引建好后，保持查询表达式与索引路径一致，再看执行计划：

EXPLAIN
SELECT id
FROM user_profile
WHERE 8 MEMBER OF (profile->'$.tags');

重点观察 key、估算行数和访问类型，而不是只看“建过索引”。如果没有使用，先排查三件事：查询是否改成了不受支持的 JSON 函数组合；左侧值是否发生了类型转换；表是否使用 InnoDB。不要用 JSON_EXTRACT(profile, '$.tags') LIKE '%8%' 代替成员判断，这既改变语义，也很难稳定使用多值索引。

4. 为什么它不能直接替你完成排序

多值索引适合回答“哪些行包含 8”，却不能自然回答“这些行按 tags 中哪个位置或哪个元素排序”。数组可能有多个成员，数据库没有一个唯一的排序键。即便按对象里的标量排序，也应把它单独表达：

ALTER TABLE user_profile
  ADD COLUMN score_value INT
    GENERATED ALWAYS AS (
      CAST(JSON_UNQUOTE(profile->'$.score') AS UNSIGNED)
    ) STORED,
  ADD INDEX idx_score_value (score_value);

SELECT id
FROM user_profile
WHERE 8 MEMBER OF (profile->'$.tags')
ORDER BY score_value DESC, id DESC
LIMIT 20;

这样由多值索引负责成员过滤，由生成列索引负责排序候选；最终计划仍要结合数据分布判断。若分页要求稳定，补上唯一键作为第二排序键，避免同分记录在翻页时漂移。

小结

数组成员检索是多值索引的主场：固定 JSON 路径、固定元素类型，用 MEMBER OF() 表达查询，再用 EXPLAIN 看实际计划。排序、聚合和分页属于另一条优化链，优先使用生成列或关系型字段承载稳定排序值。

相关问题

JSON_CONTAINS() 能用多值索引吗？在满足官方支持的查询形态时可以被优化，但它表达的是包含关系，不能与 MEMBER OF() 的单成员语义混用。

数组里存字符串数字可以吗？可以，但索引和查询必须保持同一 JSON 类型；不要指望数字 8 自动等于字符串 "8"。

建了索引却没命中怎么办？先用 EXPLAIN 核对路径、类型、函数形态和表引擎，再根据选择性判断是否值得保留，不要只凭索引存在与否下结论。

事实依据：MySQL 8.4 CREATE INDEX 文档、JSON 搜索函数文档。