子查询关联导致MySQL Aurora查询性能极差问题求助

问题描述 #

原查询语句 #

select count(*), 
    json_arrayagg(json_object('ID', o.ID, 'RECEIVERS', r.RECEIVERS, 'PRODUCTS', p.PRODUCTS))
from orders as o
inner join (
    select id,
        json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME)) RECEIVERS
    FROM receivers
    group by id
) r on o.id = r.id
  inner join (
    select id,
        JSON_ARRAYAGG(JSON_OBJECT('type', TYPE)) PRODUCTS
    FROM PRODUCTS
    group by id
) p on o.id = p.id
where o.ID = "1"

预期返回结构 #

[{
    "ID": "1",
    "ORDERN_NUM": "123",
    "RECEIVERS": [{
        "FIRST_NAME": "Pete",
        "LAST_NAME": "Tyler"
    },
    {
        "FIRST_NAME": "Sarah",
        "LAST_NAME": "Bowden"
    }],
    "PRODUCTS": [{
        "TYPE": "Towel"
    },
    {
        "TYPE": "Pen"
    }]
}]

当前问题 #

• 数据规模：ORDERS表200万条，RECEIVERS和PRODUCTS表各800万条
• 初期执行报1114 the table xyz is full错误，调整temptable_max_mmap为12GB、temptable_max_ram为6GB后可执行，但性能极差
• 查询单个ORDER主键（同时是另两表外键），在Aurora Serverless V2 MySQL RDS（4/16 min/max ACU）上耗时5分钟，ACU占用100%，内存占用达40GB

疑问 #

该查询存在哪些优化点？索引对此是否有帮助，或可能起反作用？

1. 核心优化：避免全表聚合子查询 #

原查询的两个子查询会对receivers和PRODUCTS表进行全表分组聚合——哪怕只查ORDER ID=1，子查询仍会遍历800万条数据生成所有ID的JSON数组，这是性能灾难的核心原因。
优化方式：将过滤条件提前，仅针对目标ORDER ID聚合：

select 
    json_object(
        'ID', o.ID, 
        'ORDERN_NUM', o.ORDERN_NUM,
        'RECEIVERS', (
            select json_arrayagg(json_object('firstName', FIRST_NAME, 'lastName', LAST_NAME))
            from receivers r where r.id = o.ID
        ),
        'PRODUCTS', (
            select json_arrayagg(json_object('type', TYPE))
            from PRODUCTS p where p.id = o.ID
        )
    )
from orders o
where o.ID = "1"

这种方式仅针对ID=1的订单关联查询对应数据，不会遍历全表，资源占用会大幅降低。

2. 索引的作用：绝对有帮助，无反作用 #

必须给receivers.id和PRODUCTS.id创建单列索引（若表主键为(id, ...)复合主键，主键索引可直接复用）：

CREATE INDEX idx_receivers_id ON receivers(id);
CREATE INDEX idx_products_id ON PRODUCTS(id);

索引会让子查询快速定位目标ID的所有行，彻底避免全表扫描，是高频按ID查询场景的刚需配置。

3. 移除无意义的count(*) #

原查询中的count(*)无实际价值——单订单ID查询仅返回1行，count值必然为1。直接移除该字段，减少不必要的计算开销。

4. 临时表配置的逻辑修正 #

之前调整temptable_max_mmap和temptable_max_ram是因为全表聚合产生超大临时表，但优化查询逻辑后，临时表需求会大幅降低，无需再调整这些参数。若后续存在全表操作，Aurora Serverless V2会自动将临时表转磁盘，但全表聚合本身不该出现在单条订单查询中。

5. Aurora Serverless V2资源配置建议 #

当前4/16的ACU范围下查询拉满资源，本质是查询逻辑导致的资源浪费。优化查询后，即使使用最小ACU也能快速执行，无需盲目提升ACU上限。

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