MySQL Multi-Valued Indexes Give JSON Arrays Real Index Lookups, Six Traps Included

引子

最近找到工作了,也干了几个月,最近在处理一个新需求的时候,看到有同事写了

  AND JSON_CONTAINS(su.post_codes, CONCAT('"', 'CT', '"'))
  AND JSON_CONTAINS(su.subject_list, CAST('1' AS JSON));

心里有一个疑问,在mysql使用json 这不是找死吗?不会全表扫描吗? 让我看看索引. 结果发现了我不认识的索引结构

INDEX `idx_subject`((cast(`subject_list` as char(255) array))) USING BTREE,
INDEX `idx_post_codes`((cast(`post_codes` as char(255) array))) USING BTREE

这个是什么索引?查了一下,发现这个是 MySQL 8.0.17+ 的「多值索引（multi-valued index）」 我以前确实没有使用过的,既然遇到了,那就让我们走进这个MySQL的新特性多值索引：让 JSON 数组也能走索引的索引。

小tps： 上面这个sql其实是有坑的，看完你就知道上面这个sql的问题在哪里了。

0. 故事从一个假的索引开始

先说个让人怀疑人生的场景。

你有一个 JSON 列，里面存的是数组：

{"name": "Bob", "skill_tags": [11, 23, 37]}  

需求是「查出所有带 23 号技能标签的人」。你心里有数，这种查询不加索引迟早炸，于是很自然地敲下：

CREATE INDEX idx_tag ON staff (skill_tags);  
-- ERROR 3152 (42000): JSON column 'skill_tags' cannot be used in key specification  

数据库直接拒绝：JSON 列不能当索引用。

于是你退一步，用 JSON_CONTAINS 写查询，心里默念「先这样吧，数据量还小」：

SELECT * FROM staff WHERE JSON_CONTAINS(skill_tags, '23');  

EXPLAIN 一看，type = ALL，扫全表。数据量一涨，这条 SQL 就开始在慢日志里刷存在感。

在 MySQL 8.0.17 之前，这个问题没有好答案。 常见的土办法有三种：

• 生成列（GENERATED COLUMN）+ 普通索引 —— 但它只能索引单个标量，数组天然无能为力
• 老老实实拆一张标签关系表 —— 正确但麻烦
• 上 Elasticsearch —— 用大炮打蚊子

直到 8.0.17，官方终于正面回应了这个问题：多值索引（multi-valued index） 。

1. 它到底是什么：N:1 的索引

一句话概括：

普通索引是一行数据对应一条索引记录（1:1）；
多值索引是一行数据可以对应多条索引记录（N:1）—— 数组有几个元素，就拆几条。

数据：{"name":"Bob", "zipcode":[94477, 94536]}  
  
索引里实际变成：  
  94477  → 指向 Bob 这一行  
  94536  → 指向 Bob 这一行  

官方实现方式很讨巧：MySQL 会透明地生成一个虚拟列，把 JSON 数组转成 SQL 类型数组，然后在这个虚拟列上建函数索引。

所以有个重要推论：它本质是个虚拟列上的二级索引，虚拟列索引的所有规矩，它全都继承——包括那些让人抓狂的部分。

2. 语法：那个双括号不是手滑

CREATE INDEX idx_zip ON customers ((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));  

注意这里有两层括号。第一次看到会以为是笔误，其实是两个东西叠在一起：

• 外层括号：key_part 列表的固定语法，ON tbl (a, b, c) 的那个括号
• 内层括号：(expr) 表示这是一个函数索引，而不是列名

任何函数索引都长这样，跟多值没关系：

CREATE INDEX idx_full ON staff ((first_name + last_name));   -- 普通函数索引  

多值索引只是把表达式换成了 CAST(... AS ... ARRAY)。它也可以和普通列混在同一个复合索引里：

ALTER TABLE customers  
  ADD INDEX comp (id, modified, (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));  

但注意：一个复合索引里最多只能有一个多值 key part，位置倒是随意。

三条硬性要求

1. 被索引的列必须是 JSON 类型。varchar 里塞 JSON 字符串不算。
2. ARRAY 关键字。 ARRAY 不是数组类型，是"按元素拆开建索引"的指令。不写就退化成普通函数索引（1:1），多值能力全无。
3. 必须用 CAST() ，CONVERT() 不支持。

类型能填什么

AS 后面可以跟 CAST() 支持的任意类型，但排除 BINARY、JSON、YEAR。实际常用的：

写法	适用场景
UNSIGNED ARRAY / SIGNED ARRAY	数字 ID、枚举码（首选）
CHAR(n) ARRAY	字符串编码，如 "CT"、"VIP"
DECIMAL / FLOAT / DOUBLE ARRAY	金额、评分
DATE / DATETIME / TIME ARRAY	日期数组

3. 六个容易踩的坑

官方文档里 JSON_CONTAINS 的例子值得单独看一眼：建索引之前，MEMBER OF、JSON_CONTAINS、JSON_OVERLAPS 三种写法全是 type=ALL；建完索引之后，才变成 ref 或 range。

也就是说：索引建了，不等于会被用。 下面这些是真正会让你白干的细节。

坑 1：空数组在索引里"查无此人"

官方原话：

If a multi-valued key part has an empty array, no entries are added to the index.

skill_tags = '[]' 的行根本不会进索引。这不影响你的等值查询（本来也匹配不上），但意味着「用索引查空数组」永远查不到——包括想用 NOT 之类逻辑反查的场合。

三种「空」的处理还不一样：

