MySQL多对多关联预加载查询数据重复问题最优方案

问题背景 #

• 主表为attributes，通过三张中间表与其他表构成多对多关联：
  • 经中间表attribute_groups关联groups表
  • 经中间表attribute_labels关联locals表
  • 经中间表attribute_choices关联choices表
• 需求：查询全量attributes数据，同时预加载所有关联的groups、labels、choices数据，统计各关联的数量。

原有实现的问题 #

原有SQL直接同时左连接三个多对多关系，联表阶段会产生笛卡尔积：例如单条attribute关联3个groups、3个choices、2个labels时，联表后会生成332=18条重复记录，后续JSON_ARRAYAGG聚合时会将重复值全部纳入结果，无关联数据时还会返回多余null值，结果完全失真。
原有问题SQL如下：

select a.* ,
JSON_ARRAYAGG(JSON_OBJECT('id', g.id,'name', g.name)) as groups
    ,JSON_ARRAYAGG(JSON_OBJECT("id", al.local_id, "abbreviation",l.abbreviation,"name", al.label)) as locals
    ,JSON_ARRAYAGG(JSON_OBJECT('id', ac.id,"name", ac.name)) as choices,
    (select cast(count(*) as char) from attribute_groups ag where ag.attribute_id = a.id) groups_count,
    (select cast(count(*) as char) from attribute_labels al where al.attribute_id = a.id) labels_count,
    (select cast(count(*) as char) from attribute_choices ac where ac.attribute_id = a.id) choices_count
    FROM attributes a
    left join attribute_groups ag on a.id = ag.attribute_id
    left join groups g on g.id = ag.group_id
    left join attribute_labels al on a.id = al.attribute_id
    left join locals l on l.id = al.local_id
    left join attribute_choices ac on a.id = ac.attribute_id
    group by a.id
    order by a.id desc limit 10000

异常查询结果示例：

最优实现方案 #

核心思路：先对每个多对多关系单独完成聚合，再与主表关联，从根源避免跨关联的笛卡尔积产生，性能远高于聚合后加DISTINCT去重的方案，也不会出现null值污染结果的问题。
优化后SQL：

SELECT 
  a.*,
  COALESCE(g.groups, JSON_ARRAY()) AS groups,
  COALESCE(g.groups_count, 0) AS groups_count,
  COALESCE(l.locals, JSON_ARRAY()) AS locals,
  COALESCE(l.labels_count, 0) AS labels_count,
  COALESCE(c.choices, JSON_ARRAY()) AS choices,
  COALESCE(c.choices_count, 0) AS choices_count
FROM attributes a
-- 预聚合groups关联数据与计数
LEFT JOIN (
  SELECT 
    ag.attribute_id,
    JSON_ARRAYAGG(JSON_OBJECT('id', g.id, 'name', g.name)) AS groups,
    COUNT(*) AS groups_count
  FROM attribute_groups ag
  INNER JOIN groups g ON g.id = ag.group_id
  GROUP BY ag.attribute_id
) g ON g.attribute_id = a.id
-- 预聚合locals标签关联数据与计数
LEFT JOIN (
  SELECT 
    al.attribute_id,
    JSON_ARRAYAGG(JSON_OBJECT('id', al.local_id, 'abbreviation', l.abbreviation, 'name', al.label)) AS locals,
    COUNT(*) AS labels_count
  FROM attribute_labels al
  INNER JOIN locals l ON l.id = al.local_id
  GROUP BY al.attribute_id
) l ON l.attribute_id = a.id
-- 预聚合choices选项关联数据与计数
LEFT JOIN (
  SELECT 
    ac.attribute_id,
    JSON_ARRAYAGG(JSON_OBJECT('id', ac.id, 'name', ac.name)) AS choices,
    COUNT(*) AS choices_count
  FROM attribute_choices ac
  GROUP BY ac.attribute_id
) c ON c.attribute_id = a.id
ORDER BY a.id DESC
LIMIT 10000;

方案优势 #

• 每个关联的聚合逻辑在独立子查询内完成，子查询仅按单维度关联分组，完全不会产生跨关联的笛卡尔积，无需额外加DISTINCT去重，性能损耗极低
• 通过COALESCE处理无关联场景，空值会直接返回空数组[]和计数0，不会出现null值污染结果
• 关联计数直接在预聚合子查询中与关联数据同步统计，不需要额外写3个关联子查询，减少表扫描次数，大数据量下性能提升明显
• 逻辑拆分清晰，后续需要给某个关联增加筛选条件（例如仅查询启用状态的group）时，只需修改对应子查询即可，维护成本低

优化提示 #

• 若使用MySQL 8.0及以上版本，可在JSON_ARRAYAGG中增加ORDER BY规则，例如JSON_ARRAYAGG(JSON_OBJECT(...) ORDER BY g.sort ASC)，保证返回的关联数据顺序符合业务预期
• 若数据量较大，可给三张中间表的attribute_id字段建立索引，子查询的分组性能会有显著提升

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