MySQL 多值索引怎样限制数组元素膨胀

MySQL 多值索引不能像普通配置项那样设置“每行最多 N 个数组元素”。它为 JSON 数组中的多个值分别建立索引记录；真正的硬边界取决于一条记录在单个 undo log page 中能容纳的索引键数据。实务上应先用业务规则限制数组长度，再用元素类型和字节数评估引擎上限。

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

不要把“测试中约 1604 个整数键”当成固定配额。它只是官方示例环境下的观察值；字符串、排序规则和索引表达式都会改变可容纳数量。

• 业务控制：用 JSON_LENGTH() 配合 CHECK 或应用层校验，把数组压在可接受范围内。
• 引擎控制：单行多值键总长度上限为 65221 字节，超出时会出现 3905 或 3906 错误。
• 模型控制：数组很大或需要范围、排序、覆盖索引时，优先考虑拆成关联表。

先分清两种“上限”

多值索引适合把一个 JSON 数组中的成员用于等值成员查询。InnoDB 会让一行数据对应多个索引记录，所以数组从 8 个元素扩大到 800 个元素时，索引维护量也会跟着扩大。这个结构解决的是“数组里是否包含某个值”，不是把任意长度数组变成普通列索引。

MySQL 8.4 手册说明，单行多值索引的最大键数据量由单个 undo log page 决定，当前描述为 65221 字节；键数量没有统一固定值，官方只给出过整数数组约 1604 个键的测试观察值。因而设计时应该把 1604 当作风险信号，而不是可写入的安全阈值。

按成员查询建立多值索引

先把数组限制在明确的 JSON 路径，再用 CAST(... AS ... ARRAY) 指定元素类型。下面的例子只围绕标签成员查询，使用 MEMBER OF()、JSON_CONTAINS() 或 JSON_OVERLAPS() 这类官方支持的查询形式。

CREATE TABLE user_profiles (
    id BIGINT PRIMARY KEY,
    profile JSON NOT NULL,
    -- 多值索引把 profile.tag_ids 的每个整数元素作为独立键值。
    INDEX idx_tag_ids ((CAST(profile->'$.tag_ids' AS UNSIGNED ARRAY)))
) ENGINE = InnoDB;

-- 成员查询使用数组索引；查询目标是“包含某个标签”，不是数组排序。
SELECT id
FROM user_profiles
WHERE 42 MEMBER OF (profile->'$.tag_ids');

这里的关键不是把数组“压缩”到某个固定数量，而是让索引只承担高选择性的成员查询。多值索引不支持排序、覆盖索引和范围扫描；如果业务要按数组元素排序、统计每个元素，或经常读取数组中的全部内容，它就不是合适的唯一模型。

用 CHECK 把业务阈值前置

引擎硬上限属于最后一道保护，不能替代产品规则。比如标签最多允许 64 个，就在表结构中把“必须是数组”和“长度不超过 64”写清楚；应用层仍应在接收请求时提前返回可读错误，避免把数据库异常当成正常业务反馈。

ALTER TABLE user_profiles
    ADD CONSTRAINT chk_tag_ids_count
    CHECK (
        -- 先确认目标值是数组，再限制元素数量。
        JSON_TYPE(profile->'$.tag_ids') = 'ARRAY'
        AND JSON_LENGTH(profile->'$.tag_ids') 

如果字段允许缺省，需要单独设计“缺省”和“空数组”的语义，不要无意中把缺失路径、SQL NULL 和 JSON null 混为一谈。官方手册还指出，空数组不会生成索引记录，而被索引数组中的 JSON null 不允许作为有效索引元素。

超限时怎样处理

遇到 ER_EXCEEDED_MV_KEYS_NUM（3905）或 ER_EXCEEDED_MV_KEYS_SPACE（3906）时，先确认是哪一个数组路径生成了过多或过大的索引键，再决定方案。不要只把列类型从字符串换成整数，也不要简单提高业务阈值；元素字节数和查询选择性才是核心约束。

最终可以用一句话判断：多值索引限制不了业务数组长度，只能在超过内部容量时拒绝写入；真正可控的方案是用业务校验限制数组，用 EXPLAIN 确认成员查询是否有收益，并在数组逐渐变成“子表”时及时拆模。

常见问题

能不能通过参数把每行最多元素数改成 100？

没有通用的多值索引 DDL 参数可以直接设置这个业务值。应使用 JSON_LENGTH() 配合 CHECK 或写入层校验。

官方提到的 1604 个整数键是不是固定上限？

不是。它是测试观察值；数组元素类型、键值长度和实际索引数据都会影响可容纳数量。

数组太大时只删除索引可以吗？

如果查询不再需要成员索引，可以删除；但若业务还要按元素筛选、分页或统计，通常应拆成关联表，而不是继续把大集合塞进 JSON。