MySQL多表层级关联下嵌套GROUP BY生成层级JSON的性能问题求解

你之前的写法性能差的核心原因是：内层子查询没有下推A表的过滤条件，MySQL会先对全量B、C表做关联分组生成所有B记录的JSON，再筛选出和目标AID匹配的记录，数据量大时耗时会非常高。

只需要将过滤条件下推到内层子查询，仅处理目标AID关联的B/C数据即可，性能可提升几个数量级，以下是两种可行实现：

方案1：显式下推过滤条件到子查询 #

SELECT JSON_OBJECT(
  'aid', a.id,
  'bs', IFNULL(JSON_ARRAYAGG(bjsons.bjson), JSON_ARRAY())
) AS a_json
FROM a
LEFT JOIN (
  SELECT 
    b.aid,
    JSON_OBJECT(
      'bid', b.id,
      'cs', IFNULL(JSON_ARRAYAGG(JSON_OBJECT('cid', c.id)), JSON_ARRAY())
    ) AS bjson
  FROM b
  LEFT JOIN c ON c.bid = b.id
  WHERE b.aid = 25 -- 下推AID过滤条件，仅处理目标A关联的B记录
  GROUP BY b.id
) AS bjsons ON bjsons.aid = a.id
WHERE a.id = 25
GROUP BY a.id

补充说明：加入IFNULL是为了处理没有关联B、C记录时返回空数组而非null的边界情况。

方案2：关联子查询写法（MySQL 8.0+ 适用） #

这种写法逻辑更直观，MySQL优化器会自动下推过滤条件，不需要手动重复写过滤规则：

SELECT JSON_OBJECT(
  'aid', a.id,
  'bs', (
    SELECT IFNULL(JSON_ARRAYAGG(
      JSON_OBJECT(
        'bid', b.id,
        'cs', (
          SELECT IFNULL(JSON_ARRAYAGG(JSON_OBJECT('cid', c.id)), JSON_ARRAY())
          FROM c
          WHERE c.bid = b.id
        )
      )
    ), JSON_ARRAY())
    FROM b
    WHERE b.aid = a.id
  )
) AS a_json
FROM a
WHERE a.id = 25

额外性能优化建议 #

给关联字段加索引可以进一步提升查询速度：

-- 加速B表按AID过滤的速度
CREATE INDEX idx_b_aid ON b(aid);
-- 加速C表按BID关联的速度
CREATE INDEX idx_c_bid ON c(bid);

如果需要批量生成多个A记录的JSON，仅修改外层a.id的过滤规则即可，内层子查询会自动匹配对应AID的B/C数据，不会全量扫描。

内容的提问来源于stack exchange，提问作者f.khantsis