MySQL 8.0.37中如何为JSON数组创建可用索引？

问题原因 #

你创建的索引是提取metaData.attributes数组中所有key值组成的数组，而查询逻辑是匹配完整的{"key": "Server", "value": "1"}对象，两者索引逻辑不匹配，因此MySQL无法利用该索引。另外你的索引创建语句存在表名错误（ALTER TABLE idx1应为ALTER TABLE table1）。

解决方案 #

方案1：多值索引（MySQL 8.0.14+ 推荐） #

MySQL 8.0.14及以上版本支持多值索引，可为JSON数组中的每个元素单独创建索引项，完美适配JSON_CONTAINS针对数组元素的查询需求。

创建索引的正确语句：

ALTER TABLE table1
ADD INDEX idx_meta_attributes ((CAST(data->'$.metaData.attributes' AS JSON ARRAY))) USING BTREE;

创建完成后执行原查询，通过EXPLAIN可看到执行计划中type会变为range，key列显示idx_meta_attributes，说明索引已被使用。

方案2：特定查询的函数索引 #

如果你的查询仅针对固定的key-value组合（比如仅需检查Server=1），可直接基于查询条件创建函数索引：

ALTER TABLE table1
ADD INDEX idx_server_1 ((JSON_CONTAINS(data->'$.metaData.attributes', '{"key": "Server", "value": "1"}')));

该方案仅能加速指定查询，灵活性有限，但实现简单。

方案3：重构表结构（长期最优方案） #

若attributes的结构相对固定，建议将JSON数组拆分为关联表，例如创建table1_attributes表：

CREATE TABLE table1_attributes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table1_id INT,
    `key` VARCHAR(255),
    `value` VARCHAR(255),
    FOREIGN KEY (table1_id) REFERENCES table1(id) -- 需先给table1添加主键id
);

然后将原JSON中的attributes数据同步到该表，并为key和value字段创建联合索引：

CREATE INDEX idx_key_value ON table1_attributes (`key`, `value`);

这种方式查询性能更稳定，维护性更强，适合数据量较大的场景。

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