MySQL JSON 多值索引教程：标签成员查询与类型边界

把用户标签放进 JSON 数组后，查询“包含标签 7 的用户”不能按普通标量索引理解。一行里可能有几十个标签，传统索引的一条键值无法替每个数组元素建立入口。

MySQL 8.4 的 InnoDB 支持多值索引：同一行的多个数组元素可以分别产生索引记录。这个功能适合有界、同类型的成员集合，不会把 JSON 自动变成一张可以随意关联、排序和约束的关系表。

从数字标签开始，先避开字符转换干扰

实验固定 MySQL 8.4。标签使用非负整数，数组本身不能为 SQL NULL，也不能是对象。

CREATE DATABASE IF NOT EXISTS multivalue_lab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE multivalue_lab;

CREATE TABLE customer_profile (
    id BIGINT NOT NULL,
    tenant_id INT NOT NULL,
    display_name VARCHAR(80) NOT NULL,
    tag_ids JSON NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT chk_tag_array CHECK (JSON_TYPE(tag_ids) = 'ARRAY')
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 6000;
INSERT INTO customer_profile (id, tenant_id, display_name, tag_ids)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 5000
)
SELECT n, MOD(n, 5), CONCAT('customer-', n),
       JSON_ARRAY(MOD(n, 50), 100 + MOD(n, 7))
FROM seq;

INSERT INTO customer_profile
    (id, tenant_id, display_name, tag_ids)
VALUES (9001, 2, 'no-tags', JSON_ARRAY());

chk_tag_array 只检查外形是数组，不证明所有元素都是合法的整数标签。应用入口仍要校验元素类型、取值范围和数组长度。若需要由数据库完整校验，应增加与业务范围一致的 JSON Schema 规则，不能把一次类型转换当成数据治理。

先执行不带多值索引的查询，保存基线：

EXPLAIN
SELECT id, display_name
FROM customer_profile
WHERE 7 MEMBER OF (tag_ids);

SELECT COUNT(*) AS matched
FROM customer_profile
WHERE 7 MEMBER OF (tag_ids);

按种子脚本，前 5000 行中每 50 行有一行包含标签 7，所以匹配数量是 100。空数组那一行不会增加这个数量。这个计数用于验证数据和谓词，不能当成索引优化效果。

为数组元素创建入口

CREATE INDEX idx_tag_id
ON customer_profile ((CAST(tag_ids AS UNSIGNED ARRAY)));

ANALYZE TABLE customer_profile;

EXPLAIN
SELECT id, display_name
FROM customer_profile
WHERE 7 MEMBER OF (tag_ids);

额外的一层括号属于函数键部分语法。UNSIGNED ARRAY 告诉数据库把数组元素按无符号整数处理，这个表达式用于索引定义，不能当作普通查询里随处可用的“SQL 数组类型”。

检查 possible_keys、实际 key 和访问行数。优化器是否选择索引仍受数据量、选择性及返回列影响；候选索引存在，不意味着每条查询都一定使用。需要对照时可以在实验库临时加 FORCE INDEX (idx_tag_id)，用完再回到无提示 SQL 检查自然计划。

多值索引不是覆盖索引。即使查询只取主键，也不要套用普通二级索引的覆盖读取经验来承诺不回表。它也不承担排序、通用范围扫描或外键约束。把“数组成员命中更容易定位”作为目标，边界会清楚得多。

包含全部标签，和命中任意一个标签，不是一条条件

-- 必须同时包含 7 和 102。
SELECT id, tag_ids
FROM customer_profile
WHERE JSON_CONTAINS(tag_ids, JSON_ARRAY(7, 102));

-- 包含 7 或者包含 102 即可。
SELECT id, tag_ids
FROM customer_profile
WHERE JSON_OVERLAPS(tag_ids, JSON_ARRAY(7, 102));

这两种成员查询都属于多值索引支持的谓词方向，但语义不同，返回数量也不同。接口若叫“标签筛选”，必须明确是交集筛选还是并集筛选，不能让前端勾选方式替数据库隐式决定。

