MySQL一对多表JOIN笛卡尔积冗余问题及查询优化咨询

你当前查询返回64条冗余数据，根源是把ad主表下的3个独立一对多关联（对应ad_city、ad_entry、ad_object三张子表）直接放在同个JOIN链路里，产生了444=64的笛卡尔积，这是行式返回数据库的固有特性，不是SQL写法错误。

可以，MySQL 5.7及以上版本通过原生JSON聚合函数就能实现，完全不需要在服务端循环处理冗余行。
核心思路是：

• 主表的单值关联（用户、基础城市、基础entry、基础object）直接正常LEFT JOIN，这部分不会产生行数膨胀
• 所有一对多的列表数据，通过独立的关联子查询+JSON_ARRAYAGG直接在数据库端聚合成JSON数组，避免不同一对多关系之间产生笛卡尔积
  参考写法：

SELECT
  a.id,
  a.date,
  a.text,
  -- 单值嵌套对象直接用JSON_OBJECT构造
  JSON_OBJECT(
    'id', u.id,
    'name', u.name,
    'surname', u.surname
  ) AS user,
  c.name AS city,
  JSON_OBJECT(
    'id', te.id,
    'type', tet.name,
    'region', ter.name
  ) AS entry,
  JSON_OBJECT(
    'id', to.id,
    'type', tot.name,
    'region', tor.name
  ) AS object,
  -- 三个一对多列表分别用独立子查询聚合
  (
    SELECT IFNULL(
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', ac.id,
          'cityId', main_c.id,
          'cityName', main_c.name
        )
      ),
      JSON_ARRAY()
    )
    FROM ad_city ac
    LEFT JOIN main_city main_c ON ac.main_city_id = main_c.id
    WHERE ac.ad_id = a.id
  ) AS arrCity,
  (
    SELECT IFNULL(
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', ae.id,
          'entryId', entry_obj.id,
          'entryType', entry_type.name,
          'entryRegion', entry_region.name
        )
      ),
      JSON_ARRAY()
    )
    FROM ad_entry ae
    LEFT JOIN some_object entry_obj ON ae.some_object_id = entry_obj.id
    LEFT JOIN some_type entry_type ON entry_obj.some_type_id = entry_type.id
    LEFT JOIN some_region entry_region ON entry_obj.some_region_id = entry_region.id
    WHERE ae.ad_id = a.id
  ) AS arrEntry,
  (
    SELECT IFNULL(
      JSON_ARRAYAGG(
        JSON_OBJECT(
          'id', ao.id,
          'objectId', obj.id,
          'objectType', obj_type.name,
          'objectRegion', obj_region.name
        )
      ),
      JSON_ARRAY()
    )
    FROM ad_object ao
    LEFT JOIN some_object obj ON ao.some_object_id = obj.id
    LEFT JOIN some_type obj_type ON obj.some_type_id = obj_type.id
    LEFT JOIN some_region obj_region ON obj.some_region_id = obj_region.id
    WHERE ao.ad_id = a.id
  ) AS arrObject
FROM ad a
LEFT JOIN user u ON a.user_id = u.id
LEFT JOIN main_city c ON a.main_city_id = c.id
LEFT JOIN some_object te ON a.some_entry_id = te.id
LEFT JOIN some_type tet ON te.some_type_id = tet.id
LEFT JOIN some_region ter ON te.some_region_id = ter.id
LEFT JOIN some_object to ON a.some_object_id = to.id
LEFT JOIN some_type tot ON to.some_type_id = tot.id
LEFT JOIN some_region tor ON to.some_region_id = tor.id
WHERE a.id = 1 AND a.user_id = 2

这个写法最终只会返回1行记录，所有数组字段都是数据库端生成好的合法JSON，Node.js服务端拿到后只需要把JSON类型字段做一次简单parse，就能直接拼成你预期的结构，没有任何冗余传输，也不需要写复杂的循环合并逻辑。

注意点：

• 只要在ad_city.ad_id、ad_entry.ad_id、ad_object.ad_id三个字段上建普通索引，每个子查询的执行效率都会非常高
• 用IFNULL把空结果转成空数组，避免服务端处理null值的额外逻辑
• MySQL 8.0版本对JSON聚合的性能做了优化，支持更灵活的聚合写法

分场景判断：

• 通用生产场景下，只要MySQL版本支持JSON聚合，优先选单条JSON聚合的写法：单条请求只需要一次网络往返，比拆4次请求的网络开销低很多，整体查询耗时和拆4次几乎没有差异，服务端处理逻辑也更简单。
• 以下情况再考虑拆请求：
  • MySQL版本低于5.7，不支持原生JSON函数，没法在数据库端完成聚合
  • 每个一对多关联表的单广告关联数据量极大（比如单条广告关联上千个城市/条目），子查询聚合会占用较多数据库临时内存
  • 单条SQL的整体执行时间过长，会占满连接池资源影响其他请求

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