MySQL 多值索引怎么查询 JSON 数组成员

订单表里有一个 JSON 字段 profile，其中 zip_codes 是数组。查询某个邮编时，如果直接写 JSON 条件，执行计划往往仍是全表扫描。MySQL 8.0.17 及以上的 InnoDB 可以用多值索引，把数组里的每个标量值建立成索引记录，再让成员查询走索引。

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

核心做法是：先用 MEMBER OF()、JSON_CONTAINS() 或 JSON_OVERLAPS() 表达数组匹配，再对同一 JSON 路径建立 CAST(... AS ... ARRAY) 多值索引，最后用 EXPLAIN 检查目标索引是否出现在执行计划中。

下面用 customers.profile->'$.zip_codes' 作为示例，重点是查询形态和索引边界，不依赖特定业务表名。

先确认查询形态：你查的是数组成员

多值索引服务的是“数组里是否包含某个值”，不是任意 JSON 文档搜索。假设数据类似 {"zip_codes":[310000,310001]}，单个邮编可以这样写：

-- 用标量匹配 JSON 数组中的一个成员
SELECT id, profile
FROM customers
WHERE 310001 MEMBER OF (profile->'$.zip_codes');

-- 多个候选值至少命中一个时，使用 JSON_OVERLAPS
SELECT id
FROM customers
WHERE JSON_OVERLAPS(profile->'$.zip_codes', JSON_ARRAY(310001, 310002));

MEMBER OF() 适合单个成员，JSON_CONTAINS() 适合要求候选数组全部包含，JSON_OVERLAPS() 适合两个数组存在交集。不要只把 JSON_EXTRACT() 包在函数里就期待索引一定生效，优化器要识别出支持的搜索函数和对应路径。

用 CAST ARRAY 创建多值索引

索引表达式必须把 JSON 数组转换成明确的 SQL 类型数组。整数邮编可以使用 UNSIGNED ARRAY；如果数组存的是字符串，就应选择合适的字符类型，并保持写入数据类型一致。

-- 对 zip_codes 数组建立多值索引；路径必须和查询保持一致
ALTER TABLE customers
  ADD INDEX idx_zip_codes (
    (CAST(profile->'$.zip_codes' AS UNSIGNED ARRAY))
  );

-- 统计持久化开启时，建索引后刷新优化器统计信息
ANALYZE TABLE customers;

它不是把整个 JSON 文档当成一个值，而是让一行数据对应多个索引记录。这样查询 310001 时，索引可以先定位包含该元素的行，再回表读取完整 profile。如果要做联合索引，只能放一个多值键部件；例如可以把 tenant_id 作为普通键部件，但不能在同一个索引里放两个数组表达式。

用 EXPLAIN 判断索引是否真的命中

建完索引不要只看 DDL 成功。用和线上完全相同的谓词检查执行计划：

-- 查看单成员查询是否选择目标索引
EXPLAIN SELECT id
FROM customers
WHERE 310001 MEMBER OF (profile->'$.zip_codes');

重点观察 key 是否为 idx_zip_codes，访问类型是否从 ALL 变为更有选择性的索引访问，以及 rows 估算是否明显下降。测试数据很少时，优化器可能认为全表扫描更便宜；这不等于索引定义无效，应使用接近生产分布的数据，并结合实际查询条件复查。

多值索引不是覆盖索引，查询仍可能回表；它也不支持范围扫描、排序或仅靠索引返回所有列。目标是减少“按数组成员找行”的过滤成本，而不是让所有 JSON 查询都变快。

排查空数组、NULL 与类型不一致

这类索引最容易在数据边界上“看起来不生效”。空数组不会产生索引记录，因此对应行不能通过该索引扫描找到；生成结果为 SQL NULL 时会形成 NULL 索引记录，而 JSON 数组中的 JSON null 不允许作为多值索引元素。整数 310001 和字符串 "310001" 也不是同一个匹配值，不能靠隐式转换补救。

上线前建议把检查写成清单：

结论与两个常见追问

MySQL 多值索引适合“JSON 数组成员定位”这一窄场景：查询函数、JSON 路径、CAST 类型三者要对应，执行计划要实测，数据边界要提前清理。若需求是复杂对象过滤、范围排序或覆盖读取，应该重新评估关系表拆分或生成列方案，而不是继续堆叠多值索引。

为什么索引存在但 EXPLAIN 仍显示 ALL？

常见原因是数据量小、谓词未使用支持的函数、路径或类型不一致，或者统计信息过旧。先核对表达式，再用接近生产规模的数据和 ANALYZE TABLE 后的计划复查。

JSON 数组里能放对象吗？

多值索引适合可转换为同一 SQL 标量类型的数组元素。数组对象的字段过滤通常需要不同的数据建模方式，不能直接把任意对象结构当成一个可排序的多值键。