MySQL 8.4 多值索引实战：JSON 数组字段别再全表扫

先说结论：JSON 数组能不能快，取决于你有没有把“数组元素”变成索引项

最近我会把 MySQL 8.x 里跟 JSON 有关的慢查询单独拎出来看，因为这类问题太容易被误判：开发同学觉得 JSON 字段灵活，DBA 看慢日志却只看到 rows_examined 一路飙升。最典型的场景是商品、订单、用户画像里存了一个标签数组，比如 tags=[3,8,15]，查询时用 JSON_CONTAINS 或 MEMBER OF 过滤。

如果没有多值索引，MySQL 基本只能一行一行取出 JSON，再判断数组里有没有目标值。数据量小的时候没感觉，几百万行以后，这类接口会很稳定地把 CPU、Buffer Pool 和慢日志一起点亮。

业务场景：标签筛选接口为什么越来越慢

假设有一张商品表，业务为了灵活扩展，把标签 id 放在 JSON 数组里：

CREATE TABLE goods (
  id BIGINT PRIMARY KEY,
  title VARCHAR(128) NOT NULL,
  status TINYINT NOT NULL,
  tags JSON NOT NULL,
  created_at DATETIME NOT NULL,
  KEY idx_status_id (status, id)
) ENGINE=InnoDB;

查询“带某个标签的上架商品”时，很多项目会写成这样：

SELECT id, title
FROM goods
WHERE JSON_CONTAINS(tags, CAST(8 AS JSON))
  AND status = 1
ORDER BY id DESC
LIMIT 20;

这条 SQL 最大的问题不是 JSON 函数本身，而是没有一个普通 B+Tree 索引能直接回答“数组里有没有 8”。即使有 idx_status_id，MySQL 也只能先按状态扫出大量候选行，再逐行计算 JSON 条件。标签越热门，扫描越多；标签越冷，可能扫到很后面才凑够 20 条。

复现慢查询：EXPLAIN 里最该看什么

我排这类问题时先不急着改表，先看三件事：谓词是否真的针对 JSON 数组元素、候选行是否巨大、排序和 LIMIT 有没有让扫描被放大。

EXPLAIN FORMAT=TREE
SELECT id, title
FROM goods
WHERE JSON_CONTAINS(tags, CAST(8 AS JSON))
  AND status = 1
ORDER BY id DESC
LIMIT 20;

如果你看到访问路径只命中了 idx_status_id，但 JSON 条件出现在过滤阶段，慢就不奇怪。慢日志里通常还会有这些信号：

• Rows_examined 远大于返回行数，甚至接近某个状态下的总行数。
• 同一个标签查询在高峰期波动明显，因为它强依赖缓存命中和扫描范围。
• SQL 本身返回很少，但 CPU 时间不低，说明大量成本花在逐行判断 JSON 上。

改造方案：用多值索引索引 JSON 数组元素

MySQL 8.0.17 开始支持多值索引，适合 JSON 数组里每个元素都需要参与检索的场景。它的直觉可以这样理解：一行数据里的一个数组，会在索引里产生多个可查找的元素项。比如 [3,8,15] 这行，会让 3、8、15 都具备被索引定位的机会。

商品标签如果都是无符号整数，可以这样建索引：

CREATE INDEX idx_goods_tags_mvi
ON goods ((CAST(tags->'$[*]' AS UNSIGNED ARRAY)));

然后把查询改成更直接的成员判断：

SELECT id, title
FROM goods
WHERE 8 MEMBER OF(tags)
  AND status = 1
ORDER BY id DESC
LIMIT 20;

这里有两个细节很重要。第一，索引表达式里的类型要和查询值类型一致，别一边是字符串 "8"，一边是数字 8。第二，多值索引能帮助数组成员过滤，但不代表排序、覆盖、复杂 JSON 路径都自动变快。它解决的是“数组元素能不能被索引定位”这个问题。

哪些写法更容易命中索引

多值索引不是所有 JSON 函数都能无脑命中。工程里我会优先把查询收敛到下面几类清晰表达：

-- 单个元素是否在数组中
SELECT id FROM goods
WHERE 8 MEMBER OF(tags);

-- 数组是否包含目标集合
SELECT id FROM goods
WHERE JSON_CONTAINS(tags, CAST('[8,15]' AS JSON));

-- 数组是否和目标集合有交集
SELECT id FROM goods
WHERE JSON_OVERLAPS(tags, CAST('[8,21]' AS JSON));

写完以后一定要跑 EXPLAIN，看 possible_keys、key、rows 有没有变化。不要只看 SQL 语义正确，也不要只看单次响应时间。缓存热的时候，错误方案也可能看起来很快。

踩坑原因：为什么“建了索引还是慢”

我遇到过几类常见误区：

• 数组类型混乱。 数据里既有 8 又有 "8"，索引按数字建，查询按字符串传，访问路径就很容易和预期不一致。
• 数组过长。 一行几十上百个元素，会放大二级索引维护成本。读变快了，写入和更新可能变慢。
• 把 JSON 当万能表结构。 如果标签本身需要复杂统计、强一致关联、频繁更新，关系表可能比 JSON 数组更稳。
• 只优化过滤，不处理排序。 多值索引筛出候选后，ORDER BY id DESC LIMIT 20 仍可能需要额外排序或回表。

上线检查：我会要求这几项都过

第一，先抽样清洗数据类型，确认数组元素是统一的整数或字符串，不要混。第二，在影子环境或低峰窗口建索引，记录 DDL 耗时、复制延迟和写入抖动。第三，对核心 SQL 做改造前后对比：EXPLAIN、慢日志、P95/P99、Rows examined 都要看。

-- 检查执行计划是否命中多值索引
EXPLAIN
SELECT id, title
FROM goods
WHERE 8 MEMBER OF(tags)
  AND status = 1
ORDER BY id DESC
LIMIT 20;

-- 必要时准备回滚
DROP INDEX idx_goods_tags_mvi ON goods;

生产里我还会盯写入侧。多值索引本质上会让一行 JSON 数组产生多个索引项，如果标签更新很频繁，写入放大就会比较明显。读接口省下来的时间，不能全让写接口还回去。

个人经验：JSON 可以用，但别让它逃过建模审查

我不反对在 MySQL 里用 JSON。问题是很多团队一开始为了灵活，把本该建模的关系塞进 JSON，后来又希望数据库像普通列一样高效过滤。多值索引能补上 JSON 数组检索的短板，但它不是免审查通行证。

我的建议很简单：如果只是少量标签、低频更新、查询模式明确，多值索引很好用；如果标签关系本身是核心业务，更新频繁，还要做统计、排序、权限组合，那就认真考虑拆成关联表。MySQL 能帮你优化一类访问路径，但不能替你补上数据模型的边界。

总结

MySQL 8.x 多值索引最适合解决 JSON 数组成员检索慢的问题。落地时记住四句话：数组元素类型要统一；索引表达式要和查询谓词对齐；上线前必须用 EXPLAIN 和慢日志验证；读性能收益要和写入维护成本一起算。这样用，它是一个很实在的工具；乱用，它只是把问题从查询阶段挪到写入和维护阶段。