MySQL 8.0.32中为JSON列创建索引适配JSON_OVERLAPS查询

方案说明 #

MySQL 8.0.17及以上支持多值索引（Multi-Valued Indexes），可针对JSON数组字段创建索引，优化JSON_OVERLAPS这类数组交集查询。由于直接给JSON列建索引受限于语法规则，需通过生成列配合多值索引实现。

具体操作步骤 #

1. 创建虚拟生成列 #

为groupsJSON列创建虚拟生成列（不占用物理存储空间，仅在查询时计算）：

ALTER TABLE conference ADD COLUMN group_values JSON GENERATED ALWAYS AS (`groups`) VIRTUAL;

该生成列直接复用原groups列的JSON数组内容，作为索引的基础字段。

2. 创建多值索引 #

针对生成列创建多值索引，匹配groups数组的元素类型（示例中为字符串型ID，若为数字可替换为UNSIGNED ARRAY）：

CREATE INDEX idx_conference_groups ON conference ((CAST(group_values AS CHAR(20) ARRAY)));

3. 执行查询（可选优化） #

原查询使用groups列即可命中索引，若需更明确指定，可直接用生成列：

SELECT id, duration, type, `from`, `to`, queue_name, created_at
FROM conference
WHERE duration >= 60
  AND JSON_OVERLAPS('["6","7","8"]', group_values);

验证索引有效性 #

执行EXPLAIN查看查询计划，此时type字段应显示为range或ref，而非ALL，说明索引已被正常使用。

备选方案（兼容MySQL 8.0.17以下版本） #

若版本不支持多值索引，可将JSON数组转为字符串并使用FIND_IN_SET（适合小表场景）：

1. 创建字符串类型生成列：

   ALTER TABLE conference ADD COLUMN groups_str VARCHAR(1000) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_ARRAY_TO_STRING(`groups`, ','))) VIRTUAL;
   

2. 修改查询语句：

   SELECT id, duration, type, `from`, `to`, queue_name, created_at
   FROM conference
   WHERE duration >= 60
     AND (FIND_IN_SET('6', groups_str) OR FIND_IN_SET('7', groups_str) OR FIND_IN_SET('8', groups_str));
   

内容的提问来源于stack exchange，提问作者neubert