再检查一个常见错误：数字标签和字符串标签。

SELECT
    7 MEMBER OF (JSON_ARRAY(7, 8)) AS numeric_match,
    '7' MEMBER OF (JSON_ARRAY(7, 8)) AS string_match;

这里的结果分别应为 1 和 0。MEMBER OF 不会把字符串和数字自动视为相同成员。若接口把数字参数都序列化成字符串，数据库索引建得再正确，也可能查不出符合业务直觉的结果。修复应从参数契约开始，而不是把字段随意转成字符拼接搜索。

加上租户后，再评估联合多值索引

如果每次查询都固定租户，单独按标签定位可能先命中许多其他租户的用户。可以建立联合索引做对照：

CREATE INDEX idx_tenant_tag
ON customer_profile
    (tenant_id, (CAST(tag_ids AS UNSIGNED ARRAY)));

ANALYZE TABLE customer_profile;

EXPLAIN
SELECT id, display_name
FROM customer_profile
WHERE tenant_id = 2
  AND 7 MEMBER OF (tag_ids);

一个多值索引可以包含普通键部分，但其中只能有一个多值键部分。不要尝试把两份独立数组都放进同一联合索引，期待数据库自动维护它们的笛卡尔组合。

保留 idx_tag_id 和 idx_tenant_tag 只是实验对照，并非建议最终同时保留。上线设计要看是否确实存在跨租户标签查询；如果全部入口都按租户查询，多维护一棵单列多值索引可能只增加写成本。

空数组和非法元素都需要单独验证

SELECT id, display_name
FROM customer_profile
WHERE JSON_LENGTH(tag_ids) = 0;

空数组不会为多值键部分生成索引记录，因此“没有任何标签”的查询不能假设走成员索引就能覆盖。它需要自己的筛选方案，例如由明确的标量状态或受控统计支持，具体取决于查询频率。

下面这条语句应单独执行，预期失败：

-- 预期失败：多值索引不允许被索引数组中出现 JSON null。
INSERT INTO customer_profile
    (id, tenant_id, display_name, tag_ids)
VALUES (9002, 2, 'invalid-tags', JSON_ARRAY(7, NULL));

JSON_ARRAY(7, NULL) 会包含 JSON 的 null 元素，它与整个 JSON 列为 SQL NULL 不是同一层问题。不要捕获错误以后直接删除“有问题的那个元素”再入库，这可能悄悄改变请求含义。更合理的是在写入前拒绝非法载荷，并让客户端修正原始数据。

数组还必须有业务上限。一行从两个标签膨胀到几百个标签，会增加索引记录和更新维护；修改整份数组也不是免费的文档替换。若标签关系还要保存添加人、来源、有效期或强外键，关系表通常更容易表达这些要求。

数组与集合的业务语义也要约定。[7, 7, 8] 是包含三个元素的 JSON 数组，JSON_LENGTH 不会替你统计不同标签数；查询是否包含标签 7，又只是一个布尔判断。若标签在业务上是集合，应在写入入口去重，并明确是否需要保留顺序，避免不同接口把同一字段当成不同的数据结构。

添加索引前还应先扫描历史载荷。新接口的校验已经完善，不代表旧数据没有空值、字符串编号或过长数组。先在隔离恢复的数据上完成建索引和代表性查询，才能发现那些只有读取旧记录时才触发的转换问题。

最后一个部署边界是建索引过程。MySQL 8.4 多值索引创建不支持按普通二级索引那样直接套用在线创建假设，涉及 ALGORITHM=COPY。给已有大表加索引前，要核对锁、空间及写入窗口，不能看到一条 CREATE INDEX 就认为线上负担很轻。

完成实验后清理：

DROP DATABASE multivalue_lab;

多值索引让数组成员有了查询入口，前提是你愿意约束这份数组的形状、类型和规模。缺少这些约束，查询优化只是把数据问题推迟暴露。