mysql 8.0.17+ 添加多值索引须显式指定 algorithm=inplace, lock=none，使用双括号语法 create index idx on t ((cast(col as type array)))，仅支持同构json数组且需类型一致、路径有效，生效时 explain 显示 type=range 或 ref。
ALTER TABLE ADD INDEX 会锁表，大表必须加 ALGORITHM=INPLACE
直接对大表执行 ALTER TABLE ... ADD INDEX 创建多值索引，默认可能触发表重建（COPY 算法），导致长时间写阻塞、磁盘暴涨、主从延迟飙升。MySQL 8.0.17+ 支持 ALGORITHM=INPLACE，它复用现有数据页，在线添加函数索引（多值索引本质是虚拟列上的函数索引），避免全量拷贝。
实操建议：
- 务必显式指定
ALGORITHM=INPLACE, LOCK=NONE，例如：ALTER TABLE users_with_tags ADD INDEX idx_tags ((CAST(tags AS UNSIGNED ARRAY))) ALGORITHM=INPLACE, LOCK=NONE;
- 若提示
LOCK=NONE不可用，说明存在不兼容操作（如含 FULLTEXT 索引、外键约束等），可降级为LOCK=SHARED（允许读，禁止写） - 执行前检查
innodb_online_alter_log_max_size是否足够（默认 128MB），大表建议调高，防止 DDL 中途失败
CREATE INDEX 比 ALTER TABLE 更安全，但语法易错：双括号不能少
CREATE INDEX 在语义上更聚焦“建索引”，不涉及表结构变更，对优化器行为干扰更小；但它要求多值索引表达式必须严格包裹两层括号——外层是索引列定义语法，内层是 CAST(... AS TYPE ARRAY) 表达式。漏掉任一层括号都会报错 ERROR 3146 (22032): Invalid data type for JSON data in argument 1 to function cast 或类似解析错误。
常见错误与修正：
- ❌ 错误写法：
CREATE INDEX idx_zips ON customers (CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY));（只有一层括号） - ✅ 正确写法：
CREATE INDEX idx_zips ON customers ((CAST(custinfo->'$.zipcode' AS UNSIGNED ARRAY)));（最外层额外一对括号） - 如果 JSON 路径含变量或嵌套数组，确保
->提取结果确实是 JSON 数组，否则CAST ... ARRAY会失败
多值索引只能用于 JSON 列中的同构数组，且类型必须显式声明
多值索引不是“给任意 JSON 字段加索引”，它只作用于 JSON 文档中某个路径下**类型一致的标量数组**（如 [1, 2, 5]、["a", "b"]），且建索引时必须用 CAST 显式声明目标 SQL 类型（UNSIGNED、CHAR(10)、DATE 等）。混合类型数组（如 [1, "abc"]）或非数组值（如 "94507" 单字符串）无法被正确解析为多值索引记录。
使用前提检查清单：
- 确认字段类型是
JSON，不是TEXT或VARCHAR - 确认数据中该路径（如
tags或custinfo->'$.zipcode'）实际存储的是 JSON 数组，可用JSON_TYPE(tags) = 'ARRAY'验证 - 确认数组内所有元素可无歧义转为目标类型（例如用
UNSIGNED就不能含负数或小数） - 避免对深层嵌套路径（如
data->'$.profile.addresses[*].zip'）建多值索引——MySQL 当前不支持通配符展开式路径
EXPLAIN 看不到 “Multi-Valued” 字样，但 type=range + key=xxx 就表示生效了
MySQL 的 EXPLAIN 输出里不会标注“这是多值索引”，它和普通函数索引一样显示为 type: range 或 type: ref，key 列显示索引名。真正验证是否命中，得看 rows 是否显著下降，以及是否避免了 Using where; Using filesort 等高成本 Extra。
典型有效查询模式：
- 单值匹配：
WHERE 5 MEMBER OF(tags)→ 走多值索引 - 子集包含：
WHERE JSON_CONTAINS(tags, '[1,5]')→ 走多值索引（注意：不是JSON_CONTAINS(tags, '1')，后者不走） - 不生效的写法：
WHERE tags @> '[1]'（PostgreSQL 风格）、WHERE tags LIKE '%5%'、WHERE JSON_EXTRACT(tags, '$[0]') = 5—— 这些都无法利用多值索引
真正容易被忽略的是：多值索引**不加速 JSON_ARRAY_LENGTH 或遍历类函数**，它只加速“按数组中某个具体值反查记录”的场景。如果你的业务大量需要统计标签数量或逐个取值，仍需配合生成列或应用层缓存。
