使用ORDER BY与RIGHT JOIN时触发排序内存不足，求替代解决方案

执行以下SQL时触发排序内存不足异常：

select
    *
from
    (
    select
        parent.*,
        child.key,
        child.value,
        totalGrossWeight as totalWeight
    from
        child
    right join parent on
        parent.code = child.code) as parent
where
    true
order by
    parent.changedAt desc,
    code asc
limit 100

异常信息：SQL Error [1038] [HY001]: Out of sort memory, consider increasing server sort buffer size
移除JOIN或ORDER BY操作则无此异常，不想增大服务器排序缓冲区大小，寻求替代解决方案。

可行优化方案 #

1. 提前缩小排序范围，再做关联 #

原SQL是先关联全表数据再排序，数据量过大导致内存不够。可以先从parent表取出排序后的前100条，再关联child表，这样排序的数据量从全表缩减到100条，内存压力骤降：

select
    p.*,
    c.key,
    c.value,
    p.totalGrossWeight as totalWeight
from
    (select * from parent order by changedAt desc, code asc limit 100) p
left join child c on
    p.code = c.code
order by
    p.changedAt desc,
    p.code asc

2. 添加联合索引，避免内存排序 #

给parent表的排序字段创建联合索引，让数据库直接通过索引有序读取数据，不需要在内存中对大量数据排序：

CREATE INDEX idx_parent_changedat_code ON parent(changedAt DESC, code ASC);

添加后，原SQL的排序操作会直接利用索引顺序，从根源上解决内存不足问题。

3. 避免SELECT *，只查必要字段 #

SELECT *会返回所有字段，增大单条数据体积，排序时占用更多内存。明确指定需要的字段，减少数据量：

select
    p.id,
    p.code,
    p.changedAt,
    p.totalGrossWeight as totalWeight,
    c.key,
    c.value
from
    child c
right join parent p on
    p.code = c.code
order by
    p.changedAt desc,
    p.code asc
limit 100

4. 拆分查询分步执行 #

先获取排序后的前100条parent的关键信息，再关联child表获取对应数据：

WITH top_parent AS (
    select id, code, changedAt, totalGrossWeight
    from parent
    order by changedAt desc, code asc
    limit 100
)
select
    tp.*,
    c.key,
    c.value,
    tp.totalGrossWeight as totalWeight
from top_parent tp
left join child c on tp.code = c.code
order by tp.changedAt desc, tp.code asc;

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