MySQL 多值索引为什么不能覆盖返回 JSON 数组

先说结论：MySQL 多值索引能加速 JSON 数组里的成员判断，却不能覆盖返回原始 JSON 数组。它把一个数组拆成多个独立索引记录，每条只保存一个数组元素以及指向同一聚簇记录的定位信息；完整数组仍在主表的 JSON 列中。因此，即使 EXPLAIN 显示命中了多值索引，只要投影里包含原始 JSON 文档，InnoDB 仍要读取对应的聚簇记录。

适用范围：先把“过滤”和“返回”分开

这个限制适用于 MySQL 8.4 的 InnoDB 多值索引。多值索引面向 JSON 数组成员查询，优化器可在合适条件下把它用于 MEMBER OF()、JSON_CONTAINS() 和 JSON_OVERLAPS()。它解决的是“哪些行含有这个成员”，不是“能否只读索引就重建这一行的完整数组”。

变更原理：命中元素不等于拥有完整数组

假设一行数据的 doc.category_ids 是 [11, 22, 33]。普通索引容易让人形成“一行对应一个索引键”的直觉，但多值索引会为 11、22、33 分别建立索引记录，而且这些记录都指向同一条聚簇记录。查询条件找 22 时，索引可以迅速定位候选行；然而索引项 22 并不包含另外两个元素，也没有保存可还原原数组的顺序、结构和文档上下文。

MySQL 手册把这一点写成两条限制：多值索引不能成为覆盖索引；由于同一聚簇记录对应的索引记录分散在多值索引中，它也不支持 index-only scan。这里的“不能覆盖”不是缺少某个配置，而是索引记录形态决定的能力边界。

旧代码风险：看到 key 不代表没有回表

下面的表使用整数数组，索引表达式把 JSON 数组成员转换为 UNSIGNED ARRAY。这也是官方示例采用的基本形式。

-- 建表：doc 中的 category_ids 保存同类型整数数组
CREATE TABLE product (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    doc JSON NOT NULL,
    PRIMARY KEY (id),
    INDEX idx_category_ids (
        (CAST(doc->'$.category_ids' AS UNSIGNED ARRAY))
    )
) ENGINE = InnoDB;

-- 写入两条用于观察索引行为的测试记录
INSERT INTO product (doc) VALUES
    ('{"name":"A","category_ids":[11,22,33]}'),
    ('{"name":"B","category_ids":[22,44]}');

成员条件可以利用 idx_category_ids 找到含有 22 的行：

-- 只解释访问路径：确认成员条件是否使用多值索引
EXPLAIN
SELECT id, doc
FROM product
WHERE 22 MEMBER OF (doc->'$.category_ids');

如果计划中的 key 是 idx_category_ids，它只说明候选行由该索引定位。由于结果还要求返回 doc，执行器必须根据索引记录中的主键定位读取聚簇记录。换句话说，“用了索引”与“覆盖查询”是两个不同判断。

即使把普通标量列与多值表达式放进一个复合索引，多值索引整体仍然受“不能覆盖”的限制。复合索引可以帮助额外条件缩小范围，但不能把完整 JSON 数组变成可由索引直接返回的值。

新写法：按返回目标选择数据模型

方案一：仍需完整 JSON，就接受回表并控制候选行

如果接口确实需要返回完整文档，多值索引仍有价值：它先把不相关行排除，再对少量命中行读取主表。优化重点应放在成员选择性、返回行数、分页边界和单个 JSON 文档大小上，而不是追求不存在的“多值索引覆盖 JSON”。

-- 先用成员条件缩小候选集，再限制单次返回量
SELECT id, doc
FROM product
WHERE 22 MEMBER OF (doc->'$.category_ids')
ORDER BY id
LIMIT 100;

方案二：只返回高频标量，就把标量抽出来

如果查询真正需要的是 JSON 文档中的单值属性，例如状态，而不是数组本身，可以建立生成列和普通复合索引。对于 InnoDB，普通二级索引叶子记录包含主键值，因此 (status, id) 可以直接服务只返回 id 的查询。

-- 抽取高频单值属性，避免每次读取完整 JSON 文档
ALTER TABLE product
    ADD COLUMN status VARCHAR(20)
        GENERATED ALWAYS AS (
            JSON_UNQUOTE(doc->'$.status')
        ) STORED,
    ADD INDEX idx_status_id (status, id);

-- 投影只包含复合索引中的标量字段
SELECT id
FROM product
WHERE status = 'active';

方案三：成员本身是一等实体，就关系化拆分

当业务频繁按成员分页、计数、排序、关联或维护成员属性时，数组已经不只是文档内部细节。把成员拆到关联表后，复合主键可以覆盖“按类别取商品 ID”这类查询。需要注意：如果仍要一次返回全部成员，依然要读取多条关联记录并聚合，只是数据模型更适合这类访问。

-- 关联表让每个数组成员成为可独立索引的关系记录
CREATE TABLE product_category (
    product_id BIGINT UNSIGNED NOT NULL,
    category_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (category_id, product_id),
    KEY idx_product_id (product_id)
) ENGINE = InnoDB;

-- 复合主键可直接返回匹配类别下的商品编号
SELECT product_id
FROM product_category
WHERE category_id = 22;

回归检查：迁移后不要只看一次 EXPLAIN

• 确认条件形式：成员判断使用与索引表达式兼容的数据类型，避免字符串与数值混用导致条件无法按预期利用索引。
• 确认投影字段：把“用于过滤的字段”和“最终返回的字段”分别列出来；只要返回原始 JSON，就把回表成本纳入预算。
• 确认选择性：高频成员可能命中大量行，索引可用不等于一定是最低成本计划，应使用接近生产分布的数据观察执行计划。
• 确认文档体积：同样的命中行数下，较大的 JSON 文档会放大缓冲池读取、网络传输和反序列化开销。
• 确认写入代价：数组元素越多，一行变更涉及的多值索引记录越多；压测必须同时包含写入和更新。
• 确认回归口径：比较相同过滤条件、相同投影和相同返回量，避免用“只返回 id”的新查询去对比“返回完整 doc”的旧查询。

迁移清单

1. 成员过滤仍使用多值索引，不再把它视为覆盖索引候选。
2. 接口若必须返回完整 JSON，控制命中量、分页大小和文档体积。
3. 接口若只要少量标量，优先抽取生成列并建立普通复合索引。
4. 成员需要独立排序、关联或维护属性时，评估关系化子表。
5. 通过实际数据分布检查执行计划与读行量，不用“key 非空”代替完整性能结论。

归根结底，多值索引是一种“把数组成员变成可搜索键”的结构，不是“把 JSON 文档复制进索引”的结构。明确查询最终要返回什么，才能在保留 JSON 灵活性、减少回表和关系化建模之间做出正确选择。

参考资料

• MySQL 8.4 Reference Manual：CREATE INDEX 与 Multi-Valued Indexes
• MySQL 8.4 Reference Manual：Functions That Search JSON Values