AWS Aurora主表关联多表查询结果冗余优化方案咨询

我明白你的痛点：用普通LEFT JOIN会因为多张子表的记录交叉导致主表数据被重复相乘，而想用子表LIMIT又遇到了语法错误。针对AWS Aurora（不管是MySQL兼容版还是PostgreSQL兼容版），我有两个实用的解决方案，既能一次完成查询，又能控制每张子表的返回数量：

方案1：用窗口函数限制每个主表ID对应的子表记录数 #

这个方法适合你需要保留子表每条记录结构，但只取每个主表ID对应子表前100条的场景，同时避开IN子查询的LIMIT限制：

SELECT 
    mt.*,
    t1.id AS t1_id, t1.column2 AS t1_column2, -- 只选需要的字段，避免冗余
    t2.id AS t2_id, t2.column2 AS t2_column2,
    -- 其他8张表同理，按需选择字段
FROM main_table mt
LEFT OUTER JOIN (
    -- 给table1的每条记录按mainTableId分组编号，取前100条
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY mainTableId ORDER BY id) AS row_num
    FROM table1
) t1 ON t1.mainTableId = mt.id AND t1.row_num <= 100
LEFT OUTER JOIN (
    -- table2同理
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY mainTableId ORDER BY id) AS row_num
    FROM table2
) t2 ON t2.mainTableId = mt.id AND t2.row_num <= 100
-- 依次添加其他8张子表的子查询

关键说明： #

• ROW_NUMBER() OVER (PARTITION BY mainTableId ORDER BY id)：按主表ID分组，给每个组内的子表记录编号（这里按子表id排序，你可以换成业务需要的字段比如创建时间）
• 筛选row_num <=100确保每个主表ID对应的子表最多返回100条
• 建议给每个子表的mainTableId字段加索引，能大幅提升窗口函数的分组效率

方案2：用JSON聚合将子表结果转为数组（彻底避免数据冗余） #

如果不想因为多张子表连接导致结果行数膨胀（比如主表1条记录，table1和table2各100条，方案1会返回100*100=10000条），可以把每个子表的前100条记录聚合为JSON数组，这样主表每条记录只对应一行结果：

针对Aurora MySQL（5.7+）： #

SELECT 
    mt.*,
    -- 聚合table1的前100条为JSON数组
    (SELECT JSON_ARRAYAGG(t1) FROM (
        SELECT id, column2 FROM table1 WHERE mainTableId = mt.id LIMIT 100
    ) t1) AS table1_data,
    -- table2同理
    (SELECT JSON_ARRAYAGG(t2) FROM (
        SELECT id, column2 FROM table2 WHERE mainTableId = mt.id LIMIT 100
    ) t2) AS table2_data,
    -- 其他8张表依次添加
FROM main_table mt

针对Aurora PostgreSQL： #

把JSON_ARRAYAGG换成json_agg即可：

SELECT 
    mt.*,
    (SELECT json_agg(t1) FROM (
        SELECT id, column2 FROM table1 WHERE mainTableId = mt.id LIMIT 100
    ) t1) AS table1_data,
    (SELECT json_agg(t2) FROM (
        SELECT id, column2 FROM table2 WHERE mainTableId = mt.id LIMIT 100
    ) t2) AS table2_data,
    -- 其他表同理
FROM main_table mt

为什么这个方法可行？ #

之前你遇到的limit is not supported with a sub query错误是因为在IN子查询里用了LIMIT，但在标量子查询或聚合子查询里使用LIMIT是AWS Aurora允许的，这个方法刚好避开了语法限制，同时彻底解决了数据冗余问题。

额外优化建议 #

• 只查询需要的字段：不要用SELECT *，明确指定需要的字段，减少数据传输和内存占用
• 添加合适的索引：给所有子表的mainTableId字段创建索引，加速关联查询
• 排序字段选择：如果子表的排序不是按id，可以换成业务上需要的字段（比如create_time DESC），确保取到最新的100条记录

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