情况	索引里的表现
列值是 SQL NULL	存一条 NULL 索引项
数组是 []	什么都不存
数组里有 JSON null 元素	插入时直接报 Invalid JSON value

坑 2：类型没逐字对齐 = 索引白建（重灾区）

多值索引能不能被命中，取决于探测值的类型能不能对齐到索引定义的元素类型。

-- 索引定义：元素是字符串  
INDEX idx_tag ((CAST(skill_tags AS CHAR(20) ARRAY)))  
  
-- 查询 A：探测值是 JSON 数字 23 —— 类型对不上，大概率用不上索引  
JSON_CONTAINS(skill_tags, CAST('23' AS JSON))  
  
-- 查询 B：探测值是字符串 —— 对得上  
JSON_CONTAINS(skill_tags, CAST('23' AS CHAR))  

JSON 的相等语义是严格区分类型的，数字 23 和字符串 "23" 并不相等。索引元素是 CHAR，你却拿 JSON number 去探，优化器就没法把条件下推到索引，只能老老实实回全表。

之前引子里面的坑就在这里，其实subject_list我们业务上的int 其实应该使用

INDEX idx_subject ((CAST(subject_list AS UNSIGNED ARRAY)))

来建设索引的

记住这条规则：索引用什么类型，查询就用什么类型探。

还有个小陷阱：CHAR(n) 的 n 是字符数上限，超长会截断（带 warning）。用 CHAR(20) 去索引一个可能超过 20 字符的标签，索引项和真实值就不一致了。字符串标签建议给足长度，比如 CHAR(64)。

坑 3：只有三个函数能被优化

WHERE 里只有这三种写法会触发多值索引：

23 MEMBER OF (skill_tags)                                  -- 最推荐，语义最直白  
JSON_CONTAINS(skill_tags, CAST('23' AS UNSIGNED))  
JSON_OVERLAPS(skill_tags, CAST('[23,37]' AS JSON))  

其余的统统不行，包括：

• JSON_EXTRACT / JSON_SEARCH / JSON_LENGTH
• 各种 IS NOT NULL、LIKE

也就是说 post_codes IS NOT NULL 这种条件，对多值索引没有任何加速作用，只能当回表后的过滤条件。

坑 4：不能排序、不能范围、不能当覆盖索引

因为同一行的索引记录被打散在索引里了，所以：

能力	普通索引	多值索引
ORDER BY 利用索引	支持	不支持（也不能加 ASC/DESC）
范围扫描 > < BETWEEN	支持	不支持
索引覆盖（index-only scan）	支持	不支持，必须回表
主键 / 外键 / 前缀索引	支持	全都不支持

顺带一个有意思的现象：可以建 UNIQUE 多值索引，但语义很怪——它约束的是「元素值全表唯一」，而不是「每行唯一」。想插一个已经被别人用过的标签值就会直接报重复键：

ERROR 1062 (23000): Duplicate entry '[94507, ' for key 'customers.zips'  

业务上几乎用不到，当冷知识看就好。

坑 5：建索引不能 online，只能 COPY

多值索引不支持 ALGORITHM=INPLACE，建索引时会走 ALGORITHM=COPY——也就是复制整张表。

大表上千万别在业务高峰期加。更狠的是元素数量还有上限：受单个 undo page 大小限制（65221 字节），超过会直接报错：

ERROR 3905 (HY000): Exceeded max number of values per record for multi-valued index 'idx' by 1 value(s).  

官方测试里，整数键大概能放 1600 多个。正常标签场景远远够，但如果你的数组是「用户行为序列」这种长度不设限的东西，这条路走不通。

坑 6：字符集只认两种组合

官方限制死得很窄，非 binary 的类型只允许：

• binary 字符集 + binary 排序规则
• utf8mb4 字符集 + 默认的 utf8mb4_0900_as_cs

也就是说，如果你的表是常见的 utf8mb4_general_ci，CAST(... AS CHAR(n) ARRAY) 很可能根本建不出来。踩到这个报错的时候不用怀疑人生，去查一下表的 collation 就行。

4. 那到底该不该用？

三种方案横向对比：

方案	做法	适合
生成列 + 普通索引	JSON_EXTRACT 到虚拟列再建索引	只需要索引单个标量字段
标签关系表	staff_tag(staff_id, tag_id) 一行一标签	推荐。能建普通复合索引、能 JOIN、能聚合统计、写入可控
多值索引	本文这种写法	读多写少、元素少（1~5 个）、不想改表结构的场景

我的经验是：

如果这个 JSON 数组是业务核心维度（会被反复筛选、统计、关联），老老实实拆关系表；
如果它只是个附属标签，查询频率不高、元素个数固定，多值索引是性价比很高的选择——但一定要把「类型对齐」这件事做对。

尾声

多值索引的思路其实很朴素：既然数组没法直接比较，那就先把它摊平成一组标量，再按标量去索引。

它解决的是「不想为了一个标签字段改表结构」这类痒点，代价是一堆限制和几个很容易踩的类型陷阱。

最后留一句我自己的总结：

索引不是建出来的，是被证明能用上的。EXPLAIN 里那个 key 列空着的时候，一切都只是自我感动。

最近AI盛行，我的工作也开始变成和想清楚业务逻辑，和AI进行沟通和阐明业务逻辑，最后单元测试也变成我的工作主流了。很奇怪。最近看大家写技术文章的也少了，看技术文章的也少了，现在文章基本都是如何高效，准确，团队的使用AI。连我自己基本不懂的就和AI沟通一下，学习效率都提高了不少，而且可以追问，这个太恐怖了。感觉以后孩子的学习路径可能会被AI完全重构。