MySQL 8 结构不一致JSON字段查询性能优化方案咨询

前置结论 #

你设想的带通配符路径的生成列写法无法执行。MySQL要求生成列（虚拟/存储）的表达式必须是确定性的，$.bilan.victimes.*.preview.TAGSAU这类带通配符的JSON路径匹配结果不固定（可能匹配到多个值、不同行匹配到的路径也不固定），属于非确定性表达式，不符合生成列的创建要求。

你当前查询性能差的核心原因是：每次查询都要执行全表扫描+全JSON字段解析，没有可用索引加速匹配。针对你的场景可选择以下优化方案：

优化方案 #

方案1：枚举固定路径建生成列（适用数值键数量固定/可控场景） #

如果$.bilan.victimes下的数值键最多不会超过固定上限（比如最多到10），可以给每个可能的固定路径单独建生成列并加索引：

-- 创建生成列，可根据实际最大数值键数量扩展
ALTER TABLE bilan_json
ADD COLUMN tagsau_1 DATETIME GENERATED ALWAYS AS (STR_TO_DATE(JSON_VALUE(content, '$.bilan.victimes."1".preview.TAGSAU'),'%e/%c/%Y %H%@%i')) VIRTUAL,
ADD COLUMN tagsau_2 DATETIME GENERATED ALWAYS AS (STR_TO_DATE(JSON_VALUE(content, '$.bilan.victimes."2".preview.TAGSAU'),'%e/%c/%Y %H%@%i')) VIRTUAL,
ADD COLUMN dsa_1 VARCHAR(255) GENERATED ALWAYS AS (JSON_VALUE(content, '$.bilan.victimes."1".preview.DSA')) VIRTUAL,
ADD COLUMN dsa_2 VARCHAR(255) GENERATED ALWAYS AS (JSON_VALUE(content, '$.bilan.victimes."2".preview.DSA')) VIRTUAL;

-- 给需要过滤、分组的生成列建索引
CREATE INDEX idx_tagsau_1 ON bilan_json(tagsau_1);
CREATE INDEX idx_tagsau_2 ON bilan_json(tagsau_2);

查询时改写条件匹配所有可能的路径即可：

SELECT 
    COUNT(fiche_id) AS USAGE_DSA,
    COALESCE(dsa_1, dsa_2) AS DSA
FROM bilan_json
WHERE tagsau_1 >= '2021-01-01' OR tagsau_2 >= '2021-01-01'
GROUP BY DSA;

该方案性能最优，索引可直接命中，查询效率提升幅度可达几十倍。

方案2：使用JSON多值索引（适用数值键数量不固定场景，要求MySQL 8.0.17+） #

如果victimes下的数值键数量不确定，可使用MySQL支持的多值索引匹配通配符路径：

-- 创建多值索引存储所有TAGSAU的日期值
CREATE INDEX idx_tagsau_all ON bilan_json(
    (CAST(JSON_EXTRACT(content, '$.bilan.victimes.*.preview.TAGSAU') AS DATETIME ARRAY))
);

查询时配合JSON函数即可触发索引：

SELECT 
    COUNT(fiche_id) AS USAGE_DSA,
    JSON_VALUE(content, '$.bilan.victimes.*.preview.DSA') AS DSA
FROM bilan_json
WHERE '2021-01-01' <= ANY (
    SELECT CAST(j AS DATETIME) 
    FROM JSON_TABLE(content, '$.bilan.victimes.*.preview.TAGSAU' COLUMNS (j VARCHAR(50) PATH '$')) AS t
)
GROUP BY DSA;

该方案无需提前枚举路径，性能比全表扫描高3-10倍不等。

方案3：写入时预处理字段（适用查询频率远高于写入频率的场景） #

如果业务查询量极大，可在数据写入数据库前，就把需要用到的JSON字段提前提取为普通列，直接给普通列建索引。该方案性能是所有方案中最高的，后续查询完全不需要解析JSON，仅缺点是写入逻辑需要额外做字段提取处理。

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