MySQL多层JSON数组中master_no字段的索引创建问题

问题根源 #

你之前创建的索引无效，是因为change_json->'$[*].master_no'返回的是完整的JSON数组，直接转成字符串后，索引存储的是整个数组的文本形式（比如"[115,200]"）。而MEMBER OF是检查单个数值是否属于数组元素，这种索引结构无法匹配该查询逻辑，因此MySQL只能走全表扫描。

正确解决方案（分版本） #

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

MySQL 8.0.14及以上版本支持多值索引（Multi-Valued Indexes），专门针对JSON数组的存在性查询场景（如MEMBER OF、JSON_CONTAINS）。

1. 创建多值索引：

ALTER TABLE document_change ADD INDEX idx_master_no ((CAST(change_json->'$[*].master_no' AS UNSIGNED ARRAY)));

注意：因为你的查询值是整型（115），所以将数组元素转为UNSIGNED类型，比转字符串更高效，也更匹配查询逻辑。

2. 原查询语句无需修改，执行计划会自动使用该索引：

SELECT * FROM document_change WHERE 115 MEMBER OF (change_json->'$[*].master_no');

方案2：MySQL 8.0.14以下版本—— 拆分关联表 #

低版本不支持多值索引，需将JSON数组中的元素拆分到单独的关联表中，通过传统索引实现高效查询：

1. 创建关联表：

CREATE TABLE document_change_master_no (
    id INT AUTO_INCREMENT PRIMARY KEY,
    document_change_id INT NOT NULL,
    master_no INT NOT NULL,
    FOREIGN KEY (document_change_id) REFERENCES document_change(id),
    INDEX idx_master_no (master_no)
);

2. 导入现有数据：

INSERT INTO document_change_master_no (document_change_id, master_no)
SELECT dc.id, j.master_no
FROM document_change dc
JOIN JSON_TABLE(
    dc.change_json->'$[*].master_no',
    '$[*]' COLUMNS (master_no INT PATH '$')
) j;

3. 维护数据一致性：
   后续新增/修改document_change表的change_json字段时，需同步更新document_change_master_no表（可通过触发器实现）。

4. 修改查询语句：

SELECT dc.*
FROM document_change dc
JOIN document_change_master_no dcmn ON dc.id = dcmn.document_change_id
WHERE dcmn.master_no = 115;

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