MySQL 8.0 多值索引详解：如何通过 CAST(... AS ... ARRAY) 优化 JSON 数组查询性能

MySQL 8.0 多值索引的核心：必须使用 CAST(... AS ... ARRAY)

许多开发者在尝试为JSON数组字段创建索引时，常误以为可以直接在字段上操作。实际上，MySQL多值索引的实现依赖于一个关键步骤：必须使用 CAST 函数将JSON数组显式转换为指定SQL类型的数组。这一操作会在数据库内部隐式创建一个虚拟列，并最终在该虚拟列上建立函数索引。省略此步骤将导致索引创建失败或生成无法使用的索引。

典型的错误信息是：ERROR 3152 (HY000): Cannot create a functional index on a non-stored generated column，其根源正是未明确声明数组的数据类型。

• 正确示例：CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)；直接使用 custinfo->'$.zipcode' 会导致失败。
• 数组内所有元素必须为同一类型，且可映射到SQL标准类型（如 UNSIGNED、CHAR(10)），不支持字符串与数字混合。
• JSON路径必须精确指向一个数组结构（例如 $.tags），若指向对象或标量值（如 $.name），将引发 Invalid cast 错误。

哪些查询能有效利用多值索引？

创建索引后，并非所有涉及该JSON数组的查询都能加速。MySQL优化器仅在使用特定JSON函数时才会启用多值索引，常规的路径查询无法触发。

• 可触发索引的查询函数：MEMBER OF（如 WHERE 94582 MEMBER OF(custinfo->'$.zipcode')）、JSON_CONTAINS、JSON_OVERLAPS。
• 无法触发索引的查询：使用 -> 或 ->> 操作符的路径查询，以及像 WHERE custinfo->>'$.zipcode' LIKE '%94582%' 这样的模糊匹配。
• 使用 JSON_CONTAINS() 时需注意：第二个参数（搜索值）必须是字面量或变量，不能是列名；且路径参数必须与创建索引时的路径完全一致。

复合索引中多值键部分的限制

在实际应用中，常需将多值字段与其他列组合创建联合索引。语法上允许，但有一条严格限制：一个复合索引中只能包含一个多值键部分，但其位置可以任意。

例如，以下语句合法：CREATE INDEX idx_comp ON customers (id, CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY), modified)。但若尝试在一个索引中包含两个多值字段，则会报错。

• 最左前缀原则依然适用：若查询条件未包含复合索引中的前置列（如 id），仅使用多值部分，索引仍可能被使用。
• 注意索引体积：多值索引会显著增大索引大小，因为数组中的每个元素都会生成独立的索引记录（例如数组 [94582,94536] 会生成两条索引项）。
• 当前版本不支持利用多值索引对字段进行 ORDER BY 或 GROUP BY 排序操作。

如何通过 EXPLAIN 验证多值索引是否生效

使用 EXPLAIN 分析查询时，key 列显示的是底层隐式虚拟列的系统生成名称（如 func_1），而非自定义的索引名。只要 type 列显示为 range 或更优类型，且 Extra 列出现 Using index condition，即可确认索引已生效。

• 若 key 列为 NULL，首先检查查询是否使用了 MEMBER OF 等特定函数，其次确认查询值的类型与索引定义中的 CAST 类型是否匹配。
• rows 列的预估值可能偏高，这并不一定表示索引失效。对于多值索引，MySQL的行数估算通常较为保守，建议结合 EXPLAIN ANALYZE 查看实际扫描行数。
• 高并发场景注意：多值索引不支持在 SELECT … FOR UPDATE 等锁定读操作中进行锁升级，设计时需考虑潜在的锁等待问题。