MySQL JSON_OVERLAPS 什么条件下能使用多值索引

我遇到过一种很容易误判的情况：索引已经创建成功，查询也确实写了 JSON_OVERLAPS()，但 EXPLAIN 仍然显示全表访问。关键不在函数名本身，而在存储引擎、JSON 数组表达式、查询路径和候选数组类型是否能对上。

官方文档：https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html

一、先分清 JSON_OVERLAPS 的相交语义

JSON_OVERLAPS(a, b) 对数组执行的是 OR 关系：两边只要共享一个数组元素就返回 1。它和 JSON_CONTAINS() 的“候选数组全部存在”不是一回事。例如筛选“标签包含任意一个目标标签”时，重叠查询才符合语义；如果要求所有标签都命中，应先重新确认业务条件，不能只为了用索引改函数。

-- 只要 zipcode 数组与目标数组有一个元素相同，就命中
SELECT id, custinfo
FROM customers
WHERE JSON_OVERLAPS(
  custinfo->'$.zipcode',
  CAST('[94507, 94582]' AS JSON)
);

另外，JSON 比较不会把数字字符串自动当成数字。索引数组是数值时，候选数组也要保持数值类型；"94507" 与 94507 不能按同一个数组元素理解。

二、把 JSON 数组表达式建成多值索引

多值索引的核心不是给整个 JSON 文档做一个普通键，而是把一行中的数组拆成多个索引记录。下面的表达式把 zipcode 数组中的元素转换为无符号整数数组，再建立名为 zips 的索引：

-- JSON 路径要指向数组；UNSIGNED 要与数组元素的实际类型一致
ALTER TABLE customers
  ADD INDEX zips(
    (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY))
  );

因此至少要同时满足这几个条件：

三、保持查询路径和索引路径一致

索引建在 custinfo->'$.zipcode'，查询左侧就不要换成另一个路径，也不要在外层包一层无法匹配的表达式。候选数组使用 JSON 文档形式显式转换，便于保持类型和语义清楚：

-- 右侧是 JSON 数组；OVERLAPS 表示任意一个 zipcode 相交即可
EXPLAIN SELECT id
FROM customers
WHERE JSON_OVERLAPS(
  custinfo->'$.zipcode',
  CAST('[94507, 94582]' AS JSON)
);

如果业务实际上是“同时包含 94507 和 94582”，这个查询仍然只要求命中其中一个。此时应考虑 JSON_CONTAINS() 的 AND 语义，并重新观察它的执行计划，不能把“有索引”和“语义正确”混成一件事。

四、用 EXPLAIN 判断是否真的使用索引

我排查这类问题时只看四个字段：possible_keys 表示候选索引，key 表示最终选择，type 反映访问方式，rows 是优化器估算需要检查的行数。官方示例中，建立 zips 后，JSON_OVERLAPS() 查询可以呈现 type=range 且 key=zips 的计划。

-- 先看优化器是否把 zips 放进候选并最终选中
EXPLAIN
SELECT id, custinfo
FROM customers
WHERE JSON_OVERLAPS(
  custinfo->'$.zipcode',
  CAST('[94507, 94582]' AS JSON)
);

判断可以按下面的清单进行：

• key=zips：本次计划最终选中了多值索引。
• possible_keys 有 zips 但 key=NULL：索引可用但成本模型没有选择它，先检查选择性和估算统计信息。
• type=ALL 且 key=NULL：当前计划是全表访问，优先核对路径、类型、存储引擎和索引定义。
• rows 很大：即使使用了索引，也要结合返回列、过滤比例和实际数据分布判断收益。

五、按数据形态排查不生效和边界

空数组不会产生多值索引条目，所以它不能通过索引扫描被找到；数组中的 JSON null 也不是普通 SQL NULL，建立或维护索引时可能触发无效 JSON 值错误。先把这些数据清洗规则固定下来，再决定索引是否值得保留。

多值索引还不能作为覆盖索引，也不支持排序、范围扫描或索引前缀；复合索引最多放一个多值键部分。它适合解决“按数组成员快速找行”，不适合顺便完成排序或只从索引返回全部列。

最后，别把“能使用”理解成“每次都会使用”。MySQL 优化器仍会比较成本。上线前至少准备三组计划：无索引、索引存在但路径不匹配、路径和类型都匹配；分别记录 key 与 rows，这样遇到数据量变化时才知道是语义问题、定义问题还是成本选择问题。

相关问题

JSON_OVERLAPS 能保证两个数组全部相同吗？

不能。它只判断是否至少有一个元素或键值对相交；“全部包含”要使用不同的查询语义。

看到 possible_keys=zips 就代表已经走索引吗？

不代表。possible_keys 只是候选集合，最终应看 key；如果是 NULL，还要结合成本估算解释原因。

多值索引能同时解决 ORDER BY 吗？

不能把它当排序索引使用。它主要服务于 JSON 数组成员的过滤，排序和覆盖查询要另行设